List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:June 11 1999 12:50pm
Subject:Re: more efficient queries
View as plain text  
pete collins wrote:
> 
> I wrote a keyword search engine using PHP and mysql that does a bunch of
> queries
> similar to the one below.
> 
> "SELECT fullname, id, city, state, specialty, zip FROM doctors WHERE
> fullname LIKE
> '$Udoctor%' AND id > $id ORDER BY id";
> 
> The table is about 308,428 rows
> So it is pretty big.
> Speed is acceptable, but I think I can make it even faster.
> 
> Is there anything I can do to make it quicker?
> I am stuck using LIKE  because it is a keyword search.
> My primary key is "id".
> 
> Thanks
> 
> Pete Collins
> 

You could probably split the full name into the first and last name. It
looks like you are only looking at the beginning of fullname, which
probably means that all of your names start with the last name. If you
could reduce LIKE to a smaller field, it should speed things up.

Also, was your interface originally intended to find a doctor if only
first few letters of his last name were provided? If the answer is no,
you will not need LIKE.


Post the output of DESCRIBE doctors and EXPLAIN of you query for more
detailed analysis.

Question for Monty. Using  a b-tree index for LIKE that does not start
with a wild character is theoretically not much more difficult than
using an index on = or > . Is there an easy way to incorporate it into
the existing code, though? If there is, are there any plans to do it?

Now that I've mused about LIKE, I've just thought of a hack that will
accomplish the same thing. Here is an example:

we need to do SELECT * FROM student WHERE name LIKE 'John%';
we have an index on name and would like to use it. So here is the hack:

SELECT * FROM student WHERE name >= 'John ' AND name <= 'Johnz'

You get the idea, we append the first (in the alphanum sense) character
in the charset that could possibly occur in the result, append it, and
do >= and then take the last and do the same except now <=.

  

-- 
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
Thread
more efficient queriespete collins11 Jun
  • Re: more efficient queriesSasha Pachev11 Jun
    • Re: more efficient queriesOliver Artelt11 Jun
  • more efficient queriesMichael Widenius11 Jun
  • Re: more efficient queriespete collins11 Jun
  • Re: more efficient queriespete collins11 Jun
    • Re: more efficient queriesBenjamin Pflugmann12 Jun
  • Re: more efficient queriespete collins14 Jun