Hacker News Reader

Comment by unnouinceput | Hacker News Reader

For most of the projects where the DB really mattered, throughout my 10+ freelancer carrier, it came down to one thing that client really cared about. Performance. Nothing else mattered, not license price, not whistles and bells, not hype. My clients wanted to have data in front of their eyes the same second when they clicked the button. And when you have a table with 100 million rows in it, and an application is not loading data from that table to show it to its users in the same second, you already lost the battle.

Out of my tries, my clients tries, and my friends tries, only one DB was up to this task. Not Maria, not Access, not Oracle, not NoSQL, not MSSQL, not MySQL - all of them failed. The only one was PostgreSQL.

And before starting to bash me, please do this. Make a small application that will show a map, put 100 million points of interest on that map, that are contained in the table we talk about, and now as you scroll the map, select the middle of view as your circle and select on a small radius only those points of interest inside that radius. No more then a thousand points of interest, lets say. When you do that within a second, you got yourself a good database. For me PGSQL was the only one capable to do this reliable.

View on HN

While I do love PostgreSQL (and PostGIS which is excellent at transforming, indexing and querying geographic content) - I feel like MSSQL should get a bit of a shout-out in relation to your comment on performance.

An experienced database developer with help from "SET STATISTICS IO ON"[1] and query plans[2] can achieve incredible MSSQL query optimization results.

PostgreSQL has good query plan output via the EXPLAIN[2] statement - but I haven't (yet?) seen PostgreSQL produce per-query IO statistics.

[1] - https://docs.microsoft.com/en-us/sql/t-sql/statements/set-st...

[2] - https://docs.microsoft.com/en-us/sql/relational-databases/pe...

[3] - https://www.postgresql.org/docs/current/sql-explain.html

Configuration parameter `track_io_timing = 'on'` will measure I/O time. And running `EXPLAIN (ANALYZE, BUFFERS)` will output per plan node buffer statistics and I/O time spent. On most modern system IO timing has no measurable overhead and should be permanently enabled. Collecting buffer statistics is also relatively cheap and could be enabled for all queries. For example the following configuration will get a log entry with an explain plan and per node I/O stats for every query above a threshold:

    shared_preload_libraries = 'auto_explain'
    auto_explain.log_min_duration = '5s'
    auto_explain.log_analyze = true
    auto_explain.log_buffers = true
    auto_explain.log_timing = false

You don't need to log every auto explain. Just enable track_io_timing and pg_stat_statements and you get per query IO performance metrics much cheaper.

Table F.21. pg_stat_statements Columns

https://www.postgresql.org/docs/11/pgstatstatements.html

blk_read_time

double precision

Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

blk_write_time

double precision

Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

Thanks for this - 'pg_stat_statements' definitely looks like a similar feature, in particular because it reflects the count of disk-related operations (not just I/O time).

It took some brief configuration but I've been able to try it out locally and will refer to it when doing PostgreSQL query performance tuning in future.

PostgreSQL has track_io_timing and passing buffers will then include IO timing.

\set track_io_timing=on;

explain (analyze, verbose, buffers) your-query;

Additionally, it will also include things like times for triggers executed from the query.

In the end of the day, the PostgreSQL optimizer is just great, and will probably beat anything in a well balanced comparison.

But MSSQL also has a fairy good optimizer (from the ones on the GP relation, those are the only two good options) so you can set things to get better than an unset Postgres. It is also possible (but unlikely) that you get a problem where it fits better.

About Oracle, it is pretty great on doing `select stuff from table join table using (column)`. (I dunno what it does, but it does spend a lot of memory doing it.) So if you only does that, it's the best option. But if you do anything else, that anything else will completely dominate the execution time.

For me, MySQL’s quirkiness and lesser adherence to the SQL standard kind of neuter any performance niftiness it might have. I can see using MySQL for something like logging but for production data, I prefer PostgreSQL. It’s much more predictable.

as others have said, EXPLAIN (ANALYZE, BUFFERS, VERBOSE) will give you IO stats within your query at a very granular level.

It depends a lot on the implementation strategy. Some databases have GIS features, allowing you to query all points within a radius directly. I know oracle can do this, but it’s a paid addon (spatial and graph) and I don’t know how well it scales.

On databases that have good scanning behavior you can use a geohash approach instead. Insert all points with a geohash as key, determine a geohash bounding box that roughly fits your circle, query the rows matching that geohash in their key, run a “within distance” filter on the results client-side. I used that approach in hbase and it could query really fast. I suspect it would perform well on pretty much any mainstream sql db, but admittedly I haven’t tried it.

