Hi. Last day - 0 slow queries
Sorry - I don't understand anything in SQL. I try to send your answer to my ISP.
This is his answer (sorry foe automatically Google translated)
-----------------------------
In this case, particularly the performance of your site galleries are not so important, you need to analyze and optimize database queries it.
Optimization of queries to the database should be limited to the following results:
- In all correspondence indexes are used to retrieve data,
- Minimize the use of temporary files and filesort operations.
All these data for each of the queries can be obtained using EXPLAIN:
http://dev.mysql.com/doc/refman/5.6/en/explain.htmlPreviously, we have provided you slow query log. Analyze requests from any given one of the log files, for example:
SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title
FROM cpg14x_pictures AS r
INNER JOIN cpg14x_albums AS a ON a.aid = r.aid
WHERE (1)
AND approved = 'YES'
AND hits> 0
ORDER BY hits DESC, pid ASC
LIMIT 4505, 5;
Using EXPLAIN, execute this query:
mysql> explain
SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title
-> FROM cpg14x_pictures AS r
-> INNER JOIN cpg14x_albums AS a ON a.aid = r.aid
-> WHERE (1)
-> AND approved = 'YES'
-> AND hits> 0
LIMIT 4505, 5 -> ORDER BY hits DESC, pid ASC
-> LIMIT 4505, 5;
+----+-------------+-------+--------+-------------------------------+----------+---------+-------------------------+-------+- ---------------------------- +
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------+----------+---------+-------------------------+-------+- ---------------------------- +
| 1 | SIMPLE | r | range | pic_hits, aid_approved, pic_aid | pic_hits | 4 | NULL | 19381 | Using where; Using filesort |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | wwwikarin_gallery.r.aid | 1 | Using index |
+----+-------------+-------+--------+-------------------------------+----------+---------+-------------------------+-------+- ---------------------------- +
2 rows in set (0.02 sec)
As can be seen, the sample is derived from a table with 19,381 lines. Yes, indexes are used, but the sample data from the 19381 using Using filesort type of request in any case will move all the lines and creating temporary files (which in turn can cause a strain on the drive). Using LIMIT in this case does not bring much good, so going on a first sampling of all the rows, and then cut off in accordance with LIMIT.
And such requests, according to the logs, there is a very large number. This and similar indicators of resources.