Query Performance Drops 10.3 -> 10.5 - Query Optimizer Refusing Index
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.
To add to my confusion, in 10.5, if I add a limit to the below query, the query optimizer does choose to use a key on the query: SELECT * FROM cases_meta AS cm WHERE cm.`key` = 'PAN_NUMBER' AND cm.`value` = '6822' limit 5 ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 22.29661386, "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": 307098, "r_rows": 3341, "r_table_time_ms": 21.77044251, "r_other_time_ms": 0.516935887, "filtered": 100, "r_filtered": 0.149655792, "index_condition": "ddx_lab_801615.cm.`key` = 'PAN_NUMBER'", "attached_condition": "ddx_lab_801615.cm.`value` = '6822'" } } } This substantially speeds up the query. This also seems kind of "hacky." Even if this was a reasonable way forward, it is not a versatile option. Particularly if I want to use the query in a sub select (which I do): "This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" Ideas? Thanks, Mike ________________________________ From: Michael Caplan <Michael.Caplan@henryscheinone.com> Sent: 30 July 2024 12:59 PM To: Michael Caplan via discuss <discuss@lists.mariadb.org> Subject: Query Performance Drops 10.3 -> 10.5 - Query Optimizer Refusing Index 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.
Just a follow up on my thread for anyone who might come across a similar issue. I was not aware of the FORCE INDEX (`key`) command vs. USE INDEX(`key`). FORCE INDEX has the query performing snappy as did 10.3 Thanks, Mike ________________________________ From: Michael Caplan <Michael.Caplan@henryscheinone.com> Sent: 30 July 2024 3:45 PM To: Michael Caplan via discuss <discuss@lists.mariadb.org> Subject: Re: Query Performance Drops 10.3 -> 10.5 - Query Optimizer Refusing Index To add to my confusion, in 10.5, if I add a limit to the below query, the query optimizer does choose to use a key on the query: SELECT * FROM cases_meta AS cm WHERE cm.`key` = 'PAN_NUMBER' AND cm.`value` = '6822' limit 5 ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 22.29661386, "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": 307098, "r_rows": 3341, "r_table_time_ms": 21.77044251, "r_other_time_ms": 0.516935887, "filtered": 100, "r_filtered": 0.149655792, "index_condition": "ddx_lab_801615.cm.`key` = 'PAN_NUMBER'", "attached_condition": "ddx_lab_801615.cm.`value` = '6822'" } } } This substantially speeds up the query. This also seems kind of "hacky." Even if this was a reasonable way forward, it is not a versatile option. Particularly if I want to use the query in a sub select (which I do): "This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" Ideas? Thanks, Mike ________________________________ From: Michael Caplan <Michael.Caplan@henryscheinone.com> Sent: 30 July 2024 12:59 PM To: Michael Caplan via discuss <discuss@lists.mariadb.org> Subject: Query Performance Drops 10.3 -> 10.5 - Query Optimizer Refusing Index 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.
Hey Michael, I would recommend looking at https://mariadb.com/kb/en/engine-independent-table-statistics/ That might improve your execution plans a lot Regards GL Le ven. 2 août 2024 à 14:47, Michael Caplan via discuss < discuss@lists.mariadb.org> a écrit :
Just a follow up on my thread for anyone who might come across a similar issue.
I was not aware of the FORCE INDEX (`key`) command vs. USE INDEX(`key`).
FORCE INDEX has the query performing snappy as did 10.3
Thanks,
Mike
------------------------------ *From:* Michael Caplan <Michael.Caplan@henryscheinone.com> *Sent:* 30 July 2024 3:45 PM *To:* Michael Caplan via discuss <discuss@lists.mariadb.org> *Subject:* Re: Query Performance Drops 10.3 -> 10.5 - Query Optimizer Refusing Index
To add to my confusion, in 10.5, if I add a limit to the below query, the query optimizer does choose to use a key on the query:
SELECT * FROM cases_meta AS cm WHERE cm.`key` = 'PAN_NUMBER' AND cm.`value` = '6822' limit 5
ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 22.29661386, "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": 307098, "r_rows": 3341, "r_table_time_ms": 21.77044251, "r_other_time_ms": 0.516935887, "filtered": 100, "r_filtered": 0.149655792, "index_condition": "ddx_lab_801615.cm.`key` = 'PAN_NUMBER'", "attached_condition": "ddx_lab_801615.cm.`value` = '6822'" } } }
This substantially speeds up the query. This also seems kind of "hacky." Even if this was a reasonable way forward, it is not a versatile option. Particularly if I want to use the query in a sub select (which I do): "This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"
Ideas?
Thanks,
Mike
------------------------------ *From:* Michael Caplan <Michael.Caplan@henryscheinone.com> *Sent:* 30 July 2024 12:59 PM *To:* Michael Caplan via discuss <discuss@lists.mariadb.org> *Subject:* Query Performance Drops 10.3 -> 10.5 - Query Optimizer Refusing Index
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 -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
participants (2)
-
Guillaume Lefranc -
Michael Caplan