For true geospatial data models that correctly implement most geospatial predicates and operators your only practical option is PostGIS or Oracle Spatial. This is a very complex and nuanced piece of mathematics and software engineering work that requires writing a massive amount of code, even most closed source databases don't do a real implementation due to the difficulty. Most developers are unaware of just how broken most half-assed implementations are; the databases do just enough to check a box but not actually be useful if you are doing real geospatial work.

Geohashing has a lot of weaknesses and limitations, not really recommended unless your use case is very simple and not primarily geospatial in nature. The modern version was created in the 1990s and implemented by big database companies like Oracle and IBM (based on work in the 1960s and '70s). They deprecated that indexing feature in their database engines a long time ago for good technical reasons that apply to current open source implementations.

Geospatial index performance is very sensitive to temporal and geometry distribution, and the performance variance can be extremely high. For non-trivial applications, the real world will eventually produce data that inadvertently executes a denial of service attack on naive geospatial databases.

Postgres can be combined with the PostGIS addon (which is free) to get a reasonable feature set on top of PG with GIS features.

Yea PostGIS is a must if you plan on doing any sort of geospatial work with Postgres. PostGIS is way ahead of most other databases when it comes to processing geographic (3D) types. For example calculating a 100km buffer zone for 2D geometry types is easy, but only PostGIS can support buffer zone calculations on 3D geographic types. Because of the curvatures of the earth, the math is quite involved but it can make a big difference in accuracy

Oracle now has spatial that is included on the basic license and graph that is paid. Spatial solves a lot of GIS needs, but lacks some features, in special 3D objects.

MSSQL has a GIS package included in the basic database. I'm not sure how featurefull it is now, but last time I looked (years ago) it was missing a lot.

MySQL also has a GIS package included at the database. It's missing a lot of things.

The top of the line is, as usual, Postgres. But honestly, you probably won't need the difference.

MySQL also hast lots of GIS features, including r-tree spatial indexes.

Haha, commented at the same time and that's exactly the approach I've used!

I'm curious to hear why and how Maria failed you.

For a reddit-like social site, we have a Maria database with around 3 billion rows in total, with the largest table (votes) spanning about 1,5 billion rows.

We can select data within ~3ms for about 20k-40k concurrent visitors on the site. Our setup is a simple Master-Slave config, with the slave only as backup (not used for selects in production). Our 2 DB servers have 128gb of RAM and cost around 200 EUR/month each - so, nothing special.

Of course you have to design your schema with great care to get to this point, but I imagine PG to be as lost as any other database if a crucial index is missing.

This doesn't add up. 100M rows isn't a lot. All of those databases can do it, and commercial systems like MSSQL and Oracle have millions of R&D invested into performance and geospatial features.

It's not realistic that Postgres would outclass everything else like this.

Geoquery. That's a very difficult query to efficiently index: requires learning about WGS84 to get the right answer even for computing the radius.

All of these databases have geospatial data types for efficient querying and indexing. It's been a standard feature for years.

Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes.

> Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes.

Not that trivial if you're not just dealing with points.

So you have more complicated SQL then, but it doesn't make a difference between DBs selecting from a 100M table with indexes.

If it ends up being a full table scan then PG only recently got query compilation and parallel execution so at best it would be even with, but not better than, MSSQL/Oracle that can do vectorized executions on columnstores.

You need different index structures. Yes, it matters.

Hard to believe that spatial queries over geometries more complex than points will meaningfully benefit from "vectorized executions on columnstores".

Do you have an example query? If you're not using geospatial features, then what indexes does PG offer that the others can't match (either directly with indexes or with better scan/query performance)?

> Do you have an example query?

I mean, just about any geospatial query? E.g. finding all polygons in a table that overlap with one specified in a query. https://postgis.net/docs/ST_Overlaps.html

> If you're not using geospatial features,

Why are you talking about not using geospatial features? I was just responding to your

> Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes.

statement. Which I still think is not a meaningful alternative. And then subsequently to your statement about vectorized columnstores.

I'm not all saying that only postgres has geospatial indexing support (although postgis is pretty good) - that would be ridiculous.

> then what indexes does PG offer that the others can't match (either directly with indexes or with better scan/query performance)?

I do think that postgres' indexing support (gist, gin in particular) makes it easier to add support for indexing types of data that are harder to index in many other databases. But it's nothing that can't be beat.

The point is that without geospatial features, you can still use other strategies like lat/long or geohash and they will all have the same performance.

The logic (built-in geospatial or hand-written sql) is irrelevant to the performance comparison and GP's post that Postgres was the only one that could query quickly.

Do you disagree with that? If not then I'm not sure what your argument is.

This.

TBH, that should be easily doable for any database. I've built something like this with SQLite and on another occasion with Elasticsearch (where it's obviously much easier). If you leverage Geohash, and precalculate the geohash for every location, you don't even need any builtin geo-capabilities from the database.

