Dirk,
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?
Just some ideas for you.
1: I think you should use "CHAR" instead of "VARCHAR". Might take more place, but using fixed length fields in general mean faster access time.
2: To walk through 2500 rows might take some time, as the "hidden" fields for each rows must be fetched (IMHO).
3: You should use query caching. That might speed up your query tremendously.
4: You can try a hack: instead of using a field do denote that a user is not hidden, you might want to consider using some other method, e.g. using a signed integer for userid, and storing inactive users with a negative userid. Then you could count on users "where userid > 0".
Regards, - Csongor Fagyal
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
