Re: Select Performance

[Michael Stassen]

Yes, you need to change your query for optimal speed.  Prior to 5.0.0, mysql 
chooses the best index to use, one per table.  Your indexes don't help for 
this query, because no one index does the job.  Why?  Because you are 
selecting on one column OR another column.  If you are using at least mysql 
4.0, then Harald Fuch's solution is the best you can do.  Each side of the 
union will use the appropriate index and be fast.  See "How MySQL Uses 
Indexes" <http://www.mysql.com/doc/en/MySQL_indexes.html> in the manual for 
more.

5.0.0 has a new method to use both of your indexes in an OR query like this 
and merge the results.  See "How MySQL Optimizes OR Clauses" 
<http://www.mysql.com/doc/en/OR_optimizations.html> for the details.

Michael

A Z wrote:

Thanks, but this is not what we expected.  This means
to change the SQL command in the application.  Our
understanding from the manual and other relational DBs
is that, it's suffice for existence of an index on the
field in the criteria (where clause) to run at optimal
speed.
regards

 --- Harald Fuchs <[EMAIL PROTECTED]> wrote: 

A Z <[EMAIL PROTECTED]> writes:

The problem (very slow) occurs when the query is run
on combination of these fields:
Select * from table1 where (Field1Idx Like
'Value%' or

Field2Idx Like 'Value%')
Try a UNION query:

SELECT *
FROM table1
WHERE Field1Idx LIKE 'Value%'
UNION
SELECT *
FROM table1
WHERE Field2Idx LIKE 'Value%'


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]