London Smith London Smith - 2 months ago 16
MySQL Question

Subquery for faster result

I have this query which takes me more than 117 seconds on a mysql database.

select users.*, users_oauth.* FROM users LEFT JOIN users_oauth ON users.user_id = users_oauth.oauth_user_id WHERE (

(MATCH (user_email) AGAINST ('sometext')) OR
(MATCH (user_firstname) AGAINST ('sometext')) OR
(MATCH (user_lastname) AGAINST ('sometext')) )

ORDER BY user_date_accountcreated DESC LIMIT 1400, 50


How can I use a subquery in order to optimize it ?

The 3 fields are fulltext :

ALTER TABLE `users` ADD FULLTEXT KEY `email_fulltext` (`user_email`);
ALTER TABLE `users` ADD FULLTEXT KEY `firstname_fulltext` (`user_firstname`);
ALTER TABLE `users` ADD FULLTEXT KEY `lastname_fulltext` (`user_lastname`);


There is only one search input in a website to search in different table users fields.

If the limit is for example
LIMIT 0,50
, the query will run in less than 3 seconds but when the LIMIT increase the query becomes very slow.

Thanks.

Answer

Use a single FULLTEXT index:

FULLTEXT(user_email, user_firstname, user_lastname)

And change the 3 matches to just one:

MATCH (user_email, user_firstname, user_lastname) AGAINST ('sometext')

Here's another issue: ORDER BY ... DESC LIMIT 1400, 50. Read about the evils of pagination via OFFSET . That has a workaround, but I doubt if it would apply to your statement.

Do you really have thousands of users matching the text? Does someone (other than a search engine robot) really page through 29 pages? Think about whether it makes sense to really have such a long-winded UI.

And a 3rd issue. Consider "lazy eval". That is, find the user ids first, then join back to users and users_oauth to get the rest of the columns. It would be a single SELECT with the MATCH in a derived table, then JOIN to the two tables. If the ORDER BY an LIMIT can be in the derived table, it could be a big win.

Please indicate which table each column belongs to -- my last paragraph is imprecise because of not knowing about the date column.

Update

In your second attempt, you added OR, which greatly slows things down. Let's turn that into a UNION to try to avoid the new slowdown. First let's debug the UNION:

( SELECT *   -- no mention of oauth columns
    FROM users    -- No JOIN
    WHERE users.user_id LIKE ...
    ORDER BY user_id DESC
    LIMIT 0, 50
)
UNION ALL
( SELECT *   -- no mention of oauth columns
    FROM users
    WHERE MATCH ...
    ORDER BY user_id DESC
    LIMIT 0, 50
)

Test it by timing each SELECT separately. If one of the is still slow, then let's focus on it. Then test the UNION. (This is a case where using the mysql commandline tool may be more convenient than PHP.)

By splitting, each SELECT can use an optimal index. The UNION has some overhead, but possibly less than the inefficiency of OR.

Now let's fold in users_oauth.

First, you seem to be missing a very important INDEX(oauth_user_id). Add that!

Now let's put them together.

SELECT u.*
    FROM ( .... the entire union query ... ) AS u
    LEFT JOIN users_oauth ON users.user_id = users_oauth.oauth_user_id
    ORDER BY user_id DESC   -- yes, repeat
    LIMIT 0, 50             -- yes, repeat