Hi! Interesting. Can you post the EXPLAIN SELECT for each of the statements? Also, what version of Drizzle are you using?
Thanks! jay On Sat, Apr 17, 2010 at 1:50 PM, Wilfried Schobeiri <[email protected]> wrote: > 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) > > > _______________________________________________ > Mailing list: https://launchpad.net/~drizzle-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~drizzle-discuss > More help : https://help.launchpad.net/ListHelp > _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
