Join and order performance across two tables after switching from MySql

MySQL & MariaDB Other MySQL® Questions
1

Hi,

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, 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.

Thanks.

2

Can you please provide EXPLAIN output for both queries along with table definitions of tables involved in query.

3

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:

[TABLE]
[TR]
id select_type table type possible_keys key key_len ref rows Extra [/TR]
[TR]
[TD]1[/TD]
[TD]SIMPLE[/TD]
[TD]table_b[/TD]
[TD]ref[/TD]
[TD]PRIMARY,entry_id,special_filter[/TD]
[TD]special_filter[/TD]
[TD]26[/TD]
[TD]const[/TD]
[TD]130733[/TD]
[TD]Using where; Using temporary; Using filesort[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SIMPLE[/TD]
[TD]table_a[/TD]
[TD]eq_ref[/TD]
[TD]PRIMARY[/TD]
[TD]PRIMARY[/TD]
[TD]4[/TD]
[TD]db_name.table_b.entry_id[/TD]
[TD]1[/TD]
[TD]Using index[/TD]
[/TR]
[/TABLE]

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:
[TABLE]
[TR]
id select_type table type possible_keys key key_len ref rows Extra [/TR]
[TR]
[TD]1[/TD]
[TD]SIMPLE[/TD]
[TD]table_b[/TD]
[TD]ref[/TD]
[TD]PRIMARY,entry_id,special_filter[/TD]
[TD]special_filter[/TD]
[TD]26[/TD]
[TD]const[/TD]
[TD]130733[/TD]
[TD]Using where[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SIMPLE[/TD]
[TD]table_a[/TD]
[TD]eq_ref[/TD]
[TD]PRIMARY[/TD]
[TD]PRIMARY[/TD]
[TD]4[/TD]
[TD]db_name.table_b.entry_id[/TD]
[TD]1[/TD]
[TD]Using index[/TD]
[/TR]
[/TABLE]

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]
[TR]
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment [/TR]
[TR]
[/TR]
[TR]
[TD]table_a[/TD]
[TD]0[/TD]
[TD]PRIMARY[/TD]
[TD]1[/TD]
[TD]entry_id[/TD]
[TD]A[/TD]
[TD]321147[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BTREE[/TD]
[TD] [/TD]
[/TR]
[/TABLE]

Create table:
[TABLE]
[TR]
[TD]table_a [/TD]
[TD]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[/TD]
[/TR]
[/TABLE]

table_b:

Relevant Keys:
[TABLE]
[TR]
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment [/TR]
[TR]
[/TR]
[TR]
[TD]table_b[/TD]
[TD]0[/TD]
[TD]PRIMARY[/TD]
[TD]1[/TD]
[TD]entry_id[/TD]
[TD]A[/TD]
[TD]261467[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BTREE[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]table_b[/TD]
[TD]1[/TD]
[TD]entry_id[/TD]
[TD]1[/TD]
[TD]entry_id[/TD]
[TD]A[/TD]
[TD]261467[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BTREE[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]table_b[/TD]
[TD]1[/TD]
[TD]special_filter[/TD]
[TD]1[/TD]
[TD]special_filter[/TD]
[TD]A[/TD]
[TD]14[/TD]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BTREE[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[/TABLE]

Create Table:
[TABLE]
[TR]
[TD]table_b[/TD]
[TD]CREATE TABLE exp_channel_data ( entry_id int(10) unsigned NOT NULL DEFAULT ‘0’, special_filter text NOT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1[/TD]
[/TR]
[/TABLE]

Both tables have ~ 350k rows

4

Table definitions are not complete as you mentioned so hard to check but from quick check it’s clear that second query ORDER BY doesn’t requires “Using temporary; Using filesort” as per EXPLAIN hence faster.