Percona performance issue vs MySQL

Question

Our server company advised we switch to Percona when setting up our new DB servers. So we're currently on Percona Server (GPL), Release 82.0 (version 5.6.36-82.0) , Revision 58e846a and there's one behavior I'm trying to wrap my head around that we definitely weren't experiencing before with MySql so I thought I'd reach out:

This is a query we perform fairly regularly to pull an article from our db

SELECT * FROM table_a a, table_b b WHERE a.id = b.id AND a.status_field = 'open' AND b.filter_field = 'no_filter' AND b.view_field = 'article' ORDER BY a.unixtimestamp DESC LIMIT 1

This used to complete very quickly but under Percona, the combination of the where conditions from table b and ordering from table a makes the whole query take ~3s. I don't fully understand this behaviour.

If I alter it to:

SELECT * FROM table_a a, table_b b WHERE a.id = b.id AND a.status_field = 'open' AND b.filter_field = 'no_filter' AND b.view_field = 'article' ORDER BY b.unixtimestamp DESC LIMIT 1

Then it completes very quickly (< 0.05s)

Is this sort of an expected behavior with Percona?

I just wanted to know before changing any db structure to compensate.

Edit:

For the explain, I simplified the query and it still has the same issue (id = entry_id):

Slow Query (1.5122389793):

SELECT * FROM table_a a, table_b b WHERE a.id = b.id AND b.special_filter = 'no_filter' ORDER BY a.id DESC LIMIT 1

Slow Query explain:

1 SIMPLE table_b ref PRIMARY,entry_id,special_filter special_filter 26 const 130733 Using where; Using temporary; Using filesort 1 SIMPLE table_a eq_ref PRIMARY PRIMARY 4 db_name.table_b.entry_id 1 Using index

Fast Query (0.0006549358):

SELECT * FROM table_a a, table_b b WHERE a.id = b.id AND b.special_filter = 'no_filter' ORDER BY b.id DESC LIMIT 1

Fast Query explain: 1 SIMPLE table_b ref PRIMARY,entry_id,special_filter special_filter 26 const 130733 Using where 1 SIMPLE table_a eq_ref PRIMARY PRIMARY 4 db_name.table_b.entry_id 1 Using index

I've tried to omit as much info from the table as possible for security reasons but if I'm grossly missing something I can add it back in

table_a:

Relevant Keys:

table_a 0 PRIMARY 1 entry_id A 321147 BTREE

Create table: table_a CREATE TABLE table_a ( entry_id int(10) unsigned NOT NULL AUTO_INCREMENT) ENGINE=InnoDB AUTO_INCREMENT=356198 DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1

table_b:

Relevant Keys: table_b 0 PRIMARY 1 entry_id A 261467 BTREE
table_b 1 entry_id 1 entry_id A 261467 BTREE
table_b 1 special_filter 1 special_filter A 14 8 BTREE

Create Table: table_b CREATE TABLE table_b ( entry_id int(10) unsigned NOT NULL DEFAULT '0', special_filter text NOT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1 Both tables have ~ 350k rows

Could you please further include information about the data types of the columns and indices on the tables? — morre
the status and filter field are both varchars and both indexed, the ids are int(10) and primary keys, the timestamps are int(10) the slow query uses "Using where; Using temporary; Using filesort" on the field_field sort, the fast query only uses "Using where" on the filter_field sort — JewrassicPark
No, this is not expected behavior with Percona. I'm guessing explain will give you more information on how the optimizer tries to run the query. It's to figure this out without DESC table_a; DESC table_b; EXPLAIN SELECT... — Zunderscore
I've added a bunch of stuff I had posted to the percona forum as well. — JewrassicPark
It seems like it may be the same issue experience here: dba.stackexchange.com/questions/53274/… — JewrassicPark

JewrassicPark JewrassicPark · Accepted Answer · 2017-08-01T13:49:30

It seems to be a mysql optimizer issue where it chooses not to join on the primary key under some conditions. Closely related to or the same as this issue: https://dba.stackexchange.com/questions/53274/mysql-innodb-issue-not-using-indexes-correctly-percona-5-6

Explicitly writing the query to use a STRAIGHT_JOIN with the tables in a specific order solves the issue. Writing USE INDEX(PRIMARY) after the JOIN keyword is easier though and doesn't rely on the table order.

Percona performance issue vs MySQL

Edit:

1 Answers