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 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
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_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.