Hello everyone!
I have a site with High-Loaded slow database queries. There is probably a taxonomy
for example:
SELECT DISTINCT node.nid AS nid, node.created AS node_created, node_revisions.body AS node_revisions_body, node_revisions.format AS node_revisions_format, node.title AS node_title, COUNT(node.nid) AS node_count FROM node node LEFT JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE (node.moderate = 0) AND (node.status = 1) AND (term_node.tid IN (94, 2108)) AND (node.nid != 47807) GROUP BY nid, node_created, node_revisions_body, node_revisions_format, node_title ORDER BY node_created DESC, node_count DESC LIMIT 0, 3
Query took 0.9084 seconds
StateДокументация Total Time % Time Calls ø Time
Copying To Tmp Table 866.3 ms 95.37% 1 866.3 ms
Sorting Result 29.7 ms 3.27% 1 29.7 ms
Removing Tmp Table 11.4 ms 1.25% 1 11.4 ms
Creating Tmp Table 339 µs 0.04% 1 339 µs
https://groups.drupal.org/files/prof.JPG
https://groups.drupal.org/files/graf.JPG
https://groups.drupal.org/files/explain.JPG
How can I optimize the query?
Thanks in advance for your help!
| Attachment | Size |
|---|---|
| prof.JPG | 123.87 KB |
| graf.JPG | 38.16 KB |
| explain.JPG | 32.72 KB |
Comments
Options
Increase your sort_buffer_size & join_buffer_size in my.conf.
Create a specific index for this query; using these columns from the node table
vid, moderate, status, nid, node_created, title. Note that the order matters so this is trial and error, You'll have to try several variations. Here are 2 others to trymoderate, status, vid, nid, node_created, titlenid, node_created, title, moderate, status, vidThere are several other permutations to try...
Remove node_revisions_body from the Group By
Upgrade to percona 5.5+ as that should have the option to create memory temp tables with BLOB & TEXT columns.
Switch the database to InnoDB - https://www.drupal.org/project/dbtuner
Move the MySQL temp folder to a SSD or ram drive.
Here is the same Query you gave just formatted
SELECTDISTINCT node.nid AS nid,
node.created AS node_created,
node_revisions.body AS node_revisions_body,
node_revisions.format AS node_revisions_format,
node.title AS node_title,
COUNT(node.nid) AS node_count
FROM node node
LEFT JOIN term_node term_node
ON node.vid = term_node.vid
LEFT JOIN node_revisions node_revisions
ON node.vid = node_revisions.vid
WHERE (node.moderate = 0)
AND (node.status = 1)
AND (term_node.tid IN (94, 2108))
AND (node.nid != 47807)
GROUP BY
nid,
node_created,
node_revisions_body,
node_revisions_format,
node_title
ORDER BY
node_created DESC,
node_count DESC
LIMIT 0, 3
Re:
Thanks for the help!
I can tell you, for freebsd possible to move the temporary directory to the operational and give it some kind of result?