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 < [email protected]> 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 <[email protected]> > *Sent:* 30 July 2024 3:45 PM > *To:* Michael Caplan via discuss <[email protected]> > *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 <[email protected]> > *Sent:* 30 July 2024 12:59 PM > *To:* Michael Caplan via discuss <[email protected]> > *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 -- [email protected] > To unsubscribe send an email to [email protected] >
_______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
