Hi
When you activate the joomla debug mode you can see that for each seblod list you almost have a duplicate query
the first one is the main query itself :
SELECT t0.id AS pid,t0.pk AS pk,t0.pkb AS pkb,t0.parent_id AS parent,t0.author_id AS author,t0.cck AS cck,t0.storage_location AS loc,tt.id AS type_id,tt.alias AS type_alias FROM `zkx3r_cck_core` AS t0 LEFT JOIN `zkx3r_content` AS t1 ON t1.id = t0.pk LEFT JOIN `zkx3r_cck_core_types` AS tt ON tt.name = t0.cck WHERE t1.state = 1 AND t1.access IN (1,1,5) AND ( t1.publish_up = '0000-00-00 00:00:00' OR t1.publish_up <= '2018-07-04 16:34' ) AND ( t1.publish_down = '0000-00-00 00:00:00' OR t1.publish_down >= '2018-07-04 16:34' ) AND t0.cck = 'produit' ORDER BY t1.title ASC LIMIT 25
and the second one (just after the first one) is almost the same but to get the count of the results:
SELECT COUNT(t0.id) FROM `zkx3r_cck_core` AS t0 LEFT JOIN `zkx3r_content` AS t1 ON t1.id = t0.pk LEFT JOIN `zkx3r_cck_core_types` AS tt ON tt.name = t0.cck WHERE t1.state = 1 AND t1.access IN (1,1,5) AND ( t1.publish_up = '0000-00-00 00:00:00' OR t1.publish_up <= '2018-07-04 16:34' ) AND ( t1.publish_down = '0000-00-00 00:00:00' OR t1.publish_down >= '2018-07-04 16:34' ) AND t0.cck = 'produit'
on large database (here 220 000 products) this duplicate time makes the page very slow to appear.
Can we deactivate the second query please ?
thanks
cyril
Bonjour Cyril,
Tu peux tester le paramètre suivant, depuis ton Search Type :
Configuration > Limit / Count > "Estimate" à la place de "Auto"
Cordialement,
Saba
Bonjour Seb
Oui j'avais déjà essayé mais cela ne change rien. On a toujours cette seconde requete qui prend du temps dans notre cas assez inutilement
merci
cyril
Hi
As I wrote, I already tried to set the parameter Configuration > Limit / Count to "Estimate" instead of "Auto" in the list without success. The count query is perfomed anyway
thanks
cyril
I just tried changing this in list&search type configuration and it works ok for me on Seblod 3.16.2., with articles serch type, second query is changed to
SELECT COUNT(id)
FROM #__cck_core
WHERE cck = "article"
yes, may be a little faster but still expendable. No way to get rid of it ? Again we are takling about a 220 000 articles base.
thanks
cyril
As cck column is indexed this query should run very fast, try running
EXPLAIN EXTENDED SELECT COUNT(id) FROM #_cck_core WHERE cck = "article" (replace prefix and article with your content type)
you should get something like "using index" and query should be instant, there are only very complex ways to count faster than using mysql on indexed column.
If this is not true you can add a covering index - single index on on id and cck column
