Configuring and Tuning the Server - Learning MySQL - page 386

# Query_time: 65 Lock_time: 0 Rows_sent: 8228 Rows_examined: 16577
USE LinkTracktclick;
SELECT DISTINCT * FROM Countries, clicktable ORDER BY CLICKS DESC;
This SELECT query took 65 seconds; if it's a query that's used often, we should add
indexes to improve the query speed, or perhaps redesign the query in a manner that
takes less time.
It can be hard to understand the entries in the slow-query logfile; you can use the
mysqldumpslow script from the MySQL scripts directory to help summarize and or-
ganize this information. For example, we can ask for the two queries that took the
longest time using the -t option:
$ scripts/mysqldumpslow -t 2
Reading mysql slow query log from ./log-slow.log
Count: 1 Time=65.00s (565s) Lock=0.00s (0s) Rows=8228.0 (8228), root[root]@localhost
select distinct * from tmpCountries, clicktable order by clicks desc
Count: 35 Time=12.00s (0s) Lock=0.00s (0s) Rows=3.8 (132),
RPUser[RPUser]@redback.cs.rmit.edu.au
select distinct id, surname, firstname, position_id from
student st, supervises s where st.id = s.student_id and s.status=N
and st.active=N and st.visible=N and supervisor_id = N
The Count is the number of queries that have been executed; from this, it would prob-
ably be better to focus on optimizing the second query, since it's been run 35 times,
rather than the top query, which has been run only once.
The script tries to process the slow-query logfile at the default location; if you're using
a nonstandard location, you should specify the logfile location:
$ mysqldumpslow path_to_your_slow_query_log_file
If you're using Windows, you will need to follow the steps in “Installing Perl modules
under Windows” in Chapter 2 to use this Perl script.
Query Caching
Some applications require the database to repeatedly look up and return specific data.
For example, the front page of an online store application might display all the products
in stock that have been marked as being on sale. Every visitor to the online store will
load this front page, and every page load will require the database server to look up all
the products that are on sale.
It's much more efficient for the database server to store, or cache , the result of this
query, and simply return the cached result every time it sees the same query. If the data
is changed, the database considers the cached result to be stale and runs the query again
(and caches the new result). Query caching can have a huge effect on performance; the
MySQL manual describes a speedup of more than two times as being typical.