Best way to create SQL indexes?

Question

What is the best way to create an SQL index in an SQL database?

CREATE INDEX idx ON sometable (col1, col2, col3);

or

CREATE INDEX idx1 ON sometable (col1);
CREATE INDEX idx2 ON sometable (col2);
CREATE INDEX idx3 ON sometable (col3);

What is the difference between these two methods? Does it depend on the SQL implementation? (SQLite, MySQL, MSSQL, ...)? Are there any considerations of effectiveness when using one of the two methods?

Answer 1

CREATE INDEX idx ON sometable (col1, col2, col3);

This will create a single index for all three fields together. This is ideal if your queries will use all three fields for a WHERE clause most of the time:

SELECT (fields) FROM Table WHERE col1 = 1 AND col2 = 2 AND col3 = 3

This composite index will also be used when querying only col1 and col2:

SELECT (fields) FROM Table WHERE col1 = 1 AND col2 = 2 

, col2, col3 , col2 col3 :

SELECT (fields) FROM Table WHERE col2 = 2 AND col3 = 3  -- will *NOT* use index
SELECT (fields) FROM Table WHERE col2 = 2               -- will *NOT* use index
SELECT (fields) FROM Table WHERE col3 = 3               -- will *NOT* use index

, :

CREATE INDEX idx1 ON sometable (col1);
CREATE INDEX idx2 ON sometable (col2);
CREATE INDEX idx3 ON sometable (col3);

. , , :

SELECT (fields) FROM Table WHERE col1 = 1   -- will use index on col1
SELECT (fields) FROM Table WHERE col2 = 2   -- will use index on col2
SELECT (fields) FROM Table WHERE col3 = 3   -- will use index on col3

, :

SELECT (fields) FROM Table WHERE col1 = 1 AND col2 = 2 AND col3 = 3

, , , , (col1, col2, col3).

, , , " " . , , "" .

Answer 2

3 , . MSDN.

( , , , )