[MDEV-10600] Query Planner Choosing Wrong Index Created: 2016-08-19  Updated: 2016-09-20

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.16
Fix Version/s: 10.1

Type: Bug Priority: Major
Reporter: Andrew Arvay Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

MariaDB-10.1.16 - Compiled from source
Invision Power Board Software
CentOS 7
PHP 7

Attachments: PNG File forums_posts_indexes.png    

 Description   

I have some issues where the query planner is selecting the wrong index when running queries. The odd part is that it does not match what the EXPLAIN says for the same query in most cases.

Example:

SELECT pid FROM `forums_posts` WHERE topic_id=106927 AND (queued=0 OR queued=2) ORDER BY post_date asc LIMIT 0,25;

Attached a screenshot of the indexes on the table.

It should be using the first_post index, but it is not. When I run EXPLAIN on this query, it gives this:

1 	SIMPLE 	forums_posts 	index 	topic_id,queued,first_post 	post_date 	5 	NULL	320 	Using where

Here is the slow query log:

# Query_time: 7.583572  Lock_time: 0.000034  Rows_sent: 25  Rows_examined: 3044158
 
# Rows_affected: 0
 
SET timestamp=1471611190;
 
/*IPS\Content\_Item::_comments:1635*/ SELECT pid FROM `forums_posts` WHERE topic_id=106927 AND (queued=0 OR queued=2) ORDER BY post_date asc LIMIT 0,25;

The engine is clearly using the wrong index - there is no other reason it would be examining over 3 million rows and taking so long to finish. It's worth noting that when I run this query manually and include FORCE INDEX(first_post) it works correctly in 0.001 seconds or similar. Let me know if you need any other info or details!

Thanks.

 Comments   
Comment by Elena Stepanova [ 2016-08-24 ]

Please paste SHOW CREATE TABLE for the table, and attach your cnf file(s).

Comment by Andrew Arvay [ 2016-08-24 ] 

CREATE TABLE `forums_posts` (
 
 `pid` int(10) NOT NULL AUTO_INCREMENT,
 
 `append_edit` tinyint(1) DEFAULT '0',
 
 `edit_time` int(10) DEFAULT NULL,
 
 `author_id` mediumint(8) NOT NULL DEFAULT '0',
 
 `author_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
 `ip_address` varchar(46) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 
 `post_date` int(10) DEFAULT NULL,
 
 `post` mediumtext COLLATE utf8mb4_unicode_ci,
 
 `queued` tinyint(1) NOT NULL DEFAULT '0',
 
 `topic_id` int(10) NOT NULL DEFAULT '0',
 
 `new_topic` tinyint(1) DEFAULT '0',
 
 `edit_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
 `post_key` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
 
 `post_htmlstate` smallint(1) NOT NULL DEFAULT '0',
 
 `post_edit_reason` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 
 `post_bwoptions` int(10) unsigned NOT NULL DEFAULT '0',
 
 `pdelete_time` int(11) NOT NULL DEFAULT '0',
 
 `post_field_int` int(10) DEFAULT '0',
 
 `post_field_t1` text COLLATE utf8mb4_unicode_ci,
 
 `post_field_t2` text COLLATE utf8mb4_unicode_ci,
 
 PRIMARY KEY (`pid`),
 
 KEY `topic_id` (`topic_id`,`queued`,`post_date`,`author_id`),
 
 KEY `author_id` (`author_id`,`post_date`,`queued`),
 
 KEY `post_date` (`post_date`),
 
 KEY `ip_address` (`ip_address`),
 
 KEY `post_key` (`post_key`),
 
 KEY `queued` (`queued`,`post_date`),
 
 KEY `first_post` (`topic_id`,`post_date`)
 
) ENGINE=InnoDB AUTO_INCREMENT=6695233 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC


[client]
 
port            = 3306
 
socket          = /tmp/mysql.sock
 
 
 
[mysqld]
 
port            = 3306
 
socket          = /tmp/mysql.sock
 
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
 
skip-external-locking
 
max_connections = 300
 
key_buffer_size = 16M
 
max_allowed_packet = 4M
 
table_open_cache = 4096
 
table_definition_cache = 4096
 
sort_buffer_size = 2M
 
net_buffer_length = 8K
 
read_buffer_size = 2M
 
read_rnd_buffer_size = 2M
 
myisam_sort_buffer_size = 8M
 
tmp_table_size = 64M
 
max_heap_table_size = 64M
 
join_buffer_size = 2M
 
#query_cache_type = 1
 
#query_cache_size = 32M
 
#query_cache_limit = 512K
 
#query_cache_min_res_unit = 1K
 
open_files_limit = 8192
 
thread_cache_size = 6
 
#skip-networking
 
server-id       = 1
 
slow_query_log = 1
 
slow_query_log_file = /db/mysql/slowqueries.log
 
tmpdir = /dev/shm
 
long_query_time = 0.5
 
 
 
innodb_data_home_dir = /db/mysql
 
innodb_data_file_path = ibdata1:10M:autoextend
 
innodb_log_group_home_dir = /db/mysql
 
# You can set .._buffer_pool_size up to 50 - 80 %
 
# of RAM but beware of setting memory usage too high
 
innodb_buffer_pool_size = 3G
 
# Set .._log_file_size to 25 % of buffer pool size
 
innodb_log_file_size = 768M
 
innodb_log_buffer_size = 8M
 
innodb_flush_log_at_trx_commit = 0
 
innodb_lock_wait_timeout = 50
 
innodb_ft_min_token_size = 1
 
innodb_file_format = BARRACUDA
 
innodb_large_prefix
 
innodb_buffer_pool_dump_pct = 80
 
#innodb_force_recovery = 1
 
 
 
innodb_flush_method=O_DIRECT
 
innodb_thread_concurrency=0
 
innodb_read_io_threads=64
 
innodb_write_io_threads=64
 
innodb_io_capacity=3000
 
#innodb_flush_neighbor_pages = none # MariaDB 5.5
 
innodb_flush_neighbors=0
 
 
 
[mysqldump]
 
quick
 
max_allowed_packet = 16M
 
 
 
[mysql]
 
no-auto-rehash
 
 
 
[myisamchk]
 
key_buffer_size = 20M
 
sort_buffer_size = 20M
 
read_buffer = 2M
 
write_buffer = 2M
 
 
 
[mysqlhotcopy]
 
interactive-timeout

Comment by Elena Stepanova [ 2016-08-31 ]

I'm not getting this plan on my artificial data. 

MariaDB [test]> show index in forums_posts;
 
+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| Table        | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 
+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| forums_posts |          0 | PRIMARY    |            1 | pid         | A         |     4929278 |     NULL | NULL   |      | BTREE      |         |               |
 
| forums_posts |          1 | topic_id   |            1 | topic_id    | A         |      547697 |     NULL | NULL   |      | BTREE      |         |               |
 
| forums_posts |          1 | topic_id   |            2 | queued      | A         |      616159 |     NULL | NULL   |      | BTREE      |         |               |
 
| forums_posts |          1 | topic_id   |            3 | post_date   | A         |     4929278 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| forums_posts |          1 | topic_id   |            4 | author_id   | A         |     4929278 |     NULL | NULL   |      | BTREE      |         |               |
 
| forums_posts |          1 | author_id  |            1 | author_id   | A         |       38509 |     NULL | NULL   |      | BTREE      |         |               |
 
| forums_posts |          1 | author_id  |            2 | post_date   | A         |     4929278 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| forums_posts |          1 | author_id  |            3 | queued      | A         |     4929278 |     NULL | NULL   |      | BTREE      |         |               |
 
| forums_posts |          1 | post_date  |            1 | post_date   | A         |     4929278 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| forums_posts |          1 | ip_address |            1 | ip_address  | A         |       31396 |     NULL | NULL   |      | BTREE      |         |               |
 
| forums_posts |          1 | post_key   |            1 | post_key    | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
 
| forums_posts |          1 | queued     |            1 | queued      | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
 
| forums_posts |          1 | queued     |            2 | post_date   | A         |     4929278 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| forums_posts |          1 | first_post |            1 | topic_id    | A         |      547697 |     NULL | NULL   |      | BTREE      |         |               |
 
| forums_posts |          1 | first_post |            2 | post_date   | A         |     4929278 |     NULL | NULL   | YES  | BTREE      |         |               |
 
+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
15 rows in set (0.00 sec)


MariaDB [test]> explain SELECT pid FROM `forums_posts` WHERE topic_id=106927 AND (queued=0 OR queued=2) ORDER BY post_date asc LIMIT 0,25;
 
+------+-------------+--------------+-------+----------------------------+----------+---------+------+------+------------------------------------------+
 
| id   | select_type | table        | type  | possible_keys              | key      | key_len | ref  | rows | Extra                                    |
 
+------+-------------+--------------+-------+----------------------------+----------+---------+------+------+------------------------------------------+
 
|    1 | SIMPLE      | forums_posts | range | topic_id,queued,first_post | topic_id | 5       | NULL |   26 | Using where; Using index; Using filesort |
 
+------+-------------+--------------+-------+----------------------------+----------+---------+------+------+------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select @@version;
 
+-----------------+
 
| @@version       |
 
+-----------------+
 
| 10.1.16-MariaDB |
 
+-----------------+
 
1 row in set (0.00 sec)

I'll pass it over to optimizer experts to take a look at it

Generated at Thu Feb 08 07:43:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.