Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account

Different table for insert and select

⇧
0
⇩
65 views

In order to improve insert performance and load on server, I've decided to divide a large table into 2. A big table which will only be used for "select" and a smaller table which will be used mainly for "insert" and sometimes also "select". Each time period (I thought about a day) I will merge the smaller table into the big one.

Regrading the big table: is there a way I can improve performance by telling the mysql server it's read-only? Considering it's only for select, can I assume it will handle SELECT in less than 1sec. when it will become ~1e9 rows?

Regarding the small table: any tuning I should do here? what is the best way to develop an automated merge process from the small to the big table (in php)?

php mysql sql database database-design
asked June 25, 2011 11:07 am CDT
Noam
posted via StackOverflow

3 Answers

⇧
2
⇩
 

Packed ISAM tables are perfect as MySQL readonly tables, but you'll still need the correct indexes for your heavy queries.

But do make sure this is the solution to your problem (and have considered other options, including indexes and partitioning)

answered June 25, 2011 11:23 am CDT
Konerak
⇧
1
⇩
 

I believe you don't need this..

If your goal is to give more precedence to the SELECT query you can just do inserts with INSERT DELAYED.

answered June 25, 2011 11:23 am CDT
yes123
⇧
1
⇩
 

I've seen this and similar strategies used for this purpose and it has always been counterproductive. You end up creating complexity with more overhead that you gain. For instance, you are guaranteeing that every record gets inserted at least twice.

Be sure to benchmark carefully and compare to confirm your hypothesis before going too far with this.

answered June 25, 2011 11:23 am CDT
le dorfier

Your answer

Join with account you already have

Sign in with Twitter account
Sign in with Facebook account
Sign in with Google Friend Connect

Preview
Similar questions
Insert..Select into InnoDB table with commit after each insert?
May 5, 2011
Select and then insert in sqlite table
March 23, 2011
Do I need to lock a MySQL table when doing a SELECT followed by an INSERT?
May 13, 2011
MySQL: Select row by id, CHANGE id, and insert it into another table
April 6, 2011
Insert data into table with result from another select query
June 15, 2011
Mysql -> insert into tbl (select from another table) and some default values
May 6, 2011
MySQL INSERT SELECT. Ordering insert by count but don't actually insert the count number.
June 1, 2011
Retrieving SELECT information from an INSERT .. SELECT mysql statement
February 5, 2011
Mysql insert select on duplicate key update - puzzle - different fields needed for insert & update
March 27, 2011
Insert row in table B on table A's insert query
March 9, 2011
How do I select missing table rows belonging to Table A from Table B?
March 25, 2011
Why taking too long time to insert rows in a mysql table even after using multithreading when table has some relationship
January 14, 2011
Insert column datas of one table in mysql database into another table using php
March 21, 2011
Insert rows from one table into another table, while creating the incremental values?
May 10, 2011
Insert into table from temporary table takes a long time
January 10, 2011
With MySQL, how do I insert into a table on condition that the value does not exist in same table?
May 16, 2011
How do i pull data from one table and insert it into a column in another table?
January 25, 2011
Select from one mysql table where ids match results from select of another mysql table
March 29, 2011
Help with INSERT INTO..SELECT
May 13, 2011
Select all elements from table A ordered by the number of entries in table B linked to each item
April 25, 2011
Error: select command denied to user '<userid>'@'<ip-address>' for table '<table-name>'
January 22, 2011
Myisam place table-lock on table even when dealing with 'select' query?
June 20, 2011
Create table from select then alter table by adding an auto increment column in mysql
January 11, 2011
INSERT INTO SELECT mysql
June 3, 2011
INSERT from a SELECT with WHERE - IF EXISTS?
April 6, 2011
MySQL INSERT from a SELECT with PDO
June 21, 2011
Mysql, INSERT with SELECT
March 22, 2011
Postgresql: INSERT INTO ... (SELECT * ...)
May 21, 2011
Mysql if select then insert
April 13, 2011
Postgresql: INSERT INTO ... (SELECT * ...) - II
May 22, 2011