Jason Jason - 1 month ago 5
MySQL Question

How to performance tuning the limit sentence?

I run the following MYSQL sentence, however it spends over 10 seconds, although it just return 10 rows. BTW, if I remove the LIMIT 0, 10 , it would return 1,000,000 rows.I have created Index1 for column SceneCode and Index2 for column ProviderId.

SELECT *
FROM
(SELECT * FROM gf_sceneprovider WHERE SceneCode='DL00000003' ) AS sprovider
LEFT JOIN (SELECT * FROM gf_sceneprovidertemplate WHERE SceneCode='DL00000003' ) AS stemplate
ON sprovider.ProviderId = stemplate.ProviderId
INNER JOIN gf_provider AS provider
ON provider.ProviderId = sprovider.ProviderId
LIMIT 0, 10


enter image description here

Answer

I would do away with the subqueries in favor of direct joins:

SELECT *
FROM gf_sceneprovider sprovider
LEFT JOIN gf_sceneprovidertemplate stemplate
    ON sprovider.ProviderId = stemplate.ProviderId
INNER JOIN gf_provider AS provider 
    ON provider.ProviderId = sprovider.ProviderId 
WHERE sprovider.SceneCode = 'DL00000003' AND
      stemplate.SceneCode = 'DL00000003'
LIMIT 0, 10

Then, add indices on the join columns if possible. Your original subqueries might prevent the indices on the gf_sceneprovider and gf_sceneprovidertemplate tables from being used effectively/at all. The reason for this is that your subqueries essentially create an on-the-fly table which, unlike the tables from which they select, have no indices. I think some RDMBS can cope with this in certain scenarios but it looks like that is not the case here.