… optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=4 join_buffer_size=32M join_…as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey = l_suppkey WHERE s_nationkey=’…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… featured=1) Ouch! Use INNER JOIN for this instead because it allows the optimizer to use the indexes to join rows together, potentially preventing a table scan. The trick is to know what kind of JOIN to use so that the right number of rows are scanned and returned. SELECT * FROM articles INNER JOIN users ON…
Comment: MySQL Limitations Part 3: Subqueries
October 25, 2010 By 21 Comments
… readily available to a human, isn’t available to an optimizer. Consider: select * from food where id in (select food_id… user = me) The trivial rewrite is: select food.* from food inner join meals on food.id = meals.food_id where meals.user… have to know if the inner select will contain any duplicates. An application designer knows this. The optimizer cannot, although in certain…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
… CountryCode in ( select distinct co.Code from Country as co inner join CountryLanguage as cl on cl.CountryCode = co.Code where lower… count(distinct co.Code) into res from Country as co inner join CountryLanguage as cl on cl.CountryCode = co.Code where lower… to compare apples and uhm, apples… please comment). The poorly-optimized-subquery portion of the query essentially happens inside that function…
Comment: ORDER BY ... LIMIT Performance Optimization
November 17, 2009 By 100 Comments
… FROM posts force index (idx_post_date) INNER JOIN follow ON follow.followuserid = posts.userid … | Using index | +—-+————-+——–+——–+—————+—————+———+——————————-+——+————-+ Is there a way to optimize this query i have now been…
Comment: Cache Performance Comparison
August 10, 2006 By 74 Comments
… relations between the tables and use inner join for something like: SELECT t2.searchvector FROM t1 INNER JOIN t2 ON (t1.wordid = t2.wordid… I can make a wish 
) is how one can optimize searches using LIKE on a table with singlewords? In my…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… How MySQL 5.6 Makes Query Optimization Easier” for more tips on the 5.6 optimizer[/caption] While preparing the webinar I… the performance and inner workings of one of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP… to join me at the webinar I have prepared for this Friday, March 15: “Learn How MySQL 5.6 Makes Query Optimization…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
…’t set, using stdout. Using edit When you try to optimize a query, it often involves manipulating the text of the… query: mysql> select count(*) from film left join film_category using(film_id) left join category using(category_id) where name=’Music’; and let’s say you want to change the left joins to inner joins and…
Comment: JOIN Performance & Charsets
April 3, 2010 By 2 Comments
I have one problem with join. I need to reduce query time to it optimized level. Details are as under QUERY: SELECT… FROM products p LEFT JOIN product_images pi ON (p.productid = pi.imageprodid AND pi.imageisthumb = 1) INNER JOIN product_search ps ON…
Comment: MySQL Limitations Part 3: Subqueries
October 25, 2010 By 21 Comments
… a query hint that told the optimizer “eval the inner loop first” although I’d prefer the optimizer to be brighter. As the… times you are better off with a join, but not every case converts into join semantics easily or precisely and, frankly, a lot more people can write a logically correct inner select than…
