mysqlsqlperformancequery-optimization
Why is MySQL not using my index for JOIN?
I have an incredibly complex query being produced by an ORM, but here's the relevant portions for the sake of this question:
SELECT
...
FROM
`Broadcast` `t`
LEFT OUTER JOIN
`Site` `site`
ON (`t`.`site_id` = `site`.`id`)
LEFT OUTER JOIN
`Customer` `customer`
ON (`site`.`customer_id` = `customer`.`id`)
LEFT OUTER JOIN
`Domain` `domain`
ON (`customer`.`domain_id` = `domain`.`id`)
...
My fields are defined as follows:
>SHOW FIELDS FROM Broadcast;
+---------------------------+--------------------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------------------------------+------+-----+------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| site_id | int(10) unsigned | NO | MUL | NULL | |
...
>SHOW FIELDS FROM Site;
+---------------------------+---------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+---------------------+------+-----+------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| customer_id | int(11) | NO | MUL | 0 | |
...
>SHOW FIELDS FROM Customer;
+---------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| domain_id | int(10) unsigned | NO | MUL | 1 | |
...
>SHOW FIELDS FROM Domain;
+---------------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
...
Why some of our IDs are int(11) and others are int(10) unsigned is just a matter of historical context. What's important is that the X_id foreign key always shares a type with the relevant id column. We never attempt to link an int(11) with an int(10) unsigned. Also the id field is always the primary key for the table.
We also have indexes on all of the foreign keys:
>SHOW INDEXES FROM Broadcast;
+-----------+------------+---------------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+---------------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Broadcast | 0 | PRIMARY | 1 | id | A | 139708 | NULL | NULL | | BTREE | | |
| Broadcast | 1 | site_id | 1 | site_id | A | 1060 | NULL | NULL | | BTREE | | |
...
>SHOW INDEXES FROM Site;
+-------+------------+----------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Site | 0 | PRIMARY | 1 | id | A | 1876 | NULL | NULL | | BTREE | | |
| Site | 1 | customer_id | 1 | customer_id | A | 250 | NULL | NULL | | BTREE | | |
...
>SHOW INDEXES FROM Customer;
+----------+------------+--------------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Customer | 0 | PRIMARY | 1 | id | A | 292 | NULL | NULL | | BTREE | | |
| Customer | 1 | domain_id | 1 | domain_id | A | 2 | NULL | NULL | | BTREE | | |
...
>SHOW INDEXES FROM Domain;
+--------+------------+-------------------------------------------+--------------+---------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------------------------+--------------+---------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Domain | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
...
Each of these indexes consists of a single field, so the field is the first sequence in the index.
When I run EXPLAIN ... on my query, the output I get looks as follows:
Site and Customer are joined with the eq_ref type, but Domain is joined with ALL type. Why is MySQL not using my index?
I can force MySQL to use my index by adding USE INDEX FOR JOIN (domain_id) after the Customer table name, but this causes a new issue:
The problem just moves up from the Customer->Domain relation to the Site->Customer relation. I can shift the problem up one further using USE INDEX FOR JOIN (customer_id) after the Site table name, and as you might expect this just causes the Site table to join with the ALL type instead of eq_ref
I can't for the life of me figure out why MySQL is refusing to use my indexes
Solution
Per @ysth in the comments:
MySQL will not use an index if the table you are joining has very few rows, as the overhead of reading an index from disk exceeds the benefits
- Will modifying the primary key type using ALTER in MySQL reset the auto-increment value to 0?
- Auto-increment is not resetting in MySQL
- Php update database if checkbox is not checked
- Retrieve 10 records from MySQL?
- how to use str_to_date (MySQL) to parse two-digit year correctly
- sum column value(s) if previous values has same value with current value from the same table in mysql
- JDBC connection without database definition
- Checking connection to MySQL (Java)
- WARN: Establishing SSL connection without server's identity verification is not recommended
- how to solve django.db.utils.NotSupportedError in django
- How to make an Inner Join in django?
- Can I order by a conditional combination of fields in MySQL?
- Select from 2 tables in the last 30 days, return 0 if null/not exist
- Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (38)
- Access denied for user 'root@localhost' (using password:NO)
- Watching a table for change in MySQL?
- table is specified twice both as a target for INSERT and as separate source of data
- Python Mysql, "commands out of sync; you can't run this command now"
- Does Laravel's "soft_delete" need index on MySQL?
- How do I access a field from the main query inside a sub query in mySQL?
- Not getting unpaid or partially paid records some can help me?
- Warning: The post-install step did not complete successfully when trying to install mysql using brew in Mac OS High Sierra
- MySQL Entity Attribute Value table structure proposal
- CodeIgniter SELECT query on table with JOIN and WHERE IN a subquery
- is there a MYSQL tables schema for storing raw material, intermediate and final products?
- MySQL: Total GROUP BY WITH ROLLUP curiosity
- Problem with pip install mariadb - mariadb_config not found
- nodejs mysql memory leak on large volume of high frequency queries
- `MODIFY COLUMN` vs `CHANGE COLUMN`
- Displaying BLOB image from Mysql database into dynamic div in html