Create index on table with multi columns

Question

I'm new to sql, if i use this query frequently:

SELECT * FROM student WHERE key1=? AND key2=?

I want to create index on student, what is the main difference between these two below?

CREATE INDEX idx_key1 on student (key1);
CREATE INDEX idx_key2 on student (key2);

and

CREATE INDEX idx_keys on student (key1, key2);

Thanks!

Take a look at this http://stackoverflow.com/questions/19599773/oracle-single-multicolumn-index-or-two-single-column-indexes — jarlh, Oct 06 '15 at 06:25
This might help too: http://www.techrepublic.com/article/index-on-multiple-columns-for-sql-performance/ — MusicLovingIndianGirl, Oct 06 '15 at 06:26

score 1 · Accepted Answer · answered Oct 06 '15 at 06:26

The second one (CREATE INDEX idx_keys on student (key1, key2)) will return all the rows you need in a single index seek (to find the rows) + key lookups to get the columns.

If you create 2 single-column indexes, only one of them can be used for index seek. Then for every returned row you need a key lookup to get the other key and filter the results. Or the DB engine will simply decide it's faster to just do a table scan and filter.

So the 2nd one is much better for your query.

Create index on table with multi columns

1 Answers1