GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Lookup Tables not Using Indexes?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Lookup Tables not Using Indexes?

    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

    Comment

    Working...
    X