Slow first query on MySQL

[Nutter]

I'm having some speed issues on a site I'm working on and think I've got it narrowed down to the database server, and specifically the first select query the page runs.

If I run the query

SELECT value FROM table WHERE fieldname='fieldvalue';

it runs in about 2.5 seconds the first time and 0.00 seconds the second. I've got query caching on and I assume that would explain the much faster second query. But if I do

SELECT value FROM table WHERE fieldname='anothervalue';

as the second query instead of matching it to the first it's still 0.00 seconds. And I don't think a different query would be affected by the cache.

I've got an index built on the 'fieldname' field, although this particular table only has about 50 records so I don't know how much difference that really makes.

[oxbaker]

if your selecting all records and then filtering by a value (ie first value) it caches everything in the select, only displays what meets the criteria so a second script with a different variable will still produce cached results, as the entire dataset was cached on the initial script run (even though the filter is different)
if you want to speed up the queries, try indexing that field the value is on.

hth,
mcm

[Nutter]

So, could it be that caching is what's actually causing the slow down? I ran the same query with SQL_NO_CACHE and the first query went from the 2-3 seconds that it's been running down to 0.07, but subsequent queries were all in the 0.06-0.08 range.

It still seems slow for this query though. The field I'm searching on has a unique key index. The field itself is a varchar(100). Could that slow down the searches? I could probably get away with dropping it to 25 or so and even using a char field type if that would make it faster.

[Demaestro]

Can you run the SQL through an explain() call?

If so you should try that and you can see where in the SQL you can optimize things and you can see what joins or aggregate functions have the most load associated to them.

If you haven't used it before I would be happy to help you dissect the results.

[zCat]

what kind of system is the database server running on? E.g. dedicated server, shared host etc.

[Nutter]

VPS.

There are about 50 records in the pb_config table.

explain select configvalue from pb_config where configname='company_name'\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: pb_config
type: const
possible_keys: name
key: name
key_len: 100
ref: const
rows: 1
Extra:
1 row in set (0.06 sec)

describe pb_config

field - type - null - key - default - extra
id - int(11) unsigned - - PRI - NULL - auto_increment
configname - varchar(100) - - UNI - -
configvalue - varchar(255) - - - -

[Demaestro]

Is that from the first time or the second execute that runs faster?

[Nutter]

Now that I've turned caching on to manual instead of automatic it's the same. Before turning caching on manual the explains would look exactly the same aside from the first one taking 2-3 seconds and the second 0.00 seconds.

[zCat]

I don't know whether it's the cause in this situation, but I've found VPS servers very erratic when it comes to critical performance. It wouldn't surprise me if other VPSs on the same box are actively using system resources, which means when query results have to be fetched from disk, there's a noticeable startup delay. Once they're in memory, speed is not a problem; but I suspect after a while that part of memory gets swapped out by the OS to disk, which makes the next access slow again.