I believe the conflict here is the OR. Try this... select * from table where field1 = 'VALUE1' and field2 like 'VALUE2%' union select * from table where field3 = 'VALUE1' and field2 like 'VALUE2%'
Currently, MySql can't utilize two indexes on the same table at the same time but it is on their list of to-do`s, this will be a cool feature. The UNION will allow you to use both composite indexes at the same time because it is two queries. Ed -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, July 22, 2005 6:04 AM To: Chris Faulkner Cc: mysql@lists.mysql.com Subject: Re: use of indexes 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 mysql@lists.mysql.com 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]
