介绍

我有198305个的地理编码的葡萄牙邮政代码如下SQLite表：

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公里。

问题

该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.353公里（这是比我过去定义为最大的1公里更大WHERE ）。

如果我把以下条款：

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

现在，查询需要近6秒，并返回2048条记录（我LIMIT通过订购） 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是这样做的：

利用指数coordinates的边界之外滤除记录在WHERE子句
由过滤器的记录"distance" <= 1 WHERE子句， 但distance仍然是NULL => 0 ！
填充“代码”和“距离”（通过调用UDF首次）
通过的“距离”（这是由现在填充）顺序
限制记录

我想这样的SQLite做：

利用指数coordinates的边界之外滤除记录在WHERE子句
这些记录，填写code和distance通过调用UDF
由过滤器中的记录"distance" <= 1 WHERE子句
为了用“距离”（不再次调用UDF）
限制记录

谁能解释我如何才能让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:

这也返回873条记录，通过有序的distance在〜0.04秒：

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
    GROUP BY "code"
        HAVING "distance" <= 1
    ORDER BY "distance" ASC
LIMIT 2048;

之所以这个页面不具有GROUP BY子句是MySQL的具体：

HAVING子句可以指任何列或在SELECT列表或外子查询一个select_expr命名别名 ，和聚集的功能。但是，SQL标准要求HAVING必须引用仅列在GROUP BY子句或聚合函数中使用的列。为了适应标准的SQL和能指的SELECT列表中的MySQL特定的行为外的子查询中，MySQL 5.0.2和更高版本允许具有引用列在SELECT列表，BY子句中的列组，列，并聚集函数。

如果没有主/唯一键是可用的，下面的技巧也可以（虽然有点慢 - 〜0.16秒）：

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
    GROUP BY _ROWID_
        HAVING "distance" <= 1
    ORDER BY "distance" ASC
LIMIT 2048;

Answer 2:

这个查询（ 提供@OMGPonies ）：

SELECT *
    FROM (
        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
    )
        WHERE "distance" <= 1
    ORDER BY "distance" ASC
LIMIT 2048;

正确返回873个记录，通过有序的distance在〜0.07秒。

不过，我仍然不知道为什么SQLite不评估geo()将在WHERE子句中，像MySQL ...

Answer 3:

我不能告诉从文档是否sqliteCreateFunction定义的集合，如SUM ，或者一个标量，像sqrt 。聚合函数不能在被引用WHERE子句; HAVING是必需的。

每SQLite的UDF文件，你需要知道，如果填充只xFunc，或者特步和xFinal是。这些都是指针SQLite的使用要知道你定义的那种功能，因此无论是否兑现它在WHERE子句。

Answer 4:

基本上，我使用sprintf()看看有什么样的边界坐标被计算在那里，因为我不能上运行PHP比其他任何地方（因为UDF的），我产生与预处理语句另一个查询的查询。问题是，我并没有产生最后的绑定参数（公里的distance <= ?子句）我被我骗了sprintf()版本。

我想我不应该试图代码时，我困了。我对你们的浪费的时间真的很抱歉，谢谢大家！

只是为了保持完整性，以下命令返回的缘故（正确！）873分的记录，在〜0.04秒：

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;

文章来源: SQLite - WHERE Clause & UDFs