Mysql can do this just fine with a geospatial index, or, for that matter, with a geohash index[1] (though in the case of a geohash index, you will need a somewhat more complex query). In fact, any database at all that can index on a string (i.e. all of them) can do efficient location-based search.
Honestly, if it's taking your database more than a second to pull 1000 of 100M rows on a simple query, that means you need to figure out what's wrong with your indexes, not that your choice of database vendor is bad. It is true that postgres is a very nice database, but for basic tasks, honestly any database will be fine if you know how to use it effectively.
Demo in mysql:
-- create a helper table for inserting our 100M points
create table nums (
id bigint(20) unsigned not null
);
insert into nums (id)
values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
-- the table in which we will store our 100M points
create table points (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
latitude float NOT NULL,
longitude float NOT NULL,
latlng POINT NOT NULL,
primary key (id),
spatial key points_latlng_index (latlng)
);
-- insert the 100M points with latitude between 32 and 49
-- and longitude between -120 and -75, which is roughly a
-- rectangle covering the continental US, placing them
-- randomly. Be patient, as it takes a while to create
-- 100M rows.
insert into points (latitude, longitude, latlng)
select
ll.latitude,
ll.longitude,
ST_GeomFromText(concat('POINT(',
ll.latitude, ' ', ll.longitude,
')')) as latlng
from (
select
32 + 17 * conv(left(sha1(concat('latitude', ns.n)), 8), 16, 10) / pow(2, 32) as latitude,
-120 + 45 * conv(left(sha1(concat('longitude', ns.n)), 8), 16, 10) / pow(2, 32) as longitude
from (
select
a.id * pow(10, 0) + b.id * pow(10, 1)
+ c.id * pow(10, 3) + d.id * pow(10, 4)
+ e.id * pow(10, 5) + f.id * pow(10, 6)
+ g.id * pow(10, 7) + h.id * pow(10, 8)
as n
from nums a join nums b join nums c join nums d
join nums e join nums f
join nums g join nums h
) as ns
) as ll;
-- Generate a roughly circular boundary polygon 7km around
-- the center of Las Vegas
set @earth_radius_meters := 6371000,
@lv_lat := 33.17,
@lv_lng := -115.14,
@search_radius_meters := 7000;
set @boundary_polygon := (select
ST_GeomFromText(concat('POLYGON((', group_concat(
concat(boundary_lat, ' ', boundary_lng)
order by id
separator ', '), '))')
) as boundary_geom
from (
select
nums.id,
@lv_lat + @search_radius_meters * cos(nums.id * 2 * pi() / 9)
/ (@earth_radius_meters * 2 * pi() / 360) as boundary_lat,
@lv_lng + @search_radius_meters * cos(@lv_lat * 2 * pi() / 360) * sin(nums.id * 2 * pi() / 9)
/ (@earth_radius_meters * 2 * pi() / 360) as boundary_lng
from nums
) t);
explain select count(*) from points where ST_Contains(@boundary_polygon, latlng)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: points
partitions: NULL
type: range
possible_keys: points_latlng_index
key: points_latlng_index
key_len: 34
ref: NULL
rows: 987
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.01 sec)
mysql> select count(*) from points where ST_Contains(@boundary_polygon, latlng)\G
*************************** 1. row ***************************
count(*): 1228
1 row in set (0.01 sec)