CODEWITHSUNDEEP
mysqlsql
Idan
Idan

Reputation: 31

Slow GROUP BY query due to wrong INDEX used

I have a MySQL (MariaDB, to be more precise) database which holds different "records" (not to be confused with database records, these are the data units my system works with and consist of multiple key-value fields). Each "record" is part of an "instance". Each "instance is part of a "user group". Each "record" CAN be part of a "category".

I am trying to write an SQL query that retrieves all categories for a user group - and retrieve the amount of records that exist in all the instances under each category. My SQL statement looks like this:

SELECT COUNT( r.id ) AS records_number, c.category
FROM records r
INNER JOIN feeds_instances i ON r.instance = i.id
INNER JOIN feeds_instances_categories c ON c.instance = i.id
WHERE i.user_group = '0'
AND r.reviewed IS NULL
GROUP BY c.category
LIMIT 0 , 30

When testing the query with SQL_NO_CACHE, I get a 0.2+ seconds query. When removing the GROUP BY, it reduces to 0.0008 seconds. When I do an EXPLAIN command for the SQL query, I get that the index used for table c is "instance" and not "category" (the feeds_instances_categories table has three columns: id, instance, category. Indexes exist on instance and category).

I am trying to force the GROUP BY to use the index category. I tried using USE INDEX command, but I get errors. I am most likely not doing it correctly and can't find how to do it correctly in the documentation.

Any help would be appreciated!

Upvotes: 2

Views: 86

Answers (2)

RlDDlCK
RlDDlCK

Reputation: 604

How many Entries do you have in your Database.

If you excecute the query without a group by, the LIMIT will cut all after 30 entries. BUT the group by function itterate all your entries of the table and then limit them.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

MySQL is very reluctant to use indexes for aggregation, so you probably won't succeed there.

You should be sure that you have the right indexes for the joins and where filters in your query. This would probably be: records(instance, reviewed), feeds_instances(user_group, id), and feeds_instances_categories(instance, category).

If user_group is declared as a number, then drop the single quotes. Confusing strings and numbers can also confuse the optimizer.

Upvotes: 1

Related Questions