Hello again Following on from this index question, the UNION worked. From a normal mysql client, it was returning my results sub-second. I am actually executing this over JDBC, using mysql-connector j.
WHen I put the SQL into my Java program - it takes a minute or so. I am logging the SQL and if I copy and paste it into my mysql client, it is fast. I can execute the query first in mysql and then in the JDBC client and I get the same so it is not caching. I've done a bit of searching but found nothing - any ideas ? Chris On 7/22/05, Chris Faulkner <[EMAIL PROTECTED]> wrote: > That was exactly the problem. Thanks. MySQL can't use two indexes on > the same table at the same time. Thanks for the other suggestions but > I'll use this workaround. > > Chris > > On 7/22/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > 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] > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
