Table of Contents
Issue
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?
Case 1
Two tables, products and categories.
SELECT * FROM products INNER JOIN categories ON products.category_id = categories.id
Products
id
name
category_id
Categories
id
name
Case 2
One table, products, containing all the data.
SELECT * FROM products
Products
id
name
category_name
Question(s)
- 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?
Solution
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)