Databases
StackOverflow2013
Postsdbo
POperformance: joining tables vs. large table with redundant data
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POperformance: joining tables vs. large table with redundant data
    primarykey
    data
    text
    <p>Lets say i have a bunch of products. Each product has and id, price, and long description made up of multiple paragraphs. Each product would also have multiple sku numbers that would represent different sizes and colors.</p> <p>To clarify: product_id 1 has 3 skus, product_id 2 has 5 skus. All of the skus in product 1 share the same price and description. product 2 has a different price and description than product 1. All of product 2's skus share product 2's price and description.</p> <p>I could have a large table with different records for each sku. The records would have redundant fields like the long description and price.</p> <p>Or I could have two tables. One named "products" with product_id, price, and description. And one named "skus" with product_id, sku, color, and size. I would then join the tables on the product_id column.</p> <pre><code>$query = "SELECT * FROM skus LEFT OUTER JOIN products ON skus.product_id=products.product_id WHERE color='green'"; </code></pre> <p>or</p> <pre><code>$query = "SELECT * FROM master_table WHERE color='green'"; </code></pre> <p>This is a dumbed down version of my setup. In the end there will be a lot more columns and a lot of products. Which method would have better performance?</p> <p>So to be more specific: Let's say I want to LIKE search on the long_description column for all of the skus. I am trying to compare having one table that has 5000 long_description and 5000 skus vs OUTER JOINing two tables, one has 1000 long_description records and the other has 5000 skus.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1034772
    1. USuser1034772
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POperformance: joining tables vs. large table with redundant data
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POperformance: joining tables vs. large table with redundant data
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. POperformance: joining tables vs. large table with redundant data
      1. This table or related slice is empty.
      1. VTDownMod
    1. COIn general, hypothetical "which will perform better" questions for SQL are impossible to answer. There are too many variables and a LOT of it depends on usage. Please ask a more specific question. We need to know what kind of queries will be running, how often you are inserting vs selecting, index structure, concurrency, etc.
      singulars
      1. POperformance: joining tables vs. large table with redundant data
      1. USJNK
    2. COPerformance doing what? Just listing everything? Updating product descriptions? Adding new SKUs?
      singulars
      1. POperformance: joining tables vs. large table with redundant data
      1. USJosef K
    3. COPerformance in terms of searching the table. Lets use the select/where color='green' example. Does having a table with 5,000 long descriptions where 1000 of them are distinct slow down a select query more than joining the two tables (one table only having 1000 long descriptions)?
      singulars
      1. POperformance: joining tables vs. large table with redundant data
      1. USuser1034772
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload