Hello.
Check with SHOW PROCESSLIST in which state MySQL thread which performs the query is. See: http://dev.mysql.com/doc/mysql/en/show-processlist.html Chris Faulkner <[EMAIL PROTECTED]> wrote: > 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. >>=20 >> Chris >>=20 >> On 7/22/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> > I believe the conflict here is the OR. Try this... >> > >> > select * from table >> > where field1 =3D 'VALUE1' and field2 like 'VALUE2%' >> > union >> > select * from table >> > where field3 =3D '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 hal= > f >> > >> > 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 =3D 'VALUE1') OR (field3 =3D '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 =3D 'VALUE1' and field2 like 'VALUE2%' ) >> > > > OR >> > > > ( field3 =3D '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] > gmail.com >> > >> > >> > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
