Snala a little background information.
Is there a website with a fairly small attendance (1.5 K uniques a day) which is hosted on a VPS with 512MB RAM + 1Ghz of cpu. There is a mysql database with MyISAM tables. On average, the tables participating in the query around a few hundred thousand records, with the exception of one — there are almost a million.
On the fields of the tables participating in the associations added indexes such as BTREE + unique primary keys.
Is mysql_slow log that records the slow queries. Recently looking into it, I was horrified to find that a fairly simple query (no sorting, grouping, searching, limits, subqueries) are a VERY long time. Here, for example, a query runs almost 8 seconds:
SELECT `mp3_id3`.`artist`, `mp3_id3`.`album`, `mp3_id3`.`year`, `mp3_id3`.`title`, `mp3_genres`.`name` AS `genre`, `mp3_main`.`size`, `mp3_main`.`duration`
FROM (`mp3_id3`)
JOIN `mp3_main` ON (mp3_id3.song_id = mp3_main.song_id)
JOIN `mp3_genres` ON (mp3_id3.genre = mp3_genres.number)
WHERE `mp3_id3`.`song_id` = 52596131;
What can be the reason for such a drop in performance? MB should change some settings in the config mysql? Are there any tools that can help determine the cause of the problem?
