Board index » delphi » Re: Indexing for performance

Re: Indexing for performance


2005-12-15 06:54:49 AM
delphi74
Michael Fullerton writes:
Quote
Are there any general rules for indexing to improve performance in
database servers? I am using mainly MS SQL Server, MySQL and
PostgreSQL.
Use whatever tools the database provides to display and analyze query
execution plans to optimize your queries. These tools will help you
decide when to create indices and when not to create indices.
Quote

I have worked a lot with DBISAM. In DBISAM generally speaking you want
a secondary index on every field involved in a join and on every field
involved in a WHERE clause. Are the rules similar for database
servers?
No. Indices on columns with low selectivity can hurt performance if the
optimizer is not smart enough to ignore them. The goal is to execute
the query in the least expensive way. If a query selects a large subset
of the rows in a table more total I/O may be required to read the index
then read the selected data rows than would be required by a sequential
scan of the entire table. Even if the number of reads is equal the
seqential scan will perform better because there will be less hard
drive head movement in most cases.
--
Bill Todd (TeamB)
 
 

Re: Indexing for performance

Are there any general rules for indexing to improve performance in
database servers? I am using mainly MS SQL Server, MySQL and
PostgreSQL.
I have worked a lot with DBISAM. In DBISAM generally speaking you want
a secondary index on every field involved in a join and on every field
involved in a WHERE clause. Are the rules similar for database
servers?
___
Free Project Time Tracker for Delphi Developers
www.{*word*104}-matrix.com/pcdelphi.html
 

Re: Indexing for performance

Kim Tripp is one of the most knowledgable folks about SQL Server Indexing,
some of her info can also apply to other platforms, you can find some of her
info here: www.sqlskills.com/default.asp She also does WebCasts for
MSDN.
"Michael Fullerton" <XXXX@XXXXX.COM>wrote in
message news:XXXX@XXXXX.COM...
Quote
Are there any general rules for indexing to improve performance in
database servers? I am using mainly MS SQL Server, MySQL and
PostgreSQL.

I have worked a lot with DBISAM. In DBISAM generally speaking you want
a secondary index on every field involved in a join and on every field
involved in a WHERE clause. Are the rules similar for database
servers?

___
Free Project Time Tracker for Delphi Developers
www.{*word*104}-matrix.com/pcdelphi.html