MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Brian Moon Date:August 17 2002 3:41pm
Subject:Re: Query slowing down, need advice
View as plain text  
Thanks for your input.  Especially Tod for breaking it down at the hard ware
level and making me realize what is possible.

I have re-evaluated how this is going to work.  Instead of trying this with
one query, I am using 2.  I forget somethimes that MySQL is better at
running simple queries really fast than complex single queries.

I am now using Full-Text indexing (people will just have to upgrade) to
retrieve any id of a row matching my search criteria eg.

select id from single_table where MATCH (body,author,subject) AGAINST
('search criteria')

I then read all those id in from the results.  That takes .2 seconds on my
server.  I then feed those results (current 4450 rows) into a second query.

select id from single_table where approved='Y' and id in ( big id list )
order by datestamp desc

That query takes .2 seconds.  id is the primary key so look ups on it are
really fast.  There is a key on approved, datestamp as well.

From that I have the rows I am looking for.  All in .4 seconds.  This is
with 1Mil+ rows.  The speed is consistent for all search criteria.

Brian Moon
Phorum Dev Team


Thread
Query slowing down, need adviceBrian Moon16 Aug
  • Re: Query slowing down, need adviceThomas Spahni16 Aug
    • Re: Query slowing down, need adviceBrian Moon16 Aug
      • Re: Query slowing down, need adviceTod Harter16 Aug
Re: Query slowing down, need adviceBrian Moon17 Aug