- MariaDB Cookbook
- Daniel Bartholomew
- 505字
- 2021-07-16 12:19:25
Using LIMIT ROWS EXAMINED
The LIMIT ROWS EXAMINED clause is a good way to minimize the overhead of a very large or otherwise expensive query if we don't necessarily want or need to search every row in a large table or set of tables.
Getting ready
Import the ISFDB database as described in the Importing the data exported by mysqldump recipe, earlier in this chapter.
How to do it...
- Open a terminal window and launch the
mysqlcommand-line client and connect to theisfdbdatabase. - Run the following query from the Using SHOW EXPLAIN with running queries recipe, with one small addition at the end:
SELECT titles.title_id AS ID, titles.title_title AS Title, authors.author_legalname AS Name, (SELECT COUNT(DISTINCT title_relationships.review_id) FROM title_relationships WHERE title_relationships.title_id = titles.title_id) AS reviews FROM titles,authors,canonical_author WHERE (SELECT COUNT(DISTINCT title_relationships.review_id) FROM title_relationships WHERE title_relationships.title_id = titles.title_id) >= 10 AND canonical_author.author_id = authors.author_id AND canonical_author.title_id=titles.title_id AND titles.title_parent=0 LIMIT ROWS EXAMINED 10000;
How it works...
The LIMIT clause allows us to reduce the output of a SELECT query, but the full query is still run. On very large tables, because the full query is still being run, it may consume more resources than we would like. In MariaDB, we can use LIMIT ROWS EXAMINED to specify the number of rows we want the server to examine when executing our statement, thus minimizing the resources the query needs to use during execution.
This feature was designed to benefit queries running in something like a production web application where speed is critically important, possibly more so than having a complete answer.
LIMIT ROWS EXAMINED is also useful when testing a new query that we suspect will take a long time to run and consume a lot of resources. It's like testing on a subset of our full data without having to actually export and set up such a set. Instead, we can test on a full copy of our data, but with limits so that our testing is faster.
There's more...
Just because we are limiting the number of rows examined doesn't mean we can't also limit the output. We also need to be aware of the warnings this command gives.
When using LIMIT ROWS EXAMINED, we can still LIMIT the output to a specific number of rows. For example, we can examine 10000 rows and limit the output to the first 100 using the following command line:
LIMIT 100 ROWS EXAMINED 10000
With the limit set to 10000 rows, the query in this recipe completes quickly, but it comes with the following warning:
Query execution was interrupted. The query examined at least 10002 rows, which exceeds LIMIT ROWS EXAMINED (10000). The query result may be incomplete.
This warning is understandable. We told the server we only wanted it to examine 10000 rows, and so it did, and then quit. But the full query needs to examine many more rows than that, and so the results we received are incomplete, and the server is letting us know.