19.6.2 Utiliser un index géométrique

L'optimiseur vérifie si un index géométrique est disponible et peut être utilisé pour accélérer les requêtes qui utilisent des fonctions comme MBRContains() ou MBRWithin() dans les clauses WHERE . Par exemple, imaginons que vous devons trouver les objets qui sont dans un rectangle donné :

mysql> SELECT fid,AsText(g) FROM geom WHERE mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g); +-----+-----------------------------------------------------------------------------+ | fid | AsText(g)                                                                   | +-----+-----------------------------------------------------------------------------+ |  21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30333.8 15828.8)     | |  22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8,30334 15871.4)     | |  23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4,30334 15914.2)     | |  24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4,30273.4 15823)     | |  25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882.4,30274.8 15866.2) | |  26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4,30275 15918.2)     | | 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946.8,30320.4 15938.4) | |   1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136.4,30240 15127.2)   | |   2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136,30210.4 15121)     | |   3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,30169 15113)           | |   4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30157 15111.6)       | |   5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4,30194.2 15075.2)   | |   6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,30244.6 15077)         | |   7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8,30201.2 15049.4)   | |  10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6,30189.6 15019)     | |  11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2,30151.2 15009.8)   | |  13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,30114.6 15067.8)       | | 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30278 15134)         | | 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30259 15083.4)       | | 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4,30128.8 15001)     | +-----+-----------------------------------------------------------------------------+ 20 rows in set (0.00 sec)

Maintenant, vérifions comment cette requête est exécutée, avec la commande EXPLAIN :

mysql> EXPLAIN SELECT fid,AsText(g) FROM geom WHERE mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g); +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ |  1 | SIMPLE      | geom  | range | g             | g    |      32 | NULL |   50 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)

Voyons ce qui se passe si nous n'avions pas utilisé d'index spatial :

mysql> EXPLAIN SELECT fid,AsText(g) FROM g IGNORE INDEX (g) WHERE mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g); +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ |  1 | SIMPLE      | geom  | ALL  | NULL          | NULL |    NULL | NULL | 32376 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.00 sec)

Exécutons la requête ci-dessus, en ignorant l'index spatial disponible :

mysql> SELECT fid,AsText(g) FROM geom IGNORE INDEX (g) WHERE mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g); +-----+-----------------------------------------------------------------------------+ | fid | AsText(g)                                                                   | +-----+-----------------------------------------------------------------------------+ |   1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136.4,30240 15127.2)   | |   2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136,30210.4 15121)     | |   3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,30169 15113)           | |   4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30157 15111.6)       | |   5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4,30194.2 15075.2)   | |   6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,30244.6 15077)         | |   7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8,30201.2 15049.4)   | |  10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6,30189.6 15019)     | |  11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2,30151.2 15009.8)   | |  13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,30114.6 15067.8)       | |  21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30333.8 15828.8)     | |  22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8,30334 15871.4)     | |  23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4,30334 15914.2)     | |  24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4,30273.4 15823)     | |  25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882.4,30274.8 15866.2) | |  26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4,30275 15918.2)     | | 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30278 15134)         | | 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30259 15083.4)       | | 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4,30128.8 15001)     | | 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946.8,30320.4 15938.4) | +-----+-----------------------------------------------------------------------------+ 20 rows in set (0.46 sec)

Lorsque l'index n'est pas utilisé, le temps d'exécution de la requête passe de 0.00 seconde à 0.46 second.Dans les prochaines versions, les index spatiaux seront aussi utilisés pour optimiser d'autres fonctions. Fonctions de tests des relations géométriques entre les formes .