How/where should I add an Index?

[abduraooft]

I've a simple query
SELECT M.message_id,M.response_count,M.subject,
COALESCE(M.last_response_received_date, M.posted_date) as last_update_date
from message as M
where category_id=1
order by last_update_date DESC
LIMIT 15
to fetch all latest threads from a table. I'm using the same table to store the OP and it's replies, which are distinguished by category_id.

As you may see, the COALESCE clause is the main part, which inspects the posted_date and last_response_received_date.

So how should I index the table to optimise the above query?
(I know explain can be used here, but don't know how to analyse its output)

Any help would be apreciated.

[Fumigator]

I would create an index on category_id+last_response_received_date, then test the query. If the access path still does a full table scan, then add another index category_id+posted_date, test the query again. If still nothing, add an index category_id+last_response_received_date+posted_date, test the query again.

It will be very beneficial for you to learn how to read an Explain, so you can make a change, then check the access path, make another change, check the access path, etc. If you don't check the access path after a change, you'll just be guessing as to what's happening and you won't really know if your change did anything for you. The MySQL manual has a page dedicated to the Explain; it lays it all out.

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

[guelphdad]

Your COALESCE clause does not likely do what you think it does. COALESCE returns the first non-NULL value it finds in the comparison so if you have two dates 2007-08-09 and 2009-05-05 it would return the date in 2007. You most likely want GREATEST in there as well.

COALESCE(GREATEST(M.last_response_received_date, M.posted_date),M.last_response_received_date, M.posted_date) as last_update_date


If you just use GREATEST on its own you would get NULLs returned.

I don't believe either column needs an index for the query though.

[Fumigator]

I don't believe either column needs an index for the query though.

I would actually agree with that.... though going through the exercise of adding an index and checking the explain would be a good thing. :thumbsup:

[abduraooft]

Thanks for the suggestions.
I would actually agree with that.... though going through the exercise of adding an index and checking the explain would be a good thing. :thumbsup:
I've tested all the cases suggested by you, but it shows 160 rows all the time. So I assume guelphdad's belief is correct! :)

Your COALESCE clause does not likely do what you think it does. COALESCE returns the first non-NULL value it finds in the comparison so if you have two dates 2007-08-09 and 2009-05-05 it would return the date in 2007. You most likely want GREATEST in there as well.
COALESCE(M.last_response_received_date, M.posted_date) as last_update_date
I think there can't be any case like that => receiving a response before posting a thread (please correct me if I'm wrong)
(I may need to revert the last_response_received_date to NULL again, when I delete all the responses received)

Anyway, thanks for the heads up.n

[guelphdad]

oh okay, i didn't really look at the column names there. yah COALESCE should be fine since the first column is either going to be NULL and you want the date in the second column, or the first column is going to have a date and since that is NOT NULL it would be returned.