Instead of a varchar(1) containing 'y' or 'n' you could use a BOOL or an integer.
Your query seems of the form :
SELECT FROM main_table LEFT JOIN a lot of tables ORDER BY sort_key LIMIT N OFFSET M;
I would suggest to rewrite it in a simpler way : instead of generating the whole result set, sorting it, and then grabbing a slice, generate only the ror id's, grab a slice, and then generate the full rows from that.
- If you order by a field which is in main_table :
SELECT FROM main_table LEFT JOIN a lot of tables WHERE main_table.id IN (SELECT id FROM main_table ORDER BY sort_key LIMIT N OFFSET M
) ORDER BY sort_key LIMIT N OFFSET M;
- If you order by a field in one of the child tables, I guess you only want to display the rows in the main table which have this field, ie. not-null in the LEFT JOIN. You can also use the principle above.
- You can use a straight join instead of an IN.
On Mon, 14 Mar 2005 09:58:49 +0100, Miroslav �ulc <[EMAIL PROTECTED]> wrote:
John Arbash Meinel wrote:
In fact, on MySQL I didn't see any slow reactions so I didn't measure and inspect it. But I can try it if I figure out how to copy the database from PostgreSQL to MySQL.
I figured you still had a copy of the MySQL around to compare to. You probably don't need to spend too much time on it yet.
So I have some results. I have tested the query on both PostgreSQL 8.0.1 and MySQL 4.1.8 with LIMIT set to 30 and OFFSET set to 6000. PostgreSQL result is 11,667.916 ms, MySQL result is 448.4 ms.
Both databases are running on the same machine (my laptop) and contain
the same data. However there are some differences in the data table
definitions:
1) in PostgreSQL I use 'varchar(1)' for a lot of fields and in MySQL I
use 'enum'
2) in PostgreSQL in some cases I use connection fields that are not of
the same type (smallint <-> integer (SERIAL)), in MySQL I use the same types
John =:->
Miroslav
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
