Announcement

Announcement Module
Collapse
No announcement yet.

percona mysql5.5.29 index on timestamp will not use

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • percona mysql5.5.29 index on timestamp will not use

    I have a table defination like this:

    CREATE TABLE `t_pearl_finance_detail_test` (
    `fin_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `partner_id` int(11) NOT NULL,
    `createtime` timestamp NULL,
    PRIMARY KEY (`fin_id`),
    KEY `createtime` (`createtime`),
    KEY `partner_id_2` (`partner_id`,`createtime`)
    ) ENGINE=InnoDB AUTO_INCREMENT=36023268 DEFAULT CHARSET=utf8

    on percona mysql5.5.29

    mysql> explain select * from t_pearl_finance_detail_test where createtime='2013-03-28';
    +----+-------------+-----------------------------+------+--- ------------+------+---------+------+---------+------------- +
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-----------------------------+------+--- ------------+------+---------+------+---------+------------- +
    | 1 | SIMPLE | t_pearl_finance_detail_test | ALL | NULL | NULL | NULL | NULL | 1254215 | Using where |
    +----+-------------+-----------------------------+------+--- ------------+------+---------+------+---------+------------- +

    Execution results

    mysql> select * from t_pearl_finance_detail_test where createtime='2013-03-28';
    Empty set (2.36 sec)

    on mysql5.1.48

    mysql> explain select * from t_pearl_finance_detail_test where createtime='2013-03-28';
    +----+-------------+-----------------------------+------+--- ------------+------------+---------+-------+------+--------- ----+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-----------------------------+------+--- ------------+------------+---------+-------+------+--------- ----+
    | 1 | SIMPLE | t_pearl_finance_detail_test | ref | createtime | createtime | 5 | const | 1 | Using where |
    +----+-------------+-----------------------------+------+--- ------------+------------+---------+-------+------+--------- ----+

    Execution results

    mysql> select * from t_pearl_finance_detail_test where createtime='2013-03-28';
    Empty set (0.00 sec)

    on percona mysql5.5.29 force index dot not work

    mysql> explain select * from t_pearl_finance_detail_test force index(createtime) where createtime='2013-03-28';
    +----+-------------+-----------------------------+------+--- ------------+------+---------+------+---------+------------- +
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-----------------------------+------+--- ------------+------+---------+------+---------+------------- +
    | 1 | SIMPLE | t_pearl_finance_detail_test | ALL | NULL | NULL | NULL | NULL | 1254215 | Using where |
    +----+-------------+-----------------------------+------+--- ------------+------+---------+------+---------+------------- +

    on percona mysql5.5.29 force index with from_unixtime work fine

    mysql> select unix_timestamp('2013-03-28');
    +------------------------------+
    | unix_timestamp('2013-03-28') |
    +------------------------------+
    | 1364400000 |
    +------------------------------+

    mysql> explain select * from t_pearl_finance_detail_test force index(createtime) where createtime=from_unixtime(1364400000);
    +----+-------------+-----------------------------+------+--- ------------+------------+---------+-------+------+--------- ----+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-----------------------------+------+--- ------------+------------+---------+-------+------+--------- ----+
    | 1 | SIMPLE | t_pearl_finance_detail_test | ref | createtime | createtime | 5 | const | 1 | Using where |
    +----+-------------+-----------------------------+------+--- ------------+------------+---------+-------+------+--------- ----+

    Execution results

    mysql> select * from t_pearl_finance_detail_test force index(createtime) where createtime=from_unixtime(1364400000);
    Empty set (0.00 sec)


    on percona mysql5.5.29 with from_unixtime work fine

    mysql> explain select * from t_pearl_finance_detail_test where createtime=from_unixtime(1364400000);
    +----+-------------+-----------------------------+------+--- ------------+------------+---------+-------+------+--------- ----+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-----------------------------+------+--- ------------+------------+---------+-------+------+--------- ----+
    | 1 | SIMPLE | t_pearl_finance_detail_test | ref | createtime | createtime | 5 | const | 1 | Using where |
    +----+-------------+-----------------------------+------+--- ------------+------------+---------+-------+------+--------- ----+


    my question is :
    1.why on percona mysql5.5.29 force index do not work?
    2.I think the explain result on mysql5.1 is good, why percona should use from_unixtime?

  • #2
    Seems that you are hitting this bug:

    http://bugs.mysql.com/bug.php?id=64998

    Can you try to run the same query on PS with collation_connection=utf8_general_ci and collation_connection=utf8_unicode_ci ?

    Comment


    • #3
      The default collation_connection like this:

      mysql> show variables like "%collation_connection%";
      +----------------------+-----------------+
      | Variable_name | Value |
      +----------------------+-----------------+
      | collation_connection | utf8_unicode_ci |
      +----------------------+-----------------+

      mysql> set session collation_connection='utf8_general_ci';
      Query OK, 0 rows affected (0.04 sec)

      mysql> show variables like "%collation_connection%";
      +----------------------+-----------------+
      | Variable_name | Value |
      +----------------------+-----------------+
      | collation_connection | utf8_general_ci |
      +----------------------+-----------------+

      mysql> explain select * from t_pearl_finance_detail_test where createtime='2013-03-28';
      +----+-------------+-----------------------------+------+--- ------------+------------+---------+-------+------+--------- ----+
      | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
      +----+-------------+-----------------------------+------+--- ------------+------------+---------+-------+------+--------- ----+
      | 1 | SIMPLE | t_pearl_finance_detail_test | ref | createtime | createtime | 5 | const | 1 | Using where |
      +----+-------------+-----------------------------+------+--- ------------+------------+---------+-------+------+--------- ----+

      On PS with collation_connection=utf8_general_ci work fine, just like the bug you list above.

      The solution is:
      1.set collation_connection=utf8_general_ci
      2.from_unixtime

      and others?

      thanks for your reply.

      Comment


      • #4
        Ok, seems that your server is affected by that bug. There is no fix for that bug on MySQL or Percona Server, so the workarounds in this case are the two you have specified.

        Comment


        • #5
          Thanks.

          Comment

          Working...
          X