SQLite now supports R*Trees for spatial queries.

https://sqlite.org/rtree.html

>And before starting to bash me, please do this. Make a small application that will show a map, put 100 million points of interest on that map, that are contained in the table we talk about, and now as you scroll the map, select the middle of view as your circle and select on a small radius only those points of interest inside that radius. No more then a thousand points of interest, lets say. When you do that within a second, you got yourself a good database. For me PGSQL was the only one capable to do this reliable.

This is a bad example. An SQL database is a wrong tool for the job if you care about performance.

You want a data structure like a range tree[1] or a k-d tree[2]. The results would be near-instantaneous.

100 million lat/lng pairs is something you can fit in RAM without any problem. And you can even do the geometry query on the client side for performance in milliseconds.

Other examples might need SQL, but here, this would be doing things the wrong way.

[1]https://en.wikipedia.org/wiki/Range_tree

[2]https://en.wikipedia.org/wiki/K-d_tree

While SQL will not necessarily give you the optimal solution, a btree index on a geohash will still give you O(log(n)) lookups on spatial data, which will be good enough for most use cases, and pretty much every database engine in existence can create an index on a string, which is all you need for that solution.

Sorry, it is not at all obvious to me how you can implement a query of the form "all locations within 100 miles of lat/lng" with a geohash (as in, "give me all stores within driving distance").

If you mean that nearby places have geotags with large common prefixes, then the "edge" cases there this won't work include such little-known, unpopular cities as London, UK (thorough which the Greenwich meridian passes).

That's to say, East and West London geohashes have no common prefix. The Uber driver picking you up in Greenwich will drop you off in a geohash which starts with a different letter.

To point out the obvious, the surface of Earth is two-dimensional, and a database index is one-dimensional. A solution to the 2D query problem using a 1D index is mathematically impossible (if existed, you would be able to construct a homeomorphism between a line and a plane, which does not exist).

Sure, geohashes can be used to compute the answer. But you need to know more information than just the letters in the geohash to find out which geohash partitions are adjacent to a given one. That extra information would be equivalent to building a space partitioning tree, though, and you still won't be able to use a range query to get it.

If there's an SQL range query-based approach with geohashes which compromises on correctness but is still practical, I'm all ears.

---

PS: this is a good overview of this problem:

https://dev.to/untilawesome/the-problem-of-nearness-part-1-g...

The solution there involves first producing a geohash cell covering. Of course, this means you have a BSP-like structure backed by a database.

---

PPS: the quick-and-dirty way using SQL would be keeping a table with lat / lng in different columns. It is not hard to define a lat/lng box around a point that has roughly equal sides, using basic trig.

You can filter locations outside the box with SQL, and then just loop over the results to check distances and fine-tune the answer (if needed). However, it will not be performant.

Well, a client invited me to evaluate PostgreSQL. Strong MSSQL shop, thousands of databases, millions of customers, tens thousands internal users. Big pile of licensing money they were eager to save.

We investigated the options for LDAP and Kerberos integration, commercial and free. Turns out there is no decent way to do it and grant permissions based on LDAP groups. There wasn't even a a half decent way. The only options were way to hackish to consider. MS SQL still own the house.

Why would you want LDAP integration for a database? Maybe that kind of messed up requirement is part of the problem?

One of the bigger issues with places that have made themselves dependent on MS solutions is that MS software completely permeates the place. Then when the pain of going full MS is too big, they are only looking for drop in replacements of existing parts. This will never go well. MS software never plays well with alternatives.

If you have let yourself slide too far down the slippery slope of MS Office integrations and MS Sharepoints and the like, you will have to pay the MS price. Or you have to be willing to chuck out the entire lot.

Because certain groups of users need the same sets of permissions on hundreds of databases and servers. And the members of those groups are constantly changing. In addition this ties an identity across system and facilitates SSO on the one hand and auditing on the other. There's more to it but these are the most obvious.

This is a common, standard requirement in enterprise systems, and it seem quite sensible. If you have a better, more robust solution, there are billions in this market, in the most literal way.

One more thing, although Active Directory is a standard in the enterprise, LDAP is an open protocol that existed before Microsoft were dreaming on being a player in the enterprise field and have at least half a dozen open source implementations.

> Because certain groups of users need the same sets of permissions on hundreds of databases and servers. And the members of those groups are constantly changing. In addition this ties an identity across system and facilitates SSO on the one hand and auditing on the other. There's more to it but these are the most obvious.

That is precisely what I meant with messed up requirements. Why would any of your SSO be relevant on the database level? A database user should be coupled to an application, not to a single user in AD. If you are letting constantly changing users do their own SQL requests on DB level, there is something rotten in the first place.

> This is a common, standard requirement in enterprise systems, and it seem quite sensible. If you have a better, more robust solution, there are billions in this market, in the most literal way.

I have never come across this kind of setup in a normal enterprise environment. There, you have everything behind some kind of enterprise software. Data entry, auditing, etc.

However, you could have need for this if we are talking developers sharing a single database and you don't want to manage accounts and passwords separately.

You said the solutions you found for Postgres were too "hacky". I think if you have a setup where enterprise users who are not developers or DBAs need personal access on DB level, your setup is quite "hacky" to begin with.

> One more thing, although Active Directory is a standard in the enterprise, LDAP is an open protocol that existed before Microsoft were dreaming on being a player in the enterprise field and have at least half a dozen open source implementations.

Microsoft has bent LDAP and Kerberos in its own way. Trying to use AD like an LDAP server is full of unpleasant surprises dealt to you by badly documented or outright undocumented "features", and useless error messages and logging. Believe me, it's a major nightmare to get anything integrated with AD.

There are tens of DBAs, hundreds of developers, hundreds of application users, those are the users in those groups.

I have done my fair share of integration with AD, including in some of the largest enterprises existing on this planet, where you have hundreds of groups of developers and admins. I read the RFCs and the documentation, in most cases, it is actually quite good and detailed, both on their LDAP and on their Kerberos implementation details.

> I have never come across this kind of setup in a normal enterprise environment. There, you have everything behind some kind of enterprise software. Data entry, auditing, etc.

Well, for you everything is behind the enterprise software, but I'm among those that need implementing it.

> There are tens of DBAs, hundreds of developers, hundreds of application users, those are the users in those groups.

Ah, so this is a special case with a shop that has developers and DBAs in their user base. Now that I can understand.

> I read the RFCs and the documentation, in most cases, it is actually quite good and detailed, both on their LDAP and on their Kerberos implementation details.

Then you know what a giant mess all of it is. I have done my share of LDAP/AD integration as well and found that LDAP is a very malleable tool that has been bent in many ways. There are many details to get right. MS use a certain subset of schemata and have added their special flavoured behaviour in the background. That means, what the AD admin sees is not exactly what an LDAP query might return. Similarly Kerberos or any other authentication scheme like e.g. SAML. Getting all of this right from the view of a third party application is not simple. That's why I'm never surprised why pretty much all of them are incomplete or incorrect in this manner.

Oracle offers a similar integration for AD. Do you happen to have any experience with it and if so, how does it compare to MS SQL?

I was never asked to give a hand in Oracle integration, hence I believe it is quite straight forward.

In my experience usually third party tools, i.e. ldapsearch, don't implement all AD options, if will use them for a LDAP query with AD features you will receive legitimate reply, but they will not be humanly readable as they were not parsed by the tool. However, the documentation from Microsoft in this front is actually surprisingly extensive and if anyone felt like it they could implement the correct parsing. It will also quite safe to assume at this point that intentional changes in the future in those areas are extremely unlikely.

Reading and understanding the LDAP and Kerberos implementation is quite a task, and I can fully understand why no one sat to re-implement it all in open source.

Most of the time, even features that are implemented in open source libraries, for stuff like GSSAPI as an example, are not implemented in the software that uses them. And in this I find PostgreSQL is extremely lacking.

In comparison, Apache lets you use LDAP groups for ACLs, NGinx have a unmaintained plugin stripped from the Apache base code and it only implements support for LDAP users, not groups. PostgreSQL will only let you authenticate users that match users that you already defined in PostgreSQL.

