What are the considerations for querying large tables in MySQL?

This article will explain in detail what matters needing attention in the big table of MySQL query. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

When executing a query, Mysql defaults to load all the results into memory before returning them (this mode can be understood as Oracle's ALL_ROWS optimization mode). If the table data is too large, it will cause memory overflow.

1. When mysql console connects to the database:

Add the-Q option, mysql-h hostname-u root-p-Q

2. When jdbc connects to the database:

Add useCursorFetch=true to the connection string

In the created statement, add setFetchSize, as shown in

Stmt = conn.createStatement (java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY)

Stmt.setFetchSize (Integer.MIN_VALUE)

Note:

The Integer.MIN_VALUE is used by the MySQL driver as a signal to switch to streaming result set mode. It is not used as a value.

See the documentation, under "Resultset" .in summary:

By default, ResultSets are completely

Retrieved and stored in memory. You can tell the driver to stream the

Results back one row at a time by setting

Stmt.setFetchSize (Integer.MIN_VALUE); (in combination with a

Forward-only, read-only result set).

This is the end of this article on "what are the points for attention in MySQL query?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.