CODEWITHSUNDEEP
sqldatabasesqliteindexing
Stylock
Stylock

Reputation: 1499

Which columns should be indexed?

Let's imagine I have a table named "users". The code for creation:

CREATE TABLE IF NOT EXISTS users 
(id_user INTEGER PRIMARY KEY AUTOINCREMENT, 
 username VARCHAR(32) COLLATE NOCASE, 
 passwd_hash VARCHAR(255) NOT NULL DEFAULT '', 
 passwd_salt VARCHAR(255) NOT NULL DEFAULT '', 
 email_addr VARCHAR(255) NOT NULL DEFAULT '');


CREATE INDEX IF NOT EXISTS idx_id_user ON users (id_user ASC);
CREATE INDEX IF NOT EXISTS idx_username ON users (username ASC);

When a player joins the server, it checks if the player's username is registered:

SELECT id_user 
FROM users 
WHERE username = '%s' LIMIT 1

If the username is registered, the player will be asked to login. On login attempt I have this:

SELECT passwd_hash, passwd_salt 
FROM users 
WHERE id_user = %d

Then it obviously checks if both passwords match.

So my question is, should passwd_hash and passwd_salt be indexed?

Upvotes: 1

Views: 1627

Answers (4)

Bill Karwin
Bill Karwin

Reputation: 562348

I would create a three-column index: (userid, password_hash, password_salt). This can be used as a covering index for greater efficiency.

It seems that this is only a minor improvement in SQLite, but the concept is used to greater benefit in other RDBMS implementations that can cache indexes in RAM.

http://www.sqlite.org/queryplanner.html says:

1.7 Covering Indices
The "price of California oranges" query was made more efficient through the use of a two-column index. But SQLite can do even better with a three-column index that also includes the "price" column:

This new index contains all the columns of the original FruitsForSale table that are used by the query - both the search terms and the output. We call this a "covering index". Because all of the information needed is in the covering index, SQLite never needs to consult the original table in order to find the price.

Hence, by adding extra "output" columns onto the end of an index, one can avoid having to reference the original table and thereby cut the number of binary searches for a query in half. This is a constant-factor improvement in performance (roughly a doubling of the speed). But on the other hand, it is also just a refinement; A two-fold performance increase is not nearly as dramatic as the one-million-fold increase seen when the table was first indexed. And for most queries, the difference between 1 microsecond and 2 microseconds is unlikely to be noticed.

You may be interested in reading my presentation, How to Design Indexes, Really. I did that presentation for MySQL users, but the concepts are relevant for SQLite and most other RDBMS's too.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269803

When you execute the query:

SELECT passwd_hash, passwd_salt
FROM users
WHERE id_user = %d;

The SQL engine will use the index to find the right record. It then goes into the table itself to retrieve the data needed for the select clause.

If you instead build the index as:

CREATE INDEX IF NOT EXISTS idx_id_user ON users (id_user ASC, paswd_hash, passwd_salt);

Then the SQL engine can satisfy the query just by using the index. This could provide a performance boost. The gain would be quite minor.

This is a general principle, but there are exceptions. Some databases support the notion of a clustered index on a data column. In such an index, the data in the table has to be ordered by the key, and the table itself operates as the index. However, this is not a SQLite index option.

Upvotes: 4

J28
J28

Reputation: 1110

I believe no. You have an index created on id_user which is suffice to fetch password information efficiently. Certainly we don't create an index on every field just because we retrieve it.

Upvotes: 0

Chris Van Opstal
Chris Van Opstal

Reputation: 37547

No. You only need to index columns that you are going to query against.

Once the record is found an index won't help in retrieving the other columns in that record any faster.

Upvotes: 1

Related Questions