Discussion :

[elric47]

Bonjour,

Je cherche à optimiser la table suivante avant sa mise en production.

CREATE TABLE `table1` (
`titre` text,
`creation` date NOT NULL,
`mots` varchar(256) default NULL,
`para1` text NOT NULL,
`corps` text NOT NULL,
`valid` int(2) default NULL,
`id` int(11) NOT NULL auto_increment,
`typedocid` int(2) default NULL,
`sourcename` varchar(256) default NULL,
`link` varchar(256) default NULL,
`themeid` int(2) default NULL,
`mustupdate` int(2) default NULL,
) ENGINE=MyISAM AUTO_INCREMENT=5968 DEFAULT CHARSET=utf8

Cette table contient du contenu éditorial et les requêtes qu’elle subira seront largement à plus de 90 % de type select . Les tables sont relativement petites, de 5000 lignes à 20000 lignes.

La config du serveur de prod est: windows 2003 server/2Go de ram/IIS6.0/mysql 5.0/code en asp

Je m’interroge sur la façon d’optimiser cette table avec les index


Voici les requêtes qui seront les plus utilisées

Code :

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

1
2
3
4
5
1)	select * from table1 where valid='1' order by creation desc
2)	select MATCH(titre,para1,corps,mots) AGAINST  ('yyyyyyyy') as score , id,titre,creation,para1,corps,creation,themeid,typedocid,valid,sourcename,link FROM table1 where (typedocid='1' or typedocid=' 2' ) and (themeid=’1’ or themeid=’2’) and (titre like '%yyyyyyyy%' or corps like '%yyyyyyyy%' or para1 like '%yyyyyyyy%' or mots like '%yyyyyyyy%') and valid='1' order by MATCH(titre,para1,corps,mots) AGAINST  ('yyyyyyyy')
3)	select * from table1 where id=’xxx’
4)	select MATCH(titre,para1,corps,mots) AGAINST  ('yyyyyyyy') as score , id,titre,creation,para1,corps,creation,themeid,typedocid,valid,sourcename,link FROM depeches where (titre like '%yyyyyyyy%' or corps like '%yyyyyyyy%' or para1 like '%yyyyyyyy%' or mots like '%yyyyyyyy%') and (creation <'2008-2-27' and creation >'1995-8-15') and valid='1' order by MATCH(titre,para1,corps,mots) AGAINST  ('yyyyyyyy')
5)	select * FROM table1 where (typedocid='1') and (themeid='1') and valid='1' order by creation desc

Apres avoir lu la doc officielle de mysql ainsi que divers tuto/articles sur ce forum et ailleurs j’en arrive à la conclusion que l’idéal serait de faire un index multi colonne sur l’ensemble des champs utilisés dans des clauses « where » et « order by » , ce qui est le cas dans mes requêtes pour les champs titre, para1, corps, mots, date, typedocid, themeid et id.

Je pense donc créer les index suivants :
• FULLTEXT (id,titre, para1, corps, mots, creation, typedocid, themeid) pour optimiser les requetes de lecture
• FULLTEXT (titre,para1,corps,mots) pour les recherche Full text par mots clés?
• FULLTEXT (id) pour les requetes MIN ou MAX sur id
D'autres index seraient il utiles ?

J’ai bien conscience que le préalable est de transformer certains champs existant en format INT ou DATE en format TEXT ou VARCHAR et ce sans dommages pour mon appli.
Je ne vois pas d’inconvénients majeurs à convertir mes champs typedocid et themeid en varchar mise à part de l'espace disque mais cela me semble négligeable.
En transformant INT en VARCHAR je ne peux plus autoincrement mais je devrais pouvoir contourner ce pb par une requête de type max sur le champ id lors des INSERT
Il y a peut d’être des inconvénients que je n’ai pas identifié. Qu’en est-il selon vous ?

J’avais aussi des doutes sur le résultat des clauses « where creation>date1 » et « order by creation desc » si je transforme ma date en varchar. J’ai fait des test ça semble correct. C’est normal ? Est-ce du au format de ma date « aaaa-mm-dd » ?

Par ailleurs je n’ai vraiment compris l’intérêt des KEY dans ce cas. Quelqu’un peut m’éclairer à ce sujet ?


[*]Enfin j’ai lu sur cette page : http://www.databasejournal.com/featu...le.php/3367871 que je devais paramétrer la valeur key_buffer_size entre 25% et 50 % de la RAM du serveur.

Qu’en pensez-vous ?


Merci d’avance

[elric47]

J'ai crée la table suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE `table2` (                                                                    
            `titre` text,                                                                              
            `creation` varchar(10) NOT NULL,                                                           
            `auteur` varchar(50) default NULL,                                                         
            `mots` varchar(256) default NULL,                                                          
            `para1` text NOT NULL,                                                                     
            `corps` text NOT NULL,                                                                     
            `valid` varchar(1) NOT NULL,                                                               
            `id` varchar(11) NOT NULL,                                                                 
            `typedocid` varchar(2) NOT NULL,                                                           
            `sourcename` varchar(256) default NULL,                                                    
            `link` varchar(256) default NULL,                                                          
            `themeid` varchar(2) NOT NULL,                                                             
            `mustupdate` mediumint(1) default NULL,                                                    
            PRIMARY KEY  (`id`),                                                                       
            FULLTEXT KEY `titre` (`titre`,`para1`,`corps`,`mots`),                                     
            FULLTEXT KEY `id` (`id`,`titre`,`para1`,`corps`,`mots`,`creation`,`typedocid`,`themeid`),  
            FULLTEXT KEY `id_2` (`id`),                                                                
            FULLTEXT KEY `typedocid` (`typedocid`),                                                    
            FULLTEXT KEY `themeid` (`themeid`),                                                        
            FULLTEXT KEY `creation` (`creation`)                                                       
          ) ENGINE=MyISAM DEFAULT CHARSET=utf8

J'ai réimporté les données depuis une autre table formatée en latin1.
L'import s'est passé correctement pas de pb particuliers.C'est la même table que précédemment sauf que j'ai ajouté une colonne auteur. charset et collation sont identique sur toutes les colonnes sur les 2 bases

Je lance la requête suivante :

Code :

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

select count(*) FROM table2 where (titre like '%sida%' or corps like '%sida%' or para1 like '%sida%' or mots like '%sida%') and (creation <'2008-2-27' and creation >'1999-9-27') and valid='1'

J'obtiens 380.

La même requête sur la table1 donne 405 comme résultat

La même requête sur 2 tables contenant les même donnes donne 2 résultats différents ? BUG or FEATURE ?


Par ailleurs la requête suivante me donne aussi 380 résultats mais un avertissement

Code :

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

select MATCH(titre,para1,corps,mots) AGAINST  ('sida') as score , id,titre,creation,para1,corps,creation,themeid,typedocid,valid,sourcename,link FROM table2 where (typedocid='1' or typedocid=' 2' ) and (titre like '%sida%' or corps like '%sida%' or para1 like '%sida%' or mots like '%sida%') and (creation <'2008-2-27' and creation >'1999-9-27') and valid='1' order by MATCH(titre,para1,corps,mots) AGAINST  ('sida') desc

Code :

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

Truncated incorrect DOUBLE value: '1999-9-27'

ça signifie quoi selon vous ?