查询速度慢...更快地需要它

Question

为了解释下面的查询，您应该知道我在查询中使用了当前用户的三个变量...

用户要搜索的$radius首选项

$lat用户的纬度

$lon用户的经度

zipData表中的相关列是zipcode lon和lat

我有这个查询，它可以很好地报告所需半径内的所有其他用户...

$query="
    SELECT username FROM zipData,seekers
    WHERE (POW((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(lat-\"$lat\")),\"2\"))<($radius*$radius)
    AND replace(seekers.postal,' ','') = zipData.zipcode;
";

我也有这个查询，它只返回满足特定条件的特定用户...

$query="
    SELECT * 
    FROM
    (
        SELECT a.username, MATCH(a.highlight) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score 
        FROM resume_highlights a
        JOIN seekers ON a.username = seekers.username and seekers.resume_status = 1
        HAVING score>0 

            UNION ALL

        SELECT b.username, MATCH(b.skill,b.skill_list) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score 
        FROM resume_skills b
        JOIN seekers ON b.username = seekers.username and seekers.resume_status = 1
        HAVING score>0 

            UNION ALL

        SELECT c.username, MATCH(c.education_title,c.education_organization) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score 
        FROM resume_education c
        JOIN seekers ON c.username = seekers.username and seekers.resume_status = 1
        HAVING score>0 

            UNION ALL

        SELECT d.username, MATCH(d.employer_title,d.employer_organization) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score 
        FROM resume_employer d
        JOIN seekers ON d.username = seekers.username and seekers.resume_status = 1
        HAVING score>0 

            UNION ALL

        SELECT e.username, MATCH(e.volunteer_title,e.volunteer_organization) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score 
        FROM resume_volunteer e
        JOIN seekers ON e.username = seekers.username and seekers.resume_status = 1
        HAVING score>0 

    ) AS X
    ORDER BY score desc
";

这两个查询独立工作；但是，我每次尝试将它们组合到一个查询中都会导致执行时间非常慢。

更新

我确实有关于seekers.postal zipData.lon和zipData.lat的索引。我本以为这会起作用，但事实并非如此，可能是我的查询构建得不好，所以我想看看这两个查询应该如何组合。

Answer 1

我认为这个link有你的答案：

提高SELECT操作性能的最佳方法是在查询中测试的一个或多个列上创建索引。索引条目的作用类似于指向表行的指针，允许查询快速确定哪些行与WHERE子句中的条件匹配，并检索这些行的其他列值。可以对所有MySQL数据类型进行索引。