[SOLVED] Mysql performance, one table or two

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)

Leave a Reply

Your email address will not be published. Required fields are marked *