Subject: Re: Joins and full index scans...mysql vs postgres?
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: ryan groth <postgres@cpusoftware.com>
Cc: "Steinar H. Gunderson" <sgunderson@bigfoot.com>,
	pgsql-performance@postgresql.org
In-Reply-To: <E1FBySH-0005Rw-1H@zeus.liquidweb.com>
References: <E1FBySH-0005Rw-1H@zeus.liquidweb.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1140635616.5777.35.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Wed, 22 Feb 2006 13:13:36 -0600

On Wed, 2006-02-22 at 12:11, ryan groth wrote:
> Does this work:
> 
> "Merge Left Join  (cost=0.00..2656.36 rows=6528 width=1522) (actual
> time=0.057..123.659 rows=6528 loops=1)"
> "  Merge Cond: ("outer".uid = "inner".uid)"
> "  ->  Merge Left Join  (cost=0.00..1693.09 rows=6528 width=1264)
> (actual time=0.030..58.876 rows=6528 loops=1)"
> "        Merge Cond: ("outer".uid = "inner".user_id)"
> "        ->  Index Scan using users_pkey on users  (cost=0.00..763.81
> rows=6528 width=100) (actual time=0.016..9.446 rows=6528 loops=1)"
> "        ->  Index Scan using phorum_users_base_pkey on
> phorum_users_base  (cost=0.00..822.92 rows=9902 width=1168) (actual
> time=0.007..15.674 rows=9845 loops=1)"
> "  ->  Index Scan using useraux_pkey on useraux  (cost=0.00..846.40
> rows=7582 width=262) (actual time=0.007..11.935 rows=7529 loops=1)"
> "Total runtime: 127.442 ms"

In MySQL, have you tried writing a short perl or php script or even
timing the mysql client running in one shot mode (I assume it can do
that) from the outside to see how long it takes to actually run the
query AND retrieve the data?

My guess is most of the time for both queries will be taken in
delivering the data.