Btw, whenever in doubt, you've got the 'explain' keyword to, well, explain what happens in a query. You can just prepend it to your query or hit the 'explain' link after entering a custom query in phpMyAdmin ;) At least this gives you insight in how your indexes are being used, if they are being used. Also nice is the new profiling function for MySQL 5: http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html In a recent phpMyAdmin you can also just check the profiling checkbox for this to happen. Kenny On Mon, 12 Jul 2010, Michiel Van Bel wrote:
okay, I'll get the correct results, and try to write another query to evade the lazy eval of mysql. Other database usage is not the issue.
Marijn Vandevoorde wrote:
(fyi: mysql, or sql in general's order of evaluation is: first and, then or.)
On 07/12/2010 03:39 PM, Marijn Vandevoorde wrote:
nope. it return records for which
(a=? and b =?) or c=?
which is not what you want.
mysql is probably optimizing this in a lazy way, checking C first and if that doesn't match, checking a and b.
strange that you're experiencing such a big performance jump there.
your original query can also be done lazy, but in the worst case requires 3 checks instead of (best case) 1.
so it shouldn't be more than something like 3 seconds i guess.
are you sure it wasn't just a matter of resources being used by something else, locks on tables etc.?
On 07/12/2010 03:34 PM, Michiel Van Bel wrote:
okay, I have a table with columns A, B, C There is a separate index on all columns If my query looks like this : "WHERE A=? AND (B=? OR C=?)" Then the runtime for 300 queries is about 70 seconds (which is way too long)
If my query looks like this: "WHERE A=? AND B=? OR C=?" Then the runtime for 300 queries is about 1 second (which is a lot more sensible).
Now, the question is of course, why is mysql so fucking slow, when I just add some brackets? And is the result of the first query equal to this: "WHERE A=? AND B=? OR A=? AND C=?" Or am i missing something?
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================