MySQL Query Optimizer is Silly

[Ghan]

Greetings!
 

So I've been doing some looking around at our board's performance and one of the things I've noticed comes from the slow query log. I see queries like this appearing frequently in the slow query log:

 

SELECT msg_id FROM `core_message_posts` WHERE msg_topic_id=1269 ORDER BY msg_date desc LIMIT 0,1;

 

When I run an EXPLAIN against this query, the engine returns a very reasonable result that makes me wonder why it is appearing in the slow log (> 10 seconds needed for execution).

 

 

However, the slow log tells a completely different story:

 

# Query_time: 11.201518  Lock_time: 0.000019  Rows_sent: 1  Rows_examined: 4144139

4144139 rows??

 

What is going on here? Well, what's going on is that MySQL is being silly and making the wrong decision on which index to use. It is trying to use the DATE index before using the TOPIC ID index. This means that it ends up getting a sorted index of the table by date, then having to do a full table scan (or close to it) to find the topics of interest out of the list based on the topic id.

If I explicitly instruct MySQL to use the correct index, it works fine:

 

 

This is probably only noticeable because we have so many posts, but noticeable it is, because queries that take 10+ seconds to execute aren't nice at all. I might be able to fix this by creating a new index on both columns simultaneously since the existing indices are only on single columns, but I'm not enough of an expert in the MySQL query planner to know for sure if this would work.

Is there another way I can get MySQL to realize it's choosing the wrong index? Does anyone know what part of the code generates this query? I'm quite new to IPB so I don't really know anything about the code base. It could be that simply adding FORCE INDEX would be the best fix, but I'd prefer not to be so heavy-handed as that. Thanks for any insight or advice!

[RevengeFNF]

I just made the test against MariaDB 10.1.16 and its working fine here. So its something related to MySQL 5.7.

Are you changing values for the Query Optimizer in your my.cnf?

PS: You can open a bug report and a IPS Staff member will look at it.

I also did have a similar problem in the optimizer in MariaDB a few weeks ago. I opened a bug report with MariaDB and they fixed it: https://jira.mariadb.org/browse/MDEV-10325

[Ghan]

I'm now using MariaDB 10.1.16, so that's very interesting. I don't have anything in my.cnf that I know to be changing the query planner. I do have quite a few directives in there, but nothing that appears to be changing the behavior of the planner.

[RevengeFNF]

1 minute ago, Ghan said:

I'm now using MariaDB 10.1.16, so that's very interesting. I don't have anything in my.cnf that I know to be changing the query planner. I do have quite a few directives in there, but nothing that appears to be changing the behavior of the planner.

If you are using MariaDB, open a bug report in their Jira like i did. They will look at it to find the reason why the query planner is not making the best decision.