Is simple queries taking unreasonably long in MySQL?

Question

MySQL

Snala a little introductory information.
There is a site with rather low traffic (1.5K uniques per day), which is hosted on a VPS with 512 RAM + 1Ghz cpu. I have a mysql database with MyISAM tables. On average, in the tables participating in queries, there are about several hundred thousand records, with the exception of one - there are almost a million of them.
BTREE type indexes + unique primary keys have been added to the fields of tables participating in joins.
The mysql_slow log is kept, which records slow queries. Having recently looked into it, I was horrified to find that fairly simple queries (without sorting, grouping, searching, limits, subqueries) take a VERY long time. Here, for example, such a request was executed for 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`<br/>
FROM (`mp3_id3`)<br/>
JOIN `mp3_main` ON (mp3_id3.song_id = mp3_main.song_id)<br/>
JOIN `mp3_genres` ON (mp3_id3.genre = mp3_genres.number)<br/>
WHERE `mp3_id3`.`song_id` = 52596131;

What could be the reason for such a drop in performance? MB should I change any parameters in the mysql config? Are there any tools that can help determine the cause of the problem?

Answer 1

After a million records, MySQL starts to get dumb with anything more complicated than a key select. Do you really need a join here? I understand that it is not complicated, but 3 query selects by key will work faster than a join + much more chances to pull values ​​from the cache.
I also have a big suspicion of locks, look in the logs for queries waiting most of the time for the right to lock the table.

Answer 2

whether check up indexes for columns are established.
To analyze the execution of the request, there is EXPLAIN - write explain further on the request body. Read the manual and analyze the result of the function

Answer 3

blog.mysqltuner.com/ - try running this script, it will help to identify some bottlenecks
when doing joins, check that there are indexes on the fields on which the joins are made

Answer 4

remove MyISAM, it is at the table level, not records.
denormalize, for example the genre can be stored in a table with a song or put in a SET
why do you use inner join and not left join to join tables?
you explicitly create a temporary table, hence the whole load. you can experiment with view.

Answer 5

>VPS
>performance drop
Resistance is useless.
But you can still complain to the host.

Answer 6

Do the same requests get into the log?
Do log queries executed with SQL_NO_CACHE later also result in 8 seconds?
I mean, maybe the problem is not in the requests, but in the fact that the server slows down due to the disk load?

Answer 7

Watch the global status parameters and figure out what's wrong there. There are scripts to help with this - mysqlreport and tuning-primer.sh - easy to google.
Locks and temp disk tables usually slow down MyISAM, there may be little space for indexes and so on, there may be limits on file descriptors.
If you do not own the topic, run the mentioned scripts and carefully understand what they say. Then, if there are no problems with memory, you can significantly increase possible bottlenecks, if there are, shrink those that are not used ... in general, this is a difficult iterative optimization process :)