(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
