Inspirée d’un papier intitulé The Best of the 20th Century: Editors Name Top 10 Algorithms, une série de 2 articles (part 1 et part 2) revisitant ces classiques de l’informatique!
10 algorithmes marquants
12 février 2018
Leave a Comment » | 
algorithmes, informatique | Tagué: algorithmes, Fortran, informatique, Krylov, matrices, Monte Carlo, optimisation, programmation linéaire, QR, quicksort, recherche opérationnelle, simplex, transformation de Fourier | 
Permalien
Publié par endormitoire
Le blogue de Steven Pigeon
2 octobre 2017
Ce blogue est un heureux mélange de mathématiques, d’informatique, d’optimizations, de programmation et d’algorithmes! Un autre des blogues que j’adore : Harder, Better, Faster, Stronger.
Leave a Comment » | algorithmes, informatique, mathématiques, optimisation, performance, programmation | Tagué: algorithmes, Better, Faster, Harder, informatique, mathématiques, optimisation, Stronger | Permalien
Publié par endormitoire
Du nouveau dans le problème du voyageur de commerce!
17 septembre 2017
Le problème du voyageur de commerce est un des problèmes d’optimisation les plus étudiés autant en mathématiques qu’en informatique. Bon nombre de chercheurs ont travaillé sur ce problème complexe, en grande partie à cause de son énorme utilité dans le monde réel.
Une avancée récente (décrite ici) risque de relancer l’intérêt pour cet problème d’optimisation.
Pour les plus curieux, un site web incontournable traitant de ce problème.
Leave a Comment » | algorithmes, informatique, mathématiques, optimisation | Tagué: algorithmes, graphes, informatique, mathématiques, optimisation, Travelling Salesman Problem, TSP, voyageur de commerce | Permalien
Publié par endormitoire
Problème du voyageur de commerce
6 février 2015
Le problème du voyageur de commerce, mieux connu sous le nom de Traveling Salesman Problem (TSP), me fascine depuis longtemps. C’est le genre de défi mathématique qui pousse le domaine informatique dans ses derniers retranchements. Ce problème exige des solutions algorithmiques de plus en plus intelligentes, efficaces et complexes sans qu’on en vienne à bout de façon satisfaisante.
Pour en savoir plus sur le TSP, il y a l’excellent site de l’Université de Waterloo sur le sujet.
Leave a Comment » | informatique, mathématiques | Tagué: algorithmes, mathématiques, Problème du voyageur de commerce, Traveling salesman problem, TSP | Permalien
Publié par endormitoire
Tris et musique
11 janvier 2015
Sorted Affairs, une distrayante animation (accompagnée de musique!) de divers algorithmes de tri.
Leave a Comment » | algorithmes | Tagué: algorithmes, animation, musique, tri | Permalien
Publié par endormitoire
Comment presser un citron (deuxième partie)
8 janvier 2012Dans le premier article, nous avons vu combien il était facile de solutionner des grilles de sudoku avec une seule requête SQL. Malheureusement, c’était trop beau pour être vrai…
Avant de poursuivre, voici un outil utile pour se faciliter la tâche. Pour ceux qui utilisent Smalltalk, vous pouvez vous aider du script suivant :
| uneTableOuVue uneStringSudoku stream estLePremier | uneTableOuVue := 'sudoku_rows_view'. uneStringSudoku := '.........134825697759364182397182564.........581476239825641973976538421.........'. stream := ReadWriteStream on: String new. stream nextPutAll: 'SELECT * '; cr; nextPutAll: 'FROM '; nextPutAll: uneTableOuVue; space; cr; nextPutAll: 'WHERE'; cr. estLePremier := false. 1 to: 9 do: [:r | 1 to: 9 do: [:c | | i | i := (r-1)*9 + c. ((uneStringSudoku at: i) ~= $. and: [(uneStringSudoku at: i) ~= 0]) ifTrue: [ estLePremier ifTrue: [stream nextPutAll: ' AND ';cr]. estLePremier := true. stream nextPutAll: '(r'; nextPutAll: r asString; nextPutAll: 'c'; nextPutAll: c asString; nextPutAll: ' = '; nextPutAll: (uneStringSudoku at: i) asString; nextPutAll: ')'. ].]]. Transcript cr;show: stream contents
Ce script génère les requêtes SQL à partir d’une chaîne de caractères (uneStringSudoku) représentant une grille de sudoku pour une table/vue précise (uneTableOuVue) en affichant le résultat dans le Transcript.
1. Problème majeur
Il y a une faille majeure et évidente dans la solution telle que présentée : elle repose totalement sur la capacité de MySQL de faire correspondre les révélés (givens en anglais) de la grille à des lignes. Que se passerait-il si une ligne (ou pire encore, plusieurs lignes) dans le sudoku ne contenait pas de révélés (ou pas assez)? Sans index à utiliser, une lecture séquentielle de la table nous attend sans doute! Prenons, par exemple, la grille suivante:
Ce sudoku contient 54 révélés (la première grille n’en comptait que 30, près de la moitié de celle-ci) et 6 de ses lignes seront rapidement récupérées avec les index. De plus, il y a suffisamment de révélés pour éliminer rapidement un bon nombre de lignes candidates avec les contraintes du puzzle incluses dans la vue sudoku_rows_view.
Cependant les lignes vides (1, 5 et 9) causeront probablement une lecture séquentielle (table scan) de la table. Nous faisons face à un problème majeur avec les lignes 1, 5 et 9 étant vides! Examinons la requête pour ce sudoku :
EXPLAIN SELECT * FROM sudoku_rows_view WHERE (r2c1 = 1) AND (r2c2 = 3) AND (r2c3 = 4) AND (r2c4 = 8) AND (r2c5 = 2) AND (r2c6 = 5) AND (r2c7 = 6) AND (r2c8 = 9) AND (r2c9 = 7) AND (r3c1 = 7) AND (r3c2 = 5) AND (r3c3 = 9) AND (r3c4 = 3) AND (r3c5 = 6) AND (r3c6 = 4) AND (r3c7 = 1) AND (r3c8 = 8) AND (r3c9 = 2) AND (r4c1 = 3) AND (r4c2 = 9) AND (r4c3 = 7) AND (r4c4 = 1) AND (r4c5 = 8) AND (r4c6 = 2) AND (r4c7 = 5) AND (r4c8 = 6) AND (r4c9 = 4) AND (r6c1 = 5) AND (r6c2 = 8) AND (r6c3 = 1) AND (r6c4 = 4) AND (r6c5 = 7) AND (r6c6 = 6) AND (r6c7 = 2) AND (r6c8 = 3) AND (r6c9 = 9) AND (r7c1 = 8) AND (r7c2 = 2) AND (r7c3 = 5) AND (r7c4 = 6) AND (r7c5 = 4) AND (r7c6 = 1) AND (r7c7 = 9) AND (r7c8 = 7) AND (r7c9 = 3) AND (r8c1 = 9) AND (r8c2 = 7) AND (r8c3 = 6) AND (r8c4 = 5) AND (r8c5 = 3) AND (r8c6 = 8) AND (r8c7 = 4) AND (r8c8 = 2) AND (r8c9 = 1);
Le résultat du EXPLAIN :
+----+-------------+-------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+---------+------+--------+----------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+---------+------+--------+----------------------------------------------------------------------------------+ | 1 | SIMPLE | r2 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i37,i29 | 2,2 | NULL | 68 | Using intersect(i37,i29); Using where | | 1 | SIMPLE | r3 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i37,i28 | 2,2 | NULL | 68 | Using intersect(i37,i28); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r4 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i56,i29 | 2,2 | NULL | 68 | Using intersect(i56,i29); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r6 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i12,i35 | 2,2 | NULL | 68 | Using intersect(i12,i35); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r7 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i12,i68 | 2,2 | NULL | 68 | Using intersect(i12,i68); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r8 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i37,i79,i38 | 2,2,2 | NULL | 78 | Using intersect(i37,i79,i38); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r1 | range | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i23 | 1 | NULL | 40012 | Using index condition; Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r5 | ALL | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | NULL | NULL | NULL | 362880 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r9 | ALL | NULL | NULL | NULL | NULL | 362880 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+---------+------+--------+----------------------------------------------------------------------------------+ 9 rows in set (13.39 sec)
Les lignes vides (lignes 1, 5 et 9) nous feront payer très chèrement le fait que nous ne pouvons pas utiliser pleinement les index! Il n’y a qu’à constater le nombre de lignes à potentiellement examiner pour joindre les tables r1, r5 et r9 (40012 x 362880 x 362880 = 5268855958732800) ainsi que le type de jointure utilisé (range, ALL et ALL).
Vérifions en combien de temps la requête s’exécute :
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+ | r1c1 | r1c2 | r1c3 | r1c4 | r1c5 | r1c6 | r1c7 | r1c8 | r1c9 | r2c1 | r2c2 | r2c3 | r2c4 | r2c5 | r2c6 | r2c7 | r2c8 | r2c9 | r3c1 | r3c2 | r3c3 | r3c4 | r3c5 | r3c6 | r3c7 | r3c8 | r3c9 | r4c1 | r4c2 | r4c3 | r4c4 | r4c5 | r4c6 | r4c7 | r4c8 | r4c9 | r5c1 | r5c2 | r5c3 | r5c4 | r5c5 | r5c6 | r5c7 | r5c8 | r5c9 | r6c1 | r6c2 | r6c3 | r6c4 | r6c5 | r6c6 | r6c7 | r6c8 | r6c9 | r7c1 | r7c2 | r7c3 | r7c4 | r7c5 | r7c6 | r7c7 | r7c8 | r7c9 | r8c1 | r8c2 | r8c3 | r8c4 | r8c5 | r8c6 | r8c7 | r8c8 | r8c9 | r9c1 | r9c2 | r9c3 | r9c4 | r9c5 | r9c6 | r9c7 | r9c8 | r9c9 | +------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+ | 2 | 6 | 8 | 7 | 1 | 9 | 3 | 4 | 5 | 1 | 3 | 4 | 8 | 2 | 5 | 6 | 9 | 7 | 7 | 5 | 9 | 3 | 6 | 4 | 1 | 8 | 2 | 3 | 9 | 7 | 1 | 8 | 2 | 5 | 6 | 4 | 6 | 4 | 2 | 9 | 5 | 3 | 7 | 1 | 8 | 5 | 8 | 1 | 4 | 7 | 6 | 2 | 3 | 9 | 8 | 2 | 5 | 6 | 4 | 1 | 9 | 7 | 3 | 9 | 7 | 6 | 5 | 3 | 8 | 4 | 2 | 1 | 4 | 1 | 3 | 2 | 9 | 7 | 8 | 5 | 6 | +------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+ 1 row in set (6.17 sec)
Ouf! Nous sommes passés de 0.39 seconde (grille 01) à 6.17 secondes (grille 02) malgré le fait que la grille 02 comporte près de 2 fois plus de révélés que la grille 01! Cet exemple démontre clairement que les choses peuvent rapidement mal tourner à un rythme alarmant (même exponentiel) quand nous sommes confrontés à une grille contenant des lignes vides ou presque vides!
2. Changer de point de vue!
Imaginons maintenant que nous faisons effectuer une rotation à notre grille et que les colonnes deviennent des lignes. Dans ce cas, chaque nouvelle ligne pourrait dès lors bénéficier des index!
Voyons cette nouvelle grille :
La requête aurait désormais l’air de ceci :
SELECT * FROM sudoku_rows_view WHERE (r1c2 = 1) AND (r1c3 = 7) AND (r1c4 = 3) AND (r1c6 = 5) AND (r1c7 = 8) AND (r1c8 = 9) AND (r2c2 = 3) AND (r2c3 = 5) AND (r2c4 = 9) AND (r2c6 = 8) AND (r2c7 = 2) AND (r2c8 = 7) AND (r3c2 = 4) AND (r3c3 = 9) AND (r3c4 = 7) AND (r3c6 = 1) AND (r3c7 = 5) AND (r3c8 = 6) AND (r4c2 = 8) AND (r4c3 = 3) AND (r4c4 = 1) AND (r4c6 = 4) AND (r4c7 = 6) AND (r4c8 = 5) AND (r5c2 = 2) AND (r5c3 = 6) AND (r5c4 = 8) AND (r5c6 = 7) AND (r5c7 = 4) AND (r5c8 = 3) AND (r6c2 = 5) AND (r6c3 = 4) AND (r6c4 = 2) AND (r6c6 = 6) AND (r6c7 = 1) AND (r6c8 = 8) AND (r7c2 = 6) AND (r7c3 = 1) AND (r7c4 = 5) AND (r7c6 = 2) AND (r7c7 = 9) AND (r7c8 = 4) AND (r8c2 = 9) AND (r8c3 = 8) AND (r8c4 = 6) AND (r8c6 = 3) AND (r8c7 = 7) AND (r8c8 = 2) AND (r9c2 = 7) AND (r9c3 = 2) AND (r9c4 = 4) AND (r9c6 = 9) AND (r9c7 = 3) AND (r9c8 = 1);
Le résultat du EXPLAIN correspondant :
+----+-------------+-------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+---------+------+------+----------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+---------+------+------+----------------------------------------------------------------------------------+ | 1 | SIMPLE | r3 | index_merge | i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i67,i68,i69,i78,i79,i89 | i23,i38,i48 | 2,2,2 | NULL | 64 | Using intersect(i23,i38,i48); Using where | | 1 | SIMPLE | r9 | index_merge | i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i67,i68,i69,i78,i79,i89 | i78,i68,i48 | 2,2,2 | NULL | 65 | Using intersect(i78,i68,i48); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r1 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i23,i67 | 2,2 | NULL | 68 | Using intersect(i23,i67); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r2 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i23,i68 | 2,2 | NULL | 68 | Using intersect(i23,i68); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r4 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i78,i46 | 2,2 | NULL | 68 | Using intersect(i78,i46); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r5 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i23,i68 | 2,2 | NULL | 68 | Using intersect(i23,i68); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r6 | index_merge | i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i67,i68,i69,i78,i79,i89 | i23,i67 | 2,2 | NULL | 68 | Using intersect(i23,i67); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r7 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i23,i68 | 2,2 | NULL | 68 | Using intersect(i23,i68); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | r8 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i37,i24 | 2,2 | NULL | 68 | Using intersect(i37,i24); Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+---------+------+------+----------------------------------------------------------------------------------+ 9 rows in set (42.98 sec)
Et combien de temps la requête prendrait-elle à s’exécuter?
SELECT * FROM sudoku_rows_view WHERE (r1c2 = 1) AND (r1c3 = 7) AND (r1c4 = 3) AND (r1c6 = 5) AND (r1c7 = 8) AND (r1c8 = 9) AND (r2c2 = 3) AND (r2c3 = 5) AND (r2c4 = 9) AND (r2c6 = 8) AND (r2c7 = 2) AND (r2c8 = 7) AND (r3c2 = 4) AND (r3c3 = 9) AND (r3c4 = 7) AND (r3c6 = 1) AND (r3c7 = 5) AND (r3c8 = 6) AND (r4c2 = 8) AND (r4c3 = 3) AND (r4c4 = 1) AND (r4c6 = 4) AND (r4c7 = 6) AND (r4c8 = 5) AND (r5c2 = 2) AND (r5c3 = 6) AND (r5c4 = 8) AND (r5c6 = 7) AND (r5c7 = 4) AND (r5c8 = 3) AND (r6c2 = 5) AND (r6c3 = 4) AND (r6c4 = 2) AND (r6c6 = 6) AND (r6c7 = 1) AND (r6c8 = 8) AND (r7c2 = 6) AND (r7c3 = 1) AND (r7c4 = 5) AND (r7c6 = 2) AND (r7c7 = 9) AND (r7c8 = 4) AND (r8c2 = 9) AND (r8c3 = 8) AND (r8c4 = 6) AND (r8c6 = 3) AND (r8c7 = 7) AND (r8c8 = 2) AND (r9c2 = 7) AND (r9c3 = 2) AND (r9c4 = 4) AND (r9c6 = 9) AND (r9c7 = 3) AND (r9c8 = 1); +------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+ | r1c1 | r1c2 | r1c3 | r1c4 | r1c5 | r1c6 | r1c7 | r1c8 | r1c9 | r2c1 | r2c2 | r2c3 | r2c4 | r2c5 | r2c6 | r2c7 | r2c8 | r2c9 | r3c1 | r3c2 | r3c3 | r3c4 | r3c5 | r3c6 | r3c7 | r3c8 | r3c9 | r4c1 | r4c2 | r4c3 | r4c4 | r4c5 | r4c6 | r4c7 | r4c8 | r4c9 | r5c1 | r5c2 | r5c3 | r5c4 | r5c5 | r5c6 | r5c7 | r5c8 | r5c9 | r6c1 | r6c2 | r6c3 | r6c4 | r6c5 | r6c6 | r6c7 | r6c8 | r6c9 | r7c1 | r7c2 | r7c3 | r7c4 | r7c5 | r7c6 | r7c7 | r7c8 | r7c9 | r8c1 | r8c2 | r8c3 | r8c4 | r8c5 | r8c6 | r8c7 | r8c8 | r8c9 | r9c1 | r9c2 | r9c3 | r9c4 | r9c5 | r9c6 | r9c7 | r9c8 | r9c9 | +------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+ | 2 | 1 | 7 | 3 | 6 | 5 | 8 | 9 | 4 | 6 | 3 | 5 | 9 | 4 | 8 | 2 | 7 | 1 | 8 | 4 | 9 | 7 | 2 | 1 | 5 | 6 | 3 | 7 | 8 | 3 | 1 | 9 | 4 | 6 | 5 | 2 | 1 | 2 | 6 | 8 | 5 | 7 | 4 | 3 | 9 | 9 | 5 | 4 | 2 | 3 | 6 | 1 | 8 | 7 | 3 | 6 | 1 | 5 | 7 | 2 | 9 | 4 | 8 | 4 | 9 | 8 | 6 | 1 | 3 | 7 | 2 | 5 | 5 | 7 | 2 | 4 | 8 | 9 | 3 | 1 | 6 | +------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+ 1 row in set (0.22 sec)
Nous sommes passés de 6.17 secondes à 0.22 seconde! Un gain imposant!
3. Les vues combinées
Alors pourquoi ne tenterions-nous pas de résoudre nos sudokus en effectuant la recherche par ligne ET par colonne à la fois?
Nous allons donc construire une seconde vue (sudoku_columns_view) qui sera basée sur les colonnes de la grille. Pour faire résoudre les sudokus, il nous faudra une troisième vue (sudoku_combined_view) qui combinera la vue par lignes (sudoku_rows_view) avec la vue par colonne (sudoku_columns_view). De cette façon, nous serons à l’abri des grilles avec des lignes vides et des grilles avec des colonnes vides. L’optimisateur de requête aura dorénavant tout le luxe de choisir la « méthode » la plus efficace!
Construisons la vue par colonnes en exécutant cette requête :
USE sudoku; CREATE VIEW sudoku_columns_view AS SELECT col1.c1 as r1c1, col1.c2 as r2c1, col1.c3 as r3c1, col1.c4 as r4c1, col1.c5 as r5c1,col1.c6 as r6c1, col1.c7 as r7c1, col1.c8 as r8c1, col1.c9 as r9c1, col2.c1 as r1c2, col2.c2 as r2c2, col2.c3 as r3c2, col2.c4 as r4c2, col2.c5 as r5c2, col2.c6 as r6c2,col2.c7 as r7c2, col2.c8 as r8c2, col2.c9 as r9c2, col3.c1 as r1c3, col3.c2 as r2c3, col3.c3 as r3c3, col3.c4 as r4c3, col3.c5 as r5c3, col3.c6 as r6c3, col3.c7 as r7c3,col3.c8 as r8c3, col3.c9 as r9c3, col4.c1 as r1c4, col4.c2 as r2c4, col4.c3 as r3c4, col4.c4 as r4c4, col4.c5 as r5c4, col4.c6 as r6c4, col4.c7 as r7c4, col4.c8 as r8c4,col4.c9 as r9c4, col5.c1 as r1c5, col5.c2 as r2c5, col5.c3 as r3c5, col5.c4 as r4c5, col5.c5 as r5c5, col5.c6 as r6c5, col5.c7 as r7c5, col5.c8 as r8c5, col5.c9 as r9c5,col6.c1 as r1c6, col6.c2 as r2c6, col6.c3 as r3c6, col6.c4 as r4c6, col6.c5 as r5c6, col6.c6 as r6c6, col6.c7 as r7c6, col6.c8 as r8c6, col6.c9 as r9c6, col7.c1 as r1c7,col7.c2 as r2c7, col7.c3 as r3c7, col7.c4 as r4c7, col7.c5 as r5c7, col7.c6 as r6c7, col7.c7 as r7c7, col7.c8 as r8c7, col7.c9 as r9c7, col8.c1 as r1c8, col8.c2 as r2c8,col8.c3 as r3c8, col8.c4 as r4c8, col8.c5 as r5c8, col8.c6 as r6c8, col8.c7 as r7c8, col8.c8 as r8c8, col8.c9 as r9c8, col9.c1 as r1c9, col9.c2 as r2c9, col9.c3 as r3c9,col9.c4 as r4c9, col9.c5 as r5c9, col9.c6 as r6c9, col9.c7 as r7c9, col9.c8 as r8c9, col9.c9 as r9c9 FROM positions col1, positions col2, positions col3,positions col4, positions col5, positions col6,positions col7, positions col8, positions col9 WHERE -- Nombres uniques dans chaque rangee (col1.c1 <> col2.c1) AND (col1.c1 <> col3.c1) AND (col1.c1 <> col4.c1) AND (col1.c1 <> col5.c1) AND (col1.c1 <> col6.c1) AND (col1.c1 <> col7.c1) AND (col1.c1 <> col8.c1) AND (col1.c1 <> col9.c1) AND (col1.c2 <> col2.c2) AND (col1.c2 <> col3.c2) AND (col1.c2 <> col4.c2) AND (col1.c2 <> col5.c2) AND (col1.c2 <> col6.c2) AND (col1.c2 <> col7.c2) AND (col1.c2 <> col8.c2) AND (col1.c2 <> col9.c2) AND (col1.c3 <> col2.c3) AND (col1.c3 <> col3.c3) AND (col1.c3 <> col4.c3) AND (col1.c3 <> col5.c3) AND (col1.c3 <> col6.c3) AND (col1.c3 <> col7.c3) AND (col1.c3 <> col8.c3) AND (col1.c3 <> col9.c3) AND (col1.c4 <> col2.c4) AND (col1.c4 <> col3.c4) AND (col1.c4 <> col4.c4) AND (col1.c4 <> col5.c4) AND (col1.c4 <> col6.c4) AND (col1.c4 <> col7.c4) AND (col1.c4 <> col8.c4) AND (col1.c4 <> col9.c4) AND (col1.c5 <> col2.c5) AND (col1.c5 <> col3.c5) AND (col1.c5 <> col4.c5) AND (col1.c5 <> col5.c5) AND (col1.c5 <> col6.c5) AND (col1.c5 <> col7.c5) AND (col1.c5 <> col8.c5) AND (col1.c5 <> col9.c5) AND (col1.c6 <> col2.c6) AND (col1.c6 <> col3.c6) AND (col1.c6 <> col4.c6) AND (col1.c6 <> col5.c6) AND (col1.c6 <> col6.c6) AND (col1.c6 <> col7.c6) AND (col1.c6 <> col8.c6) AND (col1.c6 <> col9.c6) AND (col1.c7 <> col2.c7) AND (col1.c7 <> col3.c7) AND (col1.c7 <> col4.c7) AND (col1.c7 <> col5.c7) AND (col1.c7 <> col6.c7) AND (col1.c7 <> col7.c7) AND (col1.c7 <> col8.c7) AND (col1.c7 <> col9.c7) AND (col1.c8 <> col2.c8) AND (col1.c8 <> col3.c8) AND (col1.c8 <> col4.c8) AND (col1.c8 <> col5.c8) AND (col1.c8 <> col6.c8) AND (col1.c8 <> col7.c8) AND (col1.c8 <> col8.c8) AND (col1.c8 <> col9.c8) AND (col1.c9 <> col2.c9) AND (col1.c9 <> col3.c9) AND (col1.c9 <> col4.c9) AND (col1.c9 <> col5.c9) AND (col1.c9 <> col6.c9) AND (col1.c9 <> col7.c9) AND (col1.c9 <> col8.c9) AND (col1.c9 <> col9.c9) AND (col2.c1 <> col3.c1) AND (col2.c1 <> col4.c1) AND (col2.c1 <> col5.c1) AND (col2.c1 <> col6.c1) AND (col2.c1 <> col7.c1) AND (col2.c1 <> col8.c1) AND (col2.c1 <> col9.c1) AND (col2.c2 <> col3.c2) AND (col2.c2 <> col4.c2) AND (col2.c2 <> col5.c2) AND (col2.c2 <> col6.c2) AND (col2.c2 <> col7.c2) AND (col2.c2 <> col8.c2) AND (col2.c2 <> col9.c2) AND (col2.c3 <> col3.c3) AND (col2.c3 <> col4.c3) AND (col2.c3 <> col5.c3) AND (col2.c3 <> col6.c3) AND (col2.c3 <> col7.c3) AND (col2.c3 <> col8.c3) AND (col2.c3 <> col9.c3) AND (col2.c4 <> col3.c4) AND (col2.c4 <> col4.c4) AND (col2.c4 <> col5.c4) AND (col2.c4 <> col6.c4) AND (col2.c4 <> col7.c4) AND (col2.c4 <> col8.c4) AND (col2.c4 <> col9.c4) AND (col2.c5 <> col3.c5) AND (col2.c5 <> col4.c5) AND (col2.c5 <> col5.c5) AND (col2.c5 <> col6.c5) AND (col2.c5 <> col7.c5) AND (col2.c5 <> col8.c5) AND (col2.c5 <> col9.c5) AND (col2.c6 <> col3.c6) AND (col2.c6 <> col4.c6) AND (col2.c6 <> col5.c6) AND (col2.c6 <> col6.c6) AND (col2.c6 <> col7.c6) AND (col2.c6 <> col8.c6) AND (col2.c6 <> col9.c6) AND (col2.c7 <> col3.c7) AND (col2.c7 <> col4.c7) AND (col2.c7 <> col5.c7) AND (col2.c7 <> col6.c7) AND (col2.c7 <> col7.c7) AND (col2.c7 <> col8.c7) AND (col2.c7 <> col9.c7) AND (col2.c8 <> col3.c8) AND (col2.c8 <> col4.c8) AND (col2.c8 <> col5.c8) AND (col2.c8 <> col6.c8) AND (col2.c8 <> col7.c8) AND (col2.c8 <> col8.c8) AND (col2.c8 <> col9.c8) AND (col2.c9 <> col3.c9) AND (col2.c9 <> col4.c9) AND (col2.c9 <> col5.c9) AND (col2.c9 <> col6.c9) AND (col2.c9 <> col7.c9) AND (col2.c9 <> col8.c9) AND (col2.c9 <> col9.c9) AND (col3.c1 <> col4.c1) AND (col3.c1 <> col5.c1) AND (col3.c1 <> col6.c1) AND (col3.c1 <> col7.c1) AND (col3.c1 <> col8.c1) AND (col3.c1 <> col9.c1) AND (col3.c2 <> col4.c2) AND (col3.c2 <> col5.c2) AND (col3.c2 <> col6.c2) AND (col3.c2 <> col7.c2) AND (col3.c2 <> col8.c2) AND (col3.c2 <> col9.c2) AND (col3.c3 <> col4.c3) AND (col3.c3 <> col5.c3) AND (col3.c3 <> col6.c3) AND (col3.c3 <> col7.c3) AND (col3.c3 <> col8.c3) AND (col3.c3 <> col9.c3) AND (col3.c4 <> col4.c4) AND (col3.c4 <> col5.c4) AND (col3.c4 <> col6.c4) AND (col3.c4 <> col7.c4) AND (col3.c4 <> col8.c4) AND (col3.c4 <> col9.c4) AND (col3.c5 <> col4.c5) AND (col3.c5 <> col5.c5) AND (col3.c5 <> col6.c5) AND (col3.c5 <> col7.c5) AND (col3.c5 <> col8.c5) AND (col3.c5 <> col9.c5) AND (col3.c6 <> col4.c6) AND (col3.c6 <> col5.c6) AND (col3.c6 <> col6.c6) AND (col3.c6 <> col7.c6) AND (col3.c6 <> col8.c6) AND (col3.c6 <> col9.c6) AND (col3.c7 <> col4.c7) AND (col3.c7 <> col5.c7) AND (col3.c7 <> col6.c7) AND (col3.c7 <> col7.c7) AND (col3.c7 <> col8.c7) AND (col3.c7 <> col9.c7) AND (col3.c8 <> col4.c8) AND (col3.c8 <> col5.c8) AND (col3.c8 <> col6.c8) AND (col3.c8 <> col7.c8) AND (col3.c8 <> col8.c8) AND (col3.c8 <> col9.c8) AND (col3.c9 <> col4.c9) AND (col3.c9 <> col5.c9) AND (col3.c9 <> col6.c9) AND (col3.c9 <> col7.c9) AND (col3.c9 <> col8.c9) AND (col3.c9 <> col9.c9) AND (col4.c1 <> col5.c1) AND (col4.c1 <> col6.c1) AND (col4.c1 <> col7.c1) AND (col4.c1 <> col8.c1) AND (col4.c1 <> col9.c1) AND (col4.c2 <> col5.c2) AND (col4.c2 <> col6.c2) AND (col4.c2 <> col7.c2) AND (col4.c2 <> col8.c2) AND (col4.c2 <> col9.c2) AND (col4.c3 <> col5.c3) AND (col4.c3 <> col6.c3) AND (col4.c3 <> col7.c3) AND (col4.c3 <> col8.c3) AND (col4.c3 <> col9.c3) AND (col4.c4 <> col5.c4) AND (col4.c4 <> col6.c4) AND (col4.c4 <> col7.c4) AND (col4.c4 <> col8.c4) AND (col4.c4 <> col9.c4) AND (col4.c5 <> col5.c5) AND (col4.c5 <> col6.c5) AND (col4.c5 <> col7.c5) AND (col4.c5 <> col8.c5) AND (col4.c5 <> col9.c5) AND (col4.c6 <> col5.c6) AND (col4.c6 <> col6.c6) AND (col4.c6 <> col7.c6) AND (col4.c6 <> col8.c6) AND (col4.c6 <> col9.c6) AND (col4.c7 <> col5.c7) AND (col4.c7 <> col6.c7) AND (col4.c7 <> col7.c7) AND (col4.c7 <> col8.c7) AND (col4.c7 <> col9.c7) AND (col4.c8 <> col5.c8) AND (col4.c8 <> col6.c8) AND (col4.c8 <> col7.c8) AND (col4.c8 <> col8.c8) AND (col4.c8 <> col9.c8) AND (col4.c9 <> col5.c9) AND (col4.c9 <> col6.c9) AND (col4.c9 <> col7.c9) AND (col4.c9 <> col8.c9) AND (col4.c9 <> col9.c9) AND (col5.c1 <> col6.c1) AND (col5.c1 <> col7.c1) AND (col5.c1 <> col8.c1) AND (col5.c1 <> col9.c1) AND (col5.c2 <> col6.c2) AND (col5.c2 <> col7.c2) AND (col5.c2 <> col8.c2) AND (col5.c2 <> col9.c2) AND (col5.c3 <> col6.c3) AND (col5.c3 <> col7.c3) AND (col5.c3 <> col8.c3) AND (col5.c3 <> col9.c3) AND (col5.c4 <> col6.c4) AND (col5.c4 <> col7.c4) AND (col5.c4 <> col8.c4) AND (col5.c4 <> col9.c4) AND (col5.c5 <> col6.c5) AND (col5.c5 <> col7.c5) AND (col5.c5 <> col8.c5) AND (col5.c5 <> col9.c5) AND (col5.c6 <> col6.c6) AND (col5.c6 <> col7.c6) AND (col5.c6 <> col8.c6) AND (col5.c6 <> col9.c6) AND (col5.c7 <> col6.c7) AND (col5.c7 <> col7.c7) AND (col5.c7 <> col8.c7) AND (col5.c7 <> col9.c7) AND (col5.c8 <> col6.c8) AND (col5.c8 <> col7.c8) AND (col5.c8 <> col8.c8) AND (col5.c8 <> col9.c8) AND (col5.c9 <> col6.c9) AND (col5.c9 <> col7.c9) AND (col5.c9 <> col8.c9) AND (col5.c9 <> col9.c9) AND (col6.c1 <> col7.c1) AND (col6.c1 <> col8.c1) AND (col6.c1 <> col9.c1) AND (col6.c2 <> col7.c2) AND (col6.c2 <> col8.c2) AND (col6.c2 <> col9.c2) AND (col6.c3 <> col7.c3) AND (col6.c3 <> col8.c3) AND (col6.c3 <> col9.c3) AND (col6.c4 <> col7.c4) AND (col6.c4 <> col8.c4) AND (col6.c4 <> col9.c4) AND (col6.c5 <> col7.c5) AND (col6.c5 <> col8.c5) AND (col6.c5 <> col9.c5) AND (col6.c6 <> col7.c6) AND (col6.c6 <> col8.c6) AND (col6.c6 <> col9.c6) AND (col6.c7 <> col7.c7) AND (col6.c7 <> col8.c7) AND (col6.c7 <> col9.c7) AND (col6.c8 <> col7.c8) AND (col6.c8 <> col8.c8) AND (col6.c8 <> col9.c8) AND (col6.c9 <> col7.c9) AND (col6.c9 <> col8.c9) AND (col6.c9 <> col9.c9) AND (col7.c1 <> col8.c1) AND (col7.c1 <> col9.c1) AND (col7.c2 <> col8.c2) AND (col7.c2 <> col9.c2) AND (col7.c3 <> col8.c3) AND (col7.c3 <> col9.c3) AND (col7.c4 <> col8.c4) AND (col7.c4 <> col9.c4) AND (col7.c5 <> col8.c5) AND (col7.c5 <> col9.c5) AND (col7.c6 <> col8.c6) AND (col7.c6 <> col9.c6) AND (col7.c7 <> col8.c7) AND (col7.c7 <> col9.c7) AND (col7.c8 <> col8.c8) AND (col7.c8 <> col9.c8) AND (col7.c9 <> col8.c9) AND (col7.c9 <> col9.c9) AND (col8.c1 <> col9.c1) AND (col8.c2 <> col9.c2) AND (col8.c3 <> col9.c3) AND (col8.c4 <> col9.c4) AND (col8.c5 <> col9.c5) AND (col8.c6 <> col9.c6) AND (col8.c7 <> col9.c7) AND (col8.c8 <> col9.c8) AND (col8.c9 <> col9.c9) AND -- Nombres uniques dans chaque maison de 3x3 (col1.c1 <> col1.c2) AND (col1.c1 <> col1.c3) AND (col1.c1 <> col2.c2) AND (col1.c1 <> col2.c3) AND (col1.c1 <> col3.c2) AND (col1.c1 <> col3.c3) AND (col1.c2 <> col1.c3) AND (col1.c2 <> col2.c1) AND (col1.c2 <> col2.c3) AND (col1.c2 <> col3.c1) AND (col1.c2 <> col3.c3) AND (col1.c3 <> col2.c1) AND (col1.c3 <> col2.c2) AND (col1.c3 <> col3.c1) AND (col1.c3 <> col3.c2) AND (col1.c4 <> col1.c5) AND (col1.c4 <> col1.c6) AND (col1.c4 <> col2.c5) AND (col1.c4 <> col2.c6) AND (col1.c4 <> col3.c5) AND (col1.c4 <> col3.c6) AND (col1.c5 <> col1.c6) AND (col1.c5 <> col2.c4) AND (col1.c5 <> col2.c6) AND (col1.c5 <> col3.c4) AND (col1.c5 <> col3.c6) AND (col1.c6 <> col2.c4) AND (col1.c6 <> col2.c5) AND (col1.c6 <> col3.c4) AND (col1.c6 <> col3.c5) AND (col1.c7 <> col1.c8) AND (col1.c7 <> col1.c9) AND (col1.c7 <> col2.c8) AND (col1.c7 <> col2.c9) AND (col1.c7 <> col3.c8) AND (col1.c7 <> col3.c9) AND (col1.c8 <> col1.c9) AND (col1.c8 <> col2.c7) AND (col1.c8 <> col2.c9) AND (col1.c8 <> col3.c7) AND (col1.c8 <> col3.c9) AND (col1.c9 <> col2.c7) AND (col1.c9 <> col2.c8) AND (col1.c9 <> col3.c7) AND (col1.c9 <> col3.c8) AND (col2.c1 <> col2.c2) AND (col2.c1 <> col2.c3) AND (col2.c1 <> col3.c2) AND (col2.c1 <> col3.c3) AND (col2.c2 <> col2.c3) AND (col2.c2 <> col3.c1) AND (col2.c2 <> col3.c3) AND (col2.c3 <> col3.c1) AND (col2.c3 <> col3.c2) AND (col2.c4 <> col2.c5) AND (col2.c4 <> col2.c6) AND (col2.c4 <> col3.c5) AND (col2.c4 <> col3.c6) AND (col2.c5 <> col2.c6) AND (col2.c5 <> col3.c4) AND (col2.c5 <> col3.c6) AND (col2.c6 <> col3.c4) AND (col2.c6 <> col3.c5) AND (col2.c7 <> col2.c8) AND (col2.c7 <> col2.c9) AND (col2.c7 <> col3.c8) AND (col2.c7 <> col3.c9) AND (col2.c8 <> col2.c9) AND (col2.c8 <> col3.c7) AND (col2.c8 <> col3.c9) AND (col2.c9 <> col3.c7) AND (col2.c9 <> col3.c8) AND (col3.c1 <> col3.c2) AND (col3.c1 <> col3.c3) AND (col3.c2 <> col3.c3) AND (col3.c4 <> col3.c5) AND (col3.c4 <> col3.c6) AND (col3.c5 <> col3.c6) AND (col3.c7 <> col3.c8) AND (col3.c7 <> col3.c9) AND (col3.c8 <> col3.c9) AND (col4.c1 <> col4.c2) AND (col4.c1 <> col4.c3) AND (col4.c1 <> col5.c2) AND (col4.c1 <> col5.c3) AND (col4.c1 <> col6.c2) AND (col4.c1 <> col6.c3) AND (col4.c2 <> col4.c3) AND (col4.c2 <> col5.c1) AND (col4.c2 <> col5.c3) AND (col4.c2 <> col6.c1) AND (col4.c2 <> col6.c3) AND (col4.c3 <> col5.c1) AND (col4.c3 <> col5.c2) AND (col4.c3 <> col6.c1) AND (col4.c3 <> col6.c2) AND (col4.c4 <> col4.c5) AND (col4.c4 <> col4.c6) AND (col4.c4 <> col5.c5) AND (col4.c4 <> col5.c6) AND (col4.c4 <> col6.c5) AND (col4.c4 <> col6.c6) AND (col4.c5 <> col4.c6) AND (col4.c5 <> col5.c4) AND (col4.c5 <> col5.c6) AND (col4.c5 <> col6.c4) AND (col4.c5 <> col6.c6) AND (col4.c6 <> col5.c4) AND (col4.c6 <> col5.c5) AND (col4.c6 <> col6.c4) AND (col4.c6 <> col6.c5) AND (col4.c7 <> col4.c8) AND (col4.c7 <> col4.c9) AND (col4.c7 <> col5.c8) AND (col4.c7 <> col5.c9) AND (col4.c7 <> col6.c8) AND (col4.c7 <> col6.c9) AND (col4.c8 <> col4.c9) AND (col4.c8 <> col5.c7) AND (col4.c8 <> col5.c9) AND (col4.c8 <> col6.c7) AND (col4.c8 <> col6.c9) AND (col4.c9 <> col5.c7) AND (col4.c9 <> col5.c8) AND (col4.c9 <> col6.c7) AND (col4.c9 <> col6.c8) AND (col5.c1 <> col5.c2) AND (col5.c1 <> col5.c3) AND (col5.c1 <> col6.c2) AND (col5.c1 <> col6.c3) AND (col5.c2 <> col5.c3) AND (col5.c2 <> col6.c1) AND (col5.c2 <> col6.c3) AND (col5.c3 <> col6.c1) AND (col5.c3 <> col6.c2) AND (col5.c4 <> col5.c5) AND (col5.c4 <> col5.c6) AND (col5.c4 <> col6.c5) AND (col5.c4 <> col6.c6) AND (col5.c5 <> col5.c6) AND (col5.c5 <> col6.c4) AND (col5.c5 <> col6.c6) AND (col5.c6 <> col6.c4) AND (col5.c6 <> col6.c5) AND (col5.c7 <> col5.c8) AND (col5.c7 <> col5.c9) AND (col5.c7 <> col6.c8) AND (col5.c7 <> col6.c9) AND (col5.c8 <> col5.c9) AND (col5.c8 <> col6.c7) AND (col5.c8 <> col6.c9) AND (col5.c9 <> col6.c7) AND (col5.c9 <> col6.c8) AND (col6.c1 <> col6.c2) AND (col6.c1 <> col6.c3) AND (col6.c2 <> col6.c3) AND (col6.c4 <> col6.c5) AND (col6.c4 <> col6.c6) AND (col6.c5 <> col6.c6) AND (col6.c7 <> col6.c8) AND (col6.c7 <> col6.c9) AND (col6.c8 <> col6.c9) AND (col7.c1 <> col7.c2) AND (col7.c1 <> col7.c3) AND (col7.c1 <> col8.c2) AND (col7.c1 <> col8.c3) AND (col7.c1 <> col9.c2) AND (col7.c1 <> col9.c3) AND (col7.c2 <> col7.c3) AND (col7.c2 <> col8.c1) AND (col7.c2 <> col8.c3) AND (col7.c2 <> col9.c1) AND (col7.c2 <> col9.c3) AND (col7.c3 <> col8.c1) AND (col7.c3 <> col8.c2) AND (col7.c3 <> col9.c1) AND (col7.c3 <> col9.c2) AND (col7.c4 <> col7.c5) AND (col7.c4 <> col7.c6) AND (col7.c4 <> col8.c5) AND (col7.c4 <> col8.c6) AND (col7.c4 <> col9.c5) AND (col7.c4 <> col9.c6) AND (col7.c5 <> col7.c6) AND (col7.c5 <> col8.c4) AND (col7.c5 <> col8.c6) AND (col7.c5 <> col9.c4) AND (col7.c5 <> col9.c6) AND (col7.c6 <> col8.c4) AND (col7.c6 <> col8.c5) AND (col7.c6 <> col9.c4) AND (col7.c6 <> col9.c5) AND (col7.c7 <> col7.c8) AND (col7.c7 <> col7.c9) AND (col7.c7 <> col8.c8) AND (col7.c7 <> col8.c9) AND (col7.c7 <> col9.c8) AND (col7.c7 <> col9.c9) AND (col7.c8 <> col7.c9) AND (col7.c8 <> col8.c7) AND (col7.c8 <> col8.c9) AND (col7.c8 <> col9.c7) AND (col7.c8 <> col9.c9) AND (col7.c9 <> col8.c7) AND (col7.c9 <> col8.c8) AND (col7.c9 <> col9.c7) AND (col7.c9 <> col9.c8) AND (col8.c1 <> col8.c2) AND (col8.c1 <> col8.c3) AND (col8.c1 <> col9.c2) AND (col8.c1 <> col9.c3) AND (col8.c2 <> col8.c3) AND (col8.c2 <> col9.c1) AND (col8.c2 <> col9.c3) AND (col8.c3 <> col9.c1) AND (col8.c3 <> col9.c2) AND (col8.c4 <> col8.c5) AND (col8.c4 <> col8.c6) AND (col8.c4 <> col9.c5) AND (col8.c4 <> col9.c6) AND (col8.c5 <> col8.c6) AND (col8.c5 <> col9.c4) AND (col8.c5 <> col9.c6) AND (col8.c6 <> col9.c4) AND (col8.c6 <> col9.c5) AND (col8.c7 <> col8.c8) AND (col8.c7 <> col8.c9) AND (col8.c7 <> col9.c8) AND (col8.c7 <> col9.c9) AND (col8.c8 <> col8.c9) AND (col8.c8 <> col9.c7) AND (col8.c8 <> col9.c9) AND (col8.c9 <> col9.c7) AND (col8.c9 <> col9.c8) AND (col9.c1 <> col9.c2) AND (col9.c1 <> col9.c3) AND (col9.c2 <> col9.c3) AND (col9.c4 <> col9.c5) AND (col9.c4 <> col9.c6) AND (col9.c5 <> col9.c6) AND (col9.c7 <> col9.c8) AND (col9.c7 <> col9.c9) AND (col9.c8 <> col9.c9);
Maintenant, chers lecteurs, patientez un peu!
ATTENTION : n’allez surtout pas essayer de solutionner une grille avant de passer au prochain point!
Pour l’instant, contentez-vous seulement de créer la nouvelle vue sudoku_combined_view :
USE sudoku; CREATE VIEW sudoku_combined_view AS SELECT t1.* FROM sudoku_rows_view t1 INNER JOIN sudoku_columns_view t2 ON -- Jointures entre représentation-colonnes et représentation-rangées (t1.r1c1 = t2.r1c1) AND (t1.r1c2 = t2.r1c2) AND (t1.r1c3 = t2.r1c3) AND (t1.r1c4 = t2.r1c4) AND (t1.r1c5 = t2.r1c5) AND (t1.r1c6 = t2.r1c6) AND (t1.r1c7 = t2.r1c7) AND (t1.r1c8 = t2.r1c8) AND (t1.r1c9 = t2.r1c9) AND (t1.r2c1 = t2.r2c1) AND (t1.r2c2 = t2.r2c2) AND (t1.r2c3 = t2.r2c3) AND (t1.r2c4 = t2.r2c4) AND (t1.r2c5 = t2.r2c5) AND (t1.r2c6 = t2.r2c6) AND (t1.r2c7 = t2.r2c7) AND (t1.r2c8 = t2.r2c8) AND (t1.r2c9 = t2.r2c9) AND (t1.r3c1 = t2.r3c1) AND (t1.r3c2 = t2.r3c2) AND (t1.r3c3 = t2.r3c3) AND (t1.r3c4 = t2.r3c4) AND (t1.r3c5 = t2.r3c5) AND (t1.r3c6 = t2.r3c6) AND (t1.r3c7 = t2.r3c7) AND (t1.r3c8 = t2.r3c8) AND (t1.r3c9 = t2.r3c9) AND (t1.r4c1 = t2.r4c1) AND (t1.r4c2 = t2.r4c2) AND (t1.r4c3 = t2.r4c3) AND (t1.r4c4 = t2.r4c4) AND (t1.r4c5 = t2.r4c5) AND (t1.r4c6 = t2.r4c6) AND (t1.r4c7 = t2.r4c7) AND (t1.r4c8 = t2.r4c8) AND (t1.r4c9 = t2.r4c9) AND (t1.r5c1 = t2.r5c1) AND (t1.r5c2 = t2.r5c2) AND (t1.r5c3 = t2.r5c3) AND (t1.r5c4 = t2.r5c4) AND (t1.r5c5 = t2.r5c5) AND (t1.r5c6 = t2.r5c6) AND (t1.r5c7 = t2.r5c7) AND (t1.r5c8 = t2.r5c8) AND (t1.r5c9 = t2.r5c9) AND (t1.r6c1 = t2.r6c1) AND (t1.r6c2 = t2.r6c2) AND (t1.r6c3 = t2.r6c3) AND (t1.r6c4 = t2.r6c4) AND (t1.r6c5 = t2.r6c5) AND (t1.r6c6 = t2.r6c6) AND (t1.r6c7 = t2.r6c7) AND (t1.r6c8 = t2.r6c8) AND (t1.r6c9 = t2.r6c9) AND (t1.r7c1 = t2.r7c1) AND (t1.r7c2 = t2.r7c2) AND (t1.r7c3 = t2.r7c3) AND (t1.r7c4 = t2.r7c4) AND (t1.r7c5 = t2.r7c5) AND (t1.r7c6 = t2.r7c6) AND (t1.r7c7 = t2.r7c7) AND (t1.r7c8 = t2.r7c8) AND (t1.r7c9 = t2.r7c9) AND (t1.r8c1 = t2.r8c1) AND (t1.r8c2 = t2.r8c2) AND (t1.r8c3 = t2.r8c3) AND (t1.r8c4 = t2.r8c4) AND (t1.r8c5 = t2.r8c5) AND (t1.r8c6 = t2.r8c6) AND (t1.r8c7 = t2.r8c7) AND (t1.r8c8 = t2.r8c8) AND (t1.r8c9 = t2.r8c9) AND (t1.r9c1 = t2.r9c1) AND (t1.r9c2 = t2.r9c2) AND (t1.r9c3 = t2.r9c3) AND (t1.r9c4 = t2.r9c4) AND (t1.r9c5 = t2.r9c5) AND (t1.r9c6 = t2.r9c6) AND (t1.r9c7 = t2.r9c7) AND (t1.r9c8 = t2.r9c8) AND (t1.r9c9 = t2.r9c9);
4. L’optimisateur de plan
Avant de tenter d’exécuter la requête de la grille présentée au point 1 de cet article, quelques explications sont de mise. Il appert que notre nouvelle façon de faire change radicalement les choses!
En effet, au lieu de joindre seulement 9 tables comme c’était le cas avec sudoku_rows_view, notre nouvelle solution joint 9 tables une première fois (sudoku_rows_view), encore une fois 9 tables (sudoku_columns_view) puis regroupe les résultats de ces 2 vues dans sudoku_combined_view.
Il faut savoir que l’optimisateur de requêtes de MySQL, par défaut, énumère toutes les jointures possibles pour déterminer le plan d’accès qu’il considère comme optimal. Dans le cas qui nous intéresse, l’optimisateur détecte donc 18 tables indirectement référencées par la vue sudoku_combined_view et aura donc à examiner 18! (18 factorielle) jointures possibles, c’est-à-dire un total de 6402373705728000 jointures! Si par malheur vous tentiez d’exécuter la requête sur la vue sudoku_combined_view, il vous faudrait attendre une éternité avant que MySQL détermine un plan d’accès! Pour vérifier, j’ai laissé rouler une requête de EXPLAIN sur la vue sudoku_combined_view pendant 2 jours avant de l’arrêter…
Heureusement, il existe une variable système nous permettant de contrôler le zèle de l’optimisateur de requêtes! Cette variable s’appelle optimizer_search_depth. Elle détermine la profondeur d’analyse de l’optimisateur quand celui-ci essaie de trouver le plan d’accès optimal. À quoi bon un plan d’accès optimal si celui-ci prend 3 semaines à être généré ? En ce qui nous concerne, un plan d’accès non optimal mais généré rapidement, compte tenu des 18 tables, est ce que nous recherchons!
La variable optimizer_search_depth peut être changée dans les fichiers de configuration de MySQL ou dynamiquement au beau milieu d’une session. Prenez garde toutefois! Si vous changez cette variable dans une session et non au niveau des fichiers de configuration, la nouvelle valeur de la variable ne s’applique qu’à la session!
Réglons cette valeur à 3 pour l’instant (plus de détails quant à cette valeur dans le prochain article) :
set @@optimizer_search_depth = 3;
Maintenant, nous pouvons exécuter notre requête :
SELECT * FROM sudoku_combined_view WHERE (r2c1 = 1) AND (r2c2 = 3) AND (r2c3 = 4) AND (r2c4 = 8) AND (r2c5 = 2) AND (r2c6 = 5) AND (r2c7 = 6) AND (r2c8 = 9) AND (r2c9 = 7) AND (r3c1 = 7) AND (r3c2 = 5) AND (r3c3 = 9) AND (r3c4 = 3) AND (r3c5 = 6) AND (r3c6 = 4) AND (r3c7 = 1) AND (r3c8 = 8) AND (r3c9 = 2) AND (r4c1 = 3) AND (r4c2 = 9) AND (r4c3 = 7) AND (r4c4 = 1) AND (r4c5 = 8) AND (r4c6 = 2) AND (r4c7 = 5) AND (r4c8 = 6) AND (r4c9 = 4) AND (r6c1 = 5) AND (r6c2 = 8) AND (r6c3 = 1) AND (r6c4 = 4) AND (r6c5 = 7) AND (r6c6 = 6) AND (r6c7 = 2) AND (r6c8 = 3) AND (r6c9 = 9) AND (r7c1 = 8) AND (r7c2 = 2) AND (r7c3 = 5) AND (r7c4 = 6) AND (r7c5 = 4) AND (r7c6 = 1) AND (r7c7 = 9) AND (r7c8 = 7) AND (r7c9 = 3) AND (r8c1 = 9) AND (r8c2 = 7) AND (r8c3 = 6) AND (r8c4 = 5) AND (r8c5 = 3) AND (r8c6 = 8) AND (r8c7 = 4) AND (r8c8 = 2) AND (r8c9 = 1);
Fantastique! 1.08 seconde! Est-il possible d’extraire plus de jus de ce citron?
Voyons ce que le EXPLAIN a à nous dire :
+----+-------------+-------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+---------+-------------------------------+------+--------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+---------+-------------------------------+------+--------------------------------------------------------------+ | 1 | SIMPLE | col3 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i23,i38,i48 | 2,2,2 | NULL | 64 | Using intersect(i23,i38,i48); Using where | | 1 | SIMPLE | col9 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i78,i68,i48 | 2,2,2 | NULL | 65 | Using intersect(i78,i68,i48); Using where; Using join buffer | | 1 | SIMPLE | r2 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i37,i29 | 2,2 | NULL | 68 | Using intersect(i37,i29); Using where; Using join buffer | | 1 | SIMPLE | r3 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i37,i28 | 2,2 | NULL | 68 | Using intersect(i37,i28); Using where; Using join buffer | | 1 | SIMPLE | r4 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i56,i29 | 2,2 | NULL | 68 | Using intersect(i56,i29); Using where; Using join buffer | | 1 | SIMPLE | r6 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i12,i35 | 2,2 | NULL | 68 | Using intersect(i12,i35); Using where; Using join buffer | | 1 | SIMPLE | r7 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i12,i68 | 2,2 | NULL | 68 | Using intersect(i12,i68); Using where; Using join buffer | | 1 | SIMPLE | col1 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i23,i67 | 2,2 | NULL | 68 | Using intersect(i23,i67); Using where; Using join buffer | | 1 | SIMPLE | col2 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i23,i68 | 2,2 | NULL | 68 | Using intersect(i23,i68); Using where; Using join buffer | | 1 | SIMPLE | col4 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i78,i46 | 2,2 | NULL | 68 | Using intersect(i78,i46); Using where; Using join buffer | | 1 | SIMPLE | col5 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i23,i68 | 2,2 | NULL | 68 | Using intersect(i23,i68); Using where; Using join buffer | | 1 | SIMPLE | col6 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i23,i67 | 2,2 | NULL | 68 | Using intersect(i23,i67); Using where; Using join buffer | | 1 | SIMPLE | r8 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i37,i79,i38 | 2,2,2 | NULL | 78 | Using intersect(i37,i79,i38); Using where; Using join buffer | | 1 | SIMPLE | col8 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i37,i24 | 2,2 | NULL | 68 | Using intersect(i37,i24); Using where; Using join buffer | | 1 | SIMPLE | col7 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i23,i68 | 2,2 | NULL | 68 | Using intersect(i23,i68); Using where; Using join buffer | | 1 | SIMPLE | r1 | ref | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i12 | 2 | sudoku.col1.c1,sudoku.col2.c1 | 5040 | Using where | | 1 | SIMPLE | r5 | ref | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i12 | 2 | sudoku.col1.c5,sudoku.col2.c5 | 5040 | Using where | | 1 | SIMPLE | r9 | ref | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i12 | 2 | sudoku.col1.c9,sudoku.col2.c9 | 5040 | Using where | +----+-------------+-------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+---------+-------------------------------+------+--------------------------------------------------------------+ 18 rows in set (0.28 sec)
5. Optimisation de la table
Il existe quelques trucs d’optimisation de table que nous avons omis de faire. C’est peut-être le temps d’y songer pour extraire chaque milliseconde et chaque octet possible!
Bien que la table positions ne subisse jamais de UPDATE, de INSERT ou de DELETE, sommes-nous certains que les pages d’index sont triées ? Pour ce faire, nous pourrions exécuter un OPTIMIZE TABLE sur la table positions. Ce serait inutile dans notre cas. Pourquoi? Parce que les permutations générées pour créer les requêtes d’insertion dans la table positions ont été générées dans l’ordre des index.
D’autre part, comme la table n’a connu aucune modification, un ANALYZE TABLE serait, lui aussi, totalement inutile!
Changer la définition de la table avec un ALTER TABLE pour y inclure AVG_ROW_LENGTH et MAX_ROWS ? Pas plus utile. Comme le format des lignes dans la table positions est Fixed et que la table est de format MyISAM, ces deux valeurs n’ont pas à être forcées puisque MySQL les détermine sans notre aide!
6. Impossible n’est pas français!
Est-ce possible de faire mieux? En éternel optimiste et têtu que je suis, je répondrai « oui » ! Mais comment?
Seulement, avons-nous tout prévu? Que faire avec une grille contenant des lignes vides et des colonnes vides comme la grille suivante :
Suite au prochain article!
Leave a Comment » | algorithmes, bases de données, MyISAM, MySQL, optimisation, Pharo, sudoku | Tagué: algorithmes, bases de données, MyISAM, MySQL, optimisation, optimizer_search_depth, Pharo, Sudoku | Permalien
Publié par endormitoire
Comment presser un citron (première partie)
7 janvier 2012Ainsi donc, cette première partie détaillera la méthode de base utilisée pour résoudre un sudoku en une seule requête SQL. Et en passant, pour ceux que la petite histoire intéresse, vous trouverez ici un excellent article sur l’évolution du sudoku.
Pour nous faciliter la tâche, nous utiliserons des tables de type MyISAM pour commencer. Dans le prochain article, nous ferons aussi en sorte que notre méthode ne retourne qu’une seule grille valide même dans le cas où plusieurs solutions existeraient. Rappelons que notre objectif ultime est d’optimiser une base de données dans le but bien précis de solutionner une grille valide dans des délais raisonnables. Mais pour cet article, nous nous contenterons de verser dans la facilité!
Si l’envie vous prenait de vous salir les mains, vous aurez besoin d’un langage de programmation (idéalement Smalltalk comme j’utilise Pharo mais Squeak ou n’importe quel autre environnement Smalltalk fera l’affaire!) pour générer vous-même certaines données. Si ce qui vous intéresse c’est seulement le résultat, n’importe quel client MySQL pourrait faire l’affaire comme je vous fournirai tous les scripts SQL nécessaires.
Ainsi, pour la suite de cet article, vous aurez besoin d’un client MySQL.
Bien que ce qui suit puisse se faire à l’aide de vos outils favoris, je vous suggère l’invite de commande et le client MySQL par souci de simplicité et de rapidité!
1. Création de la base de données
Il nous faut tout d’abord créer la base de données en exécutant:
CREATE DATABASE sudoku;
2. Création de la table de positions
La solution (et la représentation des données que j’ai envisagée) pour résoudre ce problème est triviale : il s’agit de créer toutes les permutations possibles pour une ligne et de toutes les insérer dans une table que nous joindrons ensuite 9 fois sur elle-même de façon à créer le puzzle complet. Évidemment, nous devrons aussi ajouter toutes les règles nécessaires (dans une vue) pour que la grille soit valide ainsi que les valeurs de départ connues du sudoku.
Ainsi donc, comme la grille standard a 9 colonnes, il a fallu créer 9! (9 factorielle = 362880 = 9*8*7*6*5*4*3*2*1) lignes que nous avons insérées dans cette table. Chaque enregistrement de la table positions représente une ligne de sudoku, constituée de ses 9 colonnes (de là les identifiants c1, c2, c3, c4, etc).
USE sudoku; CREATE TABLE positions ( c1 TINYINT UNSIGNED NOT NULL, c2 TINYINT UNSIGNED NOT NULL, c3 TINYINT UNSIGNED NOT NULL, c4 TINYINT UNSIGNED NOT NULL, c5 TINYINT UNSIGNED NOT NULL, c6 TINYINT UNSIGNED NOT NULL, c7 TINYINT UNSIGNED NOT NULL, c8 TINYINT UNSIGNED NOT NULL, c9 TINYINT UNSIGNED NOT NULL ) ENGINE=MYISAM;
Vous remarquerez l’absence d’index. Pour accélérer les choses, nous définirons les index seulement une fois la table remplie! Les index seront essentiels dans notre cas, en particulier pour ce qui nous attend dans le prochain article!
3. Générer les données
J’ai créé les requêtes pour les INSERT dans la table positions à l’aide d’un petit script exécuté dans un workspace Pharo Smalltalk.
Voici le script utilisé :
| file |
file := StandardFileStream forceNewFileNamed: 'positions.sql'.
(1 to: 9) permutationsDo: [:e | file
nextPutAll: 'INSERT INTO positions(c1,c2,c3,c4,c5,c6,c7,c8,c9) VALUES(';
nextPutAll: (e at: 1) printString;nextPut: $, ;
nextPutAll: (e at: 2) printString;nextPut: $, ;
nextPutAll: (e at: 3) printString;nextPut: $, ;
nextPutAll: (e at: 4) printString;nextPut: $, ;
nextPutAll: (e at: 5) printString;nextPut: $, ;
nextPutAll: (e at: 6) printString;nextPut: $, ;
nextPutAll: (e at: 7) printString;nextPut: $, ;
nextPutAll: (e at: 8) printString;nextPut: $, ;
nextPutAll: (e at: 9) printString;nextPutAll: ');';cr].
file close.
Pour les plus aventuriers d’entre vous, je suggère d’étudier le contenu du fichier positions.sql afin de pouvoir générer l’équivalent à l’aide de votre langage de programmation favori! Pour les autres, j’ai déjà fait le travail et vous trouverez le fichier ici.
L’exécution de ce script dans un workspace créera un fichier nommé positions.sql contenant toutes les requêtes de INSERT servant à remplir la table positions.
ATTENTION: la prochaine étape prend quelques minutes!
Pour insérer les données dans la table positions, ouvrez un invite de commande et exécutez ceci :
4. Générer les contraintes
Les contraintes de la requête servant à solutionner la grille sudoku ont aussi été générées à l’aide d’un script exécuté dans un workspace avec Pharo. Ce script élimine toute les clauses superflues ou dupliquées de façon à garder leur nombre au strict minimum en prenant bien soin de s’assurer d’avoir une grille de sudoku valide. Plus précisément, ce script veille à :
1) éliminer les clauses dupliquées (par exemple, nul besoin de spécifier que (r1.c3 <> r2.c3) et que (r2.c3 <> r1.c3), c’est la même chose!;
2) éliminer tous les clauses reliées aux lignes comme nous savons que toutes les lignes de la table position sont valides comme nous les avons toutes générées.
| s1 s2 s3 |
s1 := Set new.
1 to: 9 do: [:r |
1 to: 9 do: [:c | | cell |
cell := 'r', r printString, '.c', c printString.
1 to: 9 do: [:r2 | | compCell |
compCell := 'r', r2 printString, '.c', c printString.
compCell ~= cell
ifTrue: [ cell < compCell
ifTrue: [s1 add: (cell, ' <> ', compCell)]
ifFalse: [s1 add: (compCell, ' <> ', cell)].
]
]
]
].
Transcript cr;show: '-- Unique in column';cr.
s1 asSortedCollection do: [:e | Transcript show: '(', e, ') AND ';cr].
s2 := Set new.
1 to: 9 do: [:r |
1 to: 9 do: [:c | | cell deltaR deltaC |
deltaR := ((r-1) // 3) * 3.
deltaC := ((c-1) // 3) * 3.
1 to: 3 do: [:r2 |
1 to: 3 do: [:c2 | | compCell |
cell := 'r', r printString, '.c', c printString.
compCell := 'r', (r2 + deltaR) printString, '.c', (c2 + deltaC) printString.
(compCell ~= cell and: [(r2 + deltaR) ~= r])
ifTrue:[ cell < compCell
ifTrue: [s2 add: (cell, ' <> ', compCell)]
ifFalse: [s2 add: (compCell, ' <> ', cell)].
]
]
]
]
].
Transcript cr;show: '-- Unique within 3x3 square';cr.
s3 := Set new.
s2 do: [:each | (s1 includes: each) ifFalse: [s3 add: each]].
s3 asSortedCollection do: [:e | Transcript show: '(', e, ') AND ';cr].
Nous utiliserons le résultat affiché dans le Transcript pour créer notre vue!
Note: il vous faudra préalablement changer une méthode dans Pharo pour être en mesure d’afficher la totalité des contraintes.
5. Créer les index
Pour accélérer la recherche, il nous faut des index! Il est temps de les générer!
ATTENTION: la prochaine étape prend plusieurs minutes!
USE sudoku; CREATE INDEX i12 ON positions(c1, c2); CREATE INDEX i13 ON positions(c1, c3); CREATE INDEX i14 ON positions(c1, c4); CREATE INDEX i15 ON positions(c1, c5); CREATE INDEX i16 ON positions(c1, c6); CREATE INDEX i17 ON positions(c1, c7); CREATE INDEX i18 ON positions(c1, c8); CREATE INDEX i19 ON positions(c1, c9); CREATE INDEX i23 ON positions(c2, c3); CREATE INDEX i24 ON positions(c2, c4); CREATE INDEX i25 ON positions(c2, c5); CREATE INDEX i26 ON positions(c2, c6); CREATE INDEX i27 ON positions(c2, c7); CREATE INDEX i28 ON positions(c2, c8); CREATE INDEX i29 ON positions(c2, c9); CREATE INDEX i34 ON positions(c3, c4); CREATE INDEX i35 ON positions(c3, c5); CREATE INDEX i36 ON positions(c3, c6); CREATE INDEX i37 ON positions(c3, c7); CREATE INDEX i38 ON positions(c3, c8); CREATE INDEX i39 ON positions(c3, c9); CREATE INDEX i45 ON positions(c4, c5); CREATE INDEX i46 ON positions(c4, c6); CREATE INDEX i47 ON positions(c4, c7); CREATE INDEX i48 ON positions(c4, c8); CREATE INDEX i49 ON positions(c4, c9); CREATE INDEX i56 ON positions(c5, c6); CREATE INDEX i57 ON positions(c5, c7); CREATE INDEX i58 ON positions(c5, c8); CREATE INDEX i59 ON positions(c5, c9); CREATE INDEX i67 ON positions(c6, c7); CREATE INDEX i68 ON positions(c6, c8); CREATE INDEX i69 ON positions(c6, c9); CREATE INDEX i78 ON positions(c7, c8); CREATE INDEX i79 ON positions(c7, c9); CREATE INDEX i89 ON positions(c8, c9); CREATE INDEX i9 ON positions(c9);
6. Créer une vue pour chercher les sudokus
C’est ici que les contraintes générées précédemment à l’aide de Pharo deviennent utiles! Observez le nombre effarant de conditions dans la clause WHERE de la requête définissant la vue!
USE sudoku; CREATE VIEW sudoku_rows_view AS SELECT r1.c1 as r1c1, r1.c2 as r1c2, r1.c3 as r1c3, r1.c4 as r1c4, r1.c5 as r1c5, r1.c6 as r1c6, r1.c7 as r1c7, r1.c8 as r1c8, r1.c9 as r1c9, r2.c1 as r2c1, r2.c2 as r2c2, r2.c3 as r2c3, r2.c4 as r2c4, r2.c5 as r2c5, r2.c6 as r2c6, r2.c7 as r2c7, r2.c8 as r2c8, r2.c9 as r2c9, r3.c1 as r3c1, r3.c2 as r3c2, r3.c3 as r3c3, r3.c4 as r3c4, r3.c5 as r3c5, r3.c6 as r3c6, r3.c7 as r3c7, r3.c8 as r3c8, r3.c9 as r3c9, r4.c1 as r4c1, r4.c2 as r4c2, r4.c3 as r4c3, r4.c4 as r4c4, r4.c5 as r4c5, r4.c6 as r4c6, r4.c7 as r4c7, r4.c8 as r4c8, r4.c9 as r4c9, r5.c1 as r5c1, r5.c2 as r5c2, r5.c3 as r5c3, r5.c4 as r5c4, r5.c5 as r5c5, r5.c6 as r5c6, r5.c7 as r5c7, r5.c8 as r5c8, r5.c9 as r5c9, r6.c1 as r6c1, r6.c2 as r6c2, r6.c3 as r6c3, r6.c4 as r6c4, r6.c5 as r6c5, r6.c6 as r6c6, r6.c7 as r6c7, r6.c8 as r6c8, r6.c9 as r6c9, r7.c1 as r7c1, r7.c2 as r7c2, r7.c3 as r7c3, r7.c4 as r7c4, r7.c5 as r7c5, r7.c6 as r7c6, r7.c7 as r7c7, r7.c8 as r7c8, r7.c9 as r7c9, r8.c1 as r8c1, r8.c2 as r8c2, r8.c3 as r8c3, r8.c4 as r8c4, r8.c5 as r8c5, r8.c6 as r8c6, r8.c7 as r8c7, r8.c8 as r8c8, r8.c9 as r8c9, r9.c1 as r9c1, r9.c2 as r9c2, r9.c3 as r9c3, r9.c4 as r9c4, r9.c5 as r9c5, r9.c6 as r9c6, r9.c7 as r9c7, r9.c8 as r9c8, r9.c9 as r9c9 FROM positions r1, positions r2, positions r3, positions r4, positions r5, positions r6, positions r7, positions r8, positions r9 WHERE -- Nombres uniques dans chaque colonne (r1.c1 <> r2.c1) AND (r1.c1 <> r3.c1) AND (r1.c1 <> r4.c1) AND (r1.c1 <> r5.c1) AND (r1.c1 <> r6.c1) AND (r1.c1 <> r7.c1) AND (r1.c1 <> r8.c1) AND (r1.c1 <> r9.c1) AND (r1.c2 <> r2.c2) AND (r1.c2 <> r3.c2) AND (r1.c2 <> r4.c2) AND (r1.c2 <> r5.c2) AND (r1.c2 <> r6.c2) AND (r1.c2 <> r7.c2) AND (r1.c2 <> r8.c2) AND (r1.c2 <> r9.c2) AND (r1.c3 <> r2.c3) AND (r1.c3 <> r3.c3) AND (r1.c3 <> r4.c3) AND (r1.c3 <> r5.c3) AND (r1.c3 <> r6.c3) AND (r1.c3 <> r7.c3) AND (r1.c3 <> r8.c3) AND (r1.c3 <> r9.c3) AND (r1.c4 <> r2.c4) AND (r1.c4 <> r3.c4) AND (r1.c4 <> r4.c4) AND (r1.c4 <> r5.c4) AND (r1.c4 <> r6.c4) AND (r1.c4 <> r7.c4) AND (r1.c4 <> r8.c4) AND (r1.c4 <> r9.c4) AND (r1.c5 <> r2.c5) AND (r1.c5 <> r3.c5) AND (r1.c5 <> r4.c5) AND (r1.c5 <> r5.c5) AND (r1.c5 <> r6.c5) AND (r1.c5 <> r7.c5) AND (r1.c5 <> r8.c5) AND (r1.c5 <> r9.c5) AND (r1.c6 <> r2.c6) AND (r1.c6 <> r3.c6) AND (r1.c6 <> r4.c6) AND (r1.c6 <> r5.c6) AND (r1.c6 <> r6.c6) AND (r1.c6 <> r7.c6) AND (r1.c6 <> r8.c6) AND (r1.c6 <> r9.c6) AND (r1.c7 <> r2.c7) AND (r1.c7 <> r3.c7) AND (r1.c7 <> r4.c7) AND (r1.c7 <> r5.c7) AND (r1.c7 <> r6.c7) AND (r1.c7 <> r7.c7) AND (r1.c7 <> r8.c7) AND (r1.c7 <> r9.c7) AND (r1.c8 <> r2.c8) AND (r1.c8 <> r3.c8) AND (r1.c8 <> r4.c8) AND (r1.c8 <> r5.c8) AND (r1.c8 <> r6.c8) AND (r1.c8 <> r7.c8) AND (r1.c8 <> r8.c8) AND (r1.c8 <> r9.c8) AND (r1.c9 <> r2.c9) AND (r1.c9 <> r3.c9) AND (r1.c9 <> r4.c9) AND (r1.c9 <> r5.c9) AND (r1.c9 <> r6.c9) AND (r1.c9 <> r7.c9) AND (r1.c9 <> r8.c9) AND (r1.c9 <> r9.c9) AND (r2.c1 <> r3.c1) AND (r2.c1 <> r4.c1) AND (r2.c1 <> r5.c1) AND (r2.c1 <> r6.c1) AND (r2.c1 <> r7.c1) AND (r2.c1 <> r8.c1) AND (r2.c1 <> r9.c1) AND (r2.c2 <> r3.c2) AND (r2.c2 <> r4.c2) AND (r2.c2 <> r5.c2) AND (r2.c2 <> r6.c2) AND (r2.c2 <> r7.c2) AND (r2.c2 <> r8.c2) AND (r2.c2 <> r9.c2) AND (r2.c3 <> r3.c3) AND (r2.c3 <> r4.c3) AND (r2.c3 <> r5.c3) AND (r2.c3 <> r6.c3) AND (r2.c3 <> r7.c3) AND (r2.c3 <> r8.c3) AND (r2.c3 <> r9.c3) AND (r2.c4 <> r3.c4) AND (r2.c4 <> r4.c4) AND (r2.c4 <> r5.c4) AND (r2.c4 <> r6.c4) AND (r2.c4 <> r7.c4) AND (r2.c4 <> r8.c4) AND (r2.c4 <> r9.c4) AND (r2.c5 <> r3.c5) AND (r2.c5 <> r4.c5) AND (r2.c5 <> r5.c5) AND (r2.c5 <> r6.c5) AND (r2.c5 <> r7.c5) AND (r2.c5 <> r8.c5) AND (r2.c5 <> r9.c5) AND (r2.c6 <> r3.c6) AND (r2.c6 <> r4.c6) AND (r2.c6 <> r5.c6) AND (r2.c6 <> r6.c6) AND (r2.c6 <> r7.c6) AND (r2.c6 <> r8.c6) AND (r2.c6 <> r9.c6) AND (r2.c7 <> r3.c7) AND (r2.c7 <> r4.c7) AND (r2.c7 <> r5.c7) AND (r2.c7 <> r6.c7) AND (r2.c7 <> r7.c7) AND (r2.c7 <> r8.c7) AND (r2.c7 <> r9.c7) AND (r2.c8 <> r3.c8) AND (r2.c8 <> r4.c8) AND (r2.c8 <> r5.c8) AND (r2.c8 <> r6.c8) AND (r2.c8 <> r7.c8) AND (r2.c8 <> r8.c8) AND (r2.c8 <> r9.c8) AND (r2.c9 <> r3.c9) AND (r2.c9 <> r4.c9) AND (r2.c9 <> r5.c9) AND (r2.c9 <> r6.c9) AND (r2.c9 <> r7.c9) AND (r2.c9 <> r8.c9) AND (r2.c9 <> r9.c9) AND (r3.c1 <> r4.c1) AND (r3.c1 <> r5.c1) AND (r3.c1 <> r6.c1) AND (r3.c1 <> r7.c1) AND (r3.c1 <> r8.c1) AND (r3.c1 <> r9.c1) AND (r3.c2 <> r4.c2) AND (r3.c2 <> r5.c2) AND (r3.c2 <> r6.c2) AND (r3.c2 <> r7.c2) AND (r3.c2 <> r8.c2) AND (r3.c2 <> r9.c2) AND (r3.c3 <> r4.c3) AND (r3.c3 <> r5.c3) AND (r3.c3 <> r6.c3) AND (r3.c3 <> r7.c3) AND (r3.c3 <> r8.c3) AND (r3.c3 <> r9.c3) AND (r3.c4 <> r4.c4) AND (r3.c4 <> r5.c4) AND (r3.c4 <> r6.c4) AND (r3.c4 <> r7.c4) AND (r3.c4 <> r8.c4) AND (r3.c4 <> r9.c4) AND (r3.c5 <> r4.c5) AND (r3.c5 <> r5.c5) AND (r3.c5 <> r6.c5) AND (r3.c5 <> r7.c5) AND (r3.c5 <> r8.c5) AND (r3.c5 <> r9.c5) AND (r3.c6 <> r4.c6) AND (r3.c6 <> r5.c6) AND (r3.c6 <> r6.c6) AND (r3.c6 <> r7.c6) AND (r3.c6 <> r8.c6) AND (r3.c6 <> r9.c6) AND (r3.c7 <> r4.c7) AND (r3.c7 <> r5.c7) AND (r3.c7 <> r6.c7) AND (r3.c7 <> r7.c7) AND (r3.c7 <> r8.c7) AND (r3.c7 <> r9.c7) AND (r3.c8 <> r4.c8) AND (r3.c8 <> r5.c8) AND (r3.c8 <> r6.c8) AND (r3.c8 <> r7.c8) AND (r3.c8 <> r8.c8) AND (r3.c8 <> r9.c8) AND (r3.c9 <> r4.c9) AND (r3.c9 <> r5.c9) AND (r3.c9 <> r6.c9) AND (r3.c9 <> r7.c9) AND (r3.c9 <> r8.c9) AND (r3.c9 <> r9.c9) AND (r4.c1 <> r5.c1) AND (r4.c1 <> r6.c1) AND (r4.c1 <> r7.c1) AND (r4.c1 <> r8.c1) AND (r4.c1 <> r9.c1) AND (r4.c2 <> r5.c2) AND (r4.c2 <> r6.c2) AND (r4.c2 <> r7.c2) AND (r4.c2 <> r8.c2) AND (r4.c2 <> r9.c2) AND (r4.c3 <> r5.c3) AND (r4.c3 <> r6.c3) AND (r4.c3 <> r7.c3) AND (r4.c3 <> r8.c3) AND (r4.c3 <> r9.c3) AND (r4.c4 <> r5.c4) AND (r4.c4 <> r6.c4) AND (r4.c4 <> r7.c4) AND (r4.c4 <> r8.c4) AND (r4.c4 <> r9.c4) AND (r4.c5 <> r5.c5) AND (r4.c5 <> r6.c5) AND (r4.c5 <> r7.c5) AND (r4.c5 <> r8.c5) AND (r4.c5 <> r9.c5) AND (r4.c6 <> r5.c6) AND (r4.c6 <> r6.c6) AND (r4.c6 <> r7.c6) AND (r4.c6 <> r8.c6) AND (r4.c6 <> r9.c6) AND (r4.c7 <> r5.c7) AND (r4.c7 <> r6.c7) AND (r4.c7 <> r7.c7) AND (r4.c7 <> r8.c7) AND (r4.c7 <> r9.c7) AND (r4.c8 <> r5.c8) AND (r4.c8 <> r6.c8) AND (r4.c8 <> r7.c8) AND (r4.c8 <> r8.c8) AND (r4.c8 <> r9.c8) AND (r4.c9 <> r5.c9) AND (r4.c9 <> r6.c9) AND (r4.c9 <> r7.c9) AND (r4.c9 <> r8.c9) AND (r4.c9 <> r9.c9) AND (r5.c1 <> r6.c1) AND (r5.c1 <> r7.c1) AND (r5.c1 <> r8.c1) AND (r5.c1 <> r9.c1) AND (r5.c2 <> r6.c2) AND (r5.c2 <> r7.c2) AND (r5.c2 <> r8.c2) AND (r5.c2 <> r9.c2) AND (r5.c3 <> r6.c3) AND (r5.c3 <> r7.c3) AND (r5.c3 <> r8.c3) AND (r5.c3 <> r9.c3) AND (r5.c4 <> r6.c4) AND (r5.c4 <> r7.c4) AND (r5.c4 <> r8.c4) AND (r5.c4 <> r9.c4) AND (r5.c5 <> r6.c5) AND (r5.c5 <> r7.c5) AND (r5.c5 <> r8.c5) AND (r5.c5 <> r9.c5) AND (r5.c6 <> r6.c6) AND (r5.c6 <> r7.c6) AND (r5.c6 <> r8.c6) AND (r5.c6 <> r9.c6) AND (r5.c7 <> r6.c7) AND (r5.c7 <> r7.c7) AND (r5.c7 <> r8.c7) AND (r5.c7 <> r9.c7) AND (r5.c8 <> r6.c8) AND (r5.c8 <> r7.c8) AND (r5.c8 <> r8.c8) AND (r5.c8 <> r9.c8) AND (r5.c9 <> r6.c9) AND (r5.c9 <> r7.c9) AND (r5.c9 <> r8.c9) AND (r5.c9 <> r9.c9) AND (r6.c1 <> r7.c1) AND (r6.c1 <> r8.c1) AND (r6.c1 <> r9.c1) AND (r6.c2 <> r7.c2) AND (r6.c2 <> r8.c2) AND (r6.c2 <> r9.c2) AND (r6.c3 <> r7.c3) AND (r6.c3 <> r8.c3) AND (r6.c3 <> r9.c3) AND (r6.c4 <> r7.c4) AND (r6.c4 <> r8.c4) AND (r6.c4 <> r9.c4) AND (r6.c5 <> r7.c5) AND (r6.c5 <> r8.c5) AND (r6.c5 <> r9.c5) AND (r6.c6 <> r7.c6) AND (r6.c6 <> r8.c6) AND (r6.c6 <> r9.c6) AND (r6.c7 <> r7.c7) AND (r6.c7 <> r8.c7) AND (r6.c7 <> r9.c7) AND (r6.c8 <> r7.c8) AND (r6.c8 <> r8.c8) AND (r6.c8 <> r9.c8) AND (r6.c9 <> r7.c9) AND (r6.c9 <> r8.c9) AND (r6.c9 <> r9.c9) AND (r7.c1 <> r8.c1) AND (r7.c1 <> r9.c1) AND (r7.c2 <> r8.c2) AND (r7.c2 <> r9.c2) AND (r7.c3 <> r8.c3) AND (r7.c3 <> r9.c3) AND (r7.c4 <> r8.c4) AND (r7.c4 <> r9.c4) AND (r7.c5 <> r8.c5) AND (r7.c5 <> r9.c5) AND (r7.c6 <> r8.c6) AND (r7.c6 <> r9.c6) AND (r7.c7 <> r8.c7) AND (r7.c7 <> r9.c7) AND (r7.c8 <> r8.c8) AND (r7.c8 <> r9.c8) AND (r7.c9 <> r8.c9) AND (r7.c9 <> r9.c9) AND (r8.c1 <> r9.c1) AND (r8.c2 <> r9.c2) AND (r8.c3 <> r9.c3) AND (r8.c4 <> r9.c4) AND (r8.c5 <> r9.c5) AND (r8.c6 <> r9.c6) AND (r8.c7 <> r9.c7) AND (r8.c8 <> r9.c8) AND (r8.c9 <> r9.c9) AND -- Nombres uniques dans chaque maison de 3x3 (r1.c1 <> r2.c2) AND (r1.c1 <> r2.c3) AND (r1.c1 <> r3.c2) AND (r1.c1 <> r3.c3) AND (r1.c2 <> r2.c1) AND (r1.c2 <> r2.c3) AND (r1.c2 <> r3.c1) AND (r1.c2 <> r3.c3) AND (r1.c3 <> r2.c1) AND (r1.c3 <> r2.c2) AND (r1.c3 <> r3.c1) AND (r1.c3 <> r3.c2) AND (r1.c4 <> r2.c5) AND (r1.c4 <> r2.c6) AND (r1.c4 <> r3.c5) AND (r1.c4 <> r3.c6) AND (r1.c5 <> r2.c4) AND (r1.c5 <> r2.c6) AND (r1.c5 <> r3.c4) AND (r1.c5 <> r3.c6) AND (r1.c6 <> r2.c4) AND (r1.c6 <> r2.c5) AND (r1.c6 <> r3.c4) AND (r1.c6 <> r3.c5) AND (r1.c7 <> r2.c8) AND (r1.c7 <> r2.c9) AND (r1.c7 <> r3.c8) AND (r1.c7 <> r3.c9) AND (r1.c8 <> r2.c7) AND (r1.c8 <> r2.c9) AND (r1.c8 <> r3.c7) AND (r1.c8 <> r3.c9) AND (r1.c9 <> r2.c7) AND (r1.c9 <> r2.c8) AND (r1.c9 <> r3.c7) AND (r1.c9 <> r3.c8) AND (r2.c1 <> r3.c2) AND (r2.c1 <> r3.c3) AND (r2.c2 <> r3.c1) AND (r2.c2 <> r3.c3) AND (r2.c3 <> r3.c1) AND (r2.c3 <> r3.c2) AND (r2.c4 <> r3.c5) AND (r2.c4 <> r3.c6) AND (r2.c5 <> r3.c4) AND (r2.c5 <> r3.c6) AND (r2.c6 <> r3.c4) AND (r2.c6 <> r3.c5) AND (r2.c7 <> r3.c8) AND (r2.c7 <> r3.c9) AND (r2.c8 <> r3.c7) AND (r2.c8 <> r3.c9) AND (r2.c9 <> r3.c7) AND (r2.c9 <> r3.c8) AND (r4.c1 <> r5.c2) AND (r4.c1 <> r5.c3) AND (r4.c1 <> r6.c2) AND (r4.c1 <> r6.c3) AND (r4.c2 <> r5.c1) AND (r4.c2 <> r5.c3) AND (r4.c2 <> r6.c1) AND (r4.c2 <> r6.c3) AND (r4.c3 <> r5.c1) AND (r4.c3 <> r5.c2) AND (r4.c3 <> r6.c1) AND (r4.c3 <> r6.c2) AND (r4.c4 <> r5.c5) AND (r4.c4 <> r5.c6) AND (r4.c4 <> r6.c5) AND (r4.c4 <> r6.c6) AND (r4.c5 <> r5.c4) AND (r4.c5 <> r5.c6) AND (r4.c5 <> r6.c4) AND (r4.c5 <> r6.c6) AND (r4.c6 <> r5.c4) AND (r4.c6 <> r5.c5) AND (r4.c6 <> r6.c4) AND (r4.c6 <> r6.c5) AND (r4.c7 <> r5.c8) AND (r4.c7 <> r5.c9) AND (r4.c7 <> r6.c8) AND (r4.c7 <> r6.c9) AND (r4.c8 <> r5.c7) AND (r4.c8 <> r5.c9) AND (r4.c8 <> r6.c7) AND (r4.c8 <> r6.c9) AND (r4.c9 <> r5.c7) AND (r4.c9 <> r5.c8) AND (r4.c9 <> r6.c7) AND (r4.c9 <> r6.c8) AND (r5.c1 <> r6.c2) AND (r5.c1 <> r6.c3) AND (r5.c2 <> r6.c1) AND (r5.c2 <> r6.c3) AND (r5.c3 <> r6.c1) AND (r5.c3 <> r6.c2) AND (r5.c4 <> r6.c5) AND (r5.c4 <> r6.c6) AND (r5.c5 <> r6.c4) AND (r5.c5 <> r6.c6) AND (r5.c6 <> r6.c4) AND (r5.c6 <> r6.c5) AND (r5.c7 <> r6.c8) AND (r5.c7 <> r6.c9) AND (r5.c8 <> r6.c7) AND (r5.c8 <> r6.c9) AND (r5.c9 <> r6.c7) AND (r5.c9 <> r6.c8) AND (r7.c1 <> r8.c2) AND (r7.c1 <> r8.c3) AND (r7.c1 <> r9.c2) AND (r7.c1 <> r9.c3) AND (r7.c2 <> r8.c1) AND (r7.c2 <> r8.c3) AND (r7.c2 <> r9.c1) AND (r7.c2 <> r9.c3) AND (r7.c3 <> r8.c1) AND (r7.c3 <> r8.c2) AND (r7.c3 <> r9.c1) AND (r7.c3 <> r9.c2) AND (r7.c4 <> r8.c5) AND (r7.c4 <> r8.c6) AND (r7.c4 <> r9.c5) AND (r7.c4 <> r9.c6) AND (r7.c5 <> r8.c4) AND (r7.c5 <> r8.c6) AND (r7.c5 <> r9.c4) AND (r7.c5 <> r9.c6) AND (r7.c6 <> r8.c4) AND (r7.c6 <> r8.c5) AND (r7.c6 <> r9.c4) AND (r7.c6 <> r9.c5) AND (r7.c7 <> r8.c8) AND (r7.c7 <> r8.c9) AND (r7.c7 <> r9.c8) AND (r7.c7 <> r9.c9) AND (r7.c8 <> r8.c7) AND (r7.c8 <> r8.c9) AND (r7.c8 <> r9.c7) AND (r7.c8 <> r9.c9) AND (r7.c9 <> r8.c7) AND (r7.c9 <> r8.c8) AND (r7.c9 <> r9.c7) AND (r7.c9 <> r9.c8) AND (r8.c1 <> r9.c2) AND (r8.c1 <> r9.c3) AND (r8.c2 <> r9.c1) AND (r8.c2 <> r9.c3) AND (r8.c3 <> r9.c1) AND (r8.c3 <> r9.c2) AND (r8.c4 <> r9.c5) AND (r8.c4 <> r9.c6) AND (r8.c5 <> r9.c4) AND (r8.c5 <> r9.c6) AND (r8.c6 <> r9.c4) AND (r8.c6 <> r9.c5) AND (r8.c7 <> r9.c8) AND (r8.c7 <> r9.c9) AND (r8.c8 <> r9.c7) AND (r8.c8 <> r9.c9) AND (r8.c9 <> r9.c7) AND (r8.c9 <> r9.c8);
Maintenant, nous avons tout ce qu’il faut pour débuter! Si vous avez rencontré des difficultés avec le téléchargement du fichier positions.sql, contactez-moi et je me ferai un plaisir de vous envoyez le fichier compressé (format Gzip) par courriel. Consultez ma page « À propos » pour savoir à quelle adresse courriel me rejoindre!
7. Tester une grille
Asteure que toute la poutine (expression québécoise parfois utilisée pour désigner les tâches cléricales, répétitives, procédurales, techniques ou ennuyantes) est complétée, examinons comment notre solution se comporte pour cette grille :
Pour solutionner la grille, nous devons exécuter cette requête SQL :
SELECT * FROM sudoku_rows_view WHERE (r1c1 = 5) AND (r1c2 = 3) AND (r1c5 = 7) AND (r2c1 = 6) AND (r2c4 = 1) AND (r2c5 = 9) AND (r2c6 = 5) AND (r3c2 = 9) AND (r3c3 = 8) AND (r3c8 = 6) AND (r4c1 = 8) AND (r4c5 = 6) AND (r4c9 = 3) AND (r5c1 = 4) AND (r5c4 = 8) AND (r5c6 = 3) AND (r5c9 = 1) AND (r6c1 = 7) AND (r6c5 = 2) AND (r6c9 = 6) AND (r7c2 = 6) AND (r7c7 = 2) AND (r7c8 = 8) AND (r8c4 = 4) AND (r8c5 = 1) AND (r8c6 = 9) AND (r8c9 = 5) AND (r9c5 = 8) AND (r9c8 = 7) AND (r9c9 = 9);
Évidemment, je l’espère, vous aurez compris que r8c9 signifie « rangée 8 colonne 9 », etc.
Tada!
Victoire!
Voici ce que le EXPLAIN de la requête a à dire :
mysql> explain -> SELECT * -> FROM sudoku_rows_view -> WHERE -> (r1c1 = 5) AND (r1c2 = 3) AND (r1c5 = 7) AND (r2c1 = 6) AND -> (r2c4 = 1) AND (r2c5 = 9) AND (r2c6 = 5) AND (r3c2 = 9) AND -> (r3c3 = 8) AND (r3c8 = 6) AND (r4c1 = 8) AND (r4c5 = 6) AND -> (r4c9 = 3) AND (r5c1 = 4) AND (r5c4 = 8) AND (r5c6 = 3) AND -> (r5c9 = 1) AND (r6c1 = 7) AND (r6c5 = 2) AND (r6c9 = 6) AND -> (r7c2 = 6) AND (r7c7 = 2) AND (r7c8 = 8) AND (r8c4 = 4) AND -> (r8c5 = 1) AND (r8c6 = 9) AND (r8c9 = 5) AND (r9c5 = 8) AND -> (r9c8 = 7) AND (r9c9 = 9); +----+-------------+-------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+---------+------+------+--------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+---------+------+------+--------------------------------------------------------------+ | 1 | SIMPLE | r5 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i16,i19,i49 | 2,2,2 | NULL | 68 | Using intersect(i16,i19,i49); Using where | | 1 | SIMPLE | r2 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i14,i15,i16 | 2,2,2 | NULL | 78 | Using intersect(i14,i15,i16); Using where; Using join buffer | | 1 | SIMPLE | r8 | index_merge | i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i45,i46,i59 | 2,2,2 | NULL | 78 | Using intersect(i45,i46,i59); Using where; Using join buffer | | 1 | SIMPLE | r3 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i28,i38 | 2,2 | NULL | 551 | Using intersect(i28,i38); Using where; Using join buffer | | 1 | SIMPLE | r6 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i19,i59 | 2,2 | NULL | 551 | Using intersect(i19,i59); Using where; Using join buffer | | 1 | SIMPLE | r1 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i12,i15 | 2,2 | NULL | 624 | Using intersect(i12,i15); Using where; Using join buffer | | 1 | SIMPLE | r9 | index_merge | i56,i57,i58,i59,i89,i9 | i58,i59 | 2,2 | NULL | 624 | Using intersect(i58,i59); Using where; Using join buffer | | 1 | SIMPLE | r4 | index_merge | i12,i13,i14,i15,i16,i17,i18,i19,i23,i24,i25,i26,i27,i28,i29,i34,i35,i36,i37,i38,i39,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i15,i19 | 2,2 | NULL | 625 | Using intersect(i15,i19); Using where; Using join buffer | | 1 | SIMPLE | r7 | index_merge | i23,i24,i25,i26,i27,i28,i29,i45,i46,i47,i48,i49,i56,i57,i58,i59,i67,i68,i69,i78,i79,i89,i9 | i27,i28 | 2,2 | NULL | 625 | Using intersect(i27,i28); Using where; Using join buffer | +----+-------------+-------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+---------+------+------+--------------------------------------------------------------+ 9 rows in set (0.06 sec)
Voilà!
Tout fonctionne à merveille! Seulement 0.39 secondes pour solutionner la grille! Savourons le résultat :
mysql> SELECT * -> FROM sudoku_rows_view -> WHERE -> (r1c1 = 5) AND (r1c2 = 3) AND (r1c5 = 7) AND (r2c1 = 6) AND -> (r2c4 = 1) AND (r2c5 = 9) AND (r2c6 = 5) AND (r3c2 = 9) AND -> (r3c3 = 8) AND (r3c8 = 6) AND (r4c1 = 8) AND (r4c5 = 6) AND -> (r4c9 = 3) AND (r5c1 = 4) AND (r5c4 = 8) AND (r5c6 = 3) AND -> (r5c9 = 1) AND (r6c1 = 7) AND (r6c5 = 2) AND (r6c9 = 6) AND -> (r7c2 = 6) AND (r7c7 = 2) AND (r7c8 = 8) AND (r8c4 = 4) AND -> (r8c5 = 1) AND (r8c6 = 9) AND (r8c9 = 5) AND (r9c5 = 8) AND -> (r9c8 = 7) AND (r9c9 = 9); +------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+ | r1c1 | r1c2 | r1c3 | r1c4 | r1c5 | r1c6 | r1c7 | r1c8 | r1c9 | r2c1 | r2c2 | r2c3 | r2c4 | r2c5 | r2c6 | r2c7 | r2c8 | r2c9 | r3c1 | r3c2 | r3c3 | r3c4 | r3c5 | r3c6 | r3c7 | r3c8 | r3c9 | r4c1 | r4c2 | r4c3 | r4c4 | r4c5 | r4c6 | r4c7 | r4c8 | r4c9 | r5c1 | r5c2 | r5c3 | r5c4 | r5c5 | r5c6 | r5c7 | r5c8 | r5c9 | r6c1 | r6c2 | r6c3 | r6c4 | r6c5 | r6c6 | r6c7 | r6c8 | r6c9 | r7c1 | r7c2 | r7c3 | r7c4 | r7c5 | r7c6 | r7c7 | r7c8 | r7c9 | r8c1 | r8c2 | r8c3 | r8c4 | r8c5 | r8c6 | r8c7 | r8c8 | r8c9 | r9c1 | r9c2 | r9c3 | r9c4 | r9c5 | r9c6 | r9c7 | r9c8 | r9c9 | +------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+ | 5 | 3 | 4 | 6 | 7 | 8 | 9 | 1 | 2 | 6 | 7 | 2 | 1 | 9 | 5 | 3 | 4 | 8 | 1 | 9 | 8 | 3 | 4 | 2 | 5 | 6 | 7 | 8 | 5 | 9 | 7 | 6 | 1 | 4 | 2 | 3 | 4 | 2 | 6 | 8 | 5 | 3 | 7 | 9 | 1 | 7 | 1 | 3 | 9 | 2 | 4 | 8 | 5 | 6 | 9 | 6 | 1 | 5 | 3 | 7 | 2 | 8 | 4 | 2 | 8 | 7 | 4 | 1 | 9 | 6 | 3 | 5 | 3 | 4 | 5 | 2 | 8 | 6 | 1 | 7 | 9 | +------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+ 1 row in set (0.39 sec)
Trop facile ? Hmmmm!?!? Peut-être! À moins que…
C’était beaucoup trop beau! Comme nous le verrons au prochain article, plusieurs problèmes pointent à l’horizon (et espérons-le, plusieurs remèdes!) et nous devrons faire beaucoup mieux et plus vite avec les outils que nous avons pour arriver à résoudre certaines grilles très particulières.
En attendant la seconde partie de cet article, amusez-vous bien et expérimentez !
Note: j’ai déjà publié cet article en anglais sur mes autres blogues dont récemment ici (l’autre blogue est disparu depuis longtemps). Ne vous en faites pas, il ne s’agit pas d’un cas de plagiat!!!
Prochaine étape, chers lecteurs, nous apprendrons à presser un citron et à en extraire tout le jus!
Leave a Comment » | algorithmes, MyISAM, MySQL, optimisation, performance, Pharo, sudoku | Tagué: algorithmes, MyISAM, MySQL, optimisation, performance, Pharo, Sudoku | Permalien
Publié par endormitoire
Comment presser un citron (préambule).
4 janvier 2012« La simplicité est la sophistication suprême » (Léonard de Vinci).
Problème : vous avez à trouver, en seulement quelques secondes, un enregistrement unique parmi des milliards de milliards de possibilité et les seules informations dont vous disposez pour faire votre recherche sont de 17 à 35 attributs sur les 81 que contiennent la donnée tant convoitée… Est-ce possible ? Comment faire? De prime abord, ça semble impossible!
« Impossible n’est pas français » comme le dit le dicton (faussement attribué à Napoléon Bonaparte, vexé par le pessimisme de Jean Léonard, comte le Marois).
Il y a bientôt quelques années de cela, un de mes confrères de travail, Claude, m’introduisait au Sudoku. Au tout début, je ne comprenais pas la fascination de celui-ci pour un problème aussi trivial jusqu’à ce que j’essaie de résoudre une de ces grilles par moi-même… Les règles étant si peu nombreuses et si simples, j’ai rapidement été surpris par la complexité demandée pour résoudre ces puzzles. Et j’en suis devenu accroc!
Binary Sudoku
Non, aucun autre puzzle ne semble, en apparence, aussi simple que le Sudoku! Mais derrière ce petit jeu aux règles minimales se cache un univers de complexité qui englobe une branche complète des mathématiques.
Ce n’est que lorsque je me suis buté à un problème difficile (sans arriver à le résoudre) que je me suis intéressé à l’aspect algorithmique de la résolution d’un Sudoku. Évidemment, j’aurais pu télécharger des « sudoku solver » écrits en Java, en Python, en Ruby, en Smalltalk, en BASIC, en T-SQL, en macros Excel, en n’importe quoi et trouver la solution mais le défi était d’écrire moi-même un de ces « sudoku solver ».
Malheureusement, après réflexion, la tâche m’apparaissait trop simple pour être intéressante. Coder un tel outil avec les méthodes de résolution de base et y ajouter une pile de recherche pour y faire du « backtracking » était, en soi, trop facile (du moins, en Smalltalk) pour valoir la peine. Mais à cette époque, étant plongé dans des requêtes SQL dignes des pires labyrinthes pour régler une foule de problèmes au niveau de la base de donnée d’un de nos clients, l’idée m’est venue de trouver une solution « 100% SQL », sans procédures stockées, sans fonctions, seulement UNE requête SQL. Le défi était lancé!
Il est maintenant établi qu’il existe 6670903752021072936960 grilles différentes pour un Sudoku standard de 9×9. Avec les années, plusieurs raffinements et subtilités ont pris en compte les symétries ainsi que diverses particularités des autres variantes du Sudoku pour mieux comprendre et étudier tous les aspects du Sudoku standard. Diverses techniques de résolution, de plus en plus complexes et élaborées, ont vu le jour puis ont été étudiées et améliorées de fond en comble. Les grilles de Sudoku et les carrés latins ont depuis longtemps tenu les mathématiciens occupés et, encore aujourd’hui, de nombreux trésors et concepts mathématiques encore cachés ne demandent qu’à être découverts. Ce petit jeu représente une mine intarissable de surprises pour les mathématiciens et les développeurs : il ne reste qu’à creuser encore plus creux et plus longtemps!
Cet article, divisé en trois parties, s’attaquera aux grilles de Sudoku standard de 9×9, question de garder les choses simples. Nous examinerons une manière de résoudre ces grilles à l’aide d’une solution n’impliquant que du SQL, sans procédure stockée ni script ni fonction : seulement du SQL bête et méchant!
En première partie nous verrons comment créer les données et tout ce dont nous avons besoin pour poursuivre. En seconde partie, nous tenterons d’optimiser notre approche. En troisième partie, nous mesurerons l’impact des différentes optimisations en les comparant.
Mais avant de débuter, vous devez à tout le moins être familier avec les règles du Sudoku standard, les méthodes de résolution et connaître le jargon du métier! Tout ce dont vous avez besoin savoir se trouve ici.
Matériel nécessaire : une base de données MySQL (bien qu’avec un peu de débrouillardise, vous pouvez adapter les scripts SQL pour un autre SGBD).
Matériel facultatif: si vous désirez expérimenter vous-même et peut-être pousser l’aventure plus loin, un langage de programmation vous permettant de générer des données et/ou des requêtes SQL pourrait s’avérer utile. Pour vous aider à apprendre Smalltalk (avec Pharo), je vous conseille fortement Pharo par l’exemple.
Dans cette série d’articles, j’utiliserai MySQL 5.1.53 ainsi que Pharo 1.2a.
Leave a Comment » | algorithmes, jeux, mathématiques, MySQL, optimisation, performance, Pharo, programmation, Smalltalk, SQL, sudoku | Tagué: algorithmes, jeux, mathématiques, MySQL, optimisation, performance, Pharo, programmation, Smalltalk, SQL, Sudoku | Permalien
Publié par endormitoire
L'endormitoire 