0

As far as I understand, indexes should be selected on fields with high cardinality of data - e.g. sex only has two values ( male and female ) so low cardinality, whereas something like sha256sum field would be many varied strings, so high cardinality of data. That makes sense that it improves searching with simple SELECT * from table_foo WHERE foo = 'bar'; type of query. But if I have something like SELECT * from table_foo WHERE foo = 'bar' and baz > 42; how do I construct an index to improve performance ? Do i provide multiple fields to index ? Do I create two different indexes ?

I'm working with SQLite, but the topic isn't SQLite specific. However, SQLite is minimalist so if there's certain things to be kept in mind for this specific database that'd be also good to know.

1
  • How exactly indexes are uses is very dependent on the engine. Looking at SQLite tutorial on indexes it seems to be b-tree based and as a guess a compound (foo,bar), static compared fields before range comparisons, would be the ideal index. Try it and show EXPLAIN QUERY PLAN {query} and show the table structure/indexes.
    – danblack
    Commented Dec 5, 2018 at 2:45

1 Answer 1

2

Privet Sergiy,

Index design and cost based query optimizers are complicated subjects, and this isn't the place to cover all aspects, so I will address your specific question.

To satisfy the WHERE clause, the 'optimal' index for your query example might be a composite index on (foo, baz). Why?

Think about a phone book directory, and foo and baz as last name and first name respectively. Let's say you are looking for all records in the phone book where the last name is 'doh', and the first name > 'John'. The composite index on last name, first name the phone directory uses allows you to quickly navigate to the 'doh' section, find the first 'John', and from there just follow the list one by one until you exhaust all 'Doh' last names. Could you do it with an index just on Last name? yes.. but you would need to scan all 'Doh' records. An index on First name only, means you have to scan all 'John's, and all following first names to find your 'Doh's.

If your table consists of just foo and baz, it's a no-brainer. That said, the fact that your SELECT list uses a *, means that if the index doesn't contain all the data to satisfy the query, an additional 'lookup' will be needed for each row to get the rest of the columns. This would be similar to an index at the end of the book, that has a pointer to the relevant page. You could potentially include all columns of the table in the index, but that may cause challenges for data modifications and bloat database size.

The optimizer now needs to decide between using the index, and performing potentially many 'lookups', and if it assumes there will be many of those, it may decide to simply scan the whole table and avoid the lookups. This is why cost based optimizers require statistics on the columns to make the right decisions, some engines such as SQL Server also store complete histograms of value distribution for better accuracy.

Different RDBMS engines will handle this differently, so look for resources specific to the engine you are using.

HTH

1
  • Thanks for clarification and sorry it took a while to respond. Happy New Year and thanks for the answer ! Commented Jan 1, 2019 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.