MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Jan Willamowius Date:September 10 2000 12:45pm
Subject:Optimizing a query to use an index
View as plain text  
I'm doing a query on a MySQL database to find out how many rows
contain a certain phone number:

select count(*) from table where tel1="123" or tel2="123" or tel3="123";

The query does all I want, but performs way too slow. Because
of the ORi, MySQL can't use any index (I have idexes on tel1, tel2
and tel3) and ends up doing a full table scan which takes about
90 second.

Searching for a single number via the index only takes 2 seconds, but
since the same phone number might be in 2 fields of a record, I can't
just add the results of 3 separate queries in my Perl script.

Question: Is there any way I can rewrite that query to take advantage
of an index and still get the correct count ?

Thanks,
	Jan

PS: Because of other restrictions I can't change the table structure
    any time soon.


-- 
Jan Willamowius, jan@stripped, http://www.willamowius.de/
Linux - because it works.
Thread
Optimizing a query to use an indexJan Willamowius10 Sep
  • Re: Optimizing a query to use an indexJeremy D. Zawodny10 Sep
    • Re: Optimizing a query to use an indexAnn Cantelow10 Sep
    • Re: Optimizing a query to use an indexJan Willamowius10 Sep
      • Re: Optimizing a query to use an indexJames Treworgy10 Sep