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
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`);
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_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.
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
( 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
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