In the following queries, which query execution will be faster. When I tried executing them in SQLWorkbench, each time query execution time is varying.

Also, can you guys suggest some better tools for analyzing the query execution time?

Indexes are present on id's column in both the tables. The rows scan on both the tables is the same for these 2 queries.

SELECT DISTINCT sng_id FROM table1 WHERE id in (SELECT DISTINCT id from table2 WHERE LOWER(comment) LIKE '%home%')

SELECT DISTINCT sng_id FROM table1 AS a JOIN table2 AS b ON a.id = b.id WHERE LOWER(b.comment) LIKE '%home%'

migrated from stackoverflow.com Nov 13 '18 at 9:21

This question came from our site for professional and enthusiast programmers.

  • My current generic comment re "better"/"best" etc: There's no such thing as "better"/"best" in engineering unless you define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". meta.stackexchange.com/q/204461 – philipxy Nov 13 '18 at 9:07
  • How big is each table? Which version of MySQL? – Rick James Nov 13 '18 at 23:41
  • In searching for "home" that way, do you realise you will get "homework" and "homecoming"? – Rick James Nov 13 '18 at 23:42
  • It has 2137 Rows in the table, yeah i've an idea on that it will give both "homework" & "homecoming" – Venkatesh_CTA Nov 14 '18 at 10:53

In normal circumstances the JOIN is the structure that would be recommended for performance reasons. However, it looks like you're attempting some kind of search functionality.
Btree indexes don't work well with the kind of filtering you're implementing:

WHERE LOWER(b.comment) LIKE '%home%'

Using a function on the data (LOWER) will render any index in place useless as well as the wildcard filtering %string%. The query will likely run okay on small tables, but will suffer the longer it gets more rows that are scanned. This access type is eased with Fulltext indexing and relative matching rather than table scanning.

You might also try using a dedicated search product with inverted indexing like Elasticsearch, Solr or Sphinx.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.