SQL query is running very slow. It shows the results in about 23 seconds

Question

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

Answer 1

• These look like "filters", so move them to the WHERE clause and leave just "relation" conditions in the ON clause. (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 INDEX clauses; they may help today, but hurt tomorrow when the distribution of the data changes.

• What is $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 DISTINCT and GROUP BY; it probably cause an extra sort on the entire dataset (after the JOINs, but before the LIMIT).

• LIMIT 5 without an ORDER BY lets the Optimizer pick whichever 5 it likes. Add an ORDER BY if you care which 5.

• A common performance problem comes from the mixture of JOIN and 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.)