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.