1

I have a system that end users will need to query but they are not guaranteed to be well versed in query writing. We wanted to ensure every SELECT statement ends with a LIMIT 100000. I have thought of some regex to do this and some tools. It seems the native Query Rewrite is a good option but we needed to use ProxySQL for other things, thus I wanted to see if anyone knows of a way to force this.

It seems like the patter or regex would be something like: anything that starts with select needs to have a LIMIT on the final line followed by a space and int equal to or less than 100000 then maybe a semi colon.

Has anyone had any luck doing this?

1

Forget it. There are ways to write "simple" queries, even with a small LIMIT, that can take hours to run.

How, if you switch to MariaDB-5.5.21 (or later), there is a "LIMIT ROWS EXAMINED" that would be relatively effective in stopping runaway queries.

An example of where LIMIT does not help much:

SELECT ...
    FROM big_table
    WHERE lots_of_rows_kept
    GROUP BY one_column
    ORDER BY another_column
    LIMIT 1

It will

  1. fetch lots of rows.
  2. GROUP BY, possibly involving a sort of all the rows.
  3. Sort again, this time for the ORDER BY.
  4. Deliver one row.

That is, lots of time and effort went into steps 1,2,3; the final LIMIT had very little impact on the overall time.

Check out Proxy servers -- some of them might have a feature wherein they kill any process running longer than X seconds.

Query Rewrite My old thoughts on Query Rewrite: http://mysql.rjweb.org/doc.php/queryrewrite

What's the worst?

So, someone writes a long-running query. It will hog some resources and slow down other queries, but most likely won't kill the system in any way. Hopefully, he will be embarrassed and try harder next time.

Anecdotes

I have dealt with several time-series applications. The first thing I do is build a web site to provide likely information. And, behind the scenes, I build Summary tables so that queries against them are better (sometimes 10x) than against the raw (Fact) table.

The web pages present the data in an easier to read manner than the non-programmers can get via clumsy SQL. And I can test them to see that they won't harm the system. When I am finished, I have no fear of people pounding on my web pages that in turn hit a billion-row dataset.

Yes, I build in LIMITs in various places -- after all, who wants to scroll through a million-row web page (should it ever finish rendering)? I even give them the ability to change the limit from the sane default I provide. But they rarely do.

And I listen to their requests. I try to quickly build whatever they ask for. (This keeps them from demanding direct SQL access.)

Summary Tables are the key to success. They don't want item by item results; they want sums/averages by day (or week or hour or ...)

It is easier for me to write the SQL than to explain the nuances of the table.

And, yes, there is always normalization, so JOINs are required.

  • Luckily this data set is time series data that doesn't join to anything and is cached. All of that logic happens on the client side based on what they want to do with it. I'm using Aurora due to it being tied closely to other services in I need in AWS for the ecosystem. Seeing as it's a straight SELECT with no joins would you have any other thoughts on this one? – Ali Razeghi Jul 18 '18 at 6:49
  • 1
    I added a somewhat pathological, yet realistic, example. And a though on how to throttle MySQL. – Rick James Jul 18 '18 at 14:17
  • well you certainly raise some great points. Would you consider the impact negligible if all the data is coming out of memory, and we disallowed any group by / order by / joins? This should literally just be time series data that a time range is selected, then all data is returned. I agree with the sentiment that we should just limit time, but I'd like to also enforce a limit as it fits in with the business logic and billing. – Ali Razeghi Jul 18 '18 at 17:12
  • Time series begs for GROUP BY to summarize. – Rick James Jul 18 '18 at 22:30
  • @AliRazeghi - and I added a couple more sections to my Answer. – Rick James Jul 18 '18 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.