MariaDB [testdb]> set local max_statement_time=10;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [testdb]> SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard') WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
|
|
ERROR 1969 (70100): Query execution was interrupted (max_statement_time exceeded)
|
MariaDB [testdb]>
|
MariaDB [testdb]> explain SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard') WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
|
+------+-------------+-------+------------+-----------------------+----------------+---------+---------------------------+-------------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+-----------------------+----------------+---------+---------------------------+-------------+---------------------------------+
|
| 1 | SIMPLE | i | ALL | PRIMARY,date,type_id | NULL | NULL | NULL | 492879 | Using where |
|
| 1 | SIMPLE | ir | ref | invoice_idRec | invoice_idRec | 4 | testdb.i.idRec | 1 | Using where |
|
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | testdb.ir.product_id | 1 | Using index |
|
| 1 | SIMPLE | a | ref|filter | obj_id,obj_table,name | obj_table|name | 107|152 | const | 301592 (0%) | Using where; Using rowid filter |
|
+------+-------------+-------+------------+-----------------------+----------------+---------+---------------------------+-------------+---------------------------------+
|
4 rows in set (0.00 sec)
|
|
MariaDB [testdb]> analyze table temp_attributes PERSISTENT FOR COLUMNS (id, obj_id, obj_table, name, value_text, value_int)
|
-> INDEXES (PRIMARY,obj_id, obj_table, name, value_text,value_int);
|
+-----------------------------+---------+----------+-----------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+-----------------------------+---------+----------+-----------------------------------------+
|
| testdb.temp_attributes | analyze | status | Engine-independent statistics collected |
|
| testdb.temp_attributes | analyze | status | OK |
|
+-----------------------------+---------+----------+-----------------------------------------+
|
2 rows in set (3.20 sec)
|
|
MariaDB [testdb]> SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard') WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
|
+----------+
|
| count(1) |
|
+----------+
|
| 92819 |
|
+----------+
|
1 row in set (0.93 sec)
|
|
MariaDB [testdb]> explain SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard') WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
|
+------+-------------+-------+------------+-----------------------+---------------+---------+---------------------------+--------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+-----------------------+---------------+---------+---------------------------+--------+---------------------------------+
|
Query OK, 1 row affected (0.01 sec)
|
|
MariaDB [testdb]> delete from mysql.column_stats;
|
Query OK, 6 rows affected (0.00 sec)
|
|
MariaDB [testdb]> delete from mysql.index_stats;
|
Query OK, 11 rows affected (0.01 sec)
|
|
MariaDB [testdb]> SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ignore index (name) ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard') WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
|
|
+----------+
|
| count(1) |
|
+----------+
|
| 92819 |
|
+----------+
|
1 row in set (1.45 sec)
|
|
MariaDB [testdb]>
|
MariaDB [testdb]> explain SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a IGNORE INDEX (name) ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard') WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
|
+------+-------------+-------+--------+----------------------+---------------+---------+---------------------------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+----------------------+---------------+---------+---------------------------+--------+-------------+
|
| 1 | SIMPLE | i | ALL | PRIMARY,date,type_id | NULL | NULL | NULL | 492879 | Using where |
|
| 1 | SIMPLE | ir | ref | invoice_idRec | invoice_idRec | 4 | testdb.i.idRec | 1 | Using where |
|
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | testdb.ir.product_id | 1 | Using index |
|
| 1 | SIMPLE | a | ref | obj_id,obj_table | obj_id | 4 | testdb.p.id | 2 | Using where |
|
+------+-------------+-------+--------+----------------------+---------------+---------+---------------------------+--------+-------------+
|
4 rows in set (0.00 sec)
|
|
MariaDB [testdb]> show variables like 'version';
|
+---------------+--------------------+
|
| Variable_name | Value |
|
+---------------+--------------------+
|
| version | 10.4.8-MariaDB-log |
|
+---------------+--------------------+
|
1 row in set (0.00 sec)
|
|
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [testdb]> show create table temp_attributes;
|
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| temp_attributes | CREATE TABLE `temp_attributes` (
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
`obj_id` int(11) NOT NULL,
|
`obj_table` varchar(35) COLLATE utf8_unicode_ci NOT NULL,
|
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
|
`type` enum('int','text','double') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'text',
|
`value_text` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
|
`value_int` int(11) NOT NULL,
|
`value_double` double NOT NULL,
|
PRIMARY KEY (`id`),
|
KEY `obj_id` (`obj_id`),
|
KEY `obj_table` (`obj_table`),
|
KEY `value_text` (`value_text`),
|
KEY `value_int` (`value_int`),
|
KEY `name` (`name`)
|
) ENGINE=InnoDB AUTO_INCREMENT=1700849 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci STATS_PERSISTENT=1 STATS_AUTO_RECALC=1 STATS_SAMPLE_PAGES=50000 |
|
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [testdb]> show variables like '%persistent%';
|
+--------------------------------------+-------+
|
| Variable_name | Value |
|
+--------------------------------------+-------+
|
| innodb_stats_persistent | ON |
|
| innodb_stats_persistent_sample_pages | 30 |
|
+--------------------------------------+-------+
|
2 rows in set (0.00 sec)
|
|
MariaDB [testdb]> show indexes from temp_attributes;
|
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| temp_attributes | 0 | PRIMARY | 1 | id | A | 967119 | NULL | NULL | | BTREE | | |
|
| temp_attributes | 1 | obj_id | 1 | obj_id | A | 967119 | NULL | NULL | | BTREE | | |
|
| temp_attributes | 1 | obj_table | 1 | obj_table | A | 48 | NULL | NULL | | BTREE | | |
|
| temp_attributes | 1 | value_text | 1 | value_text | A | 80593 | NULL | NULL | | BTREE | | |
|
| temp_attributes | 1 | value_int | 1 | value_int | A | 7992 | NULL | NULL | | BTREE | | |
|
| temp_attributes | 1 | name | 1 | name | A | 376 | NULL | NULL | | BTREE | | |
|
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
6 rows in set (0.00 sec)
|
Bug
Major
MDEV-16188 Use in-memory PK filters built from range index scans
