Databases
StackOverflow2013
Postsdbo
POMysql optimization for select query with IN() clause inside where clause (explain output given)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMysql optimization for select query with IN() clause inside where clause (explain output given)
    primarykey
    data
    text
    <p>I have this query:-</p> <pre><code>SELECT SUM(DISTINCT( ttagrels.id_tag IN ( 1816, 2642, 1906, 1398, 2436, 2940, 1973, 2791, 1389 ) )) AS key_1_total_matches, IF(( od.id_od &gt; 0 ), COUNT(DISTINCT( od.id_od )), 0) AS tutor_popularity, td.*, u.* FROM tutor_details AS td JOIN users AS u ON u.id_user = td.id_user JOIN all_tag_relations AS ttagrels ON td.id_tutor = ttagrels.id_tutor LEFT JOIN learning_packs AS lp ON ttagrels.id_lp = lp.id_lp LEFT JOIN learning_packs_categories AS lpc ON lpc.id_lp_cat = lp.id_lp_cat LEFT JOIN learning_packs_categories AS lpcp ON lpcp.id_lp_cat = lpc.id_parent LEFT JOIN learning_pack_content AS lpct ON ( lp.id_lp = lpct.id_lp ) LEFT JOIN webclasses AS wc ON ttagrels.id_wc = wc.id_wc LEFT JOIN learning_packs_categories AS wcc ON wcc.id_lp_cat = wc.id_wp_cat LEFT JOIN learning_packs_categories AS wccp ON wccp.id_lp_cat = wcc.id_parent LEFT JOIN order_details AS od ON td.id_tutor = od.id_author LEFT JOIN orders AS o ON od.id_order = o.id_order WHERE ( u.country = 'IE' OR u.country IN ( 'INT' ) ) AND u.status = 1 AND CASE WHEN ( lp.id_lp &gt; 0 ) THEN lp.id_status = 1 AND lp.published = 1 AND lpcp.status = 1 AND ( lpcp.country_code = 'IE' OR lpcp.country_code IN ( 'INT' ) ) ELSE 1 END AND CASE WHEN ( wc.id_wc &gt; 0 ) THEN wc.wc_api_status = 1 AND wc.id_status = 1 AND wc.wc_type = 0 AND wc.class_date &gt; '2010-06-16 11:44:40' AND wccp.status = 1 AND ( wccp.country_code = 'IE' OR wccp.country_code IN ( 'INT' ) ) ELSE 1 END AND CASE WHEN ( od.id_od &gt; 0 ) THEN od.id_author = td.id_tutor AND o.order_status = 'paid' AND CASE WHEN ( od.id_wc &gt; 0 ) THEN od.can_attend_class = 1 ELSE 1 END ELSE 1 END AND ( ttagrels.id_tag IN ( 1816, 2642, 1906, 1398, 2436, 2940, 1973, 2791, 1389 ) ) GROUP BY td.id_tutor HAVING key_1_total_matches = 1 ORDER BY tutor_popularity DESC, u.surname ASC, u.name ASC LIMIT 0, 20 </code></pre> <p>The numbers inside the IN() are actually ids of another table called Tags which matched the search keywords entered by users. In this example the user has searched "class".</p> <p>See the explain output of this query here:- <a href="http://www.test.examvillage.com/Screenshot.png" rel="nofollow noreferrer">http://www.test.examvillage.com/Screenshot.png</a></p> <p>The time taken by this query is 0.0536 sec</p> <p>But, if the number of values in the ttagrels.id_tag in () increases (as user enters more search keywords), the execution time rises to around 1-5 seconds and more.For example, if user searched "class Available to tutors and students 3 times a day"<br> the execution time is 4.2226 sec. The explain query output for this query contains 2513 under rows.</p> <p>There are a total of 6,152 records in the All_Tag_Relations table. Is any further optimization possible?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSandeepan Nath
    1. USSandeepan Nath
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMysql optimization for select query with IN() clause inside where clause (explain output given)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMysql optimization for select query with IN() clause inside where clause (explain output given)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMysql optimization for select query with IN() clause inside where clause (explain output given)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI know machines don't care about line breaks in SQL code, but it makes it *much* more readable to humans if you put them in.
      singulars
      1. POMysql optimization for select query with IN() clause inside where clause (explain output given)
      1. USlc.
    2. COyes I have added now
      singulars
      1. POMysql optimization for select query with IN() clause inside where clause (explain output given)
      1. USSandeepan Nath
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload