MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Fraser MacKenzie Date:September 6 1999 11:59pm
Subject:Re: Question about indexing, etc...
View as plain text  
Thanks Joshua, I fixed the indexes this morning.  The new ones I have are
pumping the stuff out really fast.  Thanks for answering the question
though.


Fraser MacKenzie (fraserm@stripped)
---------------- (ICQ: 15389075)
Rondak's Portal  (http://www.rondaksportal.com/)

The best, free, role-playing on the internet!  
Lose yourself in the fantasy!

On Mon, 6 Sep 1999, Joshua Chamas wrote:

> > select count(*) from msg_info where receiverid='83' and msgsaved='0' and
> > senderid!='0';
> > 
> 
> For this query, you need an index on (receiverid, msgsaved, senderid).
> You already have a key on msgsaved ... KEY msgsaved (msgsaved) ...
> so you could rebuild this key as:
> 
> KEY (msgsaved, receiverid, senderid).
> 
> The point is that you want all of your test keys in the 
> relevant index, as it won't help to have these indexed 
> separately.
> 
> These multi column indexes can be very useful if constructed
> correctly to be used for many of your queries, and you should
> keep your entire application in mind whenever designing its indexes.
> 
> --Joshua
> ______________________________________________________________________
> Joshua Chamas                      Chamas Enterprises Inc.
> NODEWORKS - web link monitoring    Long Beach, CA  USA  1-562-432-2469
> http://www.nodeworks.com           http://www.chamas.com
> 
> Fraser MacKenzie wrote:
> > 
> > Ok folks, this thing has been driving me absolutely nuts for the last few
> > days since it was brought to my attention.
> > 
> > MySQL version: 3.22.22
> > OS: RedHat 5.2
> > 
> > I have a table msg_info, which is used to store information about a
> > message (not the body of the message) but information about each user who
> > has received it such as: has it been read, there username, who sent it, a
> > datetime stamp of when it was sent, etc....  Here is the table:
> > 
> > # MySQL dump 5.13
> > #
> > # Host: *****    Database: *****
> > #--------------------------------------------------------
> > # Server version        3.22.22
> > 
> > #
> > # Table structure for table 'msg_info'
> > #
> > CREATE TABLE msg_info (
> >   msgid datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
> >   receiverid int(8) unsigned DEFAULT '0' NOT NULL,
> >   username varchar(20) DEFAULT '' NOT NULL,
> >   senderid int(8) unsigned DEFAULT '0' NOT NULL,
> >   boardid smallint(5) unsigned DEFAULT '0' NOT NULL,
> >   msgread int(4) DEFAULT '0' NOT NULL,
> >   msgsaved int(1) unsigned DEFAULT '0' NOT NULL,
> >   msgflag int(1) unsigned DEFAULT '0' NOT NULL,
> >   groupid int(12) unsigned DEFAULT '0' NOT NULL,
> >   KEY msgsaved (msgsaved),
> >   KEY msgflag (msgflag),
> >   KEY groupid (groupid),
> >   PRIMARY KEY (msgid,receiverid,username,senderid,boardid),
> >   KEY msgread (msgread)
> > );
> > 
> > Now, when I do even a somewhat simple select on this table, such as:
> > 
> > select count(*) from msg_info where receiverid='83' and msgsaved='0' and
> > senderid!='0';
> > 
> > it takes 3-4 seconds (as does any query, including those with joins).
> > Now, I have done an explain on the table and get the following:
> > 
> >
> +----------+------+---------------+------+---------+------+-------+------------+
> > | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra
> > |
> >
> +----------+------+---------------+------+---------+------+-------+------------+
> > | msg_info | ALL  | msgsaved      | NULL |    NULL | NULL | 62831 | where used
> |
> >
> +----------+------+---------------+------+---------+------+-------+------------+
> > 1 row in set (0.01 sec)
> > 
> > What makes no sense, is that msgsaved is indexed (the whole table is
> > indexed because in various CGIs I do queries on different portions of the
> > table), and it still has to do an entire table scan.  Any insight into
> > this would be gratefully appreciated.
> > 
> > Fraser MacKenzie (fraserm@stripped)
> > ---------------- (ICQ: 15389075)
> > 
> > ---------------------------------------------------------------------
> > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> > posting. To request this thread, e-mail mysql-thread12338@stripped
> > 
> > To unsubscribe, send a message to the address shown in the
> > List-Unsubscribe header of this message. If you cannot see it,
> > e-mail mysql-unsubscribe@stripped instead.
> 

Thread
MySQL Importing to Web PagesSimon Hyde5 Sep
  • Re: MySQL Importing to Web PagesTamas TEVESZ5 Sep
  • Re: MySQL Importing to Web Pagessinisa6 Sep
    • Question about indexing, etc...Fraser MacKenzie6 Sep
      • Re: Question about indexing, etc...Fraser MacKenzie6 Sep
      • Re: Question about indexing, etc...sinisa6 Sep
      • Re: Question about indexing, etc...Joshua Chamas7 Sep
        • Re: Question about indexing, etc...Fraser MacKenzie7 Sep