CODEWITHSUNDEEP
mysqlperformanceindexingquery-optimization
Maulik Vora
Maulik Vora

Reputation: 2584

Indexing this mysql query

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?

Upvotes: 3

Views: 129

Answers (3)

user443355
user443355

Reputation:

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.

Upvotes: 1

bobince
bobince

Reputation: 536339

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.)

Upvotes: 1

Ljubomir Đokić
Ljubomir Đokić

Reputation: 426

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

Upvotes: 1

Related Questions