[MySQL] BETWEEN sur une table de 250.000 entrées minimum

[php_addict]

bonsoir

en quête d'optimisation j'en viens à vous.

Il s'agit de ma map. Elle comportera à terme au minimum 250.000 cases donc 250.00 entrées

je n'affiche que de toutes petites portions de map mais la requête est assez lente malgré l'utilisation à bon escient des index (index sur x, index sur y et index sur le couple x,y)

J'utilise pour l'affichage une requête avec pas mal de jointures (5 ou 6 LEFT et INNER JOIN) et surtout un BETWEEN qui semble ralentir la requête, par exemple:

WHERE (x BETWEEN 10 AND 30) AND (y BETWEEN 60 AND 100)

j'ai fait des test sur mon localhost ainsi que sur un dédié debian installé dans mon placard (avec cache d'opcode APC) et j'obtiens des temps assez élevés, je n'ai plus les chiffres en tête mais c'est beaucoup trop long...

le seule moyen que je vois c'est de rajouter un index supplémentaire sur ma TABLE map, comme par exemple diviser ma map en 4 régions (1 index par région)

voyez vous d'autres optimisations possibles pour minimiser le temps d'exécution sur une table de 250.000 entrées avec la clause BETWEEN ?

bonne soirée à toutes et à tous

[Myrina]

déjà tu peux essayer de remplacer le between : X >= 10 AND x< 30 AND y>=60 AND y<100

vérifie également avec EXPLAIN la bonne utilisation des index que tu as mis.

Maintenant, il faudrait plus d'information sur le schéma mais si tu fait plusieurs jointures avec entre autre cette table à 250 000 entrées; il serait peut être intéressant de créer une table en mémoire pour y verser les tuples correspondant à la portion de map voulue puis faire la "grosse requête" avec cette petite table.

[Argorate]

De mémoire, le BETWEEN est plus lent que l'encadrement comme le propose Myrina. (à vérifier)
Après si tu as indexé tes x et y, je ne vois pas le problème, surtout que 250k, ce n'est pas non plus énorme.
Tu peut montrer la requête en entier, le shémat de la table et les index, ainsi que le temps d'exe que tu obtiens? Suis curieux de voir se que tu appel "long".
Après le cache APC met peut être plus de temps la première fois, le temps de mettre en cache, j'en sais rien...

Sinon, tu fais des jointures avec cette requete?

[php_addict]

bon, j'ai viré toutes les jointures et toujours le même problème:

les tests sur une table d'un million d'entrées:

Time : 178.1ms ( SELECT map2.x, map2.y, map2.contenu, map2.x1, map2.y1 )
Time : 177ms ( SELECT map2.x1, map2.y1 )
Time : 23.8ms ( SELECT map2.x, map2.y )

selon les données récupérées les temps sont nettement différents (le ORDER BY ne semble pas aggraver significativement te temps d'exécution, ni l'ordre des champs dans la requete sql )

c'est vraiment mystérieux le fait que si je récupère les champs x1 et x2 cela me prenne 8 fois plus de temps que de récupérer les champs x et y

La requête qui m'intéresse (celle à 178.1ms):

SELECT map2.x,

		map2.y,

		map2.contenu,

		map2.x1,

		map2.y1

FROM map2



WHERE (map2.x >= 10 AND map2.x <= 40) AND (map2.y >= -30 AND map2.y <= 0) 

	ORDER BY map2.y ASC, map2.x ASC ");

Structure de la table

--

-- Structure de la table `map2`

--



CREATE TABLE IF NOT EXISTS `map2` ( `id` int(11) NOT NULL AUTO_INCREMENT,

									`x` smallint(11) NOT NULL,

									`y` smallint(11) NOT NULL,

									`x1` smallint(11) NOT NULL,

									`y1` smallint(11) NOT NULL,

									`contenu` int(11) NOT NULL,

  

								PRIMARY KEY (`id`),

										KEY `xy` (`x`,`y`),

										KEY `x1y1` (`x1`,`y1`),

										KEY `x` (`x`),

										KEY `y` (`y`),

										KEY `x1` (`x1`),

										KEY `y1` (`y1`),

										KEY `x_2` (`x`,`y1`),

										KEY `x1_2` (`x1`,`y`)

)

ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1222503 ;

remarque x1 et y1 sont des coordonnées alternative à x et y car j'utilise un plan projeté, mais le soucis ne semble par être là.

[Myrina]

InnoDb vs MyISAM, ca donne quoi?

[Argorate]

Je ne suis pas chez moi, je ne peux pas comparer, mais a quoi te sert le order by? tu as essayé sans? tu peux très bien récup les données dans l'ordre où elles sont et les réorganiser en les récupérant derrière dans un tableau double dimension.

Sinon les parenthèses entre AND sont inutile, il n'y a aucun notion de priorité^^

Tu as des index sur ces x,y,x1,y1?

et je n'ai pas compris l'histoire du plan projeté? Tu veux faire quoi en fait?

[php_addict]

InnoDb vs MyISAM, ca donne quoi?

pas mieux, voir même un tout petit peu plus lent

mais a quoi te sert le order by?

A classer les résultats voyons...que ce soit mysql ou php qui s'en charge faut bien le faire...et comme je l'ai déjà dit le ORDER BY ne ralentit pas la requete de façon significative...

Tu as des index sur ces x,y,x1,y1?

relis mon post...partie structure de la table

[Argorate]

Le order by n'est pas utile mais bon, tu fais comme tu veux^^
Sinon la structure de ta table indique des "KEY" pas des "INDEX", donc je voulais être sur...
J'essaie de mieux comprendre se que tu veux faire pour t'aider, mais si c'est pour répondre a coté, tant pis pour toi :p

[srm]

Tu retournes quel volume de données ?

[Holy]

Le order by n'est pas utile mais bon, tu fais comme tu veux^^

En quoi il est pas utile ? ^^ Y a rien qui dit que ces entrées seront triées dans le bon ordre, non ?

Par contre, Argorate a peut-être raison sur un point : il faudrait créer une clé primaire sur x et y. J'aurais tendance à virer le champ ID et à créer une double clé primaire sur x et y, pour pas devoir créer d'index supplémentaire sur xy.

CREATE TABLE IF NOT EXISTS `map2` (

  `x` smallint(11) NOT NULL,

  `y` smallint(11) NOT NULL,

  `contenu` int(11) NOT NULL,

  PRIMARY KEY  (`x`,`y`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

J'ai pas bien compris à quoi servait x1 et y1.

Mes propos sont soumis à caution, je suis pas un as niveau indexation .