The following SQL query is running very slowly. It prints the results to the screen in about 23 seconds. What can I do to make the query run faster.
SELECT DISTINCT P.*,U.* FROM i_friends F FORCE INDEX(ixFriend) INNER JOIN i_posts P FORCE INDEX (ixForcePostOwner) ON P.post_owner_id = F.fr_two INNER JOIN i_users U FORCE INDEX (ixForceUser) ON P.post_owner_id = U.iuid AND U.uStatus IN('1','3') AND F.fr_status IN('me', 'flwr', 'subscriber') WHERE P.post_owner_id='$uid' $morePost GROUP BY P.post_id DESC LIMIT 5
These look like "filters", so move them to the
WHEREclause and leave just "relation" conditions in the
ONclause. (This won’t change performance, but will make reading the query easier.)
AND U.uStatus IN('1','3') AND F.fr_status IN('me', 'flwr', 'subscriber')
Get rid of the
FORCE INDEXclauses; they may help today, but hurt tomorrow when the distribution of the data changes.
$morePost? I ask because it may be critical to optimizing the performance.
Add these composite indexes:
P: INDEX(post_owner_id, post_id) F: INDEX(fr_status, fr_two) U: INDEX(uStatus, iuid)
(When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.)
Don’t use both
GROUP BY; it probably cause an extra sort on the entire dataset (after the JOINs, but before the LIMIT).
LIMIT 5without an
ORDER BYlets the Optimizer pick whichever 5 it likes. Add an
ORDER BYif you care which 5.
A common performance problem comes from the mixture of
GROUP BY. I call it "explode-implode". The Joins explode the data set into lots more rows, only to have the Group-by implode back down to the rows that came from one of the tables. The typical cure is first select the desired rows from the grouped table (
P). Do this in a "derived table". then Join to the other tables. (However, I got lost in this query, so I cannot tell if it applies here.)
Answered By – Rick James
Answer Checked By – Mildred Charles (BugsFixing Admin)