Member Avatar for Budlight

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%'

  • 2 Contributors
  • 1 Reply
  • 109 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by itsols
Member Avatar for 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.
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.