Re: [Maria-developers] request for a feature -- LIMIT_ROWS_EXAMINED
OK, will have feedback in a few days. On Fri, Jan 20, 2012 at 6:45 AM, Timour Katchaounov <timour@montyprogram.com> wrote:
Hello Mark,
I want a variant of the LIMIT clause that limits the number of rows examined during SELECT processing. This should return OK when the limit is reached -- something like NESTED_LOOP_QUERY_LIMIT. While LIMIT can be LIMIT x and LIMIT x,y. This only supports one argument -- LIMIT_ROWS_EXAMINED x. Are you interested in implementing this feature for me? We will sponsor the work. The purpose of the feature is to prevent queries from taking too long when there isn't a great index and many rows might be filtered.
After examining few alternatives, I implemented a pretty complete prototype of LIMIT_ROWS_EXAMINED. Please have a look at the description of the task here: https://mariadb.atlassian.net/browse/MDEV-28
It is important to notice that the current implementation will stop execution when LIMIT_ROWS_EXAMINED is reached irrespective of the query execution strategies used (thus not only for JOINs, but also writing/reading to temp tables, sorting, and single-table access methods that scan too many rows). This approach also is future-proof with respect to adding new query processing algorithms because the counting is done at the handler level.
The current approach is such, that it should be fairly easy to backport the feature to almost any MariaDB/MySQL.
Please let me know if this is what you want. If so, then I will proceed with the remaining polishing that is in the TODO list and will get an internal review.
You can also have a look at the small example in the comments below the description to get a feeling what is the behavior.
I can also send you a patch of the current version in case you want to play with the feature before it is pushed.
Timour
-- Mark Callaghan mdcallag@gmail.com
The semantics are good for us. The potential overhead from the implementation isn't a concern for our uses. What was the feedback from the MPAB code review? On Fri, Jan 20, 2012 at 7:37 AM, MARK CALLAGHAN <mdcallag@gmail.com> wrote:
OK, will have feedback in a few days.
On Fri, Jan 20, 2012 at 6:45 AM, Timour Katchaounov <timour@montyprogram.com> wrote:
Hello Mark,
I want a variant of the LIMIT clause that limits the number of rows examined during SELECT processing. This should return OK when the limit is reached -- something like NESTED_LOOP_QUERY_LIMIT. While LIMIT can be LIMIT x and LIMIT x,y. This only supports one argument -- LIMIT_ROWS_EXAMINED x. Are you interested in implementing this feature for me? We will sponsor the work. The purpose of the feature is to prevent queries from taking too long when there isn't a great index and many rows might be filtered.
After examining few alternatives, I implemented a pretty complete prototype of LIMIT_ROWS_EXAMINED. Please have a look at the description of the task here: https://mariadb.atlassian.net/browse/MDEV-28
It is important to notice that the current implementation will stop execution when LIMIT_ROWS_EXAMINED is reached irrespective of the query execution strategies used (thus not only for JOINs, but also writing/reading to temp tables, sorting, and single-table access methods that scan too many rows). This approach also is future-proof with respect to adding new query processing algorithms because the counting is done at the handler level.
The current approach is such, that it should be fairly easy to backport the feature to almost any MariaDB/MySQL.
Please let me know if this is what you want. If so, then I will proceed with the remaining polishing that is in the TODO list and will get an internal review.
You can also have a look at the small example in the comments below the description to get a feeling what is the behavior.
I can also send you a patch of the current version in case you want to play with the feature before it is pushed.
Timour
-- Mark Callaghan mdcallag@gmail.com
-- Mark Callaghan mdcallag@gmail.com
participants (1)
-
MARK CALLAGHAN