SELECT les enregistrements qui contiennent une valeur puissance de 2

21 janv. 2008, 17:27

En fait "l'autre solution" proposée était de modifier la structure de sa base pour avoir un id classique dans sa table de langues et utiliser une table de liaison pour associer les langues aux jeux

Et on se demandait donc s'il était plus rapide, optimisé, tout ça, de passer par une jointure classique avec 3 tables (2nd message de Nico128), ou par la comparaison binaire sur les deux tables (1er message)

Mais le fait qu'on ne puisse utiliser d'index va sans doute faire pencher la balance en faveur de la première solution (bon tout dépend du nombre de jeux of course)

21 janv. 2008, 20:40

Re tout le monde ! Je vois que mon sujet a fait réagir beaucoup de personnes ^^

Désolé si j'ai pas pu répondre plus tôt, j'étais à l'école cette après-midi. Donc j'ai tester la solution de Calimero et il mérite bien sa crêpe chocolat-banane et tout ce qui va avec, puisque ça requête marche du tonnerre ^^ !!

Un grand merci à toi Calimero ! J'avais vu dernièrement en VBScript que c'était possible de tester des valeur comme ça mais j'avais aucune idée comment faire avec mysql, et je pense que j'aurais jamais trouvé. Merci encore!

Pour Hubert Roksor, je comprends pas bien ce que tu essaye d'expliquer. Et je sais pas exactement si c'est ça que tu me demandes :

Je vais essayé avec une table associative voir ce que ça vaut.

Sinon Ryle, ma table contient 1500-2000 enregistrement.

21 janv. 2008, 20:52

Pour Hubert Roksor, je comprends pas bien ce que tu essaye d'expliquer. Et je sais pas exactement si c'est ça que tu me demandes :

Il a besoin de ça : http://www.phpfrance.com/forums/voir_sujet-34456.php

Comme ca il peut éventuellement recréer la table chez lui et avoir un jeu de données pour essayer plein de choses diverses et variées dont il a le secret, et te donner plein de conseils vachement bien pour t'aider à optimiser ta base de données

21 janv. 2008, 20:57

Okey, voila donc :

Code : Tout sélectionner

-- 
-- Structure de la table `roms_config_language`
-- 

