Critère `{pagination X}` et requête SQL (PERF)
Le critère {pagination 10}
utilise une requête SQL qui retourne tous les résultats sans pagination. C’est ensuite avec l’iterateur que l’on cherche le début des éléments de la liste ->seek(...)
.
Lorsqu’une table contient de nombreuses entrées, il semble que ça soit très pénalisant comme comportement.
Alternativement, je suppose, il faudrait pouvoir dire que {pagination 10}
crée 2 requêtes :
- 1 avec
LIMIT {10} OFFSET {pagenumber * recordsperpage}
- 1 avec
SELECT COUNT(*) as nb_items ...
pour que#PAGINATION
et autre#GRAND_TOTAL
connaisse le nombre total d’éléments sans pagination.
Lu sur l’IRC donc:
<Guest48>
I was debugging a squelette with pagination and I have noticed that spip in the query always get the whole table without limit how is that possible?
Ex:
SELECT articles.date, articles.id_article, articles.date_modif, articles.titre, articles.descriptif, articles.texte, articles.lang
FROM `dbname`.spip_articles AS `articles`
WHERE (articles.statut = 'publie')
AND ((articles.id_rubrique IN (1)))
ORDER BY articles.date DESC;
<BOUCLE_rubrica(ARTICLES){branche}{!par date}{doublons}{pagination 10}>
I mean I thought it should apply a limit somehow
is that pretty inefficient? I mean suppose that I want just the first 10 records and the query return 100k record, spip will get that from db in memory and return just the first 10?
is there anyway to implement pagination and have the page links without having spip download but it seems what happen behind the scene is that the query download all records even if I then display just 10 if I have a rubrique with 100k items and I want a pagination with 10 items per page, the query load in memory (on php) all the records anyway and the just extract the items of the page I understood correctly? instead of doing something like
SELECT column FROM table LIMIT {10} OFFSET {pagenumber * recordsperpage};
i have several websites with up to 200k articles each