Not the answer you need?
Register and ask your own question!

Join and order performance across two tables after switching from MySql

jewrassicparkjewrassicpark EntrantCurrent User Role Beginner
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.

Comments

  • mirfanmirfan Database Administrator Inactive User Role Beginner
    Can you please provide EXPLAIN output for both queries along with table definitions of tables involved in query.
    mysql> SHOW CREATE TABLE tablename\G
    mysql> SHOW KEYS FROM tablename\G
  • jewrassicparkjewrassicpark Entrant Current User Role Beginner
    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:



    id select_type table type possible_keys key key_len ref rows Extra

    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:


    id select_type table type possible_keys key key_len ref rows Extra

    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 Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment



    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 Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment



    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 `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



    Both tables have ~ 350k rows
  • mirfanmirfan Database Administrator Inactive User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.