| Lookup Tables not Using Indexes? [message #3084] |
Thu, 15 May 2008 22:09  |
 StephenJ
Messages: 5
Registered: March 2008 |
Junior Member |
|
|
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.
[Updated on: Thu, 15 May 2008 22:10]
|
|
|
| Re: Lookup Tables not Using Indexes? [message #3086 is a reply to message #3084 ] |
Fri, 16 May 2008 07:59  |
StephenJ
Messages: 5
Registered: March 2008 |
Junior Member |
|
|
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
[Updated on: Fri, 16 May 2008 07:59]
|
|
|
Members
Search
Help
Register
Login
Home
