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]
