1

I have spatial column and spatial index for it. When I making query, I see that MySql not using that index. Table has 10k records. Why?

schema.rb

create_table "homes_raw", id: :string, limit: 22, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci", force: :cascade do |t|
   t.geometry "location", limit: {:type=>"point", :srid=>0}, null: false
   t.index ["location"], name: "location_spatial", type: :spatial
end

Performing query with EXPLAIN

EXPLAIN for: SELECT `homes_raw`.* FROM `homes_raw` WHERE (ST_Distance_Sphere(
            ST_GeomFromText(ST_AsText(location), 0), POINT(60.41217340143263, 64.48856982983708)
            ) <= 100000)
    ```

[EPLAIN result][1]


  [1]: https://i.sstatic.net/IRDGS.png
Improve this question
5
  • Pls share the query written in active record dsl. Commented Jul 11, 2019 at 5:22
  • Did you do benchmarks? Like, maybe the explain is just not showing that it's using the index while it still is using it when performing a query. Commented Jul 11, 2019 at 5:23
  • 1
    I'm not an expert in this particular area (MySQL geo queries), so I did a quick google search. Did you check these two resources? * stackoverflow.com/questions/38968349/… * dev.mysql.com/doc/refman/5.7/en/using-spatial-indexes.html Commented Jul 11, 2019 at 5:27
  • As described in Fazes first link, you cannot use an index on a calculated result; you have an index on location, but are evaluating on ST_GeomFromText(st_astext(location), 0). It's not entirely clear why you do this, so maybe add sample data and/or explain that conversion or just try to use "location" directly. Commented Jul 11, 2019 at 6:29
  • Check Mysql Spatial index unused. Commented Jul 11, 2019 at 9:48
Related questions
17
Use specific mysql index with rails
7
The used table type doesn't support SPATIAL indexes
7
MYSQL Using Spatial Index
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.