MySQL: χρειάζομαι ένα ισοσύναμο, πιο αποδοτικό query

[philos]

Λοιπόν, έχουμε αυτόν τον πίνακα MySQL:

CREATE TABLE IF NOT EXISTS `sc_expirations` (
`dataid` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`node_id` int(10) NOT NULL DEFAULT 0,
`stickyid` int(10) NOT NULL DEFAULT 0,
`user_id` int(10) NOT NULL DEFAULT 0,
`thread_id` int(10) NOT NULL DEFAULT 0,
`dateline` int(11) NOT NULL DEFAULT 0,
`notification_sent` int(1) NOT NULL DEFAULT 0) 

Και τρέχω συγκεκριμένα, αυτό το query:

SELECT t1.*, xf_node.node_id, xf_node.title
FROM sc_expirations AS t1
LEFT JOIN xf_node USING (node_id)
WHERE FROM_UNIXTIME(`dateline`) < DATE_SUB(NOW(), INTERVAL 5 DAY)
		AND notification_sent = 0
		AND NOT EXISTS 
			(SELECT * FROM sc_expirations AS higher 
			 WHERE higher.node_id = t1.node_id and higher.dateline > t1.dateline)
ORDER BY dateline DESC

Δυστυχώς με 31k εγγραφές, αυτό το query θέλει 2 (!) λεπτά να ολοκληρωθεί.

Έχετε καμία ιδέα ώστε να πάρω τα ίδια στοιχεία με πιο αποδοτικό τρόπο;

Υποθέτω ότι το πρόβλημα είναι στο subquery.

[Papakaliati]

2 ώρες πριν, philos είπε

Λοιπόν, έχουμε αυτόν τον πίνακα MySQL:

CREATE TABLE IF NOT EXISTS `sc_expirations` (
`dataid` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`node_id` int(10) NOT NULL DEFAULT 0,
`stickyid` int(10) NOT NULL DEFAULT 0,
`user_id` int(10) NOT NULL DEFAULT 0,
`thread_id` int(10) NOT NULL DEFAULT 0,
`dateline` int(11) NOT NULL DEFAULT 0,
`notification_sent` int(1) NOT NULL DEFAULT 0) 

Και τρέχω συγκεκριμένα, αυτό το query:

SELECT t1.*, xf_node.node_id, xf_node.title
FROM sc_expirations AS t1
LEFT JOIN xf_node USING (node_id)
WHERE FROM_UNIXTIME(`dateline`) < DATE_SUB(NOW(), INTERVAL 5 DAY)
		AND notification_sent = 0
		AND NOT EXISTS 
			(SELECT * FROM sc_expirations AS higher 
			 WHERE higher.node_id = t1.node_id and higher.dateline > t1.dateline)
ORDER BY dateline DESC

Δυστυχώς με 31k εγγραφές, αυτό το query θέλει 2 (!) λεπτά να ολοκληρωθεί.

Έχετε καμία ιδέα ώστε να πάρω τα ίδια στοιχεία με πιο αποδοτικό τρόπο;

Υποθέτω ότι το πρόβλημα είναι στο subquery.

Πόση ώρα κάνει άμα αφαιρέσεις τα subquery; Δοκιμασε πρωτα χωρις το 2ο εσωτερικο, μετα χωρις το πρωτο εσωτερικο, δες τι προκαλει το bottleneck, και προσπαθησε να το κανεις optimize.  

[masteripper]

Εικάζοντας γιατί δεν βλέπω τα δεδομένα πιστεύω ότι το query πρέπει να "πονάει" εδώ 

FROM_UNIXTIME(`dateline`) < DATE_SUB(NOW(), INTERVAL 5 DAY)

καθώς για κάθε εγγραφή εξαναγκάζεις το σύστημα να ξαναυπολογίσει την ημερομηνία.

Κάνε τον υπολογισμό πριν σε μια έξτρα μεταβλητή και κάνε με αυτήν την σύγκριση .

Επίσης αυτό το EXISTS δεν μπορείς κάπως να το περιορίσεις ?

[dhmm]

Τι μέγεθος έχει ο πίνακας xf_node ;

Που τρέχει το query ;

Αν και υποθέτω ότι θα έχεις , έχεις FK ; ( δεν φαίνεται στο CREATE )

 

Πώσα rows επιστρέφει το query σου ;

Δοκίμασε λιγάκι να βάλεις στο τέλος LIMIT 1, 1000 και πες το χρόνο απόκρισης...

[papmel]

Αναφορά σε κείμενο

SELECT * FROM sc_expirations AS higher WHERE higher.node_id = t1.node_id and higher.dateline > t1.dateline

εδω δεν χρειαζετε να ψαχνεις για Not exists σε ολα τα records.
προσθεσε το  LIMIT 1 στο τελος

SELECT * FROM sc_expirations AS higher WHERE higher.node_id = t1.node_id and higher.dateline > t1.dateline limit 1

[philos]

4 ώρες πριν, papmel είπε

