MySQL query optimization - getting the last post of all threads

Question

My MySQL query is loading very slow (over 30 secs), I was wondering what tweaks I can make to optimize it.

The query should return the last post with the string "?" of all threads.

SELECT FeedbackId, ParentFeedbackId, PageId, FeedbackTitle, FeedbackText, FeedbackDate
FROM ReaderFeedback AS c
LEFT JOIN (
  SELECT max(FeedbackId) AS MaxFeedbackId
  FROM ReaderFeedback 
  WHERE ParentFeedbackId IS NOT NULL 
  GROUP BY ParentFeedbackId
) AS d ON d.MaxFeedbackId = c.FeedbackId
WHERE ParentFeedbackId IS NOT NULL 
AND FeedbackText LIKE '%?%' 
GROUP BY ParentFeedbackId
ORDER BY d.MaxFeedbackId DESC LIMIT 50

Answer 1

Before discuss this problem, I have formatted your SQL:

SELECT feedbackid, 
       parentfeedbackid, 
       pageid, 
       feedbacktitle, 
       feedbacktext, 
       feedbackdate 
FROM   readerfeedback AS c 
       LEFT JOIN (SELECT Max(feedbackid) AS MaxFeedbackId 
                  FROM   readerfeedback 
                  WHERE  parentfeedbackid IS NOT NULL 
                  GROUP  BY parentfeedbackid) AS d 
              ON d.maxfeedbackid = c.feedbackid 
WHERE  parentfeedbackid IS NOT NULL 
       AND feedbacktext LIKE '%?%' 
GROUP  BY parentfeedbackid 
ORDER  BY d.maxfeedbackid DESC 
LIMIT  50

Since there is an Inefficient query criteria in your SQL:

feedbacktext LIKE '%?%'

Which is not able to take benefit from Index and needs a full scan, I suggest you to add a new field

isQuestion BOOLEAN 

to your table, and then add logic in your program to assign this field when insert/update a feedbacktext. Finally your can query based on this field and take benefit from index.