HI there,
Looking for some advice. A common query "structure" that performed well in
10.3 is showing a significant performance decrease in 10.5.24. The root cause
seems to be 10.5's refusal to use a key.
The table in question is has a "key => value" like structure:
CREATE TABLE `cases_meta` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`case_id` int(10) unsigned NOT NULL,
`key` varchar(50) NOT NULL,
`value` mediumtext DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `key` (`key`),
KEY `fk_caes_meta_1` (`case_id`),
CONSTRAINT `fk_caes_meta_1` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
The query in question simply is looking for a `key` with a specific value:
SELECT * FROM
cases_meta AS cm
WHERE
cm.`key` = 'PAN_NUMBER'
AND
cm.`value` = '153566'
In this example, the table has 2.8 million records.
In 10.5, it skips the `key` key, and grinds through 2.8 million records:
ANALYZE: {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 2743.92693,
"table": {
"table_name": "cm",
"access_type": "ALL",
"possible_keys": ["key"],
"r_loops": 1,
"rows": 1004130,
"r_rows": 2820048,
"r_table_time_ms": 2548.916341,
"r_other_time_ms": 195.0068087,
"filtered": 30.31918144,
"r_filtered": 0,
"attached_condition": "ddx_lab_801615.cm.`key` = 'PAN_NUMBER' and
ddx_lab_801615.cm.`value` = '153566'"
}
}
}
In 10.3, it uses the `key` key, and reads through a radically smaller subset of
tables to fetch the result:
ANALYZE: {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 357.84,
"table": {
"table_name": "cm",
"access_type": "ref",
"possible_keys": ["key"],
"key": "key",
"key_length": "152",
"used_key_parts": ["key"],
"ref": ["const"],
"r_loops": 1,
"rows": 304484,
"r_rows": 155123,
"r_total_time_ms": 340.94,
"filtered": 100,
"r_filtered": 0,
"index_condition": "ddx_lab_801615.cm.`key` = 'PAN_NUMBER'",
"attached_condition": "ddx_lab_801615.cm.`value` = '153566'"
}
}
}
I'm not sure why 10.5 is taking this substandard approach. Adding a USE INDEX
(`key`) seems to have no effect in 10.5 either, it just skips the index.
Any ideas?
Thanks,
Mike
Confidentiality Notice: The information contained in this electronic message
and any attachments to this message are intended only for the individual(s)
addressed in the message and may contain proprietary and confidential
information. If you are not the intended recipient, you should not disseminate,
distribute, or copy this e-mail. Please notify the sender and destroy this
message. WARNING: Computer viruses can be transmitted via email. The recipient
should scan this email before opening it. The company accepts no liability for
any damage caused by any virus transmitted by this email.
Confidentiality Notice: The information contained in this electronic message
and any attachments to this message are intended only for the individual(s)
addressed in the message and may contain proprietary and confidential
information. If you are not the intended recipient, you should not disseminate,
distribute, or copy this e-mail. Please notify the sender and destroy this
message. WARNING: Computer viruses can be transmitted via email. The recipient
should scan this email before opening it. The company accepts no liability for
any damage caused by any virus transmitted by this email.
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
