Neil Singh
Reputation: 73
MYSQL query is slow using group by with order by
I have a query that is using a group by and an order by, but it's very slow! I need some help to get the index correct. This is the query I'm running:
select *
from puresen_mv_shop.cache_deals
where feature_ids REGEXP 'i,t,d'
and phone_cost > 100.00
group by handset_numeric_id
order by popularity
LIMIT 0,10;
explain:
1 SIMPLE cache_deals index popularity 5 10 6635320.00 Using where; Using temporary
This runs in about 3 seconds, but I need it under a second. There are over 600k rows in the table. Here is the table structure:
mysql> describe cache_deals
-> ;
+-------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+---------------+------+-----+---------+-------+
| deal_id | varchar(100) | NO | | NULL | |
| deal_id_replication | varchar(150) | NO | PRI | | |
| handset_numeric_id | int(10) | YES | MUL | NULL | |
| handset_url | varchar(100) | YES | | NULL | |
| image_url | varchar(50) | YES | MUL | NULL | |
| handset_id | varchar(50) | YES | | NULL | |
| phone_cost | decimal(10,2) | YES | | NULL | |
| tariff_numeric_id | int(10) | YES | MUL | NULL | |
| tariff_id | varchar(100) | YES | MUL | NULL | |
| tariff_name | varchar(100) | YES | | NULL | |
| network_name | varchar(20) | YES | | NULL | |
| network_numeric_id | int(5) | YES | MUL | NULL | |
| term | int(5) | YES | MUL | NULL | |
| minutes | int(5) | YES | MUL | NULL | |
| texts | int(5) | YES | MUL | NULL | |
| data | int(5) | YES | MUL | NULL | |
| org_line_rental | decimal(10,2) | YES | MUL | NULL | |
| effective_monthly_cost | decimal(10,2) | YES | MUL | NULL | |
| free_gift_id | int(10) | YES | MUL | NULL | |
| free_gift_name | varchar(50) | YES | | NULL | |
| cashback | int(5) | YES | MUL | NULL | |
| free_lr | int(5) | YES | MUL | NULL | |
| half_lr | int(5) | YES | MUL | NULL | |
| clearance_flag | int(5) | YES | MUL | NULL | |
| manufacturer_numeric_id | int(5) | YES | MUL | NULL | |
| manufacturer_name | varchar(50) | YES | | NULL | |
| full_handset_name | varchar(100) | YES | | NULL | |
| popularity | int(20) | YES | MUL | NULL | |
| handset_colour | varchar(50) | YES | MUL | NULL | |
| feature_ids | varchar(100) | YES | | NULL | |
| operating_system | varchar(30) | YES | MUL | NULL | |
+-------------------------+---------------+------+-----+---------+-------+
Here are the indexes I've currently got on this table:
mysql> show index from cache_deals;
+-------------+------------+-------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type
+-------------+------------+-------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------
| cache_deals | 0 | PRIMARY | 1 | deal_id_replication | A | 663532 | NULL | NULL | | BTREE
| | |
| cache_deals | 1 | handset_numeric_id | 1 | handset_numeric_id | A | 759 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | network_numeric_id | 1 | network_numeric_id | A | 8 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | tariff_numeric_id | 1 | tariff_numeric_id | A | 1091 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | manufacturer_numeric_id | 1 | manufacturer_numeric_id | A | 23 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | operating_system | 1 | operating_system | A | 42 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | image_url | 1 | image_url | A | 755 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | tariff_id | 1 | tariff_id | A | 1091 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | term | 1 | term | A | 7 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | minutes | 1 | minutes | A | 26 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | texts | 1 | texts | A | 14 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | data | 1 | data | A | 14 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | org_line_rental | 1 | org_line_rental | A | 128 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | effective_monthly_cost | 1 | effective_monthly_cost | A | 2147 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | free_gift_id | 1 | free_gift_id | A | 105 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | cashback | 1 | cashback | A | 2 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | free_lr | 1 | free_lr | A | 2 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | half_lr | 1 | half_lr | A | 2 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | clearance_flag | 1 | clearance_flag | A | 2 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | handset_colour | 1 | handset_colour | A | 17 | NULL | NULL | YES | BTREE
| | |
| cache_deals | 1 | popularity | 1 | popularity | A | 718 | NULL | NULL | YES | BTREE
| | |
+-------------+------------+-------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------
Am I indexing this table correctly?
Thanks for you help.
Upvotes: 0
Views: 102
Answers (2)
Strawberry
Reputation: 33945
select [name the columns you actually want returned]
from puresen_mv_shop.cache_deals
where feature_ids = 'i,t,d'
and phone_cost > 100.00
order
by popularity
LIMIT 0,10;
Index features_id or (features_id,phone_cost)
Upvotes: 0
G-Nugget
Reputation: 8836
That REGEXP is killing the potential performance since it can't use an index. Can that part of the query be written some other way or possibly normalized to another table? If you can, that will probably help. Other than that, a composite index on phone_cost and handset_numeric_id should help. Also adding popularity to that index might help, but MySQL is already unhappy with the query since you're not really supposed to be able to work with columns that aren't in the GROUP BY clause.
As a side note, the data types of the columns could be more efficient. int(5) works the same as MEDIUMINT due to the limit on the INT except that MEDIUMINT is one byte smaller. Also, int(20) doesn't make sense since the INT type can't be that large.
Upvotes: 3
Related Questions
- query becomes slow with GROUP BY
- Optimization of a query with GROUP BY clause by using indexes
- MySql improve GROUP BY ORDER BY query with index
- MySQL slow query when using GROUP BY
- How can i speed up a group by query that already uses indexes?
- Extremely slow GROUP BY on MySQL. Not index-related
- Slow GROUP BY query due to wrong INDEX used
- Slow query when using GROUP BY
- MySQL query becomes slow
- mysql query slow down when using group by and order by in a same query