speeding up the creation of tables

davidklonski

Hello

I have an SQL script which deletes & create tables, create index for tables and so on.
Currently I have written a PHP code which traverse one SQL statement after the other and executes it using mysql_query()

Is there a way to speed this process up?
I am using MySQL 5.0
Would it possible to give MySQL the entire script file and have it perform all the needed statements without having to it manually one after the other?

It needs to be done via PHP though.

thanks in advance

planetsim

In theory MySQL can process quite a few queries in the same feild however the mysql_query() function can only handle one query at a time.

The other way you can do it if your doing a lot its put all the SQLs into an array. Use foreach and call the mysql_query(); that way.

davidklonski

Yes, this is the way I am doing it.
But I am still not satisfied with the results.
I would like to speed it up if possible.

It is already taking ~4 seconds to complete, and my schema will grow considerably in the future.

Sxooter

I would say that if you are always adding tables, you have a schema design problem.

davidklonski

what do you mean by "always" adding tables?
I am creating my tables only once. This is the script I am talking about.
The script includes DDL for:
dropping tables
creating new tables
* create index

Weedpacket

But why are you doing this? If it's only once every once in a long while, speed shouldn't be that much of an issue. If you're doing it frequently then, like Sxooter suggests, there may be something wrong with your db's design.

davidklonski

I am only doing it once.
Still, I would like to know if there is something wrong with the way I write my code, because I am still learning how to use PHP with MySQL, and I it seems to me that 4 seconds is too long.

Maybe if I will find a problem, I will learn from it and my code will improve.

I want my base to be strong.

Sxooter

Sorry, but I just figured that if you were trying to optimize table and index creation you must have been doing it more than at the very beginning.

4 seconds to create a table and indexes is no biggie.

It's how fast the tables can be accessed when you have 100,000 rows in them that counts.