Recent

Author Topic: Use of SQL table index  (Read 2544 times)

nightrider

  • Full Member
  • ***
  • Posts: 139
Use of SQL table index
« on: January 03, 2012, 12:01:12 pm »
I have to access a SQL Table in a SQLite DB that I define an index using 2 columns.

Is there a way to be sure that if I code a "select" in FPC/Lazarus application to this table it will be done using the mentioned index? The "where" clause of the select uses only these 2 columns.

If affirmative is this way valid for any RDBMS I use? In case the select use more columns besides these 2(e.g., these 2 columns and some other(s))

Greetings from São Paulo - SP

Ricardo

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Use of SQL table index
« Reply #1 on: January 03, 2012, 01:54:40 pm »
Quote
Is there a way to be sure that if I code a "select" in FPC/Lazarus application to this table it will be done using the mentioned index? The "where" clause of the select uses only these 2 columns.
The database will use an index when it speeds up the select. The user hasn't to do anything for that and fpc/lazarus isn't involved in this at all. Just to be clear, we are talking here about indices defined with the CREATE TABLE or ALTER TABLE statement, not the dataset indices (IndexName in TSqlQuery) which have nothing to do with the database.
Quote
If affirmative is this way valid for any RDBMS I use? In case the select use more columns besides these 2(e.g., these 2 columns and some other(s))
Yes, all databases behave the same. The internal SELECT optimisation algorithms are different but that is all transparent to the user. Some databases will for example make more use of temporary tables (disk or memory) to store intermediate results or cache more or less intermediate results for later re-use.
When using columns in WHERE that are not indexed then typically the database will use first the indexed columns to filter the result set and then scan the resulting rows to filter on the other columns.

A useful command in databases is "explain query plan" (for SQLite see http://www.sqlite.org/eqp.html). This will show you how the database will do the search, what indices are use, temporary tables, joins, etc. The syntax varies somewhat for the different rdbms and output is quite different. In the end, what you need to watch for is the number of records are left for a full scan. If this is very large, you want probably to add another index.  Since indices have also a cost (storage, access time, slowing down writes) choosing the right indices can be quite difficult and a solution that is optimised for 10.000 records could be awful for 1.000.000 records.

 

TinyPortal © 2005-2018