Indexing this mysql query

Question

I am querying database with follwing query. This query takes 21 seconds to execute.
I have check it by explain query. I have index on fields groupId , batchId separately.

EXPLAIN SELECT message, sentOn, maskId, isDndCheck, contentType
FROM sms_histories
WHERE groupId = 1750
GROUP BY batchId
ORDER BY batchId DESC
LIMIT 0 , 1

I am getting group's last post by this query.

My explain query shows me result

id  select_type     table       type    possible_keys   key     key_len     ref     rows    Extra
1      SIMPLE   sms_histories    index      groupId     batchId     5        NULL    888    Using where 

please tell me what is the problem , why this query takes this much long time?

Answer 1

I noticed that groupId is CHAR type. You need ascending index on groupId field. But, consider changing type of GroupId to INTEGER if possible.

Answer 2

You would need a compound index on (groupId, batchId) to make ORDER BY optimisation work. MySQL can't use the batchId index alone because all the different groupIds are mixed up in it.

(I'm a bit confused about what the query is supposed to be doing though. You have GROUP BY with no aggregate columns. If batchId is the primary key of sms_histories then the GROUP BY would appear to do nothing. If it isn't the primary key then the query is not valid SQL and MySQL will be returning a row at semi-random.)

Answer 3

I think you should use compound index on batchId and groupId as bobince said. I think there will be lotof data in your table sms_histories. and as bobince said you are using bit awkward database schema. you should try to normalize it and remove replications from it. You should focus more on database schema before starting a new project. so the main thing is try to index on batchId and groupId both.