Timeline for Best practice design when expecting large data in a table
Current License: CC BY-SA 3.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 4, 2015 at 17:05 | comment | added | Morgan Tocker | MySQL 5.7 (in development) contains spatial datatypes for InnoDB. You can try it out at dev.mysql.com/downloads/mysql (Click on "Development Releases") | |
| Oct 14, 2014 at 1:18 | history | edited | Michael Green |
Added tag.
|
|
| Oct 14, 2014 at 1:17 | answer | added | Michael Green | timeline score: 1 | |
| Oct 12, 2014 at 20:54 | comment | added | Just Lucky Really | Apologies for not supplying the db ... I'm using MySQL. And after researching a little more, it does seem that I could achieve the same result by indexing. My idea now, is to have the 'grid square' number as the index, rather than separate tables, and see how that goes. As for the caching idea, it's not something I'd be able to do, as the given longitude and latitude will be a users precise location, rather than the location of a city ... My bad for not making this clear | |
| Oct 12, 2014 at 10:21 | comment | added | Michael Green | Have you considered caching distances once they are calculated? Once one user has requested a pair of cities every other user will get the same result as the distance. Even with its level of seismic activity Tokyo is not moving all that quickly! With 20 billion plus combinations you may want to be selective and only cache the most popular combinations (London-Paris, New York-San Francisco?) The primary key to the cache table will be two city IDs so the index read will be quick. | |
| Oct 12, 2014 at 1:09 | history | tweeted | twitter.com/#!/StackDBAs/status/521105229043875840 | ||
| Oct 11, 2014 at 19:33 | answer | added | Jonathan Fite | timeline score: -1 | |
| Oct 11, 2014 at 19:13 | comment | added | John Powell | Which db? There are very good spatial functions and spatial indexes in Postgres, Oracle and SQL Server, MySQL will require a bit more work. Spatial datatype and indexes avoid the need to search far away objects, so you don't need to do n^2 comparisons for a nearest location and you don't need to chop your db up into hundreds of tables, either. | |
| Oct 11, 2014 at 16:59 | review | First posts | |||
| Oct 12, 2014 at 3:34 | |||||
| Oct 11, 2014 at 16:56 | history | asked | Just Lucky Really | CC BY-SA 3.0 |