MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:<sinisa Date:September 6 1999 11:35am
Subject:Re: Question about indexing, etc...
View as plain text  
Fraser MacKenzie writes:
 > 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)
 > 
 > 
 > 
 > 

Hi!

MySQL is not able to use keys on receiverid and senderid, as they are
only segments in one key, and none of them is a first segment. 

Try running explain on this query:

select count(*) from msg_info where msgsaved='0' and receiverid='83'
and senderid!='0';

Then on this

select msgsaved from msg_info where msgsaved='0' and receiverid='83'
and senderid!='0';

And finally on this:

select msgsaved from msg_info where msgsaved='0';

And send me the outputs.

Sinisa

+----------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___     ==  mysql@stripped            |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sinisa Milivojevic          |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sinisa@stripped|
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Larnaka, Cyprus             |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                             |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                            |
|             /*/             \*\                Developers Team       |
+----------------------------------------------------------------------+
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