1
\$\begingroup\$

My MySQL query is loading very slowly (over 30 secs) and I was wandering 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
\$\endgroup\$
  • 1
    \$\begingroup\$ Welcome to code review. Just the way you want answers we want to know a little more about your code e.g a sample of the database schema. \$\endgroup\$ – Siobhan Aug 1 '16 at 6:25
  • 1
    \$\begingroup\$ Especially info on existing indexes and such. \$\endgroup\$ – ferada Aug 1 '16 at 9:13
  • 1
    \$\begingroup\$ From what I see, your query doesn't 'return the last post with the string "?" of all threads.' If your question is how to do that, it should be asked at Stack Overflow. \$\endgroup\$ – Gert Arnold Aug 1 '16 at 22:36
2
\$\begingroup\$

Hard to give full answer without understanding the ReaderFeedback table definition. I will point to some areas of concern.

  • A LIKE clause that has a wildcard at its beginning will never be able to use a typical B-TREE index on the field you are comparing against. This will always require a full table scan. For this reason, you probably will need to look at using full text search with appropriate full text index.
  • You are using sub-selects, which oftentimes introduce performance problems. Can this be designed away?

  • It appears you are implementing a parent-child tree hierarchy, which typically is a little painful to work with in MySQL (or most any other relational database). You might want to check out this article: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ on managing hierarchical data in MySQL. You might for example find better read performance from using the nested sets model shown there (though nested sets are typically more complex when it comes to inserting/updating nodes).

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.