Following on from my previous email I have columns containing numbers
which are then used in SUM and MIN/ MAX functions should these be
indexed too ?
On 3 Oct 2010, at 16:44, Joerg Bruehe <joerg.bru...@oracle.com> 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 <gto...@ffn.com> 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.bru...@oracle.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
