Help with slow query

[davidc2]

I have a large set of data (a little over 2 million records on one of the tables, and like 5 smaller tables, with less than 1000 records each)..

When I try some queries on it, they run very slow the first time, but very fast afterwards, I tried "query_cache_type = 0" to see if I could get them slow everytime and see if cache was the problem, but it seems to me it isn´t, because it is still slow at first, then fast.

One of the queries:


SELECT
cv.nombre, contacto.manzana, nucleo.nucleo,
rep.cedula,
rep.p_apellido, rep.s_apellido, rep.p_nombre, rep.s_nombre,
contacto.telefono, contacto.calle, contacto.ncasa, contacto.apto

FROM contacto

LEFT JOIN nucleo ON contacto.id_nucleo = nucleo.id
INNER JOIN rep ON contacto.cedula = rep.cedula
LEFT JOIN cv ON rep.codigo_cv = cv.codigo_centro_votacion

WHERE contacto.vive_en_la_parroquia = 'si'

AND rep.codigo_cv
NOT IN (SELECT codigo_centro_votacion FROM cv WHERE codigo_estado = '21' AND codigo_municipio = '5' AND codigo_parroquia = '4' )
ORDER BY rep.codigo_cv, contacto.manzana, contacto.id_nucleo


I´m running WAMP so I can play with the settings if anyone has suggestions...

[davidc2]

Running explain on that query:
http://g.imagehost.org/0376/qexplain.jpg

[Old Pedant]

Might be just Windows file caching.

Regardless of whether or not MySQL is caching stuff, Windows *will* cache "hot" disk pages in memory. And since MySQL tables are just files in the operating system, this *could* be the reason.

I didn't see anything obvious in the Explain dump, but maybe somebody with more MySQL tuning experience will.

[davidc2]

Hmm I´ll keep trying stuff out...

Is it a bad idea to add an index to all the columns?

[guelphdad]

it will slow down your inserts. mysql also only makes use of a single index at a time so adding them to all columns won't necessarily speed it up.

[davidc2]

Is there any difference between using quotes and not using them for a number condition? i.e. where field = '1' vs. field = 1

:)

[Old Pedant]

I would assume not really. MySQL has to convert the '1' to a number, but let's face it, it also has to convert the 1 to a number, as it's an ASCII string within the query itself.

And one hopes/presumes that it is smart enough to make the conversion only once.

It's pretty poor coding practice to use a string where a number is wanted, and some DBs *will* choke on it, so you should get into the habit of matching data types correctly.