"IS NOT NULL" Breaks my Indexes

MySQL & MariaDB Other MySQL® Questions
1

I have a couple tables, events and medias, and am having trouble with a query and a multiple column index.

Basically I want to only get events that have medias (images and videos) attached to them. So I have a query like this:

SELECT events.* FROM eventsLEFT OUTER JOIN medias ON medias.event_id = events.id WHERE start IS NOT NULL AND is_public = 1 AND start < CURDATE() AND medias.id IS NOT NULL ORDER BY events.start ASC

To optimize the query I have created a multiple column index for event.is_public and event.start. When I run examine, however, it only uses PRIMARY:

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: medias type: indexpossible_keys: PRIMARY,index_medias_on_event_id key: index_medias_on_event_id key_len: 5 ref: NULL rows: 2753 Extra: Using where; Using index; Using temporary; Using filesort*************************** 2. row *************************** id: 1 select_type: SIMPLE table: events type: eq_refpossible_keys: PRIMARY,index_events_on_start_and_is_public key: PRIMARY key_len: 4 ref: mixer.medias.event_id rows: 1 Extra: Using where2 rows in set (0.00 sec)

When I remove “medias.id IS NOT NULL” from the query it will use the appropriate index.

Is there a way to fix this so that I don’t need to use index hinting?

Thanks.

2

First, you should re-write your query as an INNER JOIN, since you’ve said you only want events which have medias. It’s basically what you’re doing in your query by including the restriction medias.id IS NOT NULL. Try this instead:

SELECT e.* FROM events AS e, medias AS mWHERE m.event_id = e.id AND e.start IS NOT NULL AND e.is_public = 1 AND e.start < CURDATE()ORDER BY e.start ASC