Which query is more profitable performance?

Hi all.
Brief description:
The database has a table with all coordinates of the Moscow metro (id, name, lat, lng) - lat and lng are latitude and longitude.
Also in the database there is another table, e.g. table organizations, it has a lot of fields, and also have lat and lng fields (the coordinates of this organization)

To get all subway in a radius of 2000 meters from the organization.

And here is the question: which of the solutions is better in performance?
Solution 1:
From the computed entry in the radius directly in the sql query:
example written for laravel, knowing I understand
$metro = \App\Metro::select(\DB::raw('*, (
 6378137 * acos (
 cos ( radians(' . $res['lat'] . ') )
 * cos( radians( lat ) )
 * cos( radians( lng ) - radians(' . $res['lng'] . ') )
 + sin ( radians(' . $res['lat'] . ') )
 * sin( radians( lat ) )
 ) AS distance'))
 ->having('distance', '<=', $res['radius_metro'])

Under this option, we will always have a fresh result, for example if we add a new subway station.

Solution 2:
To bring the search result to the nearest metro station in a separate table and periodically update it via cron for example.

Now interested in what is the best option to use, I like the 1st.
Planned heavily loaded portal

Any thoughts?
Thank you!
July 2nd 19 at 18:09
6 answers
July 2nd 19 at 18:11
Think about caching. I think the first option better, but it is necessary to connect the caching. And if something changes (New metro station) to clear the cache.
Already I think)) - karlie_Baumbach71 commented on July 2nd 19 at 18:14
July 2nd 19 at 18:13
The idea with the table - crazy, but the direction is right. You need to use Your query, but cache the results. I don't know laravel, but in Yii it is possible to make the cache dependent on a query count() for example, which will return the number of stations. And the cache can already be stored in the database or in the file - this is the second question, I think for this filecache is more suitable. It is possible in principle to make a cache eternal and clear when adding a new station.
Thanks for the comment.
Yes the second option I do not like)) but it is better to consider all of the solution than to redo everything)
By the way I forgot about caching in laravel it is laravel.su/docs/5.0/queries#caching-queries - karlie_Baumbach71 commented on July 2nd 19 at 18:16
: Probably in laravel it is and not worse than yii, I just don't know it deeply, dig deeper into the docks, surely there is a convenient solution out of the box - karlie_Baumbach71 commented on July 2nd 19 at 18:19
: Yes there is caching of the queries, everything works fine! In the admin area will be no caching, but outside of the already satirum) - Henderson_Beat commented on July 2nd 19 at 18:22
July 2nd 19 at 18:15
All really depends on what You personally mean by "heavily loaded portal" and many other factors, including, the used iron version of MySQL (or fork) and the final parameters and settings, and so on. And, the "quality" of the administrator to whom you will entrust this database.

Subjectively, assessing Your question, I would recommend something like this:
In the "Internet", actively practiced the approach of caching the data, no matter what kind of data it is. Cache everything from individual units on the site, to whole pages with cookie's, pictures and scripts.

In favor of the cache is indicated by many factors. Against the same cache usually provides two main arguments:
a) using too much RAM and/or hard drive
b) the Cache should not be used in cases where the resource consumption (machine) to be cached is higher than the savings from getting data from the cache (typically the case when the number of write cycles is less or azacitidine more than the number of cycles of read data)

In your case, caching is available in two versions:
1. MySQL (unlike some other database) itself caches the results of queries, and he does it by default, but caching settings can (and often should) configure additional
2. The use of the cache by using specially designed for this purpose software such as Redis or Memcached. PHP (and Laravel including, if my memory serves me) is friends with them.

Continuing the idea, in Your case, you can use one of two options: either to limit that MySQL itself cache level caches the results of many queries and he will take them from the cache (and this applies not only to queries with respect to the coordinates, but almost all other requests),

or, You can use to store such data (e.g., the ratio of coordinates of search results) or in the in-memory table (in MySQL there is a type of table in MEMORY) or to store this data in Redis/Memcached/etc, globally the essence will not change, the speed of both options (in-memory Table vs Redis/Memcached) to be roughly comparable.

To update the data in the repository based on events, implementation of which is sufficiently present in Laravel. That is, in a nutshell it looks like this:
1. Updated (added, changed), a data block associated with the firm, this in turn generates an event (one event)
2. The event listener receives the data and makes the appropriate changes in the data cache, so the cache is always current (or out of sync data is quite small, within about a second) and no CRON s that no purpose will torture database or cache vault is not needed.
Thank you for the detailed answer!
A lot of food for thought) read about Redis and Memcached - karlie_Baumbach71 commented on July 2nd 19 at 18:18
July 2nd 19 at 18:17
DBMS have to give the API simpler, no arc-tangents and quickly
Postgres can do that out of the box
Probably muscle too
At least compare their performance on your case

Pre-optimize don't need
July 2nd 19 at 18:19
How often add new metro or you can change the address of the company?
You have a table with data from metro data table of companies and another table with reference to the company, on the subway and the distance between them
In the last table, enter the data when creating\updating of the company\metro
You can, of course, to save the last table to store only the records that meet the condition (distance <= 2000 meters), but 2000 meters soon it will be 1500 meters or 2500 meters.

It is not clear at all what is there to talk about caches and high load if you have a choice (on the problem statement) will always be in the same organization and you no matter how many companies
July 2nd 19 at 18:21
You're talking about a large project, but are using MySql. Don't do that.
Dig into the PostgreSQL side, he's got a great, powerful tools for working with geo-data.

Find more questions by tags LaravelMySQLPHP