CODEWITHSUNDEEP
mysqlsql
G-J
G-J

Reputation: 1068

Slow Query... Need it faster

To explain the query below, you should know that I have three variables for the current user that get used in the query...

$radius Preference the user wants to search in

$lat The user's latitude

$lon The user's longitude

The relevant columns in the zipData table are zipcode lon and lat

I have this query which will works fine to report all other users within the desired radius...

$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;
";

I also have this query which returns only specific users meeting certain criteria...

$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
";

both queries work independently; however, every attempt I have made to combine them into one query has resulted in a VERY slow execution time.

UPDATE

I do have indexes on seekers.postal zipData.lon and zipData.lat. I would have assumed that would do the trick but it didn't. It might just be that I built the query bad so I would like to see how the 2 queries should be combined.

Upvotes: 0

Views: 49

Answers (1)

Mawia HL
Mawia HL

Reputation: 3665

I think this link has your answer:

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.

Upvotes: 1

Related Questions