Re:Cost of a Join
by protomyth on Wednesday August 15, 2018 @11:24AM
(#17766742)
I really don't understand the reasoning for the 3 choices:
1. Add a status_id column to the product table and reference a new status table
2. Add a status_id column to the product table and let the app define the mapping of what each status_id is
3. Add a status column of type text to the product table
3 is unacceptable under any circumstances, and 2 really just makes no sense from a database perspective (unexplained values in a database is just wrong).
Choice 1 is pretty normal, but I am really curious about the whole idea that any app needs to join with that status table to get anything but flags, values, state, or labels associated with that status. The idea of using a status table as the starting point of any join is just wrong and shows some very poor data management in the relationship between the app folks and database folks. I can see using a text field to look up and store the id, but really the mechanism that is populating your status table (with any flags, values, state, or text that is associated with each status) should generate the equivalent code or config for the app.
Indexes on status are often painful because status is generally not a large number of values and having an index that doesn't cut down the number of rows significantly is often a problem. My big rule about this is if the app asks for a status field on an object, expect someone (probably your report writer) to want all the objects with the same value of status field. A typical example is open invoices. You really need to look at where the index is sparse and does it help in each situation. This is especially true if you track statuses through time (e.g. itemId, statusId, effBegUTC, effEndUTC)[1]. It is definitely worth the time to look at work tables, views, and each index with someone with some experience (yes, a good DBA).
1) If you are mostly looking to get all the items currently in a single status then leave out effBegUTC from the index and alway put a single value in the effEndDt for current (e.g. 9999-12-31). an index with (EffEndUTC, statusId) is better than (effBegDt, effEndDt, statusId). Including the itemId in the index really depends on the optimizer and how covered indexes are handled.
1. Add a status_id column to the product table and reference a new status table
2. Add a status_id column to the product table and let the app define the mapping of what each status_id is
3. Add a status column of type text to the product table
3 is unacceptable under any circumstances, and 2 really just makes no sense from a database perspective (unexplained values in a database is just wrong).
Choice 1 is pretty normal, but I am really curious about the whole idea that any app needs to join with that status table to get anything but flags, values, state, or labels associated with that status. The idea of using a status table as the starting point of any join is just wrong and shows some very poor data management in the relationship between the app folks and database folks. I can see using a text field to look up and store the id, but really the mechanism that is populating your status table (with any flags, values, state, or text that is associated with each status) should generate the equivalent code or config for the app.
Indexes on status are often painful because status is generally not a large number of values and having an index that doesn't cut down the number of rows significantly is often a problem. My big rule about this is if the app asks for a status field on an object, expect someone (probably your report writer) to want all the objects with the same value of status field. A typical example is open invoices. You really need to look at where the index is sparse and does it help in each situation. This is especially true if you track statuses through time (e.g. itemId, statusId, effBegUTC, effEndUTC)[1]. It is definitely worth the time to look at work tables, views, and each index with someone with some experience (yes, a good DBA).
1) If you are mostly looking to get all the items currently in a single status then leave out effBegUTC from the index and alway put a single value in the effEndDt for current (e.g. 9999-12-31). an index with (EffEndUTC, statusId) is better than (effBegDt, effEndDt, statusId). Including the itemId in the index really depends on the optimizer and how covered indexes are handled.