As a consequence of ticket #753, a criteria "LIKE" is introducted [in
the SVN version of Spip]. It allows to compile {titre LIKE 'a%'} into
: WHERE titre LIKE 'a%'
The advantage is that it enables to use MySQL INDEXes for some really
slow loops (like
<BOUCLE_s(SIGNATURES){nom_email==^#ENV{letter,.}}>) :
you have here to add an INDEX on the table spip_signatures, for
exemple here on the first 2 characters :
ALTER TABLE spip_signatures ADD INDEX nom_email (nom_email(2));
(note : SPIP doesn't do that itself because such optimisation depends
of what you do with SPIP)
The "WHERE title LIKE 'a%' " is then extremely fast, at the opposite
of "nom REGEXP '^a' " which requires a regexp on each value of
spip_signature.nom
The EXPLAIN request gives theses results :
{nom_email == '^a'}
mysql> explain select * from spip_signatures where nom_email regexp 'a%';
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | spip_signatures | ALL | NULL | NULL |
NULL | NULL | 3835 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
{nom_email LIKE 'a%'} sans INDEX
mysql> explain select * from spip_signatures where nom_email like 'a%';
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | spip_signatures | ALL | NULL | NULL |
NULL | NULL | 3835 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
{nom_email LIKE 'a%'} avec INDEX
mysql> explain select * from spip_signatures where nom_email like 'a%';
+----+-------------+-----------------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | spip_signatures | range | nom_email |
nom_email | 4 | NULL | 193 | Using where |
+----+-------------+-----------------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)
Note also that the criteria {groupby} is now named {fusion}.
Exemple :
<BOUCLE_langues(ARTICLES){fusion lang}> #LANG </BOUCLE_langues>
will only return on article for each language
Note also this "bug" :
Actuelly, '%' can't be used directly as a criteria, so you can't do
directly <BOUCLE_a(ARTICLES){titre like #ENV{initiale,A}%}>
But you can use this hack :
<BOUCLE_a(ARTICLES){titre like (#ENV{initiale,A}|concat{'%'})}>
enjoy !
.Gilles
(Approximate translation : original bellow)
---------- Forwarded message ----------
From: Fil <fil@rezo.net>
Date: 28 janv. 2007 13:38
Subject: [spip-dev] ! critere {titre like 'a%'}
To: spip-core@rezo.net
Coucou,
en liaison avec le ticket #753, introduction du critere "LIKE" qui permet de
compiler {titre LIKE 'a%'} en : WHERE titre LIKE 'a%'
Ca permet d'utiliser les INDEX de MySQL si on a des boucles "qui rament" du
genre <BOUCLE_s(SIGNATURES){nom_email==^#ENV{initiale,.}}> : il faut
alors ajouter
un INDEX sur la table spip_signatures, par exemple ici sur les deux premiers
caractères :
ALTER TABLE spip_signatures ADD INDEX nom_email (nom_email(2));
(note: SPIP ne s'occupe pas de faire ça lui-même, cette optimisation dépend
bien entendu de l'usage qu'on en fait).
Le WHERE titre LIKE 'a%' est alors ultra-rapide, contrairement à un WHERE
nom REGEXP '^a' qui oblige à calculer le regexp sur l'ensemble des valeurs
de spip_signatures.nom
(C'est expérimental, mais comme c'est difficile à faire en plugin je me suis
permis de le mettre directement dans le core.)
Pour mémoire, le explain requete donne :
{nom_email == '^a'}
mysql> explain select * from spip_signatures where nom_email regexp 'a%';
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | spip_signatures | ALL | NULL | NULL |
NULL | NULL | 3835 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
{nom_email LIKE 'a%'} sans INDEX
mysql> explain select * from spip_signatures where nom_email like 'a%';
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | spip_signatures | ALL | NULL | NULL |
NULL | NULL | 3835 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
{nom_email LIKE 'a%'} avec INDEX
mysql> explain select * from spip_signatures where nom_email like 'a%';
+----+-------------+-----------------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | spip_signatures | range | nom_email |
nom_email | 4 | NULL | 193 | Using where |
+----+-------------+-----------------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)
A signaler aussi sur cette liste, le critère {groupby} s'appelle désormais
{fusion}, exemple :
<BOUCLE_langues(ARTICLES){fusion lang}> #LANG </BOUCLE_langues>
ne donnera qu'un article de chaque langue.
-- Fil
_______________________________________________
liste: http://listes.rezo.net/mailman/listinfo/spip-dev
doc: http://www.spip.net/
dev: http://trac.rezo.net/trac/spip/
irc://irc.freenode.net/spip