The system cannot used the index on field2 because it is the second half
of the index in both cases, and it can only use indexes in order. It
cannot use the separate indexes on field 1 and field 2 because the are
ORred together.
If you rephrase your query
SELECT * from table
WHERE field2 LIKE 'VALUE2%"
AND ((field1 = 'VALUE1') OR (field3 = 'VALUE3')) ;
it becomes obvious that an index on field2 will be used, followed by
searches of the results field1 and field3 .
As a matter of interest, what numbers of hits do you expect on each of the
three terms separately? If the field2 hit is is pretty selective, it does
not really matter what the others do.
Alec
Chris Faulkner <[EMAIL PROTECTED]>
22/07/2005 12:46
Please respond to
Chris Faulkner <[EMAIL PROTECTED]>
To
[email protected]
cc
Subject
Re: use of indexes
Hi
field2 is indexed. I have 2 indexes. One is on field1 and field2, the
second indexes field3 and field2.
You mean a separate index which only indexes field2 ? Ithought that
the type of query I am doing is a good reason for doing composite
indexes.
Chris
On 7/22/05, Eugene Kosov <[EMAIL PROTECTED]> wrote:
> Chris Faulkner wrote:
> > HI
> >
> > I have a query like this
> >
> > select * from table where (
> > ( field1 = 'VALUE1' and field2 like 'VALUE2%' )
> > OR
> > ( field3 = 'VALUE1' and field2 like 'VALUE2%' )
> > )
> >
> > I have created two composite indexes - one on field1 + field2 and one
> > on field3 + field2. Explain on the SQL indicates that the indexes are
> > possibly used. The query takes an age to run and looking at my log
> > indicates a full table scan.
> >
> > I have also tried indexing just field1 and field3 separately but this
> > doesn't help. I have run an analyze.
> >
> > Chris
> >
>
> Mysql use an index only if indexed field(s) present(s) in both OR
arguments..
> Sorry, but i can't find it in docs right now, so i can't give you any
helpful link.
>
> I think index on field2 may help you here..
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
