slowquery what's wrong?

I caught this query on the slowquerys log.

SELECT GREATEST( IFNULL(`twk-status`.`status`,0) , `groups`.`status`)
FROM `groups` 
LEFT JOIN `twk-status` ON
    (`twk-status`.`groupID` = `groups`.`ID`
    AND `twk-status`.`startdate` <= FROM_UNIXTIME(1317204000)
    AND `twk-status`.`enddate` >= FROM_UNIXTIME(1317204000)
    AND ISNULL(`twk-status`.`deleted`)
    AND `twk-status`.`groupID` = 11)
WHERE `groups`.`ID` = 11;

I ran the query using EXPLAIN producing the following report:

id  table       possible_keys                  key 
开发者_运维技巧1   twk-status  groupID,groupID_2,enddate      NULL
1   groups      PRIMARY                        PRIMARY

This mean that mysql didn't used any of the three possible keys for twk-status table. Correct?

Here's the keys from twk-status

groupID (groupID)
groupID_2 (groupID, startdate, enddate, deleted)  
enddate (enddate, createdate, deleted)

The query uses groupID, startdate, enddate, deleted I thought groupID_2 whould be the key.

Or is this a query problem and not a index problem?

Thanks in advanced.

From your explain I can judge that the order or tables is swapped.

For each record from twk-status a corresponding record from groups is being searched, since you have a PRIMARY KEY join condition among the others (twk-status.groupID = groups.ID)

However, I'm almost sure that there is something wrong with this EXPLAIN, because MySQL is not able to swap the order for LEFT JOIN.

Could you please post the exact output of EXPLAIN?

Update:

When building the plans, MySQL checks existence of constant values searched in indexed fields.

Some of these checks failed. It's impossible to tell which exactly, but you either don't have groupID = 11 in twk-status or a record with enddate >= FROM_UNIXTIME(1317204000).

MySQL just replaced the whole join with an empty row value for twk-status, and will return it along with the data for group with groupID = 11.