MySQL Space type test

Mysql Description of space type:

MySQL supports spatial expansion, allowing the generation, preservation, and analysis of geographic features. These features are available for the MyISAM, InnoDB, NDB, BDB, and archive tables (however, the archive engine does not support indexing, so the spatial columns in the archive column cannot be indexed).

Test Purpose:

You want to use MySQL's dedicated space type instead of integer types to store coordinate information, which improves efficiency when searching the map.

Reference Documentation:

Http://dev.mysql.com/doc/refman/5.1/zh/spatial-extensions-in-mysql.html

Test environment:

Server: PowerEdge R710

Memory: 32G

Software: Percona MySQL 5.5.28

Test:

First, functional testing

1. Create a table of spatial type and shape type tables, respectively save the data is user_id,x - axis coordinates,y - coordinate

CREATE TABLE ' Testa ' (

' UID ' int (one) DEFAULT NULL,

' P ' point is not NULL,

SPATIAL KEY ' P ' (' P ')

) Engine=myisam DEFAULT Charset=latin1collate=latin1_bin

CREATE TABLE ' Testb ' (

' UID ' int (one) DEFAULT NULL,

' x ' int (one) DEFAULT NULL,

' Y ' int (one) DEFAULT NULL,

Key ' x ' (' x ')

) Engine=myisam DEFAULT Charset=latin1collate=latin1_bin

2. generate test Data

awk ' Begin{srand (); for (i=1;i<=100000;i++) Printn++,int (rand () *1000), Int (rand () *1000)} ' > A.txt

3. Import test data into a table of spatial type and integer type

Cat A.txt|awk ' {print ' INSERT Intotesta VALUES ("$", Geomfromtext (\ "Point" ("$ $") \ ")", "}" | Mysql

Cat A.txt|awk ' {print ' INSERT intotestb VALUES ("$", "$ $", "$ $") \ ")", "}" | Mysql

4. See if the data is plugged in successfully

Mysql> select Uid,astext (P) Fromtesta limit 3;

+------+----------------+

| UID |astext (P) |

+------+----------------+

| 0 | Point (140 955) |

| 1 | Point (912 377) |

| 2 | Point (63561) |

+------+----------------+

Mysql> Select Uid,x,y from Testblimit 3;

+------+----------------+---+

| UID | x | y |

+------+----------------+---+

| 0 | 140 | 955|

| 1 | 912 | 377|

| 2 | 635 | 61 |

+------+----------------+---+

Second, performance testing

1. Query Operations on the spatial type table and the shaped table using the same criteria

Mysql> SELECT count (uid) from Testa WHERE intersects (p, Geomfromtext (' POLYGON ((0 0, 0, 0, 0 0)) D uid>1000anduid<=2000;

+------------+

| Count (UID) |

+------------+

| 93 |

+------------+

1 row in Set (1.67 sec)

Mysql> SELECT count (uid) from Testb WHERE x>=0 andx<=300 and Y>=0 and y<=300 and uid>1000 anduid<=200 0;

+------------+

| Count (UID) |

+------------+

| 93 |

+------------+

1 row in Set (1.28 sec)

2. Performance Test Results

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/75/7C/wKiom1Y6Abuhl-2pAABXdyboU44870.jpg "title=" C.png " alt= "Wkiom1y6abuhl-2paabxdybou44870.jpg"/>

Test conclusion:

MySQL the space type is not mature enough to use as a coordinate query is slower than using the Shaping store query, and the industry is using very little, so map search cannot work with this type.

This article is from the "Zhangdh Open Space" blog, so be sure to keep this source http://linuxblind.blog.51cto.com/7616603/1709776

MySQL Space type test