Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
For the specific table and data:
> show create table mll_app_magna_pure
|
Create Table: CREATE TABLE `mll_app_magna_pure` (
|
`mp_id` char(36) NOT NULL DEFAULT '',
|
`mp_create_date` bigint(20) unsigned NOT NULL,
|
`mp_create_user` varchar(40) NOT NULL DEFAULT '',
|
`mp_change_date` bigint(20) unsigned NOT NULL,
|
`mp_change_user` varchar(40) NOT NULL DEFAULT '',
|
`mp_valid_from` bigint(20) unsigned NOT NULL,
|
`mp_valid_to` bigint(20) unsigned NOT NULL,
|
`mp_outputplate` varchar(30) DEFAULT NULL,
|
`mp_transferplate` varchar(20) DEFAULT NULL,
|
KEY `mp_id_idx` (`mp_id`),
|
KEY `mp_transferplate_idx` (`mp_valid_to`,`mp_transferplate`,`mp_outputplate`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
|
> select count(*) from mll_app_magna_pure;
|
1408028
|
|
|
> select count(*) from mll_app_magna_pure where mp_valid_to=99991231235555;
|
806751
|
|
|
> SELECT count(*) FROM mll_app_magna_pure WHERE mp_valid_to=99991231235555 AND mp_transferplate='0502212000095962';
|
0
|
the index is not used for this query:
> ANALYZE FORMAT=JSON
|
SELECT SQL_NO_CACHE * FROM mll_app_magna_pure WHERE mp_valid_to=99991231235555 AND (mp_transferplate='0502212000095962' OR mp_outputplate='0502212000095962')\G
|
ANALYZE
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 4160.197218,
|
"table": {
|
"table_name": "mll_app_magna_pure",
|
"access_type": "ALL",
|
"possible_keys": [
|
"mp_sample_no_idx",
|
"mp_valid_to",
|
"mp_status_barcode_idx",
|
"mp_exas_id_idx",
|
"mp_cdna_charge_idx",
|
"mp_transferplate_idx",
|
"mp_mpr_no_idx"
|
],
|
"r_loops": 1,
|
"rows": 1364085,
|
"r_rows": 1412113,
|
"r_table_time_ms": 3975.964708,
|
"r_other_time_ms": 184.1642187,
|
"filtered": 49.99996185,
|
"r_filtered": 0.00325753,
|
"attached_condition": "mll_app_magna_pure.mp_valid_to = 99991231235555 and (mll_app_magna_pure.mp_transferplate = '0502212000095962' or mll_app_magna_pure.mp_outputplate = '0502212000095962')"
|
}
|
}
|
}
|
even though WHERE clause is covered by the index (in a way) and the index is clearly beneficial when forced:
> ANALYZE FORMAT=JSON
|
SELECT SQL_NO_CACHE * FROM mll_app_magna_pure FORCE INDEX (`mp_transferplate_idx`) WHERE mp_valid_to=99991231235555 AND (mp_transferplate='0502212000095962' OR mp_outputplate='0502212000095962');
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 162.173876,
|
"table": {
|
"table_name": "mll_app_magna_pure",
|
"access_type": "ref",
|
"possible_keys": ["mp_transferplate_idx"],
|
"key": "mp_transferplate_idx",
|
"key_length": "8",
|
"used_key_parts": ["mp_valid_to"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 632958,
|
"r_rows": 46,
|
"r_table_time_ms": 161.9810586,
|
"r_other_time_ms": 0.135228766,
|
"filtered": 100,
|
"r_filtered": 100,
|
"index_condition": "mll_app_magna_pure.mp_transferplate = '0502212000095962' or mll_app_magna_pure.mp_outputplate = '0502212000095962'"
|
}
|
}
|
}
|
and is used by the optimizer for other content of the table:
MariaDB [test]> explain SELECT SQL_NO_CACHE * FROM mll_app_magna_pure WHERE mp_valid_to=99991231235555 AND (mp_transferplate='0502212000095962' OR mp_outputplate='0502212000095962');
|
+------+-------------+--------------------+------+----------------------+----------------------+---------+-------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------------+------+----------------------+----------------------+---------+-------+------+-----------------------+
|
| 1 | SIMPLE | mll_app_magna_pure | ref | mp_transferplate_idx | mp_transferplate_idx | 8 | const | 1 | Using index condition |
|
+------+-------------+--------------------+------+----------------------+----------------------+---------+-------+------+-----------------------+
|
1 row in set (0.037 sec)
|
The statistics for the table in the real case (InnoDB persistent one) is close to reality and was collected over as many sample pages as we have data pages. But looks like number of rows that match the condition on the first column in the index forces optimizer to ignore it.
Attachments
Issue Links
- relates to
-
-
- Open
-