Hi Bryan, all!
Bryan Cantwell wrote: > Is there a benefit to a combined index on a table? Or is multiple single > column indexes better? This is a FAQ, but I'm not aware of a place to point you for the answer. > > If I have table 'foo' with columns a, b, and c. I will have a query > like: > select c from foo where a in (1,2,3) and b < 12345; > > Is index on a,b better in any way than an a index and a b index? Any multi-column index can only be used when the values for the leading column(s) is/are known (in your example, they are). My standard example is a phone book: It is sorted by "last name", "first name"; you cannot use this order when the last name in unknown (you have to sequentially scan it). > An explain with one index sees it but doesn't use it (only the where) > and having 2 indexes sees both and uses the one on b. Testing select strategies requires that you have a meaningful amount of data, and a close-to-real distribution of values: If your tables hold too few rows, the system will notice that it is wasteful to access them via the index, a scan is faster. And if your value distribution differs too much from later "real" data, the strategy selected will also differ. > > Am I right to think that 2 indexes are better than one combined one? "It depends": AFAIK, MySQL will not yet combine several indexes, but evaluate only one per table access. If you have a usable multi-column index, it will provide better selectivity than a single-column index, so it is "better" if all the leading values are given. I cannot specifically comment on conditions using "in" and "<". HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
