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 <mdyk...@gmail.com> > 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" <spame...@gmail.com> 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.gr...@oracle.com> > > > On 10/15/2012 7:15 PM, spameden wrote: > > > >> T... > >
