List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:October 16 2012 4:57pm
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  
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