Good Man wrote:
> Hi there
>
> While getting some undesired results from a query, I looked a little
> closer at it and was surprised when my EXPLAIN statement told me that my
> query was looking through all the rows in a particular table, and no
> keys were being used on this table - "possible_keys" and "key" are both
> NULL for this table in the EXPLAIN statement. Sure enough, the
> "Select_full_join" variable in MySQL Administrator increments by one
> every time I use the query.
>
> I can't figure out WHY this is happening, as I have an index on the
> table that should be used.... here is my query:
>
> SELECT
> ue.EmailID,ue.ToAddress,ue.Subject,ue.Message,ue.S avedDate,ue.EmailKey,
> f.FileKey,f.FileName
> FROM UserEmails ue
> LEFT JOIN UserEmailAttachments ua ON ue.EmailKey=ua.EmailKey
> LEFT JOIN ProjFiles f ON ua.FileKey=f.FileKey
> WHERE ue.AdminID=1 AND ((ue.SavedDate > 1164344436) AND (ue.SentDate=0))
>
> Basically, what I want to be doing here is grabbing a users 'email
> message' in the database. LEFT JOINS are used on the
> UserEmailAttachments and ProjFiles tables because an 'email' may or may
> not have any files attached to it.
>
> The problem table in the EXPLAIN statement is ProjFiles. It has 7803
> rows so far, and they're all being looked at, even though the FileKey
> column (which is the one being checked in the query) is indexed:
>
> CREATE TABLE `ProjFiles` (
> `FileID` int(10) unsigned NOT NULL auto_increment,
> `FolderID` int(10) unsigned NOT NULL default '0',
> `ClientID` int(10) unsigned NOT NULL default '0',
> `FileSetID` mediumint(8) unsigned NOT NULL default '0',
> `FileType` varchar(255) NOT NULL default '',
> `FileSize` varchar(20) NOT NULL default '0',
> `VersionNum` varchar(10) default NULL,
> `Note` text,
> `AddedBy` int(10) unsigned default NULL,
> `AddedByUser` int(10) unsigned default NULL,
> `DateAdded` varchar(100) default 'May 15th, 2005',
> `DateStamp` bigint(20) unsigned NOT NULL default '0',
> `FileName` varchar(250) NOT NULL default '',
> `FilePath` text NOT NULL,
> `FileKey` varchar(30) NOT NULL default '',
> PRIMARY KEY (`FileID`),
> KEY `CliDex` (`ClientID`),
> KEY `FoldDex` (`FolderID`),
> KEY `SetDex` (`FileSetID`),
> KEY `KeyDex` (`FileKey`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> Any tips? Thanks.


because you do not have an index on AdminID, SaveDate and SentDate.

create index ue_idx on `UserEmails` (`AdminID`, `SaveDate`,
`SentDate`).....