=== Sample tables === fidic2k=> \d con_info Table "con_info" Attribute | Type | Modifier -----------+------------------------+--------------------- id | integer | not null default 0 company | character varying(150) | alphaname | character varying(150) | not null default '' addr1 | character varying(100) | addr2 | character varying(100) | addr3 | character varying(100) | city | character varying(100) | county | character varying(100) | country | integer | postcode | character varying(20) | tel | character varying(30) | fax | character varying(30) | email | character varying(100) | web | character varying(100) | telex | character varying(30) | assoc | integer | not null default 0 version | character(10) | not null default '' Indices: con_info_idx1, con_info_idx2, con_info_idx3, con_info_pkey fidic2k=> \d users Table "users" Attribute | Type | Modifier -----------+------------------------+--------------------- id | character(16) | not null default '' pass | character varying(16) | email | character varying(100) | auth | integer | Index: users_pkey === End sample tables === === PG Test: fields as varchar(nn) === richardh@mainbox $ /usr/local/apache/bin/ab -n100 -c10 http://localhost/~richardh/dbtest/pgtest.php This is ApacheBench, Version 1.3c <$Revision: 1.38 $> apache-1.3 Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Copyright (c) 1998-1999 The Apache Group, http://www.apache.org/ Server Software: Apache/1.3.12 Server Hostname: localhost Server Port: 80 Document Path: /~richardh/dbtest/pgtest.php Document Length: 18937 bytes Concurrency Level: 10 Time taken for tests: 140.457 seconds Complete requests: 100 Failed requests: 0 Total transferred: 1912700 bytes HTML transferred: 1893700 bytes Requests per second: 0.71 Transfer rate: 13.62 kb/s received Connnection Times (ms) min avg max Connect: 0 113 1508 Processing: 9837 13340 17096 Total: 9837 13453 18604 fidic2k=> EXPLAIN SELECT con_info.id, con_info.company, con_info.alphaname, e_country.name as hq_country, pers_info.contact, con_inf o.postcode, con_info.tel as co_tel, con_info.email as co_email, pers_info.email as pers_email, pers_info.tel as pers_tel, users.pass as password, tag_list.version, entry_opts.ent_type, entry_opts.ent_bought, entry_opts.paid_on, entry_opts.paid_until FROM con_info, e_country,entry_opts,pers_info,users,tag_list WHERE tag_list.tag='A' AND con_info.country = e_country.id AND con_info.id=entry_opts. id AND con_info.id=tag_list.id AND pers_info.id=tag_list.id AND users.id=tag_list.id AND con_info.version=entry_opts.version AND con _info.version=pers_info.version AND (con_info.version=tag_list.version OR (tag_list.version='UNKNOWN' AND con_info.version='EDITING' )) ORDER BY id,version; NOTICE: QUERY PLAN: Sort (cost=91.06..91.06 rows=1 width=236) -> Nested Loop (cost=61.64..91.05 rows=1 width=236) -> Nested Loop (cost=61.64..81.55 rows=1 width=212) -> Nested Loop (cost=61.64..79.52 rows=1 width=196) -> Hash Join (cost=61.64..77.47 rows=1 width=116) -> Seq Scan on tag_list (cost=0.00..6.20 rows=219 width=16) -> Hash (cost=61.59..61.59 rows=22 width=100) -> Hash Join (cost=7.30..61.59 rows=22 width=100) -> Seq Scan on entry_opts (cost=0.00..19.30 rows=730 width=48) -> Hash (cost=6.64..6.64 rows=264 width=52) -> Seq Scan on pers_info (cost=0.00..6.64 rows=264 width=52) -> Index Scan using con_info_pkey on con_info (cost=0.00..2.01 rows=1 width=80) -> Index Scan using e_country_pkey on e_country (cost=0.00..2.01 rows=1 width=16) -> Seq Scan on users (cost=0.00..5.60 rows=260 width=24) EXPLAIN === End of varchar() test === === PG Test: fields as char(nn) === richardh@mainbox $ /usr/local/apache/bin/ab -n1000 -c10 http://localhost/~richardh/dbtest/pgtest.php This is ApacheBench, Version 1.3c <$Revision: 1.38 $> apache-1.3 Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Copyright (c) 1998-1999 The Apache Group, http://www.apache.org/ Server Software: Apache/1.3.12 Server Hostname: localhost Server Port: 80 Document Path: /~richardh/dbtest/pgtest.php Document Length: 44882 bytes Concurrency Level: 10 Time taken for tests: 329.407 seconds Complete requests: 1000 Failed requests: 0 Total transferred: 45072000 bytes HTML transferred: 44882000 bytes Requests per second: 3.04 Transfer rate: 136.83 kb/s received Connnection Times (ms) min avg max Connect: 0 7 987 Processing: 320 3270 5448 Total: 320 3277 6435 fidic2k=> EXPLAIN SELECT con_info.id, con_info.company, con_info.alphaname, e_country.name as hq_country, pers_info.contact, con_inf o.postcode, con_info.tel as co_tel, con_info.email as co_email, pers_info.email as pers_email, pers_info.tel as pers_tel, users.pass as password, tag_list.version, entry_opts.ent_type, entry_opts.ent_bought, entry_opts.paid_on, entry_opts.paid_until FROM con_info, e_country,entry_opts,pers_info,users,tag_list WHERE tag_list.tag='A' AND con_info.country = e_country.id AND con_info.id=entry_opts. id AND con_info.id=tag_list.id AND pers_info.id=tag_list.id AND users.id=tag_list.id AND con_info.version=entry_opts.version AND con _info.version=pers_info.version AND (con_info.version=tag_list.version OR (tag_list.version='UNKNOWN' AND con_info.version='EDITING' )) ORDER BY id,version; NOTICE: QUERY PLAN: Sort (cost=128.60..128.60 rows=1 width=236) -> Nested Loop (cost=106.64..128.59 rows=1 width=236) -> Nested Loop (cost=106.64..126.56 rows=1 width=212) -> Nested Loop (cost=106.64..124.53 rows=1 width=196) -> Hash Join (cost=106.64..122.47 rows=1 width=116) -> Seq Scan on tag_list (cost=0.00..6.20 rows=219 width=16) -> Hash (cost=106.58..106.58 rows=22 width=100) -> Hash Join (cost=25.30..106.58 rows=22 width=100) -> Seq Scan on entry_opts (cost=0.00..46.30 rows=730 width=48) -> Hash (cost=24.64..24.64 rows=264 width=52) -> Seq Scan on pers_info (cost=0.00..24.64 rows=264 width=52) -> Index Scan using con_info_pkey on con_info (cost=0.00..2.03 rows=1 width=80) -> Index Scan using e_country_pkey on e_country (cost=0.00..2.01 rows=1 width=16) -> Index Scan using users_pkey on users (cost=0.00..2.02 rows=1 width=24) EXPLAIN === End of char(nn) test === === Mysql Test === richardh@mainbox $ /usr/local/apache/bin/ab -n1000 -c10 http://localhost/~richardh/dbtest/mytest.php This is ApacheBench, Version 1.3c <$Revision: 1.38 $> apache-1.3 Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Copyright (c) 1998-1999 The Apache Group, http://www.apache.org/ Server Software: Apache/1.3.12 Server Hostname: localhost Server Port: 80 Document Path: /~richardh/dbtest/mytest.php Document Length: 20480 bytes Concurrency Level: 10 Time taken for tests: 344.589 seconds Complete requests: 1000 Failed requests: 0 Total transferred: 20670000 bytes HTML transferred: 20480000 bytes Requests per second: 2.90 Transfer rate: 59.98 kb/s received Connnection Times (ms) min avg max Connect: 0 0 19 Processing: 1078 3430 5439 Total: 1078 3430 5458