Don't retrieve more data than needed in application

By:    Updated: February 28,2017

The applications retrieve more data than needed will demand extra work of the MySQL server, add network overhead, and consume memory and CPU resources on the servers.The followings are some of typical mistakes:

Fetching more rows than needed

Some developers who are familiar with other database systems may issue a SELECT statement that returns many rows, then fetching the first N rows and closing the result set. They think MySQL will only provide them with these 10 rows and stop executing the query, but what MySQL really does is generate the complete result set. The client library then fetches all the data and discards most of it. The best solution is to add a LIMIT clause to the query.

Fetching all columns from a multitable join

If you need to retrieve some columns by multitable join, you can select with a.column_1,b.column_2 instead of a.*, b.*.

Fetching all columns

Retrieving all columns can prevent optimizations such as covering indexes, as well as adding I/O, memory, and CPU overhead for the server. But, asking for more data than you really need is not always bad. It might be useful to retrieve more data than you actually need if you use some type of caching system (file cache, memory cache: redis, memcached, ...) in your application, or if you have another benefit in mind. Fetching and caching full objects might be preferable to running many separate queries that retrieve only parts of the object.

Fetching the same data repeatedly

If you're not careful, it's quite easy to write application code that retrieves the same data repeatedly from the database server, executing the same query to fetch it. You can cache them the first time you fetch them, and reuse them there after. This approach is much more efficient.

More in Development Center
New on Valinv
Related Articles
Sponsored Links