[Binari] mysql indexing
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 :-/ -- ================================================================== 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 ==================================================================
Anyone happens to have a bicycle pump with them for an American valve? (auto ventiel) -- ================================================================== Sebastian Proost, PhD Student Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Bioinformatics and Systems Biology division Ghent University Technologiepark 927, 9052 Gent, BELGIUM email: sebastian.proost@psb.vib-ugent.be <mailto:sebastian.proost@psb.vib-ugent.be> website: http://bioinformatics.psb.ugent.be/ ------------------------------------------------------------------ *PLAZA*, a resource for Plant Comparative Genomics http://bioinformatics.psb.ugent.be/plaza/ ==================================================================
hmmm, mine has this kind of clip on thing, but i doubt it goes open wide enough for that. but feel free to try it out. it's the racing bike in the far right corner when you enter the parking lot. pump is near the drinking bottle holder. it's small, but does the trick, at least for euro valves :-) On 07/15/2010 10:57 AM, Sebastian Proost wrote:
Anyone happens to have a bicycle pump with them for an American valve? (auto ventiel)
-- ================================================================== Sebastian Proost, PhD Student
Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Bioinformatics and Systems Biology division Ghent University Technologiepark 927, 9052 Gent, BELGIUM
email: sebastian.proost@psb.vib-ugent.be <mailto:sebastian.proost@psb.vib-ugent.be> website: http://bioinformatics.psb.ugent.be/ ------------------------------------------------------------------ *PLAZA*, a resource for Plant Comparative Genomics http://bioinformatics.psb.ugent.be/plaza/ ==================================================================
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Marijn Vandevoorde 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 marijn.vandevoorde@psb.vib-ugent.be http://www.psb.vib-ugent.be ================================================================== "You Can't touch this" --M.C. Hammer
aan de receptie hebben ze een pomp in het hokje zo naast de receptie staat ze waar vincent zit, het meisje die nu aan receptie zit zal van nix weten, maar ze staat zo vlak rechts naast de deur normaal (of vraag het aan vincent, die meestal daar aanwezig is) Sebastian Proost wrote:
Anyone happens to have a bicycle pump with them for an American valve? (auto ventiel)
-- ================================================================== Sebastian Proost, PhD Student
Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Bioinformatics and Systems Biology division Ghent University Technologiepark 927, 9052 Gent, BELGIUM
email: sebastian.proost@psb.vib-ugent.be <mailto:sebastian.proost@psb.vib-ugent.be> website: http://bioinformatics.psb.ugent.be/ ------------------------------------------------------------------ *PLAZA*, a resource for Plant Comparative Genomics http://bioinformatics.psb.ugent.be/plaza/ ================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Frederik Delaere Tel:+32 (0)9 331 38 14 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM frlae@psb.vib-ugent.be http://www.psb.vib-ugent.be ==================================================================
mooi, dan nog rap 'n binnenband gaan kopen en lepeltjes gaan stelen van blancheke en 't komt in orde Seb Frederik Delaere schreef:
aan de receptie hebben ze een pomp in het hokje zo naast de receptie staat ze waar vincent zit, het meisje die nu aan receptie zit zal van nix weten, maar ze staat zo vlak rechts naast de deur normaal (of vraag het aan vincent, die meestal daar aanwezig is)
Sebastian Proost wrote:
Anyone happens to have a bicycle pump with them for an American valve? (auto ventiel)
-- ================================================================== Sebastian Proost, PhD Student
Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Bioinformatics and Systems Biology division Ghent University Technologiepark 927, 9052 Gent, BELGIUM
email: sebastian.proost@psb.vib-ugent.be <mailto:sebastian.proost@psb.vib-ugent.be> website: http://bioinformatics.psb.ugent.be/ ------------------------------------------------------------------ *PLAZA*, a resource for Plant Comparative Genomics http://bioinformatics.psb.ugent.be/plaza/ ================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Sebastian Proost, PhD Student Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Bioinformatics and Systems Biology division Ghent University Technologiepark 927, 9052 Gent, BELGIUM email: sebastian.proost@psb.vib-ugent.be <mailto:sebastian.proost@psb.vib-ugent.be> website: http://bioinformatics.psb.ugent.be/ ------------------------------------------------------------------ *PLAZA*, a resource for Plant Comparative Genomics http://bioinformatics.psb.ugent.be/plaza/ ==================================================================
seb, er zitten zo'n haakjes in 't zakje onder mijn zadel (zelfde fiets, mooi roze en geel, helemaal 80' style, je kan er niet naast kijken) om je band af te nemen. een binnenband zit er ook bij, maar die mag je niet hebben. zou wsl ook wat te fijn zijn. On 07/15/2010 11:42 AM, Sebastian Proost wrote:
mooi, dan nog rap 'n binnenband gaan kopen en lepeltjes gaan stelen van blancheke en 't komt in orde
Seb
Frederik Delaere schreef:
aan de receptie hebben ze een pomp in het hokje zo naast de receptie staat ze waar vincent zit, het meisje die nu aan receptie zit zal van nix weten, maar ze staat zo vlak rechts naast de deur normaal (of vraag het aan vincent, die meestal daar aanwezig is)
Sebastian Proost wrote:
Anyone happens to have a bicycle pump with them for an American valve? (auto ventiel)
-- ================================================================== Sebastian Proost, PhD Student
Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Bioinformatics and Systems Biology division Ghent University Technologiepark 927, 9052 Gent, BELGIUM
email:sebastian.proost@psb.vib-ugent.be <mailto:sebastian.proost@psb.vib-ugent.be> website:http://bioinformatics.psb.ugent.be/ ------------------------------------------------------------------ *PLAZA*, a resource for Plant Comparative Genomics http://bioinformatics.psb.ugent.be/plaza/ ================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Sebastian Proost, PhD Student
Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Bioinformatics and Systems Biology division Ghent University Technologiepark 927, 9052 Gent, BELGIUM
email: sebastian.proost@psb.vib-ugent.be <mailto:sebastian.proost@psb.vib-ugent.be> website: http://bioinformatics.psb.ugent.be/ ------------------------------------------------------------------ *PLAZA*, a resource for Plant Comparative Genomics http://bioinformatics.psb.ugent.be/plaza/ ==================================================================
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Marijn Vandevoorde 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 marijn.vandevoorde@psb.vib-ugent.be http://www.psb.vib-ugent.be ================================================================== "Dum-dum-dum-dumdy-doo-wah, Ooh-yay-yay-yay-yeah, Oh-oh-oh-oh-wah" --R. Orbison
Sebastian Proost wrote:
mooi, dan nog rap 'n binnenband gaan kopen en lepeltjes gaan stelen van blancheke en 't komt in orde
met een beetje chewing gum dichtkleven ? :p je kan anders de mobiele fietshersteldienst bellen: http://www.ugent.be/nl/voorzieningen/fietsen ben wel niet zeker of dit ook voor vib werknemers gebruikt mag worden <http://www.ugent.be/nl/voorzieningen/fietsen>
Seb
Frederik Delaere schreef:
aan de receptie hebben ze een pomp in het hokje zo naast de receptie staat ze waar vincent zit, het meisje die nu aan receptie zit zal van nix weten, maar ze staat zo vlak rechts naast de deur normaal (of vraag het aan vincent, die meestal daar aanwezig is)
Sebastian Proost wrote:
Anyone happens to have a bicycle pump with them for an American valve? (auto ventiel)
-- ================================================================== Sebastian Proost, PhD Student
Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Bioinformatics and Systems Biology division Ghent University Technologiepark 927, 9052 Gent, BELGIUM
email: sebastian.proost@psb.vib-ugent.be <mailto:sebastian.proost@psb.vib-ugent.be> website: http://bioinformatics.psb.ugent.be/ ------------------------------------------------------------------ *PLAZA*, a resource for Plant Comparative Genomics http://bioinformatics.psb.ugent.be/plaza/ ================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Sebastian Proost, PhD Student
Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Bioinformatics and Systems Biology division Ghent University Technologiepark 927, 9052 Gent, BELGIUM
email: sebastian.proost@psb.vib-ugent.be <mailto:sebastian.proost@psb.vib-ugent.be> website: http://bioinformatics.psb.ugent.be/ ------------------------------------------------------------------ *PLAZA*, a resource for Plant Comparative Genomics http://bioinformatics.psb.ugent.be/plaza/ ================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Frederik Delaere Tel:+32 (0)9 331 38 14 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM frlae@psb.vib-ugent.be http://www.psb.vib-ugent.be ==================================================================
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 ======================================================================
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 ==================================================================
For double-indexes you can perform following queries that will result in using the index: * match full value * match leftmost prefix (what I tried to explain) e.g. Match a full last_name. * match column prefix e.g. Match all last_names starting with 'A%'. * match a range of values e.g. match all last_names between Billiau and Van Bel * match one part exactly and a range on antoher part e.g. match last_name exact and and a range query on firstname. This is simply an extension of the leftmost prefix. * covering index query e.g. you only query and SELECT last_name and first_name. this is lightning fast as all the info is stored in memory anyway. Again, rule of thumb is, match from left to right ;) hh, Kenny 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 ======================================================================
Okay, I now understand the problem we (PLAZA dudes) are having with the database indices. http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html Apparently indices cannot be combined when using "LIKE" queries. Thus, if you have a table with 2 columns containing gene identifiers, and you want to find the Arabidopsis genes (which start with AT), you get this: * SELECT * FROM `table` WHERE `gene_x` LIKE 'AT%' o uses index on column `gene_x` * SELECT * FROM `table` WHERE `gene_x` LIKE 'AT%' OR `gene_y` LIKE 'AT%' o no indices used * SELECT * FROM `table` WHERE `exp_id` = '1' AND (`gene_x` LIKE 'AT%' OR `gene_y` LIKE 'AT%') o Index on column exp_id used. * SELECT * FROM `table` WHERE `exp_id` = '1' AND `gene_x` LIKE 'AT%' o Only index on column exp_id is used However, if I use a join on another table to get the species of the genes, then multiple indices can and will indeed be used. I really don't remember my courses on databases covering these things :-( Michiel Kenny Billiau wrote:
For double-indexes you can perform following queries that will result in using the index:
* match full value * match leftmost prefix (what I tried to explain) e.g. Match a full last_name. * match column prefix e.g. Match all last_names starting with 'A%'. * match a range of values e.g. match all last_names between Billiau and Van Bel * match one part exactly and a range on antoher part e.g. match last_name exact and and a range query on firstname. This is simply an extension of the leftmost prefix. * covering index query e.g. you only query and SELECT last_name and first_name. this is lightning fast as all the info is stored in memory anyway.
Again, rule of thumb is, match from left to right ;)
hh, Kenny
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 ==================================================================
participants (5)
-
Frederik Delaere -
Kenny Billiau -
Marijn Vandevoorde -
Michiel Van Bel -
Sebastian Proost