Bonjour,

Je suis bénévole pour une association généalogique et une des bases de données contient plus de 5 millions d'entrées. Chaque requête effectuée sur la DB prend près de 40 secondes.

Comment puis-je diminuer ce temps de réponse? Puis-je optimiser la base de données?

Exemple de requete :
SELECT nom, prenom, naissance FROM citoyen_du_quebec WHERE nom like '%bertrand%'

Merci d'avance

Christian

Une requete de type "like '%bertrand%'" est une des pires chose que tu peux faire. MySQL ne peut utiliser ton index, il doit donc faire un row scan (il parcoure toutes les entrées de la table). Si tu as vraiment besoin de faire ce genre de recherche, regardes pour les index de type FullText. Cette page explique comment les utiliser.

Tu devrais peut-etre considerer utiliser un engin de recherche comme Lucene.

Il y a une version PHP de Lucene dans le Zend Framework, qui requiert donc moins de configuration sur le serveur. Sinon, il y a aussi Sphinx qui est plus rapide que la version PHP de Lucene si tu as un dédié.

La pire chose à faire: Utiliser Mysql pour 5 million d'entrées.

Mysql, c'est pas un tank. C'est pour les petits projets comme ça... Rien de plus. Vous devriez vous tourner vers quelque chose de plus "stable, puissant". PostgreSQL, ou Oracle (malheureusement payant).

Tu fais tes requêtes avec un programme? Si c'est avec php sur une page web, c'est pas du tout une bonne idée...

En plus, c'est assez dûr de migrer une base de données vers un autre sgbd.

http://www.siteduzero.com/tutoriel-3- ... s-types-de-champ-sql.html

Une source pour appuyer mon avis...
http://www.siteduzero.com/tutoriel-3- ... resql-lequel-choisir.html

Bef, vous êtes un peu mal barrés ^^' ... selon moi.

Ouf, c'est du FUD ça. Je préfère voir ses commentaires là rester sur Slashdot.

Le problème de performance dans ce cas. PostgreSQL et Oracle s'écrasent tout autant sur une requête comme ça parce qu'ils ne peuvent bénéficier des index. Il y a des cas ou d'autres SGBD performent mieux que MySQL, mais celui-ci n'en est pas un. Si j'ai tord, montre moi un benchmark.

Des bases de donnée avec 5 millions d'entrée, il y en a tout plein sur MySQL (Facebook, Yahoo, ...). Sauf qu'à ce niveau, il faut être plus prudent avec les requêtes. Un DBA PostgreSQL ou Oracle te dirais la même chose. Une base de donnée ne fait pas de magie.

Pour comprendre un peu plus comment une BD fonctionne (centré sur MySQL, mais InnoDB, c'est "by the book" comme SGBD): http://www.amazon.ca/High-Performance ... Replication/dp/0596101716

À mon avis Facebook c'est loin d'être du Mysql... Probablement un truc qu'ils ont créés eux-mêmes... Je n'ai malheureusement rien pour appuyer...

Je reste quand même sur mon avis (même avis que beaucoup de personnes): Mysql n'est pas la meilleure solution pour 5 millions d'entrées. Sinon amènes des sources... Je me tournerais sur PostgreSQL. Pourquoi? Car beaucoup de sites de grandes envergures l'utilisent et beaucoup d'articles disent que PostgreSQL est correcte pour beaucoup d'entrées (voir des millions?). Oracle aucune idée car je connais pas assez.

Quel SGBD choisir? -> http://fadace.developpez.com/sgbdcmp/
Débatre du sujet -> http://www.developpez.net/forums/f36/bases-donnees/decisions-sgbd/

Sinon voir mes autres liens...

Sinon petite question comme ça... Elle pèse combien votre base de données?

Facebook utilise MySQL avec memcached d'apres cet article sur leur blog.

Pour ce qui est des gros sites qui utilisent MySQL, nous avons plus de 10 millions de hits par jour et nous utilisons MySQL. Le seul probleme que nous avons est avec la réplication et nous avons des scripts qui détecte et regle toute erreur de réplication.

MySQL n'est pas parfait, mais bien utilisé il peut suffire dans beaucoup de cas. Je ne connais pas assez PostreSQL pour me prononcer. Et pour Oracle, c'est pas tout le monde qui a les moyens de le payer.

Taille? Quelques Mo à quelques Go.

Facebook utilise MySQL, c'est de nature publique.
http://www.mysql.com/customers/view/?id=757
http://blog.facebook.com/blog.php?post=7899307130

Sinon, google peut t'en sortir beaucoup d'autre. Faut faire un peu attention sur les liens d'opinion aussi. C'est pas basé sur des faits.

Mais pour en revenir au sujet du topic, LIKE '%quelque chose%', c'est pas indexable, peut importe la base de donnée. Voici même une discussion du sujet sur postgresql... http://stackoverflow.com/questions/15 ... ry-performance-variations

Mais êtes vous sûrs que facebook utilise Mysql pour gérer ses membres et tout et tout? En tout cas en sachant bien l'utiliser on peut bien gérer ses bases...

Sinon pour revenir au sujet,
Mieux définir tes champs?
http://www.siteduzero.com/tutoriel-3- ... s-types-de-champ-sql.html

Faire des jointures?

Le mot-clé "like" force un scan de la table. C'est ce qui provoque la lenteur peu importe la BD utilisée. C'est pas fort de dire que c'est la faute à mysql.

Parmi les BD les plus grosses, il y a ceux qui font du web-analytics comme Piwik, Crawtrack ou Open Web Analyics. Ces applications choisissent tous mysql alors qu'ils doivent prévoir pour des centaines de millions de lignes.

Il y a aussi des ad-server comme oasis qui choisissent mysql.

On peut voir les client de mysql ici :
http://www.mysql.com/customers/?id=75

Je note wikipedia entre autre. Il marche bien même s'il pèse facilement 20Go : http://stats.wikimedia.org/EN/TablesDatabaseSize.htm.

Il faudrait expliquer pourquoi tout ça. Pour quoi les gens qui ont d'énormes BD choisissent mysql et non les autres.

Les problèmes que j'ai rencontré avec mysql venaient de personnes qui utilisait encore mysql 4, mais surtout de gens qui recompilaient mysql avec leur compilateur favori parfois en version beta. J'ai vu aussi une personne qui voulait absolument utiliser un mac comme server qui avait des problème. À mon avis un mysql qui fonctionne mal est la responsabilité du sysadmin en place qui n'a pas fait sa job. C'est plus facile de dire que c'est la faute à mysql.