Learning Mysql

# Query_time: 65 Lock_time: 0 Rows_sent: 8228 Rows_examined: 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 organize this information. For example, we can ask for the two queries that took the longest time using the t option scripts/mysqldumpslow -ti 2ibReading mysql slow query log from ./log-slow.log
Count: 1 Times (s) Locks (s) Rows (8228), root[root]@localhost select distinct * from tmpCountries, clicktable order by clicks desc
Count: 35 Times (s) Locks (s) Rows (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 probably 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 lookup 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 lookup 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.

Download 4.24 Mb.

Share with your friends: