Sunday 8 August 2010

Performance comparison test between MySQL and PostgreSql

Step 1)

I have created a table in mysql with following query.

CREATE TABLE `latlong` (
`id` int(10) NOT NULL auto_increment,
`lat` decimal(12,9) NOT NULL,
`lng` decimal(12,9) NOT NULL,
`place_name` varchar(253) NOT NULL,
PRIMARY KEY (`id`),
KEY `lat` (`lat`),
KEY `lng` (`lng`),
KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=800001 DEFAULT CHARSET=latin1;


I made the similar structure in PostgreSQL too.

CREATE TABLE latlong
(
id integer NOT NULL,
lat numeric(12,9) NOT NULL,
lng numeric(12,9) NOT NULL,
place_name character varying NOT NULL,
CONSTRAINT latlong_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE latlong OWNER TO postgres;

-- Index: lat

-- DROP INDEX lat;

CREATE INDEX lat
ON latlong
USING btree
(lat);

-- Index: lng

-- DROP INDEX lng;

CREATE INDEX lng
ON latlong
USING btree
(lng);

Step 2 ) I entered 70 lakhs of random data to the table using a stored procedure.
In that i kept 1 lakh of duplicate data for testing purpose.

After that, i exported the mysql data to csv and imported to Postgresql.

Now both the table has same data and structure.

Then i ran the query on both and the time differences were amazing.

select count(*) from
(select max(id),count(id) from latlong group by lat,lng
having count(id) >1
) as tmp;

The result was 99998 in both tables.

Time taken for the outputs are,

Mysql > 29.297 seconds
PostgreSQL > 7.922 seconds

In the above test, PostgreSQL showed better performance than MySql.

mysql stored procedure example

This is an example script of mysqls stored procedure.
This will insert a large number of records into a table. I prepared this for checking the performance of db.


delimiter //
CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
declare latv,longv float;

while v < 8000000
do

set latv = FLOOR(7 + (RAND() * 5)) + rand();
set longv = FLOOR(7 + (RAND() * 5)) + rand();

INSERT INTO `sams`.`latlong` (
`id` ,
`lat` ,
`lng` ,
`place_name`
)
VALUES (
'', latv, longv, concat(latv,longv));

set v = v + 1;
end while;
end
//
delimiter ;