CODEWITHSUNDEEP
mysqlquery-optimization
Daniel C
Daniel C

Reputation: 627

Find Most Recent Comment Date in MySQL - speed issue

I have a query to find the most recent date when a Comment was made on a particular Page Article:

SELECT created_on , created_by_name, created_by_id , body 
FROM ac2012.acx_comments WHERE created_on IN (
SELECT MAX(created_on)
FROM ac2012.acx_comments WHERE parent_id = '642')

The query is working, printing out the most recent comment date for the parent_id of 642.

However, this query takes around 4 seconds. This is compared to most of my queries which take just 0.1 seconds on average.

I am wondering - is there a reason why it is taking so long and how can I make it go faster? For example, I thought that limiting it to parent_id "642" would begin the query with looking through only 3 comment rows... which should make the calculation of the maximum of created_on not take very long.

Any suggestions would be appreciated. Perhaps the WHERE should be placed somewhere else to optimize speed?

Please see Table Schema here: https://docs.google.com/spreadsheet/pub?key=0AjkotydroXWqdHBEOHAtalJpazROaHh3R0VpM0hZSlE&output=html

Upvotes: 0

Views: 81

Answers (1)

Neo
Neo

Reputation: 5463

you should add index on parent_id and created_on like

AlTER TABLE acx_comments ADD INDEX parent_id_created_on(parent_id, created_on)

rewrite your query

SELECT created_on, created_by_name, created_by_id, body FROM acx_comments a JOIN (SELECT MAX(created_on) as created_on FROM acx_comments where parent_id='642') b ON a.created_on=b.created_on

Upvotes: 1

Related Questions