How to index a MySQL table, where 99% of time I need to query 1% of the data

Question

Hi I have a MySQL table, let's say it is a helpdesk ticketing system. It has a CLOSED column, which I want to index. 99% of time, I will need to only select on OPEN tickets, so something like

"SELECT * FROM TICKET_TABLE where CLOSED='N'; "

And over time, more and more tickets are CLOSED, while a small constant number of OPEN tickets remain. OPEN/CLOSED ratio is like 1/99.

I have an index

ALTER TABLE TICKET_TABLE ADD INDEX ( CLOSED );

But this index is not chosen ( I have a bunch of other indexes, which get chosen when I do EXPLAIN ). I can understand this CLOSED index is not good when I query

"SELECT * FROM TICKET_TABLE where CLOSED='Y'; "

but it's perfect when I query

"SELECT * FROM TICKET_TABLE where CLOSED='N'; "

How should I index my table?

Answer 1

create a single index that spans the closed column and whatever other column you are searching on.

I would recommend not creating any indexes until you figure out what queries are slow. Then come and ask what indexes to create to speed up those queries.

Answer 2

This is basic index theory.

Index will be utilized only when the data ratio in the query is small enough('selective") like closed='N'.

So in the case closed='Y', full table scan(which is not using index) will be faster than using index.

To make you query faster, try to restrict the condition further. Maybe closing date, or certain customer or area.

Answer 3

One option would be to archive all old tickets that have been closed for a while, to keep the number of rows down. When you need to search over all tickets, you can join the two tables quite easily.

Answer 4

You can force mysql to use your index by doing e.g.

"SELECT * FROM TICKET_TABLE FORCE INDEX (name_of_index) where CLOSED='N'; "

mysql can only use 1 index per table for your where clause, maybe it figured one of the other indexes were more appropriate - mysql might select a "wrong" index if its statistics are not up to date. (try e.g. running analyze table on the table).