mysql - 查询速度慢...需要更快

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有你的答案:

The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns that are tested in the query. The index entries act like pointers to the table rows, allowing the query to quickly determine which rows match a condition in the WHERE clause, and retrieve the other column values for those rows. All MySQL data types can be indexed.