← Back to team overview

drizzle-discuss team mailing list archive

Delayed join improvement

 

Hi all! Good hanging out with those of you at MySQLConf & Drizzle Day this past week. Hope you guys got home safe.

I give you a sample query. id is primary key, uid is indexed. The table has about 20 non-indexed columns. on top of taht.

SELECT * FROM user_facebook WHERE uid < 1000000 ORDER BY id DESC LIMIT 0,12

(2 min 24.54 sec)

vs

SELECT * FROM user_facebook INNER JOIN
(
	SELECT id FROM user_facebook WHERE uid < 1000000 ORDER BY id DESC LIMIT 0,12
) 
as SUBSELECT_TABLE
ON user_facebook.id = SUBSELECT_TABLE.id

(1.95 sec)


Same results, substantial performance boost. Worth noting is that the more complicated or large the where set is (especially if there's an IN() set), or if there's any aggregation going on, the delayed join version gets almost exponentially faster. One could see 10-1000x increases in query time.

I'm not sure what's going on with the first query (or if we can do anything about it), but it seems like it's trying to do the order and limiting on the entire column set instead of using only what's applicable to do the ordering and limiting. If we can optimize around the primary key, perhaps there can be some massive performance gains out of it.

-Wilfried (nphase)




Follow ups