-2

I'm Using php and Mysql database. which one is the faster way for select data from database

  1. Input category id/*name* to each row of product table, then select * from product table WHERE category name = something

or

  1. Make a relationships table, search in this table first, then select matched result from product table

and what about ID or NAME? Is it necessary to search for IDs, then convert them to NAMEs which way is better?

  1. Using php to convert category id to category name
  2. Using category-name table to convert id to category name
  3. Get category name from the prosuct's row in product table
Hüseyin BABAL
  • 15,400
  • 4
  • 51
  • 73
iCode98
  • 41
  • 1
  • 7

4 Answers4

3

Unless there is an obvious performance reason, you should stick to a normalized database design. In your specific case, you will probably have a worse performance by repeating the category name because you will end up with a larger table and will have to compare strings instead of a 4 or 8 byte integer.

Tarik
  • 10,810
  • 2
  • 26
  • 40
1

Please don't worry about "fastest" without having first done some sort of measurement that it matters.

Rather than worrying about fastest, think about which way is clearest.

In databases especially, think about which way is going to protect you from data errors.

It doesn't matter how fast your program is if it's buggy or gives incorrect answers.

Andy Lester
  • 91,102
  • 13
  • 100
  • 152
0

Ideally, you would build a table for you categories, and then use the primary key (id field) from that table in the product table for the category_id. You can then still do your search on the category_id field, but it will be closer to being normalized and you can index that integer field with better performance results than trying to index a text field.

Brian H.
  • 505
  • 2
  • 12
  • I should have added this to Tarik's answer, but we were apparently typing at the same time. +1 for Tarik.. – Brian H. Jan 13 '14 at 19:28
0

It is faster to have a separate category table that lists the names. You put the category id in the product table that corresponds to the same category id in the category table. You don't need to "search in this table." You just use joins:

SELECT product_table.*, category_table.CategoryName 
FROM product_table
INNER JOIN category_table ON category_table.CategoryID = product_table.CategoryID
WHERE CategoryName = ?

If you can select by CategoryID instead of CategoryName, it will be faster, because CategoryID would be your primary key.

miyasudokoro
  • 1,705
  • 1
  • 15
  • 23
  • The question is: Does it make a bit of difference if one is faster than the other? If one returns records in 17ms and the other returns in 18ms, does anyone notice? – Andy Lester Jan 13 '14 at 19:28
  • Well, it's certainly faster than the option #1 of using PHP to figure out which name goes with each id, or (how I read it) doing two separate queries. – miyasudokoro Jan 13 '14 at 19:30