List:General Discussion« Previous MessageNext Message »
From:Anders Nielsen Date:July 26 2002 3:19am
Subject:RE: Accessing multiple indexes
View as plain text  
Ask not what MySQL can do for you, ask what you can do for yourself!

If you create an index that spans multiple columns in the same table, this
index will make the query you present below, faster.

Take a look at http://www.mysql.com/doc/C/R/CREATE_INDEX.html and take
notice of the paragraph beginning with "column list of the form
(col1,col2,...) creates a multiple-column index".

regards,
Anders Nielsen


-----Original Message-----
From: Orr, Steve
To: mysql@stripped
Sent: 25-07-2002 23:13
Subject: Accessing multiple indexes

It's my understanding that MySQL will only use one index per table on a
given query. For example...

SELECT * FROM <HUGE_TABLE>
WHERE <col1> = <val1>
AND <col2> < <val2>
AND <col3> > <val3> ;

If col1, col2, and col3 are indexed the query can only use one index,
right?


Single index access is a problem when you very large tables. What if you
have a query with a result set of just 10 rows but there are no indexed
columns that can limit the result set to < 1 million rows? I really need
to
be able to use multiple indexes in a single table query and I don't want
to
have to perform self joins or create temp tables.

Is there another way? Are there plans to overcome this limitation?


TIA



---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread115585@stripped>
To unsubscribe, e-mail
<mysql-unsubscribe-anders=visator.dk@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Thread
Accessing multiple indexesSteve Orr25 Jul
  • Re: Accessing multiple indexesDan Nelson26 Jul
  • Re: Accessing multiple indexesEgor Egorov26 Jul
RE: Accessing multiple indexesAnders Nielsen26 Jul