Yeah, I just thought that the index would be used for the last-name part of the query, and not for the first-name part. It would then still show some improvements I think. It's just that we are a bit struggling with growing tables (22 million entries for colinearity data , woohoo!), and it is really slowing down some queries (big difference between 22 million and 2 million apparently, especially with some joins thrown in). So I decided to have a look at the mysql index pages. Kenny Billiau wrote:
Hi,
with OR on a non-indexed column (first_name) a full table scan is done. You have to consider those double indexes as a concatenated index of last_name.first_name, which means that mysql can only take advantage if you have conditions in the order of last_name AND first_name (as mysql can concat this result), but not with last_name OR first_name (there is no way to concat last_name with first_name in a way to take advantage of the index. It might be used only for last_name, but still a full table scan is performen for first_name :/).
Does this make a bit sense?
On Thu, 15 Jul 2010, Michiel Van Bel wrote:
okay, I'm a bit confused: http://dev.mysql.com/doc/refman/4.1/en/multiple-column-indexes.html
You see 2 examples at the bottom, where the name index is NOT used. I understand why the index is not used in the first example, but why is it not used for the "last_name" part in the query of the second example?
I think I need to revise my knowledge on indexing a bit :-/
-- ====================================================================== 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 ====================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Michiel Van Bel PhD student Tel:+32 (0)9 331 36 95 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM mibel@psb.vib-ugent.be http://www.psb.vib-ugent.be http://bioinformatics.psb.ugent.be ==================================================================