MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Joshua Chamas Date:September 6 1999 10:00pm
Subject:Re: Question about indexing, etc...
View as plain text  
> 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