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.
