Slow query, yes, but it is such a simple table.

On Mon, 25 Jun 2007 09:56:37 -0400, FFMG
<FFMG.2sqlvv@no-mx.httppoint.com> wrote:

>Sorry, my bad for missing that one, I was half asleep :).

No offense, first time i didn't notice myself :).

subtenante <zzsubtenantezz@gmail.com> wrote:
> On Mon, 25 Jun 2007 16:41:53 +0200, Willem Bogaerts
> <w.bogaerts@kratz.maardanzonderditstuk.nl> wrote:
>
>>>> http://www.mysqlperformanceblog.com/...nalyzes-tools/
>
>>This does make sense also: A full table scan can become slow very
>>rapidly on "live data". It is nice that MySQL sees this is a very early
>>stage, before it becomes a real problem.

No need to guess. Indeed MySQL logs *all* full table scans to the slow
query log when log-queries-not-using-indexes is effective. Regardless
of how long the execution time is or how many rows there are.

The beforementioned patch contains another goodie:
min_examined_row_limit - set the minimum number of rows for a full
table scan to be logged into the slow query log. This can be used to
suppress false positives from log-queries-not-using-indexes.

> Full table scans are pretty common, a
> mysql -u ... -p... -e "SELECT * FROM blabla" > dummy.file
> on a table of 90000 rows, 5Mb, takes less than one second on my
> machine.

This query is not at all common. And on a already loaded database it
would probably take much longer, because of I/O contention.

> Besides i'm really not convinced MySQL is as smart as you say here :
> if it were, the link you give there wouldn't exist, for MySQL would
> already do the job without the patch.

Just FYI: this patch *is* from MySQL developers. It just didn't make it
into the product for various reasons.

[For the interested: the mentioned patch was quick'n dirty solution
for http://forge.mysql.com/worklog/task.php?id=1277 but has been
suspended in favour of a more complete query profiling solution]


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/