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