Fab
Reputation: 51
Mariadb: Why my queries is not using the indexes?
I am trying to figure why this query is not using index (and so it's very long).
explain select count(*) as aggregate
from `megabase_sms_data_1`
left join `megabase_sms_thematic_repulse_1`
on `megabase_sms_data_1`.`contact_id` = `megabase_sms_thematic_repulse_1`.`megabase_data_id`
where not (megabase_sms_thematic_repulse_1.thematic_id <=> 6)
and `age` in ('2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '-1');
+------+-------------+---------------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+-------------------------------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+-------------------------------------------+----------+-------------+
| 1 | SIMPLE | megabase_sms_data_1 | ALL | tmp_megabase_sms_data_1_age_index,tmp_megabase_sms_data_1_age_incomes_index,tmp_megabase_sms_data_1_age_housing_zipcode_sex_index,tmp_megabase_sms_data_1_age_zipcode_type_sex_index,tmp_megabase_sms_data_1_age_family_zipcode_sex_index,tmp_megabase_sms_data_1_age_couple_zipcode_sex_index,tmp_megabase_sms_data_1_age_department_sex_housing_index,tmp_megabase_sms_data_1_age_type_department_sex_index,tmp_megabase_sms_data_1_age_family_department_sex_index,tmp_megabase_sms_data_1_age_sex_couple_department_index | NULL | NULL | NULL | 20508191 | Using where |
| 1 | SIMPLE | megabase_sms_thematic_repulse_1 | ref | tmp_megabase_sms_thematic_repulse_1_megabase_data_id_index | tmp_megabase_sms_thematic_repulse_1_megabase_data_id_index | 8 | adsconsole.megabase_sms_data_1.contact_id | 1 | Using where |
I tried to add indexes to the table on the contact_id but it doesn't use it. May be I misunderstood some concepts.
I have those two tables :
DESCRIBE megabase_sms_data_1;
+----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+----------------+
| contact_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| sms_md5 | varchar(255) | NO | MUL | NULL | |
| sms | varchar(255) | NO | MUL | NULL | |
| age | tinyint(4) | NO | MUL | NULL | |
| sex | tinyint(4) | NO | MUL | NULL | |
| couple | tinyint(4) | NO | MUL | NULL | |
| family | tinyint(4) | NO | MUL | NULL | |
| type | tinyint(4) | NO | MUL | NULL | |
| housing | tinyint(4) | NO | MUL | NULL | |
| gather_date | date | NO | | NULL | |
| freshness_date | date | NO | | NULL | |
| zipcode | char(5) | NO | MUL | NULL | |
| department | char(2) | NO | MUL | NULL | |
| address | varchar(255) | NO | | NULL | |
| city | varchar(255) | NO | | NULL | |
| latitude | double | NO | | NULL | |
| longitude | double | NO | | NULL | |
| incomes | tinyint(4) | NO | MUL | NULL | |
| csp | tinyint(4) | NO | | NULL | |
| adr_iris | varchar(255) | NO | | NULL | |
| adr_inse | varchar(255) | NO | | NULL | |
+----------------+------------------+------+-----+---------+----------------+
21 rows in set (0.00 sec)
SHOW INDEX FROM megabase_sms_data_1;
+---------------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ ---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ ---------------+
| megabase_sms_data_1 | 0 | PRIMARY | 1 | contact_id | A | 20508191 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 0 | tmp_megabase_sms_data_1_contact_id | 1 | contact_id | A | 20508191 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_sms_md5_index | 1 | sms_md5 | A | 20508191 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_sex_index | 1 | sex | A | 3 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_couple_index | 1 | couple | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_family_index | 1 | family | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_type_index | 1 | type | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_housing_index | 1 | housing | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_incomes_index | 1 | incomes | A | 10 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_incomes_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_incomes_index | 2 | incomes | A | 131 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_type_age_index | 1 | type | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_type_age_index | 2 | age | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_couple_age_index | 1 | couple | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_couple_age_index | 2 | age | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_family_age_index | 1 | family | A | 2 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_family_age_index | 2 | age | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_sex_age_index | 1 | sex | A | 3 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_sex_age_index | 2 | age | A | 52 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_incomes_age_index | 1 | incomes | A | 10 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_incomes_age_index | 2 | age | A | 131 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_sex_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_sex_index | 2 | sex | A | 43266 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_age_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_age_index | 2 | age | A | 110259 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_couple_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_couple_index | 2 | couple | A | 37085 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_family_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_family_index | 2 | family | A | 38261 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_type_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_type_index | 2 | type | A | 38549 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_housing_index | 1 | zipcode | A | 21497 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_zipcode_housing_index | 2 | housing | A | 38841 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_sex_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_sex_index | 2 | sex | A | 396 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_age_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_age_index | 2 | age | A | 1393 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_couple_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_couple_index | 2 | couple | A | 299 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_family_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_family_index | 2 | family | A | 299 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_type_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_type_index | 2 | type | A | 299 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_housing_index | 1 | department | A | 99 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_department_housing_index | 2 | housing | A | 299 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_housing_zipcode_sex_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_housing_zipcode_sex_index | 2 | housing | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_housing_zipcode_sex_index | 3 | zipcode | A | 253187 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_housing_zipcode_sex_index | 4 | sex | A | 539689 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_zipcode_type_sex_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_zipcode_type_sex_index | 2 | zipcode | A | 110259 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_zipcode_type_sex_index | 3 | type | A | 256352 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_zipcode_type_sex_index | 4 | sex | A | 554275 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_zipcode_sex_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_zipcode_sex_index | 2 | family | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_zipcode_sex_index | 3 | zipcode | A | 256352 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_zipcode_sex_index | 4 | sex | A | 554275 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_couple_zipcode_sex_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_couple_zipcode_sex_index | 2 | couple | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_couple_zipcode_sex_index | 3 | zipcode | A | 250099 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_couple_zipcode_sex_index | 4 | sex | A | 525851 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_department_sex_housing_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_department_sex_housing_index | 2 | department | A | 1393 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_department_sex_housing_index | 3 | sex | A | 5067 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_department_sex_housing_index | 4 | housing | A | 12192 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_type_department_sex_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_type_department_sex_index | 2 | type | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_type_department_sex_index | 3 | department | A | 3913 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_type_department_sex_index | 4 | sex | A | 12207 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_department_sex_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_department_sex_index | 2 | family | A | 39 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_department_sex_index | 3 | department | A | 3931 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_family_department_sex_index | 4 | sex | A | 11759 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_sex_couple_department_index | 1 | age | A | 13 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_sex_couple_department_index | 2 | sex | A | 52 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_sex_couple_department_index | 3 | couple | A | 132 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_age_sex_couple_department_index | 4 | department | A | 10868 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | tmp_megabase_sms_data_1_sms_index | 1 | sms | A | 6836063 | NULL | NULL | | BTREE | | |
| megabase_sms_data_1 | 1 | contact_id | 1 | contact_id | A | 20508191 | NULL | NULL | | BTREE | | |
And the second one :
describe megabase_sms_thematic_repulse_1;
+------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------+------+-----+---------+-------+
| megabase_data_id | bigint(20) | NO | MUL | NULL | |
| thematic_id | int(11) | NO | MUL | NULL | |
+------------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
SHOW INDEX FROM megabase_sms_thematic_repulse_1;
+---------------------------------+------------+------------------------------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------------------+------------+------------------------------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| megabase_sms_thematic_repulse_1 | 1 | tmp_megabase_sms_thematic_repulse_1_megabase_data_id_index | 1 | megabase_data_id | A | 3 | NULL | NULL | | BTREE | | |
| megabase_sms_thematic_repulse_1 | 1 | tmp_megabase_sms_thematic_repulse_1_thematic_id_index | 1 | thematic_id | A | 1 | NULL | NULL | | BTREE | | |
+---------------------------------+------------+------------------------------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
What am I doing wrong? What can I do to optimize the query or the database structure?
Thanks a lot
Upvotes: 3
Views: 2941
Answers (2)
Ari Singh
Reputation: 1296
Depending on how many rows your query is returning, mysql may estimates it to be more efficient to use full scan, rather than use index.
To narrow down the cause, run the query without the where clause and see if the index is being used. Then add the where clause one-by-one to see which one of them is causing the index to be not used. That where clause may be too generic - making it more efficient to do the full table scan, rather than use the index.
If let's say age is causing the index to be not used, you can create an index on contact_id + age.
Upvotes: 1
Simulant
Reputation: 20112
you are joining on megabase_sms_data_1.contact_id and filtering on megabase_sms_data_1.age so you should create an index containing both columns ans only one index per table can be used you can currently eather support the join or the filter but not both. The database query optimizer can still decide to use a full table scan instead.
to support your query try:
alter table megabase_sms_data_1 add index test(contact_id, age)
or
alter table megabase_sms_data_1 add index test(age, contact_id)
Upvotes: 0
Related Questions
- Single Table Queries Very Slow With Indexes MariaDB
- Maria DB - Help on Index
- MySQL BETWEEN query not using index
- MySQL index is not used when certain columns are returned
- How to improve slow query with indexed tables MariaDB?
- How to force mariaDB to use index?
- Maria DB INDEX selection - Why maria is choosing a suboptimal index?
- mariadb not using the correct index with subquery
- Slow performance on query despite indices
- Mysql not using any index