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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
