Since J3.1.4 the debug mode gives now better information about the queries. It is easy now to identify the slow queries and find solutions to improve.
Most of the users might use Cobalt with a 1 section <-> 1 Type approach, while only a few might use several types per section.
Therefore the queries should be optimized for the single TYPE mode.
When checking on the slow queries on an intro view, i noticed 2 queries responsible for a slow down for a sigle type approach:
1)
SELECT DISTINCT `type_id`
FROM xxxxx_js_res_record
WHERE section_id = 3
2)
SELECT id as value, title as text
FROM xxxxx_js_res_record
WHERE published = 1
AND hidden = 0
AND section_id = 3
AND type_id IN(3)
AND ctime < '2013-08-04 03:25:22'
AND (extime = '0000-00-00 00:00:00' OR extime > '2013-08-04 03:25:22')
If query 1's only purpose is to determine the TYPES used in a section, than it can be ignored with only 1 TYPE available and the condition " AND type_id IN (3) " in query 2 can be removed to avoid an index_merge under MySQL.
If type_id in js_res_record is only to determine Multi Type condition then a multi column index (section_id, type_id) might be a better approach for Multi Tytpe setup. (see here )
In my setup i have a section intro view with 57K records having a relation field to another section of 38K records.
Query time:
SELECT DISTINCT .... section_id = 2 --> 215ms
SELECT DISTINCT .... section_id = 3 --> 138ms
SELECT id as value ... AND type_id .. --> 310ms
Just this 3 queries account for 663ms where it could be reduced to only around 150ms in removing the SELECT DISTINCT and adjusting the SELECT id query.
It is not only the performance gain, it also put less burden on the MySQL server.
Idea
Announcement
Conversation
Praise
Problem
Question
Open
Since J3.1.4 the debug mode gives now better information about the queries. It is easy now to identify the slow queries and find solutions to improve.
Most of the users might use Cobalt with a 1 section <-> 1 Type approach, while only a few might use several types per section.
Therefore the queries should be optimized for the single TYPE mode.
When checking on the slow queries on an intro view, i noticed 2 queries responsible for a slow down for a sigle type approach:
1)
2)
If query 1's only purpose is to determine the TYPES used in a section, than it can be ignored with only 1 TYPE available and the condition " AND type_id IN (3) " in query 2 can be removed to avoid an index_merge under MySQL.
If type_id in js_res_record is only to determine Multi Type condition then a multi column index (section_id, type_id) might be a better approach for Multi Tytpe setup. (see here )
In my setup i have a section intro view with 57K records having a relation field to another section of 38K records.
Query time:
SELECT DISTINCT .... section_id = 2 --> 215ms
SELECT DISTINCT .... section_id = 3 --> 138ms
SELECT id as value ... AND type_id .. --> 310ms
Just this 3 queries account for 663ms where it could be reduced to only around 150ms in removing the SELECT DISTINCT and adjusting the SELECT id query.
It is not only the performance gain, it also put less burden on the MySQL server.