List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:September 14 2004 4:47pm
Subject:Re: MySQL speed
View as plain text  
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
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