Using LIMIT ROWS EXAMINED_MariaDB Cookbook-QQ阅读中文现言网

上QQ阅读APP看书，第一时间看更新

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 mysql command-line client and connect to the isfdb database.

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.

Using LIMIT with LIMIT ROWS EXAMINED

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

Warning of incomplete results

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.