Mysql – Optimal theSQL design for 500k+ products with up to 50 specs

database-designeavMySQLschema

I facing a problem where I have to design a MySQL database capable of containing +500k products in approx. 1.500 categories, that can hold up to 50 specs. (like size, weight, speed, color, etc).

My goal is to find/use a design that is optimal (fast) for selecting products base on the input of up to 50 specs. Later on, I may implement precached on often use search patterns (specs), when I have collected statistics on that.

As my raw data is in Python (dicts/lists) I was initially thinking of using JSON-objects, but as I see it, this would result in huge overhead and maybe bad search performance. (Actually, I have no experience in using JSON in MySQL)

BTW The easiest would have been using MongoDB, but this is a no-go. I'm also trying to find a way to design it as generic as possible without having product-specific tables and like.

Based on the information above… What design/schema would be the optimal/best, regarding fast search in specs. ?
Is EAV (Entity-Attribute-Value) the way to go or is the a better way ?

If anybody knows if this question is answered somewhere, please link to it 🙂

Thanks

Best Answer

A common request. You are actually a step ahead of most questions tagged with [eav] -- you have thought about "common use patterns" and how to optimize once you gather that info.

You should be able to make an initial guess at what is most common, put them in columns for efficient searching, put flag-like attributes in a FULLTEXT-indexed column, then throw the rest into a JSON column for testing in the app.

More: http://mysql.rjweb.org/doc.php/eav

Related Solutions

Thesql products db design

I am not a database designer.

You don't make dbs but tables.

Both of your table structures can be implemented as innodb tables and as MyIsam tables.

Your first idea is not a good design. It violates first normal form, so i would propose the second idea with innodb. the table db1 can be decomposed further in two tables

id | name      | details      | add_date   | brand
1  | some name | some details | 1333128354 | 1
2  | some name | some details | 1333128355 | 1
3  | some name | some details | 1333128357 | 2

id | brand
1  | nike
2  | puma

Mysql – Choosing MyISAM over InnoDB for these project requirements; and long term options

InnoDB and MyISAM each have their strengths and weaknesses.

May 03, 2012 : Which is faster, InnoDB or MyISAM?
Sep 20, 2011 : Best of MyISAM and InnoDB

If you have enough RAM, I would choose InnoDB because it caches data and index pages in the Buffer Pool. MyISAM only caches index pages in the Key Cache.

MyISAM tables experience full table locks for each INSERT, UPDATE, and DELETE. MyISAM tables always require disk access for data.

InnoDB tables always incur disk I/O in the following areas:

Double Write Buffer : Changes are posted in ibdata1 to avoid OS caching
Insert Buffer : Changes to Secondary (non-Unique) Indexes as posted in ibdata1
Data and Indexes
- With innodb_file_per_table = 0, changes are written to ibdata1
- With innodb_file_per_table = 1, changes are written to .ibd tablespace file. Read I/O against ibdata1 still necessary to crosscheck table metadata

SUMMARY

In an environment with the following:

heavy writes
heavy reads
tons of RAM
heavy connections

I would always choose InnoDB. Please check out my other post about InnoDB over MyISAM : When to switch from MyISAM to InnoDB?

When would I every choose MyISAM?

Under the following scenario

Using MySQL Replication
Master with all InnoDB
Slave with all tables converted to MyISAM
ALTER TABLE ... ROW_FORMAT=Fixed for all tables on the Slave

Disk I/O wise, MyISAM has a slight edge with ROW_FORMAT-Fixed because you only interact with one file, the .MYD file. The row size is completely predictable because VARCHAR is treated as CHAR this shortening access time for data retrieval.

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes

On the other hand, InnoDB has to interact with multiple files (ibdata1, serveral read/write threads upon the .ibd of the InnoDB table).

Best Answer

Related Solutions

Thesql products db design

Mysql – Choosing MyISAM over InnoDB for these project requirements; and long term options

SUMMARY

Related Question