I have the following query:

SELECT event.* FROM event
    invalid.event_id LIKE event.id
    AND invalid.userid LIKE '<userID>'
    owner.id LIKE event.owner_id
LEFT OUTER JOIN location_event ON
    location_event.event_id LIKE event.id
WHERE event.type IN (<listoftypes>)
    AND invalid.id IS NULL
    AND lower(location_event.location_name) LIKE CONCAT('%', '<searchtext>', '%')
ORDER BY event.start_timestamp DESC
LIMIT 0, 10

The WHERE clauses match a large proportion of the total rows in the event table. I'm trying to set an index on event.start_timestamp to improve the query time. However, using EXPLAIN, it does not appear in the possible_keys. EXPLAIN does state that it is Using temporary and Using filesort.

I believe that Using filesort causes the query to not use indexes for the ORDER BY clauses. To avoid it I need to shrink the size of the rows caused by the 3 joins (which are all necessary). Is it possible to reduce the number of columns brought through with a join?

Also, are there any other parts of this query which stick out as being slow/inefficient?

  • 4
    Can you add table structures and execution plan. The like isn't very efficient and might be the source of issues instead of the joins – Tom V Jul 22 '15 at 13:15
  • The right index should contain two columns (type, start_timestamp), but index only on start_timestamp should be still usable. You do not want to join tables using LIKE even if your IDs are VARCHAR (which is usually not very good idea itself). – jkavalik Jul 22 '15 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.