SQLite-WHERE子句和UDF

Question

我有下面的SQLite表，其中包含198,305个地理编码的葡萄牙邮政编码：

CREATE TABLE "pt_postal" (
  "code" text NOT NULL,
  "geo_latitude" real(9,6) NULL,
  "geo_longitude" real(9,6) NULL
);

CREATE UNIQUE INDEX "pt_postal_code" ON "pt_postal" ("code");
CREATE INDEX "coordinates" ON "pt_postal" ("geo_latitude", "geo_longitude");

我在PHP中还具有以下用户定义的函数，该函数返回两个坐标之间的距离：

$db->sqliteCreateFunction('geo', function ()
{
    if (count($data = func_get_args()) < 4)
    {
        $data = explode(',', implode(',', $data));
    }

    if (count($data = array_map('deg2rad', array_filter($data, 'is_numeric'))) == 4)
    {
        return round(6378.14 * acos(sin($data[0]) * sin($data[2]) + cos($data[0]) * cos($data[2]) * cos($data[1] - $data[3])), 3);
    }

    return null;
});

只有 874个 记录的距离38.73311, -9.138707小于或等于1 km。

问题

UDF在SQL查询中可以完美地工作，但是由于某些原因，我不能在WHERE子句中使用它的返回值-例如，如果我执行查询：

SELECT
    "code",
    geo(38.73311, -9.138707, "geo_latitude", "geo_longitude") AS "distance"
    FROM "pt_postal" WHERE 1 = 1
        AND "geo_latitude" BETWEEN 38.7241268076 AND 38.7420931924
        AND "geo_longitude" BETWEEN -9.15022289523 AND -9.12719110477
        AND "distance" <= 1
    ORDER BY "distance" ASC
LIMIT 2048;

它返回1035条记录， _ 排序distance时间约为0.05秒， _但是
最后一条记录的距离为1.353km（大于我定义为最后一条的最大1 km WHERE）。

如果我删除以下子句：

AND "geo_latitude" BETWEEN 38.7241268076 AND 38.7420931924
AND "geo_longitude" BETWEEN -9.15022289523 AND -9.12719110477

现在，查询将花费近6秒钟的时间，并返回LIMIT排序的2048条记录（我的）distance。本来应该花很长时间，但它只应返回具有的
874条记录"distance" <= 1。

该EXPLAIN QUERY PLAN原始查询返回：

SEARCH TABLE pt_postal USING INDEX coordinates (geo_latitude>? AND geo_latitude<?)
#(~7500 rows)
USE TEMP B-TREE FOR ORDER BY

并且没有坐标边界：

SCAN TABLE pt_postal
#(~500000 rows)
USE TEMP B-TREE FOR ORDER BY

我想做什么

我想我知道为什么会这样，SQLite正在这样做：

1. 使用索引coordinates过滤掉WHERE子句中边界之外的记录
2. 通过"distance" <= 1 WHERE子句过滤那些记录， _ 但是distance仍然NULL => 0_！
3. 填充“代码”和“距离”（通过首次调用UDF）
4. 按“距离”排序（目前已填充）
5. 限制记录

我想要SQLite做什么：

1. 使用索引coordinates过滤掉WHERE子句中边界之外的记录
2. 对于这些记录，填充code并distance通过调用UDF
3. 通过"distance" <= 1 WHERE子句过滤记录
4. 按“距离”排序（无需再次调用UDF）
5. 限制记录

谁能解释我如何使SQLite表现出（如果可能的话）我想要的方式？

后记

出于好奇，我尝试对两次调用UDF的速度进行基准测试：

SELECT
    "code",
    geo(38.73311, -9.138707, "geo_latitude", "geo_longitude") AS "distance"
    FROM "pt_postal" WHERE 1 = 1
        AND "geo_latitude" BETWEEN 38.7241268076 AND 38.7420931924
        AND "geo_longitude" BETWEEN -9.15022289523 AND -9.12719110477
        AND geo(38.73311, -9.138707, "geo_latitude", "geo_longitude") <= 1
    ORDER BY "distance" ASC
LIMIT 2048;

令我惊讶的是，它仍然在约0.06秒内运行-并且仍然（错误地！）返回了1035条记录。

似乎第二个geo()电话甚至都没有被评估…但是应该，对吧？

Answer 1

基本上，我sprintf()用来查看要计算哪种边界坐标，并且由于无法在PHP以外的任何地方运行查询（由于UDF），因此我正在使用准备好的语句生成另一个查询。问题是，我没有生成最后一个绑定参数（distance <= ?子句中的公里数），而被我的sprintf()版本愚弄了。

猜猜我在困倦时不应该尝试编码。对于您所浪费的时间，我们深表歉意，谢谢大家！

为了完整起见，以下记录（正确！）在约0.04秒内返回了873条记录：

SELECT "code",
    geo(38.73311, -9.138707, "geo_latitude", "geo_longitude") AS "distance"
    FROM "pt_postal" WHERE 1 = 1
        AND "geo_latitude" BETWEEN 38.7241268076 AND 38.7420931924
        AND "geo_longitude" BETWEEN -9.15022289523 AND -9.12719110477
        AND "distance" <= 1
    ORDER BY "distance" ASC
LIMIT 2048;