List:General Discussion« Previous MessageNext Message »
From:Donny Simonton Date:September 14 2004 4:49pm
Subject:RE: MySQL speed
View as plain text  
Why not just add an index on touser+hidden.   Problem solved.

Donny

> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@stripped]
> Sent: Tuesday, September 14, 2004 11:48 AM
> To: Dirk Schippers
> Cc: mysql@stripped
> Subject: Re: MySQL speed
> 
> In the last episode (Sep 14), Dirk Schippers said:
> > For several years I am hosting a popular website using PHP and MySQL.
> > As the site is growing and evolving, speed is becoming more and more
> > important. With my latest inventions on the website, I notice that
> > the website is becoming slow and I want to find out what's causing
> > this.
> >
> > And this is the question that makes me totally confused: How is it
> > possible that the following query: "SELECT COUNT(*) FROM messages
> > WHERE touser = 20 AND hidden = 0" with a key on touser can take up to
> > 1 second (I even had moments where it would take up to 18 seconds!!!)
> > even if EXPLAIN says mysql only has to walk trough 2500 rows thanks
> > to the key on touser. (Oh yeah, I did an ANALYSE on the table)
> >
> > If I think of making my own program walking trough a datafile with
> > 2500 items, checking every item for the flag "hidden" I would think
> > that should not take up to even 0.01 second! Of course MySQL is more
> > complicated than this, but I think it still is a very big difference.
> 
> That's up to 2500 random disk seeks, and even the fastest SCSI disks do
> only 300 seeks/sec (ATA disks max at ~150).  Best case is where all the
> records are in memory and it doesn't have to hit the disk at all, but
> depending on how many tables you have and your RAM, that may not always
> be true.  Try creating a multicolumn index on (touser,hidden), which
> will let mysql process the query without doing any record fetches at
> all.
> 
> --
> 	Dan Nelson
> 	dnelson@stripped
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
> 



Thread
MySQL speedDirk Schippers14 Sep
  • Re: MySQL speedFagyal Csongor14 Sep
    • Re: MySQL speedRoel Schroeven14 Sep
  • Re: MySQL speedDan Nelson14 Sep
    • RE: MySQL speedDonny Simonton14 Sep
  • Re: [Bulk] Re: MySQL speedO'K Web Design14 Sep
  • Re: MySQL speedTobias Asplund14 Sep
    • Re: MySQL speedRoel Schroeven15 Sep