CREATE TABLE roms_config_language (
  id_language int(11) NOT NULL default '-1',
  language varchar(255) NOT NULL default '',
  PRIMARY KEY  (id_language)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Contenu de la table `roms_config_language`
-- 

INSERT INTO roms_config_language VALUES (1, 'Français');
INSERT INTO roms_config_language VALUES (2, 'Anglais');
INSERT INTO roms_config_language VALUES (4, 'Chinois');
INSERT INTO roms_config_language VALUES (8, 'Danois');
INSERT INTO roms_config_language VALUES (16, 'Néerlandais');
INSERT INTO roms_config_language VALUES (32, 'Finlandais');
INSERT INTO roms_config_language VALUES (64, 'Allemand');
INSERT INTO roms_config_language VALUES (128, 'Italien');
INSERT INTO roms_config_language VALUES (256, 'Japonais');
INSERT INTO roms_config_language VALUES (512, 'Norvégien');
INSERT INTO roms_config_language VALUES (1024, '?');
INSERT INTO roms_config_language VALUES (2048, 'Portugais');
INSERT INTO roms_config_language VALUES (4096, 'Espagnol');
INSERT INTO roms_config_language VALUES (8192, 'Suédois');
INSERT INTO roms_config_language VALUES (16384, '?');
INSERT INTO roms_config_language VALUES (32768, '?');
INSERT INTO roms_config_language VALUES (65536, 'Coréen');

22 janv. 2008, 05:01

Désolé, j'étais un peu pressé lorsque j'ai tapé mon précédent message et je n'ai pas pris le temps de copier/coller les bons liens.

J'ai parlé du type de données "ENUM" dans mon précédent post, c'était une erreur, il fallait vraiment lire 'le type de données "SET"'. Calimero disait que les bitmasks étaient simples et très utilisés, et en effet ils sont tellement utilisés que MySQL possède un type de données (SET) spécialement adapté à leur utilisation. Dans ce message, je vais t'expliquer comment l'utiliser mais avant toute chose j'aimerais précisé que ce n'est pas la solution que je recommanderais. Même si c'est un peu plus compliqué et même un tout petit poil plus lent, je recommanderais la version avec trois tables utilisant une table de relations. Voilà donc pour les avertissements

J'ai créé sur mon système une table "roms_config_language", ainsi qu'une table "tb_jeux" pour pouvoir exécuter des requêtes identiques aux tiennes. Voici cette table :

Code : Tout sélectionner

CREATE TABLE tb_jeux (
	id_jeu INT UNSIGNED NOT NULL,
	nom VARCHAR(255) NOT NULL,
	language SET('Français','Anglais','Chinois','Danois','Néerlandais','Finlandais','Allemand','Italien','Japonais','Norvégien','vide1','Portugais','Espagnol','Suédois','vide2','vide3','Coréen')
) DEFAULT CHARSET=latin1;

INSERT INTO tb_jeux VALUES
	(1, 'jeu 1 FR-EN', 'Français,Anglais'),
	(2, 'jeu 2', ''),
	(3, 'jeu 3 FR-ZH', 'Chinois,Français'),
	(4, 'jeu 4 DA-NL-FI', 'Danois,Néerlandais,Finlandais'),
	(5, 'jeu 5 FR', 'Français')

Le type de données SET est un type hybride, que l'on peut utiliser à la fois comme une chaine en listant une liste de valeurs séparées par des virgules, ou comme un nombre correspondant au bitmask de ses données. En effet, dans la liste de langues utilisée lors de la définition de la colonne, chaque membre (valeur de la colonne) s'est vue attribuée une valeur correspondant à 1^r (1 puissance r) où r est le rang de la valeur à partir de 1. En clair, 'Français' vaut 1, 'Anglais' vaut 2, 'Chinois' vaut 4, etc... Le type SET ne permet pas d'avoir des "trous" dans la progression arithmétique de ses membres, j'ai donc ajoutés des langues "vide" pour les combler et faire correspondre les langues à ton système actuel.

À partir de là, puisque le type SET est considéré comme un nombre et les valeurs sont les même que ton système actuel, je peux réutiliser toutes les requêtes citées dans ce sujet à l'identique

Code : Tout sélectionner

SELECT j.*
FROM tb_jeux j
JOIN roms_config_language l ON l.id_language & j.language > 0
WHERE l.language = 'Français'

Code : Tout sélectionner

+--------+-------------+------------------+
| id_jeu | nom         | language         |
+--------+-------------+------------------+
|      1 | jeu 1 FR-EN | Français,Anglais |
|      3 | jeu 3 FR-ZH | Français,Chinois |
|      5 | jeu 5 FR    | Français         |
+--------+-------------+------------------+

Mais le type SET est une chaine aussi, donc je peux l'utiliser directement ! Si je veux récupérer les jeux qui ne sont disponibles qu'en français

Code : Tout sélectionner

SELECT *
FROM tb_jeux
WHERE language = 'Français'

Code : Tout sélectionner

+--------+----------+----------+
| id_jeu | nom      | language |
+--------+----------+----------+
|      5 | jeu 5 FR | Français |
+--------+----------+----------+

En utilisant la fonction FIND_IN_SET(), les jeux possédant 'Français' dans leur liste

Code : Tout sélectionner

SELECT *
FROM tb_jeux
WHERE FIND_IN_SET('Français', language)

Code : Tout sélectionner

+--------+-------------+------------------+
| id_jeu | nom         | language         |
+--------+-------------+------------------+
|      1 | jeu 1 FR-EN | Français,Anglais |
|      3 | jeu 3 FR-ZH | Français,Chinois |
|      5 | jeu 5 FR    | Français         |
+--------+-------------+------------------+

Et pour vérifier que le type s'utilise bien comme un nombre

Code : Tout sélectionner

SELECT *
FROM tb_jeux
WHERE language = 5

Code : Tout sélectionner

+--------+-------------+------------------+
| id_jeu | nom         | language         |
+--------+-------------+------------------+
|      3 | jeu 3 FR-ZH | Français,Chinois |
+--------+-------------+------------------+

Normalement tu devrais maintenant être incollable sur le type SET.

Puisque le type est compatible avec ton système actuel, tu pourrais directement changer le type de ta table sans rien changer à tes requêtes actuelles. Sous phpMyAdmin, tu peux éditer ta colonne, remplacer le type INT par SET et dans le champs valeurs entrer

Code : Tout sélectionner

'Français','Anglais','Chinois','Danois','Néerlandais','Finlandais','Allemand','Italien','Japonais','Norvégien','vide1','Portugais','Espagnol','Suédois','vide2','vide3','Coréen'

La conversion sera automatique (en fait il n'y aura même pas de conversion puisque SET est un type numérique) et si tu ne souhaites plus utiliser SET tu pourras reconvertir ta colonne en INT de la même façon. Mais que cela ne t'empêche pas de faire une copie de tes tables par sécurité.

Attention toutefois, ce système n'est pas aussi flexible qu'un système à 3 tables, ou même ton système de bitmask. Pour ajouter une nouvelle langue il te faudra modifier la colonne à chaque fois. Comme dans ton système de bitmask, il est impossible d'effacer une langue sans la remplacer par une valeur quelconque. De plus, ces valeurs doivent être uniques (on ne peut pas avoir 2 fois 'vide' dans la liste).

Dernière chose, pour ceux qui se poseraient des questions sur les performances, FIND_IN_SET() n'utilise pas les index, comme & et les opérateurs bitwise. D'un autre côté, je remarque qu'il n'y a déjà pas d'index sur roms_config_language.language...

22 janv. 2008, 09:06

Très intéressant ce sujet ...

Il va falloir trouver une place pour ne pas le perdre

22 janv. 2008, 11:33

juste au passage, mais il me semble avoir lu quelque part que dans SQL server, les bitmask pouvaient se subdiviser automatiquement en colonnes (rendant possible leur interrogation directe, et donc plus besoin d'index dans la colonne puisqu'il n'y a que deux valeurs possibles).

22 janv. 2008, 12:53

En effet, très intéressant ce sujet.

Mais dans le cas présent, je pense que modifier la structure de la base donnée pour utiliser une table de liaison reste la solution la plus propre.
Surtout si on risque à un moment ou un autre de devoir ajouter d'autre fonctionnalitée: par exemple savoir combien de jeu sont en Français, etc.
En plus, j'émets des doutes sur les performances d'opérations bits à bits utilisées en grands nombres dans des requêtes

SELECT les enregistrements qui contiennent une valeur puissance de 2

Qui est en ligne