What is add_index for?
Someone told me it was to add indexes to the database, but I still dont
know
what indexes are… first I thought they where other columns of the
table,
but apparently not.
Thank you for the help,
Rodrigo
On Jul 7, 2011, at 7:28 AM, Rodrigo R. wrote:
What is add_index for?
Someone told me it was to add indexes to the database, but I still
dont know what indexes are… first I thought they where other
columns of the table, but apparently not.
Indexes are a feature of the relational database. One way they work is
to organize common query features (sort by date added, for example) in
the database’s structure so that they can happen much more quickly
than a simple brute-force approach could do. Other types of indexes
can enforce uniqueness (only one copy of this e-mail address may be
added ever) at a fundamental level.
Adding an index to a column can return dramatic speed improvements on
reads, at the expense of slightly longer write times. Like anything,
it’s a trade-off.
Like anything to do with the raw database design, you’ll have to read
up on the specifics of your particular RDBMS to figure out which sort
of index structure makes the most sense for your application. If you
have access to someone who is a DBA, they can tell you lots of useful
tricks that you might use.
But always, add an index (for speed) when you are tuning, not before.
An index for uniqueness you can add if it makes sense in your design.
Walter
When you want to look up a specific word in a 500-page paper tome, where
do
you look? That’s the same idea as database indexes =)
Walter D. wrote in post #1009492:
On Jul 7, 2011, at 7:28 AM, Rodrigo R. wrote:
What is add_index for?
Someone told me it was to add indexes to the database, but I still
dont know what indexes are… first I thought they where other
columns of the table, but apparently not.Indexes are a feature of the relational database. One way they work is
to organize common query features (sort by date added, for example) in
the database’s structure so that they can happen much more quickly
than a simple brute-force approach could do. Other types of indexes
can enforce uniqueness (only one copy of this e-mail address may be
added ever) at a fundamental level.Adding an index to a column can return dramatic speed improvements on
reads, at the expense of slightly longer write times. Like anything,
it’s a trade-off.Like anything to do with the raw database design, you’ll have to read
up on the specifics of your particular RDBMS to figure out which sort
of index structure makes the most sense for your application. If you
have access to someone who is a DBA, they can tell you lots of useful
tricks that you might use.But always, add an index (for speed) when you are tuning, not before.
An index for uniqueness you can add if it makes sense in your design.
The use of “always” here seems a bit extreme to me. As an experience
developer, there are certainly times when you know adding an index is
what you want, before any metrics proves it. Some examples would be for
columns that are commonly used in searches, or indexes on foreign keys.
Missing indexes on foreign keys are probably the #1 most common cause
for poor query performance when joining database tables.
Of course, this could be done during the tuning phase, but why take the
chance of forgetting these important indexes where you know you’ll need
them?