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
