How can I speed up a count (*) that is already using indexes? (MyISAM)
I have 3 large tables (10k, 10k and 100M rows) and I am trying to do a simple count to join them where all the joined columns are indexed. Why is COUNT (*) taking so long and how can I speed it up (no triggers and pivot)?
mysql> describe SELECT COUNT(*) FROM `metaward_alias` INNER JOIN `metaward_achiever` ON (`metaward_alias`.`id` = `metaward_achiever`.`alias_id`) INNER JOIN `metaward_award` ON (`metaward_achiever`.`award_id` = `metaward_award`.`id`) WHERE `metaward_award`.`owner_id` = 8;
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+------+-------------+
| 1 | SIMPLE | metaward_award | ref | PRIMARY,metaward_award_owner_id | metaward_award_owner_id | 4 | const | 1552 | |
| 1 | SIMPLE | metaward_achiever | ref | metaward_achiever_award_id,metaward_achiever_alias_id | metaward_achiever_award_id | 4 | paul.metaward_award.id | 2498 | |
| 1 | SIMPLE | metaward_alias | eq_ref | PRIMARY | PRIMARY | 4 | paul.metaward_achiever.alias_id | 1 | Using index |
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+------+-------------+
3 rows in set (0.00 sec)
But it actually takes about 10 minutes to execute the query and I am in MyISAM to have the tables completely locked for that duration
source to share
My guess is that the reason is that you are doing a huge join on three tables (without the where clause, the result will be 10k * 10k * 100M = 10 16 rows at the beginning ). Try reordering the joins (e.g. start with metaward_award
and then join only to metaward_achiever
see how long it will take, then try connecting metaward_alias
, perhaps using a subquery to force your preferred evaluation order).
If that doesn't help you, you might have to denormalize your data, for example by storing the number of aliases for a specific one metaward_achiever
. Then you get rid of one connection entirely. You might even be able to cache amounts for metaward_award
, depending on how and how often your data is updated.
Another thing that might help is to get all the contents of your database into RAM :-)
source to share
Make sure you have indices:
metaward_alias id
metaward_achiever alias_id
metaward_achiever award_id
metaward_award id
metaward_award owner_id
I'm sure a lot of people will also want to count on a specific column, but in MySql that doesn't make any difference to your query.
UPDATE
You can also try to set a condition on the master table instead of one of the concatenated tables. This will give you the same result, but it might be faster (I don't know how smart MySql is):
SELECT COUNT(*) FROM `metaward_award`
INNER JOIN `metaward_achiever`
ON (`metaward_achiever`.`award_id` = `metaward_award`.`id`)
INNER JOIN `metaward_alias`
ON (`metaward_alias`.`id` = `metaward_achiever`.`alias_id`)
WHERE `metaward_award`.`owner_id` = 8
source to share
10 minutes is too long for this request. I think you should have a really small key cache. You can get its size in bytes:
SELECT @@key_buffer_size
First of all, you must run ANALYZE TABLE or OPTIMIZE TABLE . They will sort your index and may improve performance slightly.
You should also see if you can use more compact types for your columns. For example, if you don't have more than 16 million owners or awards or aliases, you can change the INT columns to MEDIUMINT (UNSIGNED of course). Maybe even SMALLINT in some cases? This will decrease your index footprint and put more of it in the cache.
source to share