List:General Discussion« Previous MessageNext Message »
From:Dirk Schippers Date:September 14 2004 4:12pm
Subject:MySQL speed
View as plain text  
Hello,

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.

More information: table messages is an MyISAM table of 48MB which has 
67000 rows, a primary key on id, a key on touser and a key on isread 
with the following fields: id - bigint(20) autoincrement, fromuser - 
int(10) unsigned, touser - int(10) unsigned, ts - datetime, message - 
text, isread - tinyint(1) unsigned, ipnumber - varchar(20), hidden - 
tinyint(1).

I experience this problem also with other tables and queries but as this 
is the most simple one, I show this one as an example.

So: is it normal that the difference is so big, and that tables which 
are not so very big (I think 67000 rows, or 48MB is not that huge for a 
good database) are so hard to find rows into, keeping in mind that the 
key makes the query only walk trough 2500 rows? Or is this normal?

That I would find very strange because I wrote several Oracle based 
server applications with huge queries much more complicated as the one I 
use here, which take up tot at most 0.1 seconds. Those Oracle tables 
have millions of records and the full database dump (not sql dump) is 
18GB! And I really don't think that Oracle is that much smarter than MySQL.

Can anyone tell me what's going on? Are there MySQL parameters that can 
improve things? Or is MySQL really that slow????? I won't believe the 
last one...

I tested these queries on a MySQL 3.3 and a MySQL 4.0 database, all with 
the same speed-result.

Regards,
Dirk.

-- 

Schippers Dirk
Zaakvoerder Frixx-iT
http://www.frixx-it.com
------------------------------------------------------------------------
Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen.

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