εδω δεν χρειαζετε να ψαχνεις για Not exists σε ολα τα records.
προσθεσε το  LIMIT 1 στο τελος

SELECT * FROM sc_expirations AS higher WHERE higher.node_id = t1.node_id and higher.dateline > t1.dateline limit 1

Είσαι σίγουρος γι' αυτό;

Αν θυμάμαι καλά το πρόβλημα (καθώς πάει καιρός που το έφτιαξα), το NOT EXISTS εξαιρεί όλα τα (πολλά) records που είναι πέραν από την τάδε ημερομηνία.

Αν βάλω LIMIT 1 λογικά δεν εξαιρεί μόνο 1 record;

Χίλια συγγνώμη που δε θυμάμαι 100% το πρόβλημα, απλά το query εν τέλει βγάζει σωστά αποτελέσματα.

Αν πράγματι με το να βάλω LIMIT 1 στο subquery, τα αποτελέσματα είναι ίδια (βάσει της λογικής των subqueries), τότε να το δοκιμάσω (το query τρέχει πλέον σε production site που δεν έχω πρόσβαση και μου έγινε η αναφορά ότι το query είναι αργό).

Σε ευχαριστώ!

Επεξ/σία 15 Δεκεμβρίου 2020 από philos

[philos]

Στις 15/12/2020 στις 8:51 ΠΜ, masteripper είπε

Εικάζοντας γιατί δεν βλέπω τα δεδομένα πιστεύω ότι το query πρέπει να "πονάει" εδώ 

FROM_UNIXTIME(`dateline`) < DATE_SUB(NOW(), INTERVAL 5 DAY)

καθώς για κάθε εγγραφή εξαναγκάζεις το σύστημα να ξαναυπολογίσει την ημερομηνία.

Κάνε τον υπολογισμό πριν σε μια έξτρα μεταβλητή και κάνε με αυτήν την σύγκριση .

Επίσης αυτό το EXISTS δεν μπορείς κάπως να το περιορίσεις ?

Έχω ξανά χρησιμοποιήσει τη συγκεκριμένη WHERE ημερομηνιών, σε πίνακα με περισσότερα records χωρίς πρόβλημα! Μάλλον το πρόβλημα είναι στο subquery.

Στις 15/12/2020 στις 11:32 ΠΜ, dhmm είπε

Τι μέγεθος έχει ο πίνακας xf_node ;

Που τρέχει το query ;

Αν και υποθέτω ότι θα έχεις , έχεις FK ; ( δεν φαίνεται στο CREATE )

 

Πώσα rows επιστρέφει το query σου ;

Δοκίμασε λιγάκι να βάλεις στο τέλος LIMIT 1, 1000 και πες το χρόνο απόκρισης...

Ο xf_node δε χρειάζεται να μας απασχολεί, έχει περίπου 1500 - 2000 records.

[dhmm]

Προσπάθησα να προσομοιώσω και όσον έβαζα data σε αυτό τον πίνακα xf_node αυξάνοντας το time. Ενώ δεν είχα καθυστέρηση καθόλου με 60κ. Όσον έκαμνα insert στο xf_node το επόμενο run αργούσε περισσότερο.

Το LIMIT φαίρνει την πρώτη γραμμή απο το αποτέλεσμα. Αν κάνεις για test ένα desc θα δείς ότι θα σου φέρει το τελευταίο

Επεξ/σία 16 Δεκεμβρίου 2020 από dhmm

[philos]

11 λεπτά πριν, dhmm είπε

Προσπάθησα να προσομοιώσω και όσον έβαζα data σε αυτό τον πίνακα xf_node αυξάνοντας το time. Ενώ δεν είχα καθυστέρηση καθόλου με 60κ. Όσον έκαμνα insert στο xf_node το επόμενο run αργούσε περισσότερο.

Το LIMIT φαίρνει την πρώτη γραμμή απο το αποτέλεσμα. Αν κάνεις για test ένα desc θα δείς ότι θα σου φέρει το τελευταίο

Ναι, ο πίνακας xf_node έχει πολύ λίγες εγγραφές (1500 - 2000 όπως είπα), συνεπώς δε νομίζω να είναι το πρόβλημα.

Το πρόβλημα πρέπει να είναι στο subquery, απλά δεν είμαι σίγουρος ότι με το να βάλω LIMIT 1 στο subquery θα έχω τα ίδια αποτελέσματα. Το LIMIT 1 θα φέρει μια γραμμή, ενώ χωρίς το LIMIT εξαιρεί πολλές εγγραφές. Είναι πράγματι ισοδύναμο;

[dhmm]

1 ώρα πριν, philos είπε

Ναι, ο πίνακας xf_node έχει πολύ λίγες εγγραφές (1500 - 2000 όπως είπα), συνεπώς δε νομίζω να είναι το πρόβλημα.

Το πρόβλημα πρέπει να είναι στο subquery, απλά δεν είμαι σίγουρος ότι με το να βάλω LIMIT 1 στο subquery θα έχω τα ίδια αποτελέσματα. Το LIMIT 1 θα φέρει μια γραμμή, ενώ χωρίς το LIMIT εξαιρεί πολλές εγγραφές. Είναι πράγματι ισοδύναμο;

Όχι δεν είναι εννοείται... Διότι όπως είπα θα φέρει μόνο την ρώτη γραμμή. Αν χρειάζεσαι και τις άλλες δεν θα βργεί και σωστό αποτέλεσμα.

[philos]

Στις 16/12/2020 στις 5:53 ΜΜ, dhmm είπε

Όχι δεν είναι εννοείται... Διότι όπως είπα θα φέρει μόνο την ρώτη γραμμή. Αν χρειάζεσαι και τις άλλες δεν θα βργεί και σωστό αποτέλεσμα.

Θα σας εξηγήσω τι ακριβώς κάνει το query. Στην ουσία λειτουργεί σωστά, ωστόσο με πολλές εγγραφές αποτυγχάνει. Κάνω ήδη προσπάθειες να σκεφτώ μια εναλλακτική λύση, αλλά κάθε βοήθεια ευπρόσδεκτη

Αρχικό query κι ο πίνακας:

CREATE TABLE IF NOT EXISTS `sc_expirations` (
`dataid` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`node_id` int(10) NOT NULL DEFAULT 0,
`stickyid` int(10) NOT NULL DEFAULT 0,
`user_id` int(10) NOT NULL DEFAULT 0,
`thread_id` int(10) NOT NULL DEFAULT 0,
`dateline` int(11) NOT NULL DEFAULT 0,
`notification_sent` int(1) NOT NULL DEFAULT 0) 

SELECT t1.*, xf_node.node_id, xf_node.title
FROM sc_expirations AS t1
LEFT JOIN xf_node USING (node_id)
WHERE FROM_UNIXTIME(`dateline`) < DATE_SUB(NOW(), INTERVAL 5 DAY)
		AND notification_sent = 0
		AND NOT EXISTS 
			(SELECT * FROM sc_expirations AS higher 
			 WHERE higher.node_id = t1.node_id and higher.dateline > t1.dateline)
ORDER BY dateline DESC

Ο πίνακας sc_expirations περιλαμβάνει τα δεδομένα αγορασμένων συνδρομών που ας το πούμε έληξαν. Δηλαδή κάθε φορά που λήγει μια συνδρομή, μια νέα γραμμή δημιουργείται στον πίνακα. Θέλουμε οι διαχειριστές της σελίδας να λάβουν μια ειδοποίηση αν δεν υπάρχουν νέες αγορές στη κατηγορία (node_id) μέσα σε 5 μέρες.

(dateline η unix time λήξης του προιόντος (stickyid), notification_sent bool για το αν στάλθηκε ειδοποίηση)

Μόλις γίνει το SELECT και εντοπιστούν στοιχεία, μπαίνει μια δομή επανάληψης για να σταλεί η ειδοποίηση και το αντίστοιχο notification_sent γίνεται από 0 σε 1 ώστε να μην ξανά ληφθεί υπόψιν στις μετέπειτα SELECT.

Τώρα που το σκέφτομαι, δε βρίσκω το νόημα του subquery, ωστόσο όταν το είχα τεστάρει έκανε ακριβώς αυτό που πρέπει. χμμμμ

 

Επεξ/σία 18 Δεκεμβρίου 2020 από philos

[Επισκέπτης]

Καλησπέρα. Πρώτων ούτε είχα την γνώση ότι υπάρχουν και τέτοια sql queries.

Γιατί δεν κάνεις το εξής (σε PHP)

$after5 = mktime(0, 0, 0, date('m'), date('d') + 5, date('Y'));
$sql = "SELECT t1.*, xf_node.node_id, xf_node.title
FROM sc_expirations AS t1
LEFT JOIN xf_node USING (node_id)
WHERE `dateline` < $after5
		AND notification_sent = 0
		AND NOT EXISTS 
			(SELECT * FROM sc_expirations AS higher 
			 WHERE higher.node_id = t1.node_id and higher.dateline > t1.dateline)
ORDER BY dateline DESC";

 

Επεξ/σία 26 Δεκεμβρίου 2020 από Επισκέπτης

[tapandagr]

@philos θέλεις να μας περιγράψεις τι ακριβώς θέλεις να πετύχεις (χωρίς να αναφέρεις σχέσεις της βάσης); Μπορεί να σκεφτούμε κι άλλες ιδέες/συνδεσμολογίες.

[tsofras]

Μπορείς να δοκιμάσεις κάποιο εργαλείο που ένα σου κάνει explain το query? Παίζει να σου κάνει table scan το sub query , ο πίνακας έχει index?

[hCyborg]

Θα μπορούσες να βάλεις στο sc_expirations table να βάλεις index στο dateline, ως μια γρήγορη και lazy λύση, σίγουρα θα βελτιώσει την ταχύτητα των εργασιών στον πίνακα.