Databases Reference
In-Depth Information
Fetching more rows than needed
One common mistake is assuming that MySQL provides results on demand, rather
than calculating and returning the full result set. We often see this in applications
designed by people familiar with other database systems. These developers are used
to techniques such as issuing a
SELECT
statement that returns many rows, then
fetching the first
N
rows and closing the result set (e.g., fetching the 100 most recent
articles for a news site when they only need to show 10 of them on the front page).
They think MySQL will 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 want to retrieve all actors who appear in the film
Academy Dinosaur
, don't
write the query this way:
mysql>
SELECT * FROM sakila.actor
->
INNER JOIN sakila.film_actor USING(actor_id)
->
INNER JOIN sakila.film USING(film_id)
->
WHERE sakila.film.title = 'Academy Dinosaur';
That returns all columns from all three tables. Instead, write the query as follows:
mysql>
SELECT sakila.actor.* FROM sakila.actor...;
Fetching all columns
You should always be suspicious when you see
SELECT *
. Do you really need all
columns? Probably not. Retrieving all columns can prevent optimizations such as
covering indexes, as well as adding I/O, memory, and CPU overhead for the server.
Some DBAs ban
SELECT *
universally because of this fact, and to reduce the risk of
problems when someone alters the table's column list.
Of course, asking for more data than you really need is not always bad. In many
cases we've investigated, people tell us the wasteful approach simplifies develop-
ment, because it lets the developer use the same bit of code in more than one place.
That's a reasonable consideration, as long as you know what it costs in terms of
performance. It might also be useful to retrieve more data than you actually need
if you use some type of caching 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. For
example, if you want to find out a user's profile image URL to display next to a list
of comments, you might request this repeatedly for each comment. Or you could
cache it the first time you fetch it, and reuse it thereafter. The latter approach is
much more efficient.
