List:General Discussion« Previous MessageNext Message »
From:O'K Web Design Date:September 14 2004 4:59pm
Subject:Re: [Bulk] Re: MySQL speed
View as plain text  
You have not mentioned the use of a multiple column index, for that matter,
no indexes at all.  I would suggest a multiple column index in the form of
hidden, touser.  That index form should speed up your queries and solve the
problem.  Mike


----- Original Message -----
From: "Fagyal Csongor" <concept@stripped>
To: "Dirk Schippers" <Dirk.Schippers@stripped>
Cc: <mysql@stripped>
Sent: September 14, 2004 12:24 PM
Subject: [Bulk] Re: MySQL speed


> 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/mysql?unsub=1
>

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