6 years ago
6
Topic

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

Get a VIP membership
6 years ago
0
Level 1

Bonjour Cyril,

Tu peux tester le paramètre suivant, depuis ton Search Type :

Configuration > Limit / Count > "Estimate" à la place de "Auto"

Cordialement,
Saba

6 years ago
2
Level 1

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

6 years ago
1
Level 2

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

4229 Posts
Kadministrator
6 years ago
0
Level 3

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"

6 years ago
1
Level 1

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

4229 Posts
Kadministrator
6 years ago
0
Level 2

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

Get a VIP membership