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]
