MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Fraser MacKenzie Date:September 6 1999 11:08am
Subject:Re: Question about indexing, etc...
View as plain text  
FYI, I forgot to add that I have the same problem, and get the same
output, whenever I specify any of the KEY fields, other than those in the
PRIMARY KEY.  I have moved over the msgread, msgsaved, etc. fields into
the primary key, but get the same message (with the exception of I don't
have any possible keys then).


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, 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)
> 
> 
> 
> 

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