mysql: when to split table in two?

Iīd like to hear from you guys your advice about this approach

[phoenix_fly]

Hello my code friends,

I wonder if you can give me some advice, or direct me to some links about it, regarding mysql (or any else database, I think this is not mysql specific) optimization.

I have a CATALOG table with these fields:

catalog_id int
title varchar(50)
merchant_id varchar(50)
year varchar(50)
price decimal(7,2)
shelf varchar(50)
reg_date datetime
alt_date datetime
active int(1)

And, for each product that has a description and/or photo, I have this auxiliary table DETAILS:

detail_id int
catalog_id int
descr tinytext
photo varchar(50)

I did this because I thought, as only a small amount of the products would have photos and descriptions, it would be smart to do like this, to make the CATALOG table "lighter".

Does this procede? I am thinking that maybe not, because in the search script I have to make a JOIN anyway. I wish I had a better notion of the strenght joins require in mysql, comparing to handle a table with many columns.

Can you guys can help me out?

Thanks a lot

phoenix_fly

[jatar_k]

did you figure this out phoenix_fly?

[phoenix_fly]

Hey, jatar_k, thanks for asking.
In fact, no. I am still with this issue... Can you help me?

[arran]

phoenix_fly,

There are advantages and disadvantages to both single table and multiple table solutions.

Let's assume your main concern is "select speed" i.e. you only care about pulling products back from the database fast.

A lot depends on the design of your site - do you include descriptions or just the title when showing a list of products i.e. would you need to pull back multiple product descriptions in one query? If so, using the 2-table design would be slower (obviously a select statement left joining both tables would be more expensive than selecting from one table).
If you pull back only one description at a time (e.g. when someone clicks on that product), it might be worth using the 2-table solution because of the slight IO gains you would achieve when extracting a list of products (e.g. the average row size of the 'CATALOG' table would be less therefore you would get more rows/page).

Generally speaking, I would go with the 1-table solution. How many products do you have anyway?

[AlexK]

If you do not have it already, I strongly recommend that you install phpMyAdmin [phpmyadmin.net] - it will help enormously with optimisation.

A fixed-format table [dev.mysql.com] is bigger (on disk) than a dynamic-format table, but far, far quicker to access. Use of VARCHAR, BLOB, or TEXT types will convert the table to dynamic, so avoid these if you can. If you cannot avoid them, strip them out into a separate table and LEFT JOIN them together. Use a (same-name) field_ID to join them. This combo is far quicker than a single table.

VARCHAR can be replaced with CHAR[80] (just an example), and makes a big difference.

My own experience suggests that use of multiple tables does not slow affairs down, although it does complicate the SQL, of course, and bad sql will slow things as much as bad table design. The use of EXPLAIN in selects is your saviour here.

In one recent example, I have a names table which had had a Hits field added to track page-hits. The index page was essentially only a mass of stats SELECT statements, and the whole page took 0.08 secs to produce. Extracting this Hits field to it's own table (mostly to allow the QCache room to breathe) plus a little bit of LEFT JOIN optimisation dropped the page production time to 0.02 secs.

Bottom line - do not hesitate to use multiple tables if the design makes sense.

[phoenix_fly]

Hey arran and Alex_k,

Thanks a lot for the replies.

Let me give you the info that was missing. I talk about a 2-hundred-thousand-products database. So, every search people perform, I have to LEFT JOIN all records from the CATALOG table with the DETAILS table, because the keyword must be looked for in the DESCR field. You see?

In this case, better go just one table, right?

I donīt know why I splitted them at first, if this DESCR field is used almost every time, in massive-searches and single product details display. Also, mysql seems to do ANYthing in warp-speed, so the extra syntax work to code the queries may seem worthless in this case...

[coopster]

I've run some very large queries with multi-table joins over much larger tables. If you normalize the database, you MUST analyze your statements and create indexes accordingly. I've seen queries go from almost a minute to sub-second response time by simply applying the correct indexes. MySQL offers plenty of tools to get this job done. The application design you seemed to have thought through, so why not consider optimizing your queries first? If that doesn't work, you can always combine the tables later to see if that improves your application. Just some food for thought.

[phoenix_fly]

Yeah, thatīs a good one, coopster. I have some indexes there already.

My concern is because this DESCR field is a [tinytext], and also I will have other [text] fields being added to this DETAILS table - this new ones being shown only in the single-product screen, and not being considered in the search - , so I think maybe itīs good to keep these details in a table apart, right?

[coopster]

Not necessarily. Probably not the answer you are looking for, but honest nonetheless.

Your separation of columns so far based on limited possibilities of required data for each product makes sense. I mean, if you are going to have 2,000 products but only a handful will have associated text and a picture, a separate relational table for these columns may be the appropriate design.

I guess I would encourage you to go with your gut instinct and make adjustments later on if necessary. The beauty of a database is that it lends itself nicely to future enhancements, including table separation or merging. You wouldn't be the first that traveled this road ;)

[phoenix_fly]

Hey Cooper, thatīs pretty much like you said. Only a few products have these extended descriptions. So Iīll keep them in a table apart.

Thanks for the help, my friend!