List:General Discussion« Previous MessageNext Message »
From:spameden Date:October 15 2012 9:17pm
Subject:Re: mysql logs query with indexes used to the slow-log and not
logging if there is index in reverse order
View as plain text  
Hi, I've just checked on MySQL-5.5.28

it acts absolutely same.

I need to use (priority,time) KEY instead of (time, priority) because query
results in better performance.

With first key used there is no need to sort at all, whilst if using latter:
mysql> *desc select * from send_sms_test FORCE INDEX (time_priority) where
time<=UNIX_TIMESTAMP(NOW()) order by priority limit 0,13;*
+----+-------------+---------------+-------+---------------+---------------+---------+------+-------+-----------------------------+
| id | select_type | table         | type  | possible_keys | key
| key_len | ref  | rows  | Extra                       |
+----+-------------+---------------+-------+---------------+---------------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | send_sms_test | range | time_priority | time_priority
| 8       | NULL | 73920 | Using where; *Using filesort* |
+----+-------------+---------------+-------+---------------+---------------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)

It uses filesort and results in a worser performance...

Any suggestions ? Should I submit a bug?

2012/10/16 spameden <spameden@stripped>

> Hi, list.
>
> Sorry for the long subject, but I'm really interested in solving this and
> need a help:
>
> I've got a table:
>
> mysql> show create table send_sms_test;
>
>
> +---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | Table         | Create
> Table
> |
>
>
> +---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | send_sms_test | CREATE TABLE `send_sms_test` (
>   `sql_id` bigint(20) NOT NULL AUTO_INCREMENT,
>   `momt` enum('MO','MT') DEFAULT NULL,
>   `sender` varchar(20) DEFAULT NULL,
>   `receiver` varchar(20) DEFAULT NULL,
>   `udhdata` blob,
>   `msgdata` text,
>   `time` bigint(20) NOT NULL,
>   `smsc_id` varchar(255) DEFAULT 'main',
>   `service` varchar(255) DEFAULT NULL,
>   `account` varchar(255) DEFAULT NULL,
>   `id` bigint(20) DEFAULT NULL,
>   `sms_type` tinyint(1) DEFAULT '2',
>   `mclass` bigint(20) DEFAULT NULL,
>   `mwi` bigint(20) DEFAULT NULL,
>   `coding` bigint(20) DEFAULT NULL,
>   `compress` bigint(20) DEFAULT NULL,
>   `validity` bigint(20) DEFAULT NULL,
>   `deferred` bigint(20) DEFAULT NULL,
>   `dlr_mask` bigint(20) DEFAULT NULL,
>   `dlr_url` varchar(255) DEFAULT NULL,
>   `pid` bigint(20) DEFAULT NULL,
>   `alt_dcs` bigint(20) DEFAULT NULL,
>   `rpi` bigint(20) DEFAULT NULL,
>   `charset` varchar(255) DEFAULT NULL,
>   `boxc_id` varchar(255) DEFAULT NULL,
>   `binfo` varchar(255) DEFAULT NULL,
>   `meta_data` text,
>   `task_id` bigint(20) DEFAULT NULL,
>   `msgid` bigint(20) DEFAULT NULL,
>   `priority` int(3) unsigned NOT NULL DEFAULT '500',
>   PRIMARY KEY (`sql_id`),
>   KEY `task_id` (`task_id`),
>   KEY `receiver` (`receiver`),
>   KEY `msgid` (`msgid`),
>   KEY `priority_time` (`priority`,`time`)
> ) ENGINE=InnoDB AUTO_INCREMENT=7806318 DEFAULT CHARSET=utf8
>
> Slow-queries turned on with an option:
> | log_queries_not_using_indexes | ON    |
>
> mysqld --version
> mysqld  Ver 5.1.65-rel14.0 for debian-linux-gnu on x86_64 ((Percona Server
> (GPL), 14.0, Revision 475))
>
> If I check with EXPLAIN MySQL says it would use the index:
> mysql> *desc select * from send_sms_test where
> time<=UNIX_TIMESTAMP(NOW()) order by priority limit 0,11;*
>
>
> +----+-------------+---------------+-------+---------------+---------------+---------+------+------+-------------+
> | id | select_type | table         | type  | possible_keys | key
> | key_len | ref  | rows | Extra       |
>
>
> +----+-------------+---------------+-------+---------------+---------------+---------+------+------+-------------+
> |  1 | SIMPLE      | send_sms_test | index | NULL          | priority_time
> | 12      | NULL |  * 11* | Using where |
>
>
> +----+-------------+---------------+-------+---------------+---------------+---------+------+------+-------------+
> 1 row in set (0.00 sec)
>
> But If I issue the query I see in the mysql-slow.log:
> select * from send_sms_test where time<=UNIX_TIMESTAMP(NOW()) order by
> priority limit 0,11;
>
> If I do create INDEX time,priority (in reverse order instead of
> priority,time) I get still the same usage of priority_time key with the
> same length, but rows now are doubled):
> mysql> *create index time_priority ON send_sms_test (time,priority);*
> Query OK, 0 rows affected (0.67 sec)
> Records: 0  Duplicates: 0  Warnings: 0
>
> mysql> *desc select * from send_sms_test where
> time<=UNIX_TIMESTAMP(NOW()) order by priority limit 0,11;*
>
>
> +----+-------------+---------------+-------+---------------+---------------+---------+------+------+-------------+
> | id | select_type | table         | type  | possible_keys | key
> | key_len | ref  | rows | Extra       |
>
>
> +----+-------------+---------------+-------+---------------+---------------+---------+------+------+-------------+
> |  1 | SIMPLE      | send_sms_test | index | time_priority | priority_time
> | 12      | NULL |   *22* | Using where |
>
>
> +----+-------------+---------------+-------+---------------+---------------+---------+------+------+-------------+
>
> And if both indexes created I do not have anymore this query in the
> slow-log.
>
> Of course If I disable log_queries_not_using_indexes I get none of the
> queries.
>
> So is it a bug inside Percona's implementation or it's generally MySQL
> behavior?
>
> Thanks
>

Thread
mysql logs query with indexes used to the slow-log and not logging ifthere is index in reverse orderspameden15 Oct
  • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden15 Oct
  • RE: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderRick James15 Oct
    • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden15 Oct
      • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden15 Oct
        • RE: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderRick James15 Oct
          • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden15 Oct
            • Re: mysql logs query with indexes used to the slow-log and not loggingif there is index in reverse orderShawn Green16 Oct
              • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden16 Oct
                • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderMichael Dykman16 Oct
                  • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden16 Oct
                  • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderhsv16 Oct
                    • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden16 Oct
      • RE: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderRick James15 Oct