Huge delay when creating a large MySQL table in PHP

Question

I am trying to combine 2 existing MySQL MyISAM tables into a new one using PHP. Here is my PHP code:

$sql = "CREATE TABLE new_table SELECT * FROM table_1 UNION SELECT * FROM table 2";
mysql_query("$sql");

Note: the tables are fairly huge, with over 30 million entries.

So I open the PHP file in the browser so that it executes the script. And I monitor the database with phpmyadmin and at first absolutely nothing happens. Then after about 30 minutes the table "new_table" shows up with all the correct data inside.

Is PHP buffering or caching "new_table" untill its complete and then creating it in MySQL? If so, is there any way to tell php not to? I am afraid that if this is the case, once the table reaches into the billions of rows it will no longer fit into the buffer when I try to copy it.

Does anyone know how to make PHP do the query immediately without and buffer or cache delay?

I tried mysql_unbuffered_query("$sql"); and still had the huge delay.

Any help would be greatly appreciated!

You should probably wrap that `create table` call within a `start transaction; create table ...; commit;` block. — j.w.r, Jun 15 '11 at 02:18
Any reason you're not simply doing this from the SQL command line? — , Jun 15 '11 at 02:25
I have a xampp apache setup and cant figure out how to do it on the command line. — Tommy Mckinnon, Jun 15 '11 at 03:04

score 0 · Answer 1 · answered Jun 15 '11 at 02:20

0

Issue a show processlist; in another MySQL query during the table creation and you'll see what's happening.

answered Jun 15 '11 at 02:20

Koterpillar

7,883
2
25
41

Ok I checked the processlist and it says "converting HEAP to MyISAM" for about 10 seconds and then "Sending data" for about 20 minutes. – Tommy Mckinnon Jun 15 '11 at 03:02

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

This has more to do with the amount of data - 30 million records - and the way you are creating the table - CREATE TABLE via a SELECT with UNION.

The point being, this is probably the slowest way you can do it. Running this query from the command line, pre-CREATE TABLE + batch INSERT INTO should all be faster.

I'd also suggest looking into changing your table's storage engine. MyISAM can degrade with large records and has other performance considerations. You should consider others, like InnoDB, depending on your DB interaction. Read more about MyISAM versus InnoDB

edited May 23 '17 at 12:04

Community

1
1

answered Jun 15 '11 at 02:30

Jason McCreary

71,546
23
135
174

Thanks for the quick response. How exactly would I batch insert into the table via the command line? After creating my table from the 2 existing tables, 100% of the queries will be reads and no writes so I think MyISAM is still the way to go. – Tommy Mckinnon Jun 15 '11 at 02:53
It might be best to take a step back and actually describe what you are wanting to do. As far as the storage engine, I am not a MySQL DBA, so I won't challenge that last part. – Jason McCreary Jun 15 '11 at 03:34

Huge delay when creating a large MySQL table in PHP

2 Answers2