HowTo SpeedUp MySQL SELECT Query
I had as basis the following Query, running very slow on large tables
SELECT
fields_list
FROM t1
INNER JOIN t2 on t2.id = t1.t2_id
...
INNER JOIN tn on tN.id = t1.tN_id
ORDER BY t1.id DESC
LIMIT 0 , my_limit
Optimisation on indexes did not gave me a significant result , the query used to run slowly again :(
The dead body was in the ORDER BY clause of the SELECT query
The Solution
Creating a simple view ( or just using sub queries)
CREATE VIEW v_name AS SELECT id from t1 ORDER BY t1.id DESC LIMIT 0 , my_limit
and than implementing an INNER JOIN in the main Query
SELECT
fields_list
FROM t1
INNER JOIN t2 on t2.id = t1.t2_id
...
INNER JOIN tn on tN.id = t1.tN_id
INNER JOIN v_name as v on v.id = t1.id
This little otimisation made my query running very fast
SELECT
fields_list
FROM t1
INNER JOIN t2 on t2.id = t1.t2_id
...
INNER JOIN tn on tN.id = t1.tN_id
ORDER BY t1.id DESC
LIMIT 0 , my_limit
Optimisation on indexes did not gave me a significant result , the query used to run slowly again :(
The dead body was in the ORDER BY clause of the SELECT query
The Solution
Creating a simple view ( or just using sub queries)
CREATE VIEW v_name AS SELECT id from t1 ORDER BY t1.id DESC LIMIT 0 , my_limit
and than implementing an INNER JOIN in the main Query
SELECT
fields_list
FROM t1
INNER JOIN t2 on t2.id = t1.t2_id
...
INNER JOIN tn on tN.id = t1.tN_id
INNER JOIN v_name as v on v.id = t1.id
This little otimisation made my query running very fast
Comments
Write the first comment
Leave a trace
Post being viewed right now
