Βελτιστοποίηση ταχύτητας MySQL ερωτήματος

[philos]

Καλησπέρα!

Λοιπόν, χουμε το ακόλουθο query:

                        SELECT paymentslog.*, user.username, purchase_temp.threadid
                        FROM " . TABLE_PREFIX . "paymentslog AS paymentslog
                        LEFT JOIN " . TABLE_PREFIX . "user AS user USING (userid)    
                        LEFT JOIN " . TABLE_PREFIX . "paymenttransaction AS paymenttransaction ON (paymenttransaction.transactionid = paymentslog.transactionid)
                        LEFT JOIN " . TABLE_PREFIX . "paymentinfo AS paymentinfo ON (paymentinfo.paymentinfoid = paymenttransaction.paymentinfoid)
                        LEFT JOIN " . TABLE_PREFIX . "purchase_temp AS purchase_temp ON (purchase_temp.hash = paymentinfo.hash)
                        GROUP BY paymentslog.logid
                        ORDER BY paymentslog.dateline DESC
                        LIMIT $startat, $perpage

                        
Πρόκειται για μια php σελίδα που εμφανίζονται τα payment logs και όλα αυτά τα JOINs γίνονται για να τραβήξω το threadid στη SELECT.
Παρόλα αυτά σε μια σελίδα που έχει δοκιμαστεί, με αρκετά δεδομένα στους πίνακες, θέλει 18 seconds η σελίδα για να φορτώσει (LIMIT 100).

Για κάποιο λόγο επίσης μου έβγαζε 10 δες φορές κάποιο συγκεκριμένο record, οπότε αναγκάστηκα να κάνω GROUP BY paymentslog.logid.

Έχετε καμιά ιδέα βελτιστοποίησης με το ίδιο SELECT αποτέλεσμα; Ή μήπως το να γράφω στον paymentslog, και το threadid σε νέο column, με κάποιον τρόπο είναι μονόδρομος;

[tsofras]

Το πρώτο που παρατηρώ είναι τα JOIN ... ON που κάνεις

 

Πρέπει να είναι 

ΛEFT JOIN " . TABLE_PREFIX . "paymenttransaction AS paymenttransaction ON (paymentslog.transactionid = paymenttransaction.transactionid)
LEFT JOIN " . TABLE_PREFIX . "paymentinfo AS paymentinfo ON (paymenttransaction.paymentinfoid = paymentinfo..paymentinfoid)
LEFT JOIN " . TABLE_PREFIX . "purchase_temp AS purchase_temp ON (paymentinfo.hash = purchase_temp.hash)

Με το τρόπο που το έχεις γράψει εσύ (ανάποδα δλδ) η MySql θα κάνει full table scan πριν εκτελέσει τα join

 

http://dev.mysql.com/doc/refman/5.7/en/left-join-optimization.html

The join optimizer calculates the order in which tables should be joined. The table read order forced by LEFT JOIN or STRAIGHT_JOIN helps the join optimizer do its work much more quickly, because there are fewer table permutations to check. Note that this means that if you do a query of the following type, MySQL does a full scan on b because the LEFT JOIN forces it to be read before d:

SELECT *
  FROM a JOIN b LEFT JOIN c ON (c.key=a.key)
  LEFT JOIN d ON (d.key=a.key)
  WHERE b.key=d.key;

The fix in this case is reverse the order in which a and b are listed in the FROM clause:
(Εγώ δεν σου άλλαξα το FROM απλά το JOIN ON στα κλειδιά)

SELECT *
  FROM b JOIN a LEFT JOIN c ON (c.key=a.key)
  LEFT JOIN d ON (d.key=a.key)
  WHERE b.key=d.key;

Indexes έχεις? Αν όχι φτιάξε σε αυτά που σε απασχολούν και ξανατρέξε

 

Τα πολλά αποτελέσματα μάλλον τα έχεις απο το left join  στους users , δεν έχεις κάποιο id για εκεί?

Επίσης κανένα where? θέλεις όλες τις εγγραφές?

 

Τέλος είσαι σίγουρος ότι θέλεις left join ή μήπως inner (που είναι και ποιο γρήγορο)

[philos]

Ωχ, σοβαρά γίνεται αυτό με τα ανάποδα ON;    Είχα μια εντύπωση ότι δεν έχει καμία σημασία η σειρά που τα βάζεις όταν τα συνδέεις!

 

Θα το δοκιμάσω και θα σου πω.

 

Όσο για τα indexes, οι περισσότεροι πίνακες είναι οι default της μηχανής vBulletin και δε θέλω να τους πειράξω.

 

Τι εννοείς αν έχω ή όχι κάποιο id για το θέμα των users;

Το join με τον πίνακα user τον κάνω γιατί θέλω να κάνω SELECT το username για κάθε payments_log.

 

Να σημειωθεί το σημαντικό, ότι δεν έχουν όλα τα επιστρεφόμενα αποτελέσματα (payment log items), threadid.

Δηλαδή θέλω να εμφανίζω όλα τα logid items, και αν κάποιο έχει αντιστοιχία με threadid, τότε να το τραβάει... το query λειτουργεί εντάξει σε αυτό το θέμα.

 

