Re: MySQL InnoDB Performance vs Postgres

[Ram Gudavalli]

Hi Robin,

I am running an OPTIMIZE TABLE right now and will perform the benchmark again. This query can return anywhere from 1 to 1000 rows. Most results will be about 30-40 rows.

OPTIMIZE TABLE has been running for over 6 hours now on this table.


Thanks,

Ram



Here are the MySQL and Postgres EXPLAIN outputs:

MySQL --
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: FRIEND
type: ref
possible_keys: friend_userid_idx
key: friend_userid_idx
key_len: 4
ref: const
rows: 88
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: UNION
table: FRIEND
type: ref
possible_keys: friend_friendid_idx2
key: friend_friendid_idx2
key_len: 4
ref: const
rows: 30
Extra: Using where
*************************** 3. row ***************************
id: NULL
select_type: UNION RESULT
table: <union1,2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Using filesort
3 rows in set (0.00 sec)


Postgres --

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Sort (cost=112.18..112.43 rows=99 width=8)
Sort Key: friendid
-> Unique (cost=108.41..108.90 rows=99 width=8)
-> Sort (cost=108.41..108.66 rows=99 width=8)
Sort Key: friendid
-> Append (cost=0.00..105.13 rows=99 width=8)
-> Subquery Scan "*SELECT* 1" (cost=0.00..26.44 rows=29 width=8)
-> Index Scan using friend_userid_idx on friend (cost=0.00..26.15 rows=29 width=8)
Index Cond: (userid = 1000)
Filter: (status = 1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..78.69 rows=70 width=8)
-> Index Scan using friend_friendid_idx2 on friend (cost=0.00..77.99 rows=70 width=8)
Index Cond: (friendid = 1000)
Filter: (status = 1)
(14 rows)