(Hi all, I'm a friend of Dirk and helping him trying to speed up everything)
Fagyal Csongor wrote:
> Dirk Schippers wrote:
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!!!)
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.
We're using Debian stable, which means MySQL 3.23. I like to use the packages and versions that come with Debian, but if necessary we'll look into upgrading.
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".
That's not how it works: it's not the users that are hidden; only some of their messages are hidden.
Anyway, I'm confused too... I have a copy of a week-old version of Dirk's database on my home machine. When I run the same query, it takes between 0.00 and 0.02 seconds, on a machine that is in more or less the same performance category as Dirk's server.
There is a world of difference between that and the >1 seconds Dirk is gettig. So it seems there is something wrong somewhere. I checked CPU and memory usage on the server, but everything looks pretty normal. Now I'm guessing there's something wrong with the MySQL configuration, but I don't know enough about MySQL to know what it could be. Any pointers in the right direction would be greatly appreciated.
-- "Codito ergo sum" Roel Schroeven
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
