MySQL simple queries are executed unnecessarily long?


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
6 views
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?
by | 6 views

7 Answers

0 like 0 dislike
After a million records, MySQL is beginning to blunt with everyone, which is harder than sampling by key. Do You really need here join? I understand that it is not difficult, but 3 the select query for the key will work faster than join + much more likely to get values from the cache.
\r
I have a large suspicion on lock, look in the logs do not wait for the queries most of the time the right to lock tables.
by
0 like 0 dislike
check whether the indexes for the columns.
\r
For the analysis of the query is the EXPLAIN write explain further, the body of the request. Read the manual and analyze the result of the function
by
0 like 0 dislike
blog.mysqltuner.com/ — try to run this script, it will help to identify some bottlenecks
\r
when doing joiny, check below for the fields which are goiny was indexes
by
0 like 0 dislike
remove MyISAM, it is at the level of tables, not records.
guide denormalizing, for example, the genre can be stored in the table with a song or stick in the SET
why do you use inner join and not left join for joining tables?
\r
you have explicitly created temporary table, hence the full load. can you experiment with view.
by
0 like 0 dislike
>VPS
>the performance drop
\r
Resistance is futile.
But you can still complain to the host.
by
0 like 0 dislike
In the log I get the same requests?
The queries from the log, executed with SQL_NO_CACHE later, also give the result in 8 seconds?
\r
I mean, what could be the problem is not the query, and that the server slows down because of usage load?
by
0 like 0 dislike
Watch global status parameters and understand what there is not. There are scripts that help in this mysqlreport and tuning-primer.sh — easy to googletest.
\r
Usually slow MyISAM lock and temp disk tables, there may be little space for indexes, and so on, there may be limits on file descriptors.
\r
If you do not own the theme — run the mentioned scripts and carefully deal with what they say. Then, if memory is no problem, it is possible to significantly increase the possible bottlenecks, if you have to shrink those that are not used... well this is a difficult iterative process of optimization :)
by

Related questions

0 like 0 dislike
2 answers
0 like 0 dislike
4 answers
0 like 0 dislike
7 answers
0 like 0 dislike
4 answers
0 like 0 dislike
6 answers
110,608 questions
257,186 answers
0 comments
27,942 users