can somebody help me with this select? I'd like to know if I can make it faster?

SELECT distinct wb.id FROM websites AS wb
LEFT JOIN websites_meta_keys AS kw ON wb.id = kw.site_id
INNER JOIN websites_meta AS wm ON wm.site_id = wb.id
WHERE kw.keyword like '%test%' OR wm.title like '%test%'

6 Years
Discussion Span
Last Post by itsols

How many records do you have? 25 million? If it's just a couple of thousand, it shouldn't take anything more than 1 second. Make sure that the id field is indexed. If it's a primary key already, then no indexing is needed.
And now to something more serious. you have included this:

WHERE kw.keyword like '%test%' OR wm.title like '%test%'

This is bad. It will definitely slow your queries even if you had a thousand records. One improvement is to do this:

WHERE kw.keyword like 'test%' OR wm.title like 'test%'

In the above, we are looking for words starting with test. And now you can go one step further by indexing the columns title and keyword.
But the above will not meet your requirement if you wanted to search within the keywords and titles.
for this I have two suggestions.

  1. Create a separate table with single keywords and titles, and link them to the current tables. This is what I'd do but it's a little extra work.
  2. The eaiser way (but i don't still like this) is to setup full text searches. This has some restrictions - please check the documentation.
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.