It should be trivially easy to push down the LIMIT and OFFSET to the engine. Not sure why this isn't done today, but it should be. The SARG should have fields which indicate the LIMIT and OFFSET (they are currently available to the Cursor and Engine via Session::lex::select_lex::select_lilmit::val_uint() and Session::lex::select_lex::offset_limit::val_uint() though it's a little more complicated for subselects...), and the SARG should be passed to the engine in both Cursor::index_init() and the MRR interface (which is currently unfortunately tightly coupled with the Cursor itself).
It should be possible during join::optimize() to ask each engine if it can satisfy a SARG with a LIMIT and OFFSET without a scan (in other words, it can do it via a skip-list or the STL equivalent of std::algorithm::search_n() with a limiting functor constructed from the SARG. -jay On Sun, Mar 14, 2010 at 7:56 AM, Yuan Wang <[email protected]> wrote: > Yes, large offsets could be a problem, we know this problem and we > solved it with our customized JDBC driver, forbiding too large > OFFSETs. However, a JDBC driver can not make OFFSETs faster. > > And, if OFFSETs can be pushed down, the index can be built into a skip > list, which is fast even with lists of millions of elements. > > On Sat, Mar 13, 2010 at 11:21 PM, MARK CALLAGHAN <[email protected]> wrote: >> On Sat, Mar 13, 2010 at 12:59 AM, Yuan Wang <[email protected]> wrote: >>> I think Drizzle should consider the ability to push down OFFSET to the >>> storage engine. LIMIT/OFFSET is very common in most Web applications. >>> Where there are lists, there are LIMIT/OFFSETs. For example, each user >>> in Blog has a list of his articles. For this list could be long, we >>> display it in pages, using the following SQL: >> >> This is a nice description of the problem and the feature that you >> need. But I have been burned so many times in production by queries >> with large offsets that I also want a feature to allow me to either >> ban queries with an offset or set a limit on the max offset that can >> be used. Of course, I can do that whether or not the optimization you >> have requested is supported. >> >> Note that I don't write SQL, I just debug it and make it less slow and >> this was written from experience -- >> http://www.facebook.com/note.php?note_id=206034210932 >> >> -- >> Mark Callaghan >> [email protected] >> > > _______________________________________________ > 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
