Discussion :

[Sindel]

Y a t'il un moyen d'optimiser un order by sur une recherche full text ?

J'ai une table de référence toute simple qui contient la clé vers un élément, et son contenu indexer en full text. Cette table fait plus de 800mo et contient a peu pres 1,5millions d'enregistrements.

mon my.cnf a les paramètres suivant :
ft_min_word_len = 3
ft_max_word_len = 32

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `t_news_index` (
  `c_news_link` int(10) unsigned NOT NULL auto_increment,
  `c_news_index_keyword` text NOT NULL,
  PRIMARY KEY  (`c_news_link`),
  FULLTEXT KEY `c_tags` (`c_news_index_keyword`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2912861 ;

Lorsque je fait une requete full text SANS ORDER BY sur cette table, elle s'execute en 0,0005s :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT SQL_NO_CACHE c_news_link, c_news_index_keyword
FROM t_news_index
WHERE MATCH (
t_news_index.c_news_index_keyword
)
AGAINST (
'une recherche sans order by qui fait peur'
IN boolean
MODE
)
LIMIT 5

La meme requete avec un ORDER BY met 13,7788s

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT SQL_NO_CACHE c_news_link, c_news_index_keyword
FROM t_news_index
WHERE MATCH (
t_news_index.c_news_index_keyword
)
AGAINST (
'une recherche avec order by qui fait peur'
IN boolean
MODE
)
ORDER BY c_news_link
LIMIT 5

Cette requête est normalement accompagné de nombreuses jointures, mais j'ai volontairement simplifier car le problème viens essentiellement de la.
Pensez vous qu'il y est une solution ?
(mon vrai order by se fait normalement sur les tables jointes, par sur cette table)

Merci,

[Antoun]

En fait, ça semble assez normal. Avec LIMIT sans ORDER BY, j'imagine que l'optimiseur prend les 5 premières lignes où le MATCH est vérifié, et s'arrête ensuite. Avec le ORDER BY, il n'a plus le droit de sélectionner arbitrairement ; il doit donc ressortir l'ensemble des lignes qui MATCHent, et pour ne garder ensuite que les cinq premières c_news_link. Autrement dit, ta requête sans ORDER BY présente des performances artificielles.

Pour optimiser, il faut faire des hypothèses... je vais supposer que si tu as 100 lignes, tu es sûr d'en avoir 5 qui matchent.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT SQL_NO_CACHE c_news_link, c_news_index_keyword
FROM (
  SELECT * FROM t_news_index ORDER BY c_news_link LIMIT 100
  ) AS t_news_index
WHERE MATCH (t_news_index.c_news_index_keyword)
AGAINST ('une recherche avec order by qui fait peur' IN BOOLEAN MODE)
ORDER BY c_news_link
LIMIT 5

[Sindel]

En effet cette méthode est très efficace,

J'ai par ailleurs enrichie considérablement les stopwords mysql (j'avais garder ceux par default, uniquement anglais j'imagine), et juste avec ça je suis descendu à moins de 3s par recherche au lieu de 13.
Avec ta solution en plus, je descend a moins de 0,5s avec un limit sur 300 000 lignes, c'est parfait et amplement suffisant !

Merci beaucoup !

[Sindel]

Finalement après plusieurs test c'est pas si parfait le limit sur le FROM (en tous cas pas avec un limit a plus de 100 000), dans un sens ça me permet de vraiment gérer la taille des requêtes, mais le problème c'est que les requêtes prennent toujours le même temps (c'est monté à 2 secondes actuellement, heure de forte affluence).

Alors que sans le limit sur le FROM certaines requêtes sorte en quelques millisecondes, d'autres sont encore exécutés en 4 a 5 secondes, mais pas tant que ça au final.

Peut être que j'ai mal compris l'implémentation de la solution que tu m'a proposé.


Je vais tenter d'expliquer plus précisément mon problème :

J'exécute actuellement une requête pour sélectionner les champs, et une seconde requête COUNT(*) pour calculer le nombre total de lignes et savoir combien de page je doit afficher. bref classique mais lors du chargement de la page 1 on exécute 2 requêtes.
Par la suite, la requête COUNT(*) reste dans le query cache, seul la requête de sélection des champs est exécuté.

J'utilisais précédemment la function SQL_CALC_FOUND_ROWS, mais sur une pagination le LIMIT change, et la query n'est plus identique de la page 1 a la page 2, et donc le query cache n'est pas utilisé.

Mais au final SQL_CALC_FOUND_ROWS ne ralentie pas énormément l'exécution de mes requêtes, donc je pense le réutiliser pour construire mes pages de listing.

Qu'en pensez vous ?
Avez vous une idée pour optimisé les systèmes de pagination ?

Merci,