How worth is it to assign an FK Index?_问答_开发者

I'm a normal "would be DBA" level developer. I've been handling some databases with a few million records. A lot goes around importing data between database and its clone and then using that clone in the web-app environment.

Well, I've known that keeping PK indexes automatically and so it helps speedup the data access. Now, from this discussion I derive that if I'm using JOINs in my SQL-Queries then I shud use FK and index it to make the JOIN operations efficient.

For example, I have a table OrgMaster (contains all the Org records) then I have a BookingMaster table (contains all the Booking records). Now, the OrgMaster.Id is being 'referenced' as BookingMaster.OrgId. So, I have an FK for the OrgId-to-Id relationship and I shud 'index' it for better performance of any JOIN operation between both of these tables .. did I get it correctly?

All the above - at the cost of extra overhead of space and time (while inserting record in the table with FK).

I'd request that you provide me a list of points to be considered,开发者_如何学编程 like:

Is FK-index going to eat up too much space\time as table grows few million records?
In that case, is it worth to go for an FK-index "each time"?
In what case shud I NOT apply FK or Index it or do neither of it (of course I can handle a LOT from the app)
Any other tricky to speedup JOIN or other such time-consuming lookups?

Thank you.

Your questions:
Is FK-index going to eat up too much space/time as table grows few million records?

No worries, here, at least not a concern "as the table grows". Both space and time requirements will grow linearly with regards to the number of records added.
(well technically not quite, if you cross boundaries that introduce an extra level in the tree, but typically a database with readily million of records, tree depth is readily where it is supposed to be)

In that case, is it worth to go for an FK-index "each time?"

Typically yes, but it is indeed a case-by-case situation. One think to consider too, rather than plain FK index are indexes that include additional columns and may be used both for searching and to cover [parts of] the select list. Again deciding on such alternative (or additional indexes) is a case-by-case, sorry ;-) ...

In what case should I NOT apply FK or Index it or do neither of it (of course I can handle a LOT from the app)

Of course all such cases, exclude ones where it is important that referential integrity be intrinsically supported by the dbms itself (Such integrity can alternatively be managed at the level of the application / processes which Insert and Delete rows in the database)

cases when most of [time or resource] critical queries imply other filters on the table, and such that SQL can then resolve the JOIN by checking the values in the table per-se (or in a covering index, specifically, one where the FK is not the first column listed) for the [small] subset of possible results produced by these other filters.
cases where the table table is relatively small (lookup tables and such), as SQL often decide on scan strategy for them and also as they get cached). But then, they are small, and typically relatively static, so the cost for extra indexes would not be an issue...
there may be a few more cases...

Any other tricky to speedup JOIN or other such time-consuming lookups?

When it comes to moving the data around, for example when significant amount of data is added, etc. It is often a worthwhile strategy to drop the indexes (or some of them), do the CUD (INSERT / UPDATE / DELETE) operations, and then re-create the indexes. Of course this is not always possible, if the database is concurrently searched during the updates etc.

Also watch for the FILL_FACTOR associated with indexes, as a judicious choice for these keep the index fragmentatation to a minimum (at the cost of consuming, up from a bit more space) at least between scheduled maintenance of the indexes

If you want to take advantage of referential integrity constraints, you must use foreign keys.

If you have normalized your data, then you should be using Foreign Key constraints; it's the only practical way to guarantee your data is not invalid.

Whether you should create an index on that foreign key is slightly more complicated. Index creation for foreign keys is not automatic in all RDBMSs. Like any other index, it trades space and insertion time for faster reads (could be especially noticeable because JOIN operations tend to be among the slower operations in your DB). You also need to consider whether the FK column will be covered by another index and would possibly not need its own index.

I'm not an expert, but I may provide some common opinions on your list of questions:

FK-index adds a bit space/time, but it is still worth it
yes, worth it
FK comes with an index
FK are good for joins ; other lookups are a completely different story.

For most lookups, it is worth not optimizing up-front, but wait until observing performance problems, then:

measure precisely
make a change
measure again, compare
if not gaining, or not worth of the trouble, discard the changes

Also note that indexes do not necessarily cover only one column, but several columns. This calls for more reasoning, as to which columns to use, and in what order. These questions will become essential for performance.