mysql index performance on small "fast-moving" tables

Question

Hi All, We've got a table we use as a queue. Entries are constantly being added, and constantly being updated, and then deleted. Though we might be adding 3 entries/sec the table never grows to be more than a few hundred rows.

To get entries out of table we are doing a simple select.

SELECT * FROM queue_table WHERE some_id = ?

We are debating adding an index on some_id. I think the small size and speed at which we are adding and removing rows would say no, but conventionally, it seems we should have an index.

Any thoughts?

Answer 1

To be sure, a benchmark using both techniques would be needed.

But generally, if the access is 50% reads and 50% writes, the penalty of updating an index could well not be worth it. But if the number of rows increase, that weights both the read and write performance such that an index should be used.

Answer 2

The only way to know for sure would be doing some benchmarks in actual/real conditions ; for example, measure the time each query takes, and :

• for one day, collect that information each time the query is run -- without the index
• and for another day, do exactly the same -- with the index.

For a table with a few hundreds rows doing both lots and inserts/deletes and select/updates, the difference should not be that big, so I think you can test in your production environment (and in real conditions) without much danger.

Yes, I know, testing in production is bad ; but in that case, it's the best way to know for sure : those conditions are probably too hard to replicate in a testing environment...

Answer 3

If you are using InnoDB (which you should do having a table of this kind) and the table is accessed concurrently, then you should definitely create the index.

When performing DML operations, InnoDB locks all rows it scans, not only those that match the WHERE clause conditions.

This means that without an index, a query like this:

DELETE
FROM    mytable
WHERE   some_id = ?

will have to do a full table scan and lock all rows.

This kills all concurrency (even if the threads access different some_id's they'll still have to wait for each other), and may even result in deadlocks.

With 3 transactions per second, no index should be a problem, so just create it.