I have the following query:
SELECT event.* FROM event LEFT OUTER JOIN invalid ON invalid.event_id LIKE event.id AND invalid.userid LIKE '<userID>' LEFT OUTER JOIN owner ON 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
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
EXPLAIN does state that it is
Using temporary and
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?