MySQL Lists are EOL. Please join:

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



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