Thanks Gavin and Joerg, that was very helpful!
-- Jonas
On Sun, Oct 3, 2010 at 12:44 PM, Joerg Bruehe <joerg.bruehe@stripped>wrote:
> Hi Neil, all!
>
>
> Tompkins Neil wrote:
> > So if you have individual indexes for example field_1, field_2 and
> field_3
> > etc and then perform a search like
> >
> > WHERE field_1 = 10
> > AND field_3 = 'abc'
> >
> > This wouldn't improve the search ? You have to create a index for all
> > possible combined field searches ?
>
> No - you didn't read Gavin's mail exact enough:
>
> > On Fri, Oct 1, 2010 at 9:35 PM, Gavin Towey <gtowey@stripped> wrote:
> >
> >> [[...]]
> >>
> >> Additionally indexes are always read left to right. So an index on
> >> ('user_id', 'product_id') will help when doing WHERE user_id=N AND
> >> product_id IN (1,2,3), but wouldn't help for just the condtion on
> >> product_id.
>
> What Gavin calls "left to right" is what I call "most significant
> first", the result is the same:
>
> In a multi-column index, the columns are listed in the order of their
> significance. Any DBMS (this is not limited to MySQL) can use such an
> index only if a condition for the first (= most significant) field(s) is
> (are) specified.
>
> Example: Assume the index is on fields A, B, and C in that order.
>
> A statement "... where A = x and B = y and C = z" can use the index.
> A statement "... where A = x and B = y" can use the index, limited to
> the first two fields.
> A statement "... where A = x" can use the index. the first field only.
> A statement "... where A = x and C = z" can also use the index for A,
> but will have to evaluate the condition on C by scanning all records
> matching A.
>
> A statement "... where B = y and C = z" cannot use the index, because
> there is no condition on A.
>
> If there are many searches based on A and C only (not B), and there are
> many records matching A with different values of C, then an additional
> index on these two columns may be helpful.
>
> Compare the index with a phone book, which (typically) lists the entries
> sorted by last name (most significant), then first name, then ... :
> If you don't know the last name, you cannot profit from the sorting and
> have to scan the wole book.
>
> >>
> >> See the manual for full details on how mysql uses indexes:
> >> http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html>
> HTH,
> Jörg
>
> --
> Joerg Bruehe, MySQL Build Team, joerg.bruehe@stripped> 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=1>
>

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.