Lookup Tables not Using Indexes?

MySQL & MariaDB Other MySQL® Questions
1

I have some tables that are lookup tables. They contain a set of IDs, with text representations for them and they are joined against a table with the IDs. This seems like a pretty common thing.

Somethign like this:

STATUSES
STATUS_ID TEXT
1 Deleted
2 Viewed
3 Unread

MESSAGES
MESSAGE_ID STATUS_ID
1 1
2 1
3 2
4 3

When I enable “log queries not using indexes” the queries with the status_id table, are showing up in the slow query log as not being indexed.

Anyone know why?

My hunch is that the optimizer knows it needs every row and doesn’t use the index… but that seems odd.

2

As a follow on to this I also have an indexed table that has no rows. The server appears to know that it returns nothing and thus doesn’t use an index, but puts it in the slow log when I have log queries without indexes turned on. For us, we have a table like this hit quite often that has “emergency announcemens” in it. Typically, the table is empty. So for 364 days out of the year this table will put an entry in our slow query log on every page.

Any idea how to deal with this? Force it to use an index in the query?

-Stephen