Performance Comparison between mysql and oracle databases in PHP. Use a very simple table. mysql and oracle use the same table structure and have only three Fields. the structure is as follows: mysql table structure: CREATETABLEboard (board_idsmallint (6) A simple table is used. mysql and oracle use the same table structure and have only three Fields. the structure is as follows:
Mysql table structure:
Create table board (
Board_id smallint (6) not null auto_increment,
Board_name char (16) not null,
Board_manager char (20 ),
Primary key (board_id)
);
Oracle structure:
Create table PHP_ORACLE. "BOARD"
("BOARD_ID" FLOAT,
"BOARD_NAME" CHAR (16) not null,
"BOARD_MANAGER" CHAR (20 ));
We only tested the INSERT operation time. for select, no tests were conducted.
In win32, only PHP3 can connect to the oracle Database. Therefore, only the performance of connecting oracle with PHP3 is tested. Phase
After the official version of PHP4 is released, the speed of connecting oracle with PHP4 should be improved.
In LINUX, I only tested the performance of mysql because I didn't install oracle. It is said that in LINUX, oracle
The performance is good, but cannot be tested.
In addition, we put all the code used for database connection and oracle to analyze SQL statements out of the statistical code.
Therefore, the test only takes time to execute SQL operations.
Programs used to test mysql:
$ Dblink = mysql_connect ("localhost", "root", "shh123 ");
Mysql_select_db ("bbs ");
$ Counter = 1;
Set_time_limit (300 );
$ Query = "insert into board (board_name, board_manager) values (test, test )";
$ Begin_time = time ();
For ($ I = 1; $ I <= 10000; $ I ++ ){
Mysql_db_query ("bbs", $ query );
$ Counter ++;
}
$ End_time = time ();
Mysql_close ($ dblink );
Echo "test db speed...
";
Echo "begin time:". $ begin_time ."
";
Echo"
End time: ". $ end_time ."
";
$ Total = $ end_time-$ begin_time;
Echo "total spent time:". $ total;
?>
Programs used to test oracle:
$ Handle = OCILogon ("php_oracle", "php_oracle ");
$ Counter = 1;
Set_time_limit (300 );
$ Query = "insert into board (board_id, board_name, board_manager) values (: board_id, test, test )";
$ State = OCIParse ($ handle, $ query );
OCIBindByName ($ state, ": board_id", & $ I, 32 );
$ Begin_time = time ();
For ($ I = 1; $ I <= 10000; $ I ++ ){
Ociexecute ($ state );
}
$ End_time = time ();
OCIFreeStatement ($ state );
Ocilogoff ($ handle );
Echo "test db speed...
";
Echo "begin time:". $ begin_time ."
";
Echo"
End time: ". $ end_time ."
";
$ Total = $ end_time-$ begin_time;
Echo "total spent time:". $ total;
?>
Test results:
Environment: win32 + apache + php4 + mysql
Result: 28 seconds
Environment: win32 + apache + php3 + mysql
Result: 34 seconds
Environment: win32 + apache + php3 + oracle8.0.5 (oci function)
Result: 46 seconds
Environment: linux + apache + php4 + mysql
Result: 10 seconds
Conclusion:
In WIN32, although mysql performance is not very good, it is much faster than oracle8, especially when I
The database connection statement is not included in the test program. Therefore, this test result is only used to insert data.
And oracle connection, day, too slow! On my machine, connect once, at least 1-2 seconds.
In LINUX, mysql has a great leap over WIN32. Decreases from 28 seconds to 10 seconds.
Therefore, if you do not need the support of stored procedures, and the database volume is not as large as that, it is still in LINUX
Use mysql as your database! This lightweight database gives you the best performance, manageability, and consistency.
Good security.
Optimize mysql TABLE structure: create table board (board_id smallint (6 )...