Τέλος σχετικά με τη WHERE, την ορίζω μέσω PHP μεταβλητής κειμένου, για να εμφανίζω τα αποτελέσμα βάσει φίλτρων.

Σε defaut κατάσταση, είναι "WHERE 1 = 1", και αν έχω εφαρμόσει φίλτρα, γίνεται "WHERE 1 = 1 AND.......... ). Ελπίζω να μην παίζει κάποιο ρόλο αυτό...

[tsofras]

οκ εκεί που έχεις το payments_log έχεις user_id ?

Βασικά κάνε την πρώτη αλλαγή , μάλλον θα δείς τρελλή διαφορά

[philos]

Δυστυχώς δεν υπήρξε βελτίωση με τα ανεστραμμένα ON...

 

Ναι, ο πίνακας paymentslog έχει userid column (όχι όμως username column). Επειδή λοιπόν θέλω να αντλήσω και το username από τον πίνακα user, κάνω το join USING (userid).

Να πω ότι userid column έχουν και άλλοι πίνακες στο query, εμένα με ενδιαφέρει όμως κάθε paymentslog row στα αποτελέσματα να έχει και το username.

 

Δε μπορώ να καταλάβω γιατί θέλει 20 seconds το συγκεκριμένο query, με LIMIT 50 (X,Y)

Έχω την εντύπωση ότι έχω δει και γράψει πολύ πιο βαριά queries τα οποία θέλανε στη χειρότερη μερικά seconds για να τρέξουν...

 

Αν χρειάζεστε κάποιο debug screenshot από κάπου, πείτε μου τι να κάνω για να το δημοσιεύσω. 

[Predatorkill]

Δεν θα αλλαξει κατι αμα δεν βαλεις κι αλλα index, οτι και να κανεις. Κανε clone ολο το site σε subfolder στον σερβερ φτιαξε μια κοπια της βασης και παιξε εκει με τα index. EXPLAIN δοκιμασες να κανεις;

 

Υγ. Λιγο offtopic: Ριξε και μια ματια σε percona sql, ειναι η optimized εκδοχη της mysql και δεν ειναι δυσκολο να την εγκαταστησεις.

[tsofras]

Δηλαδή θα μπορούσες να το αλλαξεις από

LEFT JOIN " . TABLE_PREFIX . "user AS user USING (userid)    

σε

LEFT JOIN " . TABLE_PREFIX . "user AS user ON (paymentslog.userid = user.userid)    

[philos]

Οπότε παιδιά, να προσθέσω index στους ακόλουθους πίνακες που είναι δικοί μου;

paymentslog --> index στο transactionid

purchase_temp --> index στο hash

 

Σωστά;

 

Από τους άλλους να πω:

Ο paymenttransaction έχει ήδη ευρετήριο στο transactionid

Ο paymentinfo έχει πρωτεύουν και AUTO INCREMENT το paymentinfoid και ευρετήριο στο hash.

 

Θα αλλάξω και το ON σε (paymentslog.userid = user.userid) μήπως δούμε διαφορά!

[tsofras]

Ακριβώς index σε όλα τα joins

[antonisid]

Τέτοια πολύπλοκα queries δύσκολα να βελτιωθούν δραστικά στην απόδοσή τους. Αν η βάση δεδομένων δε χρησιμοποιεί κάποιο ORM model (πχ Eloquent) , τότε το καλύτερο είναι να κάνεις cache (με memcache πιθανώς) τα αποτελέσματα που θέλεις και με κάθε νέο payment να ξαναγράφεις την cache ώστε να έχεις τα τελευταία δεδομένα.

[philos]

Λοιπόν παιδιά, τελικά διορθώθηκε το θέμα. Το τελικό query διαμορφώθηκε ως εξής και τρέχει σε 1-2 seconds!

						SELECT paymentslog_new.*, user.username, user.usergroupid, user.displaygroupid, purchase_temp.threadid
                    	                                   FROM ( SELECT * FROM " . TABLE_PREFIX . "paymentslog AS paymentslog 
							   $filterlogs_where
							   ORDER BY dateline DESC
							   LIMIT  $startat, $perpage ) AS paymentslog_new
						LEFT JOIN " . TABLE_PREFIX . "user AS user ON (paymentslog_new.userid = user.userid) 
					        LEFT JOIN " . TABLE_PREFIX . "paymenttransaction AS paymenttransaction ON (paymentslog_new.transactionid = paymenttransaction.transactionid)
						LEFT JOIN " . TABLE_PREFIX . "paymentinfo AS paymentinfo ON (paymenttransaction.paymentinfoid = paymentinfo.paymentinfoid)
						LEFT JOIN " . TABLE_PREFIX . "purchase_temp AS purchase_temp ON (paymentinfo.hash = purchase_temp.hash)
						ORDER BY paymentslog_new.dateline DESC

Απ' ότι καταλαβαίνω, το αρχικό sub-query τραβάει όσες εγγραφές χρειάζεται η τρέχουσα σελίδα, οπότε μόνο για εκείνες τις εγραφές κάνει LEFT JOIN.

[tsofras]

Μπράβο, ακριβώς αλλά δεν γνωρίζαμε τις λεπτομέρειες για να βοηθήσουμε περισσότερο

[philos]

Τέλος καλό όλα καλά!