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
