Recently I changed MySQL version to 5.6.23 from 5.5.8 and got stuck in some query performance issue. In old MySQL; query is executed in some reasonable time, but in new one freebsd server+mysql server becomes unresponsive. Linux command
[linux~] top
show mysql >100% wcpu when query is executed. If I execute each subquery seperately, results are fetched in seconds. At this moment mysql and apache needs to be restarted to continue with work.
So my problem is optimization of next query:
SELECT concat_ws('##', pos.name,
( SELECT COUNT(*) FROM nalogi n
WHERE n.other_createtime >= '2015-03-01 00:00:00'
AND n.other_createtime <= '2015-03-31 23:59:59'
AND n.nalog_invalid = 'N' AND n.other_pos = pos.id ),
( SELECT COUNT(*) FROM nalogi n, posiljke_nalogi pn, posiljke p
WHERE p.other_createtime >= '2015-03-01 00:00:00'
AND p.other_createtime <= '2015-03-31 23:59:59'
AND pn.nalogid = n.id
AND pn.masterId = p.id
AND p.path_from_type = 'center'
AND n.nalog_invalid = 'N'
AND (p.path_to_type = 'pos' OR p.path_to_type = 'customer')
AND n.other_pos = pos.id )) 'entry' FROM spl_pos pos ORDER BY pos.id
Currently I am using indexes and caching of tables in memory.
Any speed optimization or other advices are welcome.
I searched the net and found some good sources source1, source2 and many others which I already use in existing query. I also checked type of strings that I compare. Based on experiences I have some issues when selecting some rows with:
where x.field=field (slow)
where x.field="field" (much faster)
So I also checked for that, and date type too. Now I am almost without any more idea to try. I can provide additional data if needed.
Currently I am saving situation with executing queries seperately (which I would prefer to do in one query).
Copyright Notice：Content Author:「Matej Murn」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/29489001/mysql-query-server-optimization