I use PHP and mysql.
Let’s say I have a database table with 10 000 rows. Which of the cases below it the best performance wise?
SELECT * FROM products INNER JOIN categories ON products.category_id = categories.id
id name category_id
products, containing all the data.
SELECT * FROM products
id name category_name
- Which of these cases have the best performance?
- Guess, would it take long to get data with 10 000 rows with a structure like it?
- Any pitfalls with one of the cases?
From my perspective
Case 1 is the “correct” way of doing it, but I will save some developing time by using
Case 2. Maybe performance too?
The first is the correct (i.e. SQLish) way of storing this data. It allows you to do the following:
- Validate the category names as they are inserted and updated, using standard foreign key relationships.
- Change a category name and have it affect all products.
- Include other information about a category, such as short names, long descriptions, date added, and so on.
Performance is not the main consideration. The SQL engine takes care of performance through the use of fancy join algorithms and indexes. It does this so you can structure the data in the most sensible and maintainable way for your application.
That said, which performs better depends on a number of factors (how long the category names are, how many different names there are, how wide the product record is). Differences in performance between the two scenarios are probably not at all important in getting an application to work optimally.
Answered By – Gordon Linoff
Answer Checked By – Robin (BugsFixing Admin)