… single-column indexes on them, MySQL could sometimes make use of the multiple indexes. For instance, “SELECT foo FROM bar WHERE indexed_colA… AND t.status = 1 LIMIT 1 Finally, we can use index hints. Index hints are exactly what they sound like – “help” for the… status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type) WHERE user_type=2…
Post: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 6, 2006 By 51 Comments
… yet. SESSION1: mysql> commit; Query OK, 0 rows affected (0.01 sec) SESSION2: mysql> select * from tst; Empty set (0.00 sec) mysql> select * from tst lock in share mode; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) mysql> select * from tst… these hints are very powerful and helpful for application development but should be used wisely. Do not assume you can simply add SELECT…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… is using GROUP BY WITH ROLLUP so I can get grouped result set together with total value for the groups: mysql> select grp… from single query ? Sure. Here is Way Number Three: mysql> select * from (select grp, count(*) cnt from dt where slack like “a%” group… I forced FileSort execution method for GROUP BY by using SQL_BIG_RESULT hint I can see GROUP BY executing about same…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… only have single column indexes (by hinting optimizer to ignore combined index) mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest… of the hint in MySQL which would allow forcing using index merge when MySQL does not think it should be used. I hope hint would be…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
…: const,const rows: 296338 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> explain select * from goods force index(cat… Extra: Using where 1 row in set (0.00 sec) As you can see if given no hint MySQL will prefer to use index… badly selective using index scan is much better idea. Until MySQL is able to handle this you will have to use force index hint. The…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… the part on which range condition is applied cannot be used for filtering records. For example, suppose you have a key…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
…find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, … the query. I noticed in production hints are used much more frequently than setting session variables …is executed, so I could do something like: SELECT SQL_SORT_BUFFER_SIZE=50000000 NAME FROM …
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
… values mysql> select max(id) from test2; +———+ | max(id) | +———+ | 220 | +———+ 1 row in set (0.00 sec) mysql> insert ignore into test2 select * from… in this example. Another hint – you may want to CHECK your MyISAM table you use for recovery after MySQL crashes to make sure…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you…, have extra space or use different case – these would be different queries for query cache. Only SELECT queries are cached SHOW commands… to “DEMAND” and use only SQL_CACHE hint for queries which you want to have cached – such as SELECT SQL_CACHE col from…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
…MySQL check WHERE clause while scanning table in index order. We can just use FORCE INDEX hint to override MySQL index choice: mysql> explain select…| 4 | NULL | 549117 | Using where | +—-+————-+——-+——-+—————+———+———+——+——–+————-+ mysql> select…
