MySQL查询非常慢（20到60秒！） - 为什么？

Question

I have an issue with the following query. It selects log records from a database table.

SELECT  paymentslog.*, user.username, user.usergroupid, user.displaygroupid,
        purchase_temp.threadid
    FROM  " . TABLE_PREFIX . "paymentslog AS paymentslog
    LEFT JOIN  " . TABLE_PREFIX . "user AS user
       ON (paymentslog.userid = user.userid)
    LEFT 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) $filterlogs_where
    GROUP BY  paymentslog.logid
    ORDER BY  paymentslog.dateline DESC
    LIMIT  $startat, $perpage 

• all the joins are needed to access threadid on SELECT except the join with user table to access username, usergroupid and displaygroupid
• it was needed 20 seconds to run (!), and during my try to fix it, after add of INDEX for column transactionid (of table paymentslog), now it needs... 60 seconds!
• for some reason, it was return a specific row multiple times, and for this reason I fixed it by add a "GROUP BY paymentslog.logid"

I have to notice that:
- $filterlogs_where PHP variable has the WHERE of the query (I build via php different filters for the paymentslog). By default, $filterlogs_where has a value of "1 = 1", and if I have to apply a filter, I add a .= " AND paymentslog.userid = X" etc etc.

Any idea why this query is so slow?
I think I have seen and write more complicated queries which runs in a few seconds or milliseconds. Why this issue with the above query?

Answer 1

First I must admonish you not to have multiple tables with the same schema. That is usually a bad design.

Do you really need LEFT? That is, you want a row returned even when the 'right' table has nothing? If not, simply use JOIN.

Getting rid of the LEFTs may get rid of the GROUP BY. Joins followed by GROUP BY usually cause "inflate the number of rows" followed by "deflate via group by". This creates a huge temp table in the process, thereby slowing things down.

Then I need to point out that Pagination via Offset is problematic -- often leading to lengthy query times. However, we need to get rid of the GROUP BY to make this technique possible.

Please use shorter aliases.

Now, back to your specifics... Well, first work on the stuff above. And supply SHOW CREATE TABLE. Then something like this will speed things up:

SELECT ...
    FROM ( SELECT id
               ORDER BY  dateline DESC
               LIMIT  $startat, $perpage ) AS pl
    JOIN user ON ...
    JOIN ...
    ORDER BY dateline DESC;   -- yes, needs repeating