On 10/15/2012 7:15 PM, spameden wrote:
Thanks a lot for all your comments!
I did disable Query cache before testing with
set query_cache_type=OFF
for the current session.
I will report this to the MySQL bugs site later.
First. What are all of your logging settings?
SHOW GLOBAL
Will do.
mysql SHOW GLOBAL VARIABLES LIKE '%log%';
+-+-+
| Variable_name | Value
|
+-+-+
| back_log
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%';
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
2012/10/16 12:57 -0400, Michael Dykman
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.
Quote:
Functions that return the current date or time each are evaluated only
That's exactly what I thought when reading Michael's email, but tried
anyways, thanks for clarification :)
2012/10/16 h...@tbbs.net
2012/10/16 12:57 -0400, Michael Dykman
your now() statement is getting executed for every row on the select. try
ptting the phrase up front
as in:
set @ut=
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;
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
* Rows = 11 / 22 -- don't take the numbers too seriously; they are crude
approximations based on estimated cardinality.
* The 11 comes from the LIMIT -- therefore useless in judging the efficiency.
(The 22 may be 2*11; I don't know.)
* Run the EXPLAINs without LIMIT -- that will avoid the
Sorry, my previous e-mail was a test on MySQL-5.5.28 on an empty table.
Here is the MySQL-5.1 Percona testing table:
mysql select count(*) from send_sms_test;
+--+
| count(*) |
+--+
| 143879 |
+--+
1 row in set (0.03 sec)
Without LIMIT:
mysql desc select * from
Sorry, forgot to say:
mysql show variables like 'long_query_time%';
+-+---+
| Variable_name | Value |
+-+---+
| long_query_time | 10.00 |
+-+---+
1 row in set (0.00 sec)
It's getting in the log only due:
mysql
I don't fully understand Handler numbers, either. But note the vast difference
in Handler_read_next, as if the second test had to read (sequentially scan) a
lot more stuff (in the index or the data).
Summary:
INDEX(time, priority) -- slower; bigger Handler numbers; shorter key_len;
Ø My initial question was why MySQL logs it in the slow log if the query uses
an INDEX?
That _may_ be worth a bug report.
A _possible_ answer... EXPLAIN presents what the optimizer is in the mood for
at that moment. It does not necessarily reflect what it was in the mood for
when it ran
Thanks a lot for all your comments!
I did disable Query cache before testing with
set query_cache_type=OFF
for the current session.
I will report this to the MySQL bugs site later.
2012/10/16 Rick James rja...@yahoo-inc.com
**Ø **My initial question was why MySQL logs it in the slow log
14 matches
Mail list logo
