Some SQL tips and notes
About indexes
An index helps database to find and sort records faster. When you create a database and tune it for performance, you should create indexes for the columns used in queries to find data.
Indexes in databases are similar to indexes in books. In a book, an index allows you to find information quickly without reading the entire book. In a database, an index allows the database program to find data in a table without scanning the entire table.
You can create indexes based on a single field or on multiple fields. Multiple-field indexes enable you to distinguish between records in which the first field may have the same value.
The performance benefits of indexes do come with a cost. Tables with indexes require more storage space in the database. The commands that insert, update, or delete data can take longer and require more processing time. You should ensure that the performance benefits outweigh the extra cost.
Which fields to index?
You'll probably want to index fields you search frequently, fields you sort, or fields that you join to fields in other tables in queries. The primary key of a table is automatically indexed, and you can't index a field whose data type is OLE Object. If many of the values in the field are the same, the index may not significantly speed up queries. For other fields, you should consider indexing a field if the following apply:
• The field's data type is Text, Number, Date/Time, or Currency.
• You anticipate searching for values stored in the field.
• You anticipate sorting values in the field.
• You anticipate storing many different values in the field.
An index helps database to find and sort records faster. When you create a database and tune it for performance, you should create indexes for the columns used in queries to find data.
Indexes in databases are similar to indexes in books. In a book, an index allows you to find information quickly without reading the entire book. In a database, an index allows the database program to find data in a table without scanning the entire table.
You can create indexes based on a single field or on multiple fields. Multiple-field indexes enable you to distinguish between records in which the first field may have the same value.
The performance benefits of indexes do come with a cost. Tables with indexes require more storage space in the database. The commands that insert, update, or delete data can take longer and require more processing time. You should ensure that the performance benefits outweigh the extra cost.
Which fields to index?
You'll probably want to index fields you search frequently, fields you sort, or fields that you join to fields in other tables in queries. The primary key of a table is automatically indexed, and you can't index a field whose data type is OLE Object. If many of the values in the field are the same, the index may not significantly speed up queries. For other fields, you should consider indexing a field if the following apply:
• The field's data type is Text, Number, Date/Time, or Currency.
• You anticipate searching for values stored in the field.
• You anticipate sorting values in the field.
• You anticipate storing many different values in the field.
Comments