How to utilise indexes in SQL

Question

I'm building a rather large database - which has around 6.9 million records.

A simple select is taking 6-7 seconds, so I'm now working on optimising and investigating other options.

An obvious one is to create an index or two.

Sample:

CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)

That worked well.

However, I cannot get valid sql syntax to use the index in a select statement. One assumes you can use "select index_name from table_name", well at least I did :) However it appears to be invalid, and I cannot for the life of me find an example on the net, which either implies I'm coming from the wrong angle totally, or nobody has documented it...

Any help on how to use indices would be appreciated!

Answer 1

I think it might depend on which kind of dstabase you use, but for my experience with SQL Server (2000, 2005, 2008) just don't really have to worry about the index name. You just create it on columns you might often use as filters in your queries (WHERE clause or joins) and then do your queries normally. The database server will take care to use the index whenever it thinks it can help.

Hope this helps

Answer 2

I don't think you understand how indexes work. You perform a select on an indexed column the same way you perform one on an indexed one.

Answer 3

The database will use the indicies without you telling it. This is what the query optimizer will do.

Look at the query plan - example for Sybase and sql-server os set showplan on select .....

This will show if an index is used

On odd occasions you do need to force indices but this is rare

Answer 4

SQL Server will try to dertermine which index will be the best for the query you have written. This will be done with the help of statistics he has on the various indices. (You can have more then one index per table, but you should limit the number of indexes you create)

Also you can create something thats calles a covering index. Thats an index that will have all fields for a query in it. This allows SQL Server to query only the index, and not the table it is indexing.

You also have the option to create an index hint in a query to force sql server to prefer one index over another, but you should not use that option, and rather keep your statistics up to date on the index.

Hope that helps

Answer 5

You do not need to explicitly specify the indexes you want the database to use. The database optimizer will look at what it knows about available indexes and algorithms for answering your query, and (hopefully) select the best plan for the task at hand.

So you just need to select from your table as before; the database will "do the right thing" automatically.

Because the optimizer is a bit opaque to even the most experienced developers, it is important when developing queries to look at the "execution plan" generated for them. You can see the plan using your database's flavor of the EXPLAIN command.

There is art to setting up the right indexes. Here is a recent example that goes through what algorithms and index combinations are best for certain kinds of selects on the MySQL database: http://www.mysqlperformanceblog.com/2009/09/19/multi-column-indexes-vs-index-merge/ This is probably a bit too advanced to be useful to you right now, but read through it to get a feeling for the kinds of things the optimizer is trying to figure out for you.

Answer 6

If your using MySQL you can specify which index to use or not use.

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index)
  WHERE col1=1 AND col2=2 AND col3=3;

You can find more information here: http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

Warren Brian Noronha