No Indexes on Small Tables

No indexes on small tables?

The value of indexes is in speeding reads. For instance, if you are doing lots of SELECTs based on a range of dates in a date column, it makes sense to put an index on that column. And of course, generally you add indexes on any column you're going to be JOINing on with any significant frequency. The efficiency gain is also related to the ratio of the size of your typical recordsets to the number of records (i.e. grabbing 20/2000 records benefits more from indexing than grabbing 90/100 records). A lookup on an unindexed column is essentially a linear search.

The cost of indexes comes on writes, because every INSERT also requires an internal insert to each column index.

So, the answer depends entirely on your application -- if it's something like a dynamic website where the number of reads can be 100x or 1000x the writes, and you're doing frequent, disparate lookups based on data columns, indexing may well be beneficial. But if writes greatly outnumber reads, then your tuning should focus on speeding those queries.

It takes very little time to identify and benchmark a handful of your app's most frequent operations both with and without indexes on the JOIN/WHERE columns, I suggest you do that. It's also smart to monitor your production app and identify the most expensive, and most frequent queries, and focus your optimization efforts on the intersection of those two sets of queries (which could mean indexes or something totally different, like allocating more or less memory for query or join caches).

Indexes on small tables

Indices are there to avoid table scans - which lead to locks. Even a 1000 row table will get a lot less throughput without an index and given that no index is available in joins this will lead to certain constructs being favoured which you will not like in loops.

Do very small MySQL tables ignore indexes?

Do very small tables ignore indexes?

Yes. When the entire table can be read in a single disk access, there's no point in performing a separate disk access to read the index.

If so, how can I prevent this query from flooding my slow query log?

Turn off log_queries_not_using_indexes. This is one of the reasons why it isn't on by default.

Why is PostgreSQL not using my indexes on a small table?

Maybe, the reason is that I have too few rows in my table?

Yes. For a total of 20 rows in a table a seq scan is always going to be faster than an index scan. Chances are that those rows are located in a single database block anyway, so the seq scan would only need a single I/O operation.

If you use

explain (analyze true, verbose true, buffers true) select ....

you can see a bit more details about what is really going on.

Btw: you shouldn't use text as a column name, as that is also a datatype in Postgres (and thus a reserved word).

Can a table be too small to have any gain by indexing it?

Like anything and everything SQL, IT DEPENDS.

For a 10 record table you probably will never see the index used. The optimizer will see the difference between a table scan and an index scan as being nil.

For larger tables, it's going to depend. There is no "cut off point" where it becomes beneficial for every table. It will depend on row width, selectivity of the field you index, width of the index, if it's clustered or non-clustered, etc.

I would say if you start having performance issues, see if an index makes a difference. If a table is over 1000 rows I normally index it if I will be joining on it, since the space used and time to create/maintain the index is trivial (assuming you aren't deleting/inserting a lot in a table that small).

Should I add indices to small table?

It is unlikely that a table that size will ever use the index. If the table will be stable in size and always be small, I would not add indexes.

From Books Online:

Indexing small tables may not be
optimal because it can take the query
optimizer longer to traverse the index
searching for data than to perform a
simple table scan. Therefore, indexes
on small tables might never be used,
but must still be maintained as data
in the table changes.

So it likely to be a poor idea to index small tables.

No indexes found in any table - is that possible?

Sure it's possible.

Common, even :)

It just means nobody's created any indexes.

If the query returned nothing, it means that you DO have permission ... and there simply aren't any indexes.

Here's a good link on "Managing indexes in Oracle" (it sounds like you're probably running Oracle):

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm

When is an index justified on a small reference table?

An explicit index is justified but not necessary. The referencing column should be declared as a primary key. This will automatically create an index. Why do you want a primary key? Referential integrity. You want to be sure that the tables that reference such tables have correct values.

I would suggest that you use a 2-byte or 4-byte number as the primary key. This reduces the overhead in other tables that reference the reference tables. Also, indexes are slightly more efficient when the keys are fixed length versus variable length.

So, I would suggest something like this:

CREATE TABLE category (
    categoryId smallint generated always as identity primary key
    category VARCHAR unique
);

How to determine if an Index is required or necessary

I use Jason Strate's index analysis scripts. They tell you how much your existing indexes are used as well as how much missing indexes would have been used. I typically don't add indexes unless they make up more than 5 or 10% of the queries on a table.

Most importantly, though, it's about making sure the application responds fast enough for the users.

Jason Strate's index analysis blog articles)

These days, I use sp_BlitzIndex® when performing index analysis.

---

Related Topics