Why not just add an index on touser+hidden. Problem solved. Donny
> -----Original Message----- > From: Dan Nelson [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 14, 2004 11:48 AM > To: Dirk Schippers > Cc: [EMAIL PROTECTED] > 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 > [EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
