Hosting problem - Slow database queries lot of time

[Αndré]

Find

Code Select Expand

    // Meta albums
    switch($album) {
and above, add

Code Select Expand

return;
to disable all meta albums.

This is a problem of my ISP MySQL DB?

I think so. It seems the MySQL server has a very bad performance, for whatever reason.

[ikar]

Find

Code Select Expand

    // Meta albums
    switch($album) {
and above, add

Code Select Expand

return;
to disable all meta albums.

Above? Or below?

switch($album) {
return;
...
}

[ikar]

Well


return;
switch($album) {

...
}

[ikar]

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.html

Previously, 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.

[Αndré]

They suggest to use (additional) indexes in the pictures table. May be worth a try. If I have some spare time, I'll make some tests with "EXPLAIN" on my local testbed.

If some database expert reads this, feel free to reply with some useful tips