List:General Discussion« Previous MessageNext Message »
From:Roel Schroeven Date:September 14 2004 9:43pm
Subject:Re: MySQL speed
View as plain text  
(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

Thread
MySQL speedDirk Schippers14 Sep
  • Re: MySQL speedFagyal Csongor14 Sep
    • Re: MySQL speedRoel Schroeven14 Sep
  • Re: MySQL speedDan Nelson14 Sep
    • RE: MySQL speedDonny Simonton14 Sep
  • Re: [Bulk] Re: MySQL speedO'K Web Design14 Sep
  • Re: MySQL speedTobias Asplund14 Sep
    • Re: MySQL speedRoel Schroeven15 Sep