Mysql – Which query execution will be faster

join;MySQLmysql-workbenchsubquery

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

Best Answer

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.

Related Solutions

Mysql – Optimizing a query so MySQL will use the indexes for the joins

Here are my notices:

Make sure that creator exists only in table1, or add [... WHERE table1.creator=..]
It could be normal that the index on table2 is not used, specially if the majority or rows have the same value of the key.
From the profiling, sending data is the longest status. This means that the amount of data the query returns is huge (You mentioned 'long list of fields..'). Try to execute the query by selecting one field only, or be select count(*) and see if there is a significant change
Note that 400*270=108000, which is not a tiny number of scanned rows :)

MySQL – Subquery or Multiple Left Joins, or Both?

There are many ways to do this. Most common are with a LEFT JOIN / IS NULL check, NOT IN or NOT EXISTS subquery. Here's a solution with the 3rd option:

SELECT t2.id, t2.name 
FROM table2 AS t2
WHERE NOT EXISTS
      ( SELECT *
        FROM table3 AS t3
        WHERE t3.table1_id = @t1_id     -- the t1.id value (choice) that you want to check
          AND t3.table2_id = t2.id
      ) ;

Best Answer

Related Solutions

Mysql – Optimizing a query so MySQL will use the indexes for the joins

MySQL – Subquery or Multiple Left Joins, or Both?

Related Question