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

Hi,

This is what I get from my slow log.

# Time: 070625 8:29:06
# User@Host: xxx @ yyy
# Query_time: 0 Lock_time: 0 Rows_sent: 14 Rows_examined: 14
SELECT myvalueA, myvalueB FROM mytable;

But those are my config values needed by all the pages, so what should
I change to make it more efficient?

I have a key
PRIMARY KEY (`myvalueA`) but only to prevent duplicates rather than
anything else.

So, what should I do to 'fix' this?

Would a SELECT * be any better?

Thanks

FFMG


--

'webmaster forum' (http://www.httppoint.com) | 'webmaster Directory'
(http://www.webhostshunter.com/) | 'Recreation Vehicle insurance'
(http://www.insurance-owl.com/other/car_rec.php)
'Free URL redirection service' (http://urlkick.com/)
------------------------------------------------------------------------
FFMG's Profile: http://www.httppoint.com/member.php?userid=580
View this thread: http://www.httppoint.com/showthread.php?t=17789

Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

On Mon, 25 Jun 2007 02:46:05 -0400, FFMG
<FFMG.2sq2fb@no-mx.httppoint.com> wrote:

>
>Hi,
>
>This is what I get from my slow log.
>
># Time: 070625 8:29:06
># User@Host: xxx @ yyy
># Query_time: 0 Lock_time: 0 Rows_sent: 14 Rows_examined: 14
>SELECT myvalueA, myvalueB FROM mytable;
>
>But those are my config values needed by all the pages, so what should
>I change to make it more efficient?
>
>I have a key
>PRIMARY KEY (`myvalueA`) but only to prevent duplicates rather than
>anything else.
>
>So, what should I do to 'fix' this?
>
>Would a SELECT * be any better?
>
>Thanks
>
>FFMG

Query_time: 0

I'm not an expert but it seems to me you will have hard time making
it faster. Your configuration might be a bit... exacting regarding
what should be logged in the slow query log.

subtenante;78196 Wrote:
>
> I'm not an expert but it seems to me you will have hard time making
> it faster. Your configuration might be a bit... exacting regarding
> what should be logged in the slow query log.

Well, I have the default values set.
And not all the queries appear in the log.

I am not sure what the time is supposed to show as most of my queries
are '0' or '1'

FFMG


--

'webmaster forum' (http://www.httppoint.com) | 'webmaster Directory'
(http://www.webhostshunter.com/) | 'Recreation Vehicle insurance'
(http://www.insurance-owl.com/other/car_rec.php)
'Free URL redirection service' (http://urlkick.com/)
------------------------------------------------------------------------
FFMG's Profile: http://www.httppoint.com/member.php?userid=580
View this thread: http://www.httppoint.com/showthread.php?t=17789

Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

On Mon, 25 Jun 2007 07:04:13 -0400, FFMG
<FFMG.2sqd52@no-mx.httppoint.com> wrote:

>
>subtenante;78196 Wrote:
>>
>> I'm not an expert but it seems to me you will have hard time making
>> it faster. Your configuration might be a bit... exacting regarding
>> what should be logged in the slow query log.
>
>Well, I have the default values set.
>And not all the queries appear in the log.
>
>I am not sure what the time is supposed to show as most of my queries
>are '0' or '1'
>
>FFMG

*sigh* :'(
I was just so happy to make a good joke this time.
But hey !

5.12.4. The Slow Query Log
The slow query log consists of all SQL statements that took more than
long_query_time seconds to execute. The time to acquire the initial
table locks is not counted as execution time. The minimum and default
values of long_query_time are 1 and 10, respectively.

Minimum value=1, you log value=0... We really need an expert there.

> 5.12.4. The Slow Query Log
> The slow query log consists of all SQL statements that took more than
> long_query_time seconds to execute. The time to acquire the initial
> table locks is not counted as execution time. The minimum and default
> values of long_query_time are 1 and 10, respectively.
>
> Minimum value=1, you log value=0... We really need an expert there.

(See also http://dev.mysql.com/doc/refman/5.1/...r-options.html)

Is the --log-queries-not-using-indexes set? Then it still would surprise
me that such a query ends up in the slow query log, as there is a
primary key defined. If the optimizer want a key, there is one.

Just curious: does it help to switch the above option off (if it was set)?

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Sorry to follow up on my own post, but this site gives an answer, I think...

http://www.mysqlperformanceblog.com/...nalyzes-tools/

--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

On Mon, 25 Jun 2007 14:41:39 +0200, Willem Bogaerts
<w.bogaerts@kratz.maardanzonderditstuk.nl> wrote:

>Sorry to follow up on my own post, but this site gives an answer, I think...
>
>http://www.mysqlperformanceblog.com/...nalyzes-tools/

The question is now : how can the slow query log log a query that is
less slow than the least slow query it should log, *before* our friend
applied the patch. (Without touching anything it should be by default
10 seconds.)

I'm afraid to ask it, but... is it really the slow query log ?

subtenante;78212 Wrote:
>
> *sigh* :'(
> I was just so happy to make a good joke this time.
> But hey !
>

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

FFMG


--

'webmaster forum' (http://www.httppoint.com) | 'webmaster Directory'
(http://www.webhostshunter.com/) | 'Recreation Vehicle insurance'
(http://www.insurance-owl.com/other/car_rec.php)
'Free URL redirection service' (http://urlkick.com/)
------------------------------------------------------------------------
FFMG's Profile: http://www.httppoint.com/member.php?userid=580
View this thread: http://www.httppoint.com/showthread.php?t=17789

Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

>> http://www.mysqlperformanceblog.com/...nalyzes-tools/
>
> The question is now : how can the slow query log log a query that is
> less slow than the least slow query it should log, *before* our friend
> applied the patch. (Without touching anything it should be by default
> 10 seconds.)
>
> I'm afraid to ask it, but... is it really the slow query log ?

I had to read the above sentence a few times, but it does make sense.

There are two settings here: a minimum execution time (which is patched
by the page at the above link) and a setting that marks all full table
scans as "slow".

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.
The problem is that a full table scan will not become slow on a
"definition table" that barely grows. So this is a false alarm.

It would make sense to set the --log-queries-not-using-indexes option ON
on a development machine or a test server, and OFF on a live server. But
you may want to leave the minimum execution time ON on a live server, to
be alerted on performance issues when the database grows.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

On Mon, 25 Jun 2007 16:41:53 +0200, Willem Bogaerts
<w.bogaerts@kratz.maardanzonderditstuk.nl> wrote:

>>> http://www.mysqlperformanceblog.com/...nalyzes-tools/
>>
>> The question is now : how can the slow query log log a query that is
>> less slow than the least slow query it should log, *before* our friend
>> applied the patch. (Without touching anything it should be by default
>> 10 seconds.)

>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.

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.
His query returned 14 rows... If that is considered slow, it's the
Speedy Gonzalez release he downloaded from MySQL.

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.

>The problem is that a full table scan will not become slow on a
>"definition table" that barely grows. So this is a false alarm.

Even if it grows, if the expected output is above 10% of the size of
the table, the optimizer goes for a full table scan: faster. A slow
select involes joins, unions, joined unions and spaghetti design. Or a
286.

I would say there is a trick somewhere, a config file that defines a
slow query log and overdefines it as another log somewhere else.
Or else his MySQL has a bug.
Or it's on acid.