CODEWITHSUNDEEP
sqlsql-execution-plan
Victor
Victor

Reputation: 17097

Sql indexes vs full table scan

While writing complex SQL queries, how do we ensure that we are using proper indexes and avoiding full table scans? I do it by making sure I only join on columns that have indexes(primary key, unique key etc). Is this enough?

Upvotes: 2

Views: 5643

Answers (4)

XtremeBytes
XtremeBytes

Reputation: 1497

Use Database Tuning Advisor(SQL Server) to analyse your query. It will suggest necessary indexes to add to tune your query performance

Upvotes: 1

Igor
Igor

Reputation: 21

It is hard to say what is the best indexing because there are different strategies depend on situation. Still there are coupe things you should now about indexes.

  1. Index SOMETIMES increase performance on select statement and ALWAYS decrease performance on insert and update.
  2. To index table it is not necessary to make it as key on certain field. Also, real life indexes almost always include several fields.
  3. Don't create any indexes for "future purposes" if your performance is satisfactory. Even if you don't have indexes at all.

  4. Always try to analyze execution plan when tuning indexes. Don't be afraid to experiment.

      +
  5. Table scan is not always bad thing.

That is all from me.

Upvotes: 2

W van Noort
W van Noort

Reputation: 383

Look at the execution plan of the query to see how the query optimizer thinks things must be retrieved. The plan is generally based on the statistics on the tables, the selectivity of the indices and the order of the joins. Note that the optimizer can decide that performing a full table scan is 'cheaper' than index lookup.

other Things to look for:

  • avoid subqueries if possible.

  • minimize the use of 'OR'-predicates
    in the where clause

Upvotes: 2

Axel Fontaine
Axel Fontaine

Reputation: 35169

Ask the database for the execution plan for your query, and proceed from there.

Don't forget to index the columns that appear in your where clause as well.

Upvotes: 4

Related Questions