List:General Discussion« Previous MessageNext Message »
From:Tod Harter Date:August 16 2002 7:05pm
Subject:Re: Query slowing down, need advice
View as plain text  
On Friday 16 August 2002 12:43 pm, Brian Moon wrote:
> No, it should be and is scanning through the 500,000+ rows that meet the
> key of (approved, datestamp).  The table has 1M+ in it. My question is, how
> can it be faster?  Can it not be faster?  Is MySQL on my size server just
> not going to handle scanning 500K rows?  That seems like a low number to
> have problems with.  The query is not just slow, it does not return.

There's no RDBMS on earth that would be fast for this query.  If the string 
'myphorum' is a constant expression which is always getting searched for then 
I would simply flag every record that it occurs in. 

I would generally try to redesign my application so this sort of query is not 
used. Or else maybe there are other fields you can index on as well to cut 
down the numbers of candidate records.

I mean consider this. I don't know your schema, but if you have record 
lengths of say 256 characters then 1 million records is going to be 256 
megabytes. I suspect your data is more like a gig. Given that most disk 
subsystems in realistic use get about 5 meg/sec of sustained transfer you're 
talking about a 1 minute READ time for the entire table (and your index is 
likely not helping a bit, in fact I'm surprised MySQL bothers with it, at the 
hit rate of 50% practically every page of the database has to be loaded 
anyhow, so the index is practically worthless, in fact it may even be slowing 
your query, try dropping it).

My other advice would be to get lots of RAM and increase the size of the 
database's buffers. If you can get the entire table buffered into RAM you 
might get some decent performance.

>
> Brian.
>
> ----- Original Message -----
> From: "Thomas Spahni" <tsp@stripped>
> To: "Brian Moon" <brian@stripped>
> Cc: <mysql@stripped>
> Sent: Friday, August 16, 2002 10:51 AM
> Subject: Re: Query slowing down, need advice
>
> | On Fri, 16 Aug 2002, Brian Moon wrote:
> | > I ran into a problem when the queries started to call for 500,000+
> | > rows. The query would not return.  Here is the query and explain:
> | >
> | > select SQL_NO_CACHE id from single_table where approved='Y' AND
>
> datestamp >=
>
> | > '2001-08-16 00:00:00' AND  ( ( author LIKE '%myphorum%'  OR  subject
>
> LIKE
>
> | > '%myphorum%'  OR  body LIKE '%myphorum%' ) ) order by datestamp desc;
> |
> | Brian,
> |
> | this query, having a joker at the beginning of each search string, scans
> | through the whole table. It can't possibly be fast on large tables.
> |
> | Regards,
> | Thomas
>
> ---------------------------------------------------------------------
> 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-thread117369@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-tharter=aptusventures.com@stripped> Trouble
> unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
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