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" <[EMAIL PROTECTED]> > To: "Brian Moon" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> Trouble > unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
