…of conditions which was *always* used by this query. We wouldn’t see, for instance, this query looking…the same time ends up taking longer – if we stopped after retrieving the 17k …MySQL should be looking at, we can rewrite the query like so, with the following result: SELECT user_id FROM users USE…
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
…select a Primary Component, we have then a split-brain condition…mysql> insert into percona values (0,’percona1′,’jaime’); Query OK, 1 row affected (0.02 sec) percona1 mysql> select…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
…MySQL uses single index – there are more complex rules of how indexes will be used if…conditions index can be used…use full index for sorting (note MySQL may not select to use index for sort if…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… as I used for MySQL Group by Performance Tests to see how much MySQL can… ? mysql> set sort_buffer_size=100000000; Query OK, 0 rows affected (0.00 sec) mysql> select * … given platform and given query (I did not test if it is the same… so small at least in some conditions. What do we learn from these …
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_method=O_DIRECT query_cache_size=0 query_cache_type=0 optimizer_switch=’index_condition_pushdown=on’ …used for this test together with its EXPLAIN output as returned by MySQL 5.5 is as follows: SELECT…_cache_level controls which algorithms are enabled. If join_cache_level=4 all algorithms numbered …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now… enhancement actually is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in… index parts after the part on which range condition is applied cannot be used for filtering records. For example, suppose you have…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…query to aggregate over the rows of the view and we add a filter condition on c1, the view is accessed quicky: mysql> select…using a filter on c1, performance is not good. MySQL is scanning the entire table to produce the result: mysql> select…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
…, location; integer – age 3) All queries fall into X IN (…) AND Y IN (…) AND … Some conditions fall into X = const – ref access… always more selective than any of its columns alone. If you don’t have such compound index then mysql most probably use index on… or may be consider using (City, Age). Whatever you select to use can be used as prefix for other indexes cuz conditions on these fields…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…which conditions it would do the index merge: mysql [localhost] {msandbox} (test) > explain select avg(…if we leave MySQL no choice but only to use combined index: mysql [localhost] {msandbox} (test) > explain select… use them for this query. The query takes 3370ms if this plan is used. Note the query…
Post: Checking for a live database connection considered harmful
May 5, 2010 By 38 Comments
… at the MySQL protocol level, which will increment Com_admin_commands in SHOW GLOBAL STATUS, or a trivial query such as ‘SELECT 1… reconnected. A better approach is to use the same code that fixes the race condition. Again, if the connection is down, you’ll find out when you try to run the query. Until then…