I'm far from being Microsoft fan in general, and I suffer whenever I have to use their OS. But AD is a robust piece of work and pretty much the only game in town. I feel that is is not in their focus right now, as is isn't part of their everything Azure strategy, and that's a shame.

This is very typical in an enterprise shop where you have end users connecting directly to the database for reporting purposes. Being able to take advantage of your existing directory structure and security groups is huge.

Why do end users have direct access to a database? What do they do with it? Copy and paste from their Excel sheets into a GUI database client? Why is there no layer of software in between?

Situations where you have enterprise setups have users measured in the 10s of thousands.

There will be existing tooling around ACL management (typically AD). User Access Management groups, geo-separated local account manager, multiple help-desks to allow for disaster plans (pandemics, fires, localized grid failures).

Access can be as simple as:

- my application will use a system-account, and we'll manage access

- my application will pass user credentials (based on AD), these are the groups (based on AD) that have read/write/delete access; regional team leaders will request access using existing UAM, regional managers will authorize requests, your existing tooling will process those requests as you are the designated authority over those groups

Changing this requires either exceptional levels of observable returns on effort, or extreme will-power and political power.

During evaluation of MS SQL / PostgreSQL / whatever, fitting into existing UAM will be a requirement.

Because the world and especially the enterprise world is full of smart analysts who aren't programmers but who are pretty hot shots with SQL and Excel. They can answer a LOT of ad-hoc business queries just with those two tools and don't need expensive slow programmers to spend 3 months twiddling JIRA tickets when they could get the same answer in a few hours, when they have direct database access.

Exactly. Why should I have to rewrite a whole ACL infrastructure when I can store it all in one place, and it doesn't matter how you access the database, it's all enforced.

LDAP is amazingly convenient. Why not centralize the user/group structure and use it for db access as well?

Would enabling LDAP auth at PAM level be too hackish for the purpose ?

Not at all if was possible. But PosgtreSQL only map users that already exists in PostgreSQL to LDAP users, matching the user name. And there is no way to map the groups. That's ridiculous.

The hackish implementations are syncing users from LDAP to PostgreSQL, on schedule. There are more than 3 different implementations of this madness.

I've done a lot of DBA'ing as a dev/sysadmin/SRE in the past 25 years. While performance matters greatly, there are two features that make me prefer PostgreSQL and that I would rate higher: stability and maintainability.

I have mainly used SQL Server but some Oracle and Informix earlier. I think all of these has been rock solid. When working as a consultant you rarely hear anyone saying that the db was instable.

Heh, I agree with you minus one consulting case: over a span of 10 years, my client had built an unholy disaster of Oracle PL/SQL on top of their not-great schema. While I wouldn’t call it unstable (queries did always return), query performance would vary dramatically even with the same inputs on an isolated install. I spent more time than I should have trying to track it down, but ultimately gave up.

What was my task? Add XML export into a poorly done XML schema, and do it all in PL/SQL; the data that went into the XML was a mixture of normal queries and computed PL/SQL results.

That schema + stored procedure monstrosity sticks out in my mind as the most unholy disasters I’ve ever worked on.

Not sure what strategy you tried to retrieve the data but I did this kind of stuff years ago with MSSQL even before it had geospatial types and it was fast.

If you have LAT/LON in the table as normal columns indexed you just query the bounding box of your radius so it can use the standard b-tree indexes then post filter to a circle cutting off the corners. Of curse you have to do some work to account for the curvature of the earth but this is pretty standard geospatial stuff.

If you have a DB with geospatial types and indexes like MSSQL 2008+, Oracle, PG etc then this becomes trivial as they can do this directly.

This is quite a specific thing though.

If you want to do spatial things in the database, then PostGIS for PostgreSQL or Spatialite for SQLite are definitely your best options.

For general performance, PostgreSQL is consistently excellent, but it's not the best in all cases.

That might be selection bias: those who look for you are mostly concerned about performance. And performance is usually not "solved" -- it's often a treadmill where hardware and software performance is trying to outrun the data growth -- so people who need performance are constantly coming back for more (usually with a pile of cash).

A lot of users case mostly about accessibility, simplicity, integration with existing experience/tools (e.g. JSON), managability, ecosystem, etc. Those users might be happy from the start; or if they aren't happy, they complain for a while until their problem is solved, and then go silent.

I think that clients also care about data consistency and safety too, they just don't know that they care about it until they don't have it.

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)

Reply on news.ycombinator.com