List:General Discussion« Previous MessageNext Message »
From:spameden Date:October 16 2012 6:54pm
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  
Interesting thought, but I get the same result.

# Query_time: 0.001769  Lock_time: 0.001236 Rows_sent: 0  Rows_examined: 0
use kannel;
SET timestamp=1350413592;
select * from send_sms FORCE INDEX (priority_time) where time<=@ut order by
priority limit 0,11;

the MySQL i'm using is 5.5.28 from dotdeb.org, pretty sure it's close to
the original except packaging scripts.

I will check this on the release from MySQL site and report back.

Thanks to all.

2012/10/16 Michael Dykman <mdykman@stripped>

> your now() statement is getting executed for every row on the select.  try
> ptting the phrase up front
> as in:
> set @ut= unix_timestamp(now())
> and then use that in your statement.
>
> On 2012-10-16 8:42 AM, "spameden" <spameden@stripped> wrote:
>
> Will do.
>
> mysql>  SHOW GLOBAL VARIABLES LIKE '%log%';
>
> +-----------------------------------------+---------------------------------+
> | Variable_name                           | Value
> |
>
> +-----------------------------------------+---------------------------------+
> | back_log                                | 50
> |
> | binlog_cache_size                       | 32768
> |
> | binlog_direct_non_transactional_updates | OFF
> |
> | binlog_format                           | MIXED
> |
> | expire_logs_days                        | 5
> |
> | general_log                             | OFF
> |
> | general_log_file                        | /var/run/mysqld/mysqld.log
> |
> | innodb_flush_log_at_trx_commit          | 2
> |
> | innodb_flush_log_at_trx_commit_session  | 3
> |
> | innodb_locks_unsafe_for_binlog          | OFF
> |
> | innodb_log_block_size                   | 512
> |
> | innodb_log_buffer_size                  | 8388608
> |
> | innodb_log_file_size                    | 2145386496
> |
> | innodb_log_files_in_group               | 2
> |
> | innodb_log_group_home_dir               | ./
> |
> | innodb_mirrored_log_groups              | 1
> |
> | innodb_overwrite_relay_log_info         | OFF
> |
> | log                                     | OFF
> |
> | log_bin                                 | ON
> |
> | log_bin_trust_function_creators         | ON
> |
> | log_bin_trust_routine_creators          | ON
> |
> | log_error                               | /var/log/mysql-error.log
> |
> | log_output                              | FILE
> |
> | log_queries_not_using_indexes           | ON
> |
> | log_slave_updates                       | OFF
> |
> | log_slow_admin_statements               | OFF
> |
> | log_slow_filter                         |
> |
> | log_slow_queries                        | ON
> |
> | log_slow_rate_limit                     | 1
> |
> | log_slow_slave_statements               | OFF
> |
> | log_slow_sp_statements                  | ON
> |
> | log_slow_timestamp_every                | OFF
> |
> | log_slow_verbosity                      | microtime
> |
> | log_warnings                            | 1
> |
> | max_binlog_cache_size                   | 18446744073709547520
> |
> | max_binlog_size                         | 104857600
> |
> | max_relay_log_size                      | 0
> |
> | relay_log                               | /var/log/mysql/mysqld-relay-bin
> |
> | relay_log_index                         |
> |
> | relay_log_info_file                     | relay-log.info
> |
> | relay_log_purge                         | ON
> |
> | relay_log_space_limit                   | 0
> |
> | slow_query_log                          | ON
> |
> | slow_query_log_file                     | /var/log/mysql/mysql-slow.log
> |
> | slow_query_log_microseconds_timestamp   | OFF
> |
> | sql_log_bin                             | ON
> |
> | sql_log_off                             | OFF
> |
> | sql_log_update                          | ON
> |
> | suppress_log_warning_1592               | OFF
> |
> | sync_binlog                             | 0
> |
> | use_global_log_slow_control             | none
> |
>
> +-----------------------------------------+---------------------------------+
> 51 rows in set (0.01 sec)
>
> Here is full output, but writing happens ONLY if
> log_queries_not_using_indexes turned ON.
>
> Query takes:
> # Query_time: 0.291280  Lock_time: 0.000050  Rows_sent: 0  Rows_examined:
> 133876  Rows_affected: 0  Rows_read: 1
> # Bytes_sent: 1775  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
> # InnoDB_trx_id: F229398
> SET timestamp=1350389078;
> SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id,
> service, account, id, sms_type, mclass, mwi, coding, compress, validity,
> deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo,
> meta_data, task_id, msgid FROM send_sms_test FORCE INDEX (priority_time)
> WHERE time <= UNIX_TIMESTAMP(NOW()) ORDER by priority LIMIT 0,50;
>
>
>
> 2012/10/16 Shawn Green <shawn.l.green@stripped>
>
> > On 10/15/2012 7:15 PM, spameden wrote:
> >
> >> T...
>
>

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