Which one is faster?

Megha

I am using MySQL.

I have large data (current_data + history_data) for an entity. I need to run frequent Select and Insert statements against this data.

I want to know which operation is faster: Keeping data into two tables and then using Join operation to access the combined data OR keeping all data inside a single big table, maintaining indexes for frequently accessed keys and so accessing the data?

Experienced users welcome!
-megha

jweissig

hello,

Can you please define how "big", big is? Are we talking 1GB database with 100 columns in the table, ect?

I would keep the data in one table and not use a join as it is better to not have a join. You want to make sure you are using index's on the select statment data.

For example say that you have something like "select data, blah, bal1 where blah1 = "something", data = 1"; You want to make sure you have an index on all where columns as this will really speed the access times up.

If you have many, many, many inserts (100+ / sec) into one table you might want to split the insert and select tables to get around locking. If you give me more information i can help you out though.

thanks,
- justin

Megha

Originally posted by jweissig
hello,

Can you please define how "big", big is? Are we talking 1GB database with 100 columns in the table, ect?

This is big in the sense that I always want to keep the data even if it becomes history_data. Initially data is current_data and after certain time it becomes history_data. If I keep adding into just one table, at some point of time, performance will start deteriorating. Also I will be accessing current_data more frequently than history_data.

I would keep the data in one table and not use a join as it is better to not have a join. You want to make sure you are using index's on the select statment data.

For example say that you have something like "select data, blah, bal1 where blah1 = "something", data = 1"; You want to make sure you have an index on all where columns as this will really speed the access times up.

I understand joins are complex but I am not sure if this is a good strategy to always try to eliminate joins from the design. Otherwise I will incur the overhead of searching for the data inside a big table which will have inserts at the same time.

If you have many, many, many inserts (100+ / sec) into one table you might want to split the insert and select tables to get around locking. If you give me more information i can help you out though.

So far, I anticipate 15-20 inserts / sec. Ya but every insert triggers an immediate select operation.

thanks,
- justin

jweissig

You should benchmark your solution then. Say that in 6 months you will have 300,000 current records and 2,500,000 history records in a table.

Benchmark your system and see how long the queries are going to take to execute? Do the same using a two table solution this is the only real way to ensure that you are picking the correct method.

Possibly estimate where you will be in a year from now and redo the tests. This will help you understand where your system is slow.

Megha

My system is in early phases. Currently I am working on the first prototype - I wanted to get an idea concerning the two design strategies I had in mind.

What I understand from your response is that both strategies are good (design wise). During the development I can pick whichever suits better as per the evolved requirements and environment.

Thanks again for the prompt response!
-megha