Index question

[Damian Hischier]

I have the following select statement in a php application:

SELECT DutyTime.DtiId AS dutytime_id, DutyTime.DtiDtyCode as duty_code FROM DutyTime, DutyType WHERE DutyTime.DtiEmpId=%d AND DutyTime.DtiDateTimeCko<='%s' AND DutyType.DtyCode=DutyTime.DtiDtyCode AND (LENGTH(TRIM(DutyType.DtyRt))=0) ORDER BY DutyTime.DtiDateTimeCko DESC LIMIT 1

Since there are a couple of thousand records in the table "DutyTime" now, the performance is pretty bad now. It takes quite a long time to execute the above select statement. I played around with creating indexes on that table, but somehow it didn't change much. What indexes do I have to create in order to increase the performance on that select?

That is the output of the describe statement of the table:
+-----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+----------------+
| DtiId | int(11) | NO | PRI | NULL | auto_increment |
| DtiEmpId | int(11) | NO | | | |
| DtiDate | date | NO | | | |
| DtiDtyCode | int(11) | NO | | | |
| DtiTimeRest | time | NO | | | |
| DtiDateTimeCki | datetime | NO | | | |
| DtiDateTimeCko | datetime | NO | | | |
| DtiTimeDuty | time | NO | | | |
| DtiDateTimeNext | datetime | NO | | | |
| DtiRemark | varchar(1024) | NO | | | |
| DtiLocked | varchar(2) | NO | | | |
| DtiSubmited | varchar(2) | NO | | | |
+-----------------+---------------+------+-----+---------+----------------+

Thank you very much for your help