On 01/11/2013 09:33 PM, Matthew Lagoe wrote: > I was talking with a few people about a query such as > > > |select * from table order by X, Y, Z limit G;| > > > As the "ORDER BY" for me could change to any number of 12 values in any > order I am not able to use a multicolumn index as such I was looking at > how to optimize in such case where sorting is dynamic. > > On my dataset the query takes ~7 seconds for a full table scan but only > .3 seconds when using the column index and returns ~80,000 records for > the query (before the LIMIT) > > I was wondering if there is any reason that the order by syntax doesn't > use the following logic, I was thinking of looking at the code but was > told such a simple optimization must have a reason that its not already > in the code, so I am asking here before I spend hours being stupid. > > > This logic block will be based on the above query. > > 1. If index exists for column X and there is a limit on the query > then use the index and sort the list, then return the first G > values, continue past G until you hit a different value for X then > the value that was at G > 2. Use this smaller subset of data for then ordering the data based on Y > 3. Order the data again based on Z > 4. Return the sorted data like normal > > If there is a reason this optimization isn't in the code please someone > let me know, if not I think I will take a look at adding it.
Mathew, It would be nice if you could support such an optimization for ORDER BY <columns> LIMIT BY N when only a prefix of the <columns> list is compatible with some index or with its major components. Regards, Igor. > > > -Botanic > > > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-developers > Post to : [email protected] > Unsubscribe : https://launchpad.net/~maria-developers > More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
