MySQL – Split Table vs. Same Values in Multiple Rows

database-designMySQL

I have a table products:

#products
ID | category | type | criteria1 | criteria2

, with category and type being foreign keys of other tables.

Should I split this table up into category1_type1_products, category1_type2_products and so on? It seems to me I should, because right now in my table, there are some thousands of entries with the same ID and category value. Loads of redundand information.

Also, a query could perform faster if mysql didn't have to search for all rows with the specific category and type first. (right?)

Is this recommended structuring behaviour at all? If not, what if my table has got 5 million tuples?

Best Answer

No. That would mean the table NAME contains DATA - you then have to modify the database structure (create new table) just to add one more category or type.

If your category/type column is tinyint or smallint (depending on reasonably expected number of possible values), then you actually have proper normalization in place. If there is no (functional) dependence between two keys to categories for two different rows then those two values are not a redundancy but a minimal way to store the actual data.

From performance standpoint the important part is proper indexing for your queries. If you often search for rows with specific category and/or type, you should have in place index on that column (or composite index on both columns, and possibly other) to optimize such queries. You should enable the slow query log and periodically check what queries are taking most time (you may use pt-query-digest to analyze the log) and optimizing those (adding proper indexes and/or rewriting the queries).

When you have proper indexes in place then in your proposed case of multiple tables actually selecting the right table to read will take about the same time as MySQL needs to "jump" to the right part of the index. (It is a bit oversimplification but the point is that selecting the right table to read is an overhead in itself).

From the "schema" standpoint I would be more concerned about those two columns criteria1 and criteria2 if these are their real names and usage then that seems like a possible problem - but that may depend on your use case and may be actually OK. But if there is any chance you will once add criteria3 and others then that would tell you to add separate table for those, one row per criteria and FK to the current table.

Related Solutions

Mysql – index all columns of a table in MySQL safely

When it comes to querying, indexing of a table should never be your first concern.

The queries you plan to use should dictate the indexes you need.

Based on the queries, some columns can be individually indexed. Other queries require compound indexes. The ORDER BY and GROUP BY clauses should provide immediate hints for indexes to make. Not using such hints may result in temp table sorting rather than using the indexes for data in the desired order needed.

Low cardinality of column values should eliminate the need for an index.

Even with these things taken into consideration, you may find that query may need some adjustment (a.k.a. refactoring) for performance gains.

When you reach the point of having the right indexes, not you have to worry about the size of those indexes. For a MyISAM table, this would mean that the .MYI file may grow significantly.

The size of the index file as well as the number of indexes should now be weighed against the performance of your queries, especially if the indexes provide the proper ordering of data and fastest retrieval.

Explain plans for queries may change over time depending on the number of rows, cardinality of columns, number of DELETEs and UPDATEs. Once a query's explain plan changes from what it looked like months ago, you should explore the need to add or remove indexes.

Mysql – Database Design (Stores + Products)

Your problem can be modelled by an entity relationship diagram (created with dia):

ER-diagram .

There are two entities "product" and "store" and there is an m-n-relationship "stocks" between these entities. "product" has an attribute "productId" that uniquely determines the product. "store" has the unique attribute "storeId" that determines uniquely the store.

I have added the attributes "quanity", "storeAddress" and "productName" to make the model a little bit more complex. These attributes cannot be derived by your description, you can remove it from the diagram and from the resulting tables if you want the design follow your description closely. An n-m-realtionship means that a store can stock different products and a product can be in stock in different stores. We have the following problem desription:

Each store has a unique Id and an address
Each product has a unique Id an a name
a store can stock different products with different quantities

The last sentence can also be formulated in this way

a product can be stocked in different stores with different quantities

Such a diagram can be transformed in a relational model. Each entity is a table where the attributes are the columns. The unique identifier is the primary key. The m-n-relation is a table where the primary key is the a (storeId,productId) pair. If the relation has an attribute this attribute is a column in the table.

so we have the following relations (tables)

store("storeId", storeAddress)
product("productId",productName)
stock("storeId","productId",quantity)

Best Answer

Related Solutions

Mysql – index all columns of a table in MySQL safely

Mysql – Database Design (Stores + Products)

Related Question