January 29, 2016 at 8:35 am
I have a large database table (on the server it is larger with about 4 million rows) that has the following query (see my signature for details on my setup that is on my localhost):
SELECT P.IDENTIFIER, P.LAST_NAME, P.FIRST_NAME, P.REGISTRATION_ADDRESS, P.REGISTRATION_CITY, P.REGISTRATION_ZIP, P.COUNTY_FIP,
cast(ST_Distance(P.GEO_LOCATION, ST_GeomFromText('POINT(-90.341614 38.678802)')) as decimal(11,8)) AS DISTANCE
FROM PERSON P
WHERE
ST_Distance(P.GEO_LOCATION, ST_GeomFromText('POINT(-90.341614 38.678802)')) < 5 AND P.COUNTY_FIP = 189
ORDER BY DISTANCE ASC
I've run the EXPLAIN command to see the details of the execution and to my surprise, it is not using the spatial index that is on the GEO_LOCATION column.
id: 1
select type: SIMPLE
table: P
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 612602
Extra: Using where; Using filesort
I'm trying to make the execution as fast as possible. The purpose of the query is to essentially get the nearest neighbor and sort by distance. The GEO_LOCATION is made from latitude and longitude columns that are decimal. It has a spatial index called sp_geo_index. Currently on my localhost using MySQL Workbench, it shows the query takes 2.5 seconds to compute and return 1000 records. When the query is run from inside the php page, it has a setMaxResults = 200 (Using Doctrine Querybuilder).
How can I optimize this query? Also, I may have to perform a join on this query which will select all in table PERSON that are not in table ASSIGNED. This slows the query down to non acceptable speeds. Any suggestions on optimization is appreciated.
Evan
Server type: MariaDB
Server version: 10.1.8-MariaDB - mariadb.org binary distribution
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)
Apache/2.4.17 (Win32) OpenSSL/1.0.2d PHP/5.6.14
Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503
PHP extension: mysqli Documentation
PHP version: 5.6.14
January 29, 2016 at 8:48 am
Hi Evan,
welcome to the forum. Although I'm now about to try and scare you off. This is a SQL Server forum and although I'm sure there are people that can help you on here, you'll probably get a better answer on a dedicated MySQL forum.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 29, 2016 at 9:11 am
True this is SQL Server; however, I have seen quite a few posts with MySQL or MariaDB. Just thought the great minds on this forum might be able to assist. I'll look for other forums as well to post. Any suggestions on where/what forum is appreciated.
Evan
Server type: MariaDB
Server version: 10.1.8-MariaDB - mariadb.org binary distribution
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)
Apache/2.4.17 (Win32) OpenSSL/1.0.2d PHP/5.6.14
Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503
PHP extension: mysqli Documentation
PHP version: 5.6.14
January 31, 2016 at 3:44 am
There are certainly some great minds on here.
If you're looking for a MySQL forum, the first place I'd try is here. I can't vouch for how good it is but I've used it in the past.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply