Execution plan

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';

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)