…IN (2-4)” be worse then “WHERE a IN (2,3,4)”? Another word is range for IN clause better than BETWEEN? A: IN… to be in memory only those portions of them which are accessed frequently. The size of this… table in recent MySQL versions. However it is often too many. Instead of thinking about hard limit …
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…in the data set. mysql> select count(*) from dim_airport; +———-+ | count(*) | +———-+ | 396 | +———-+ 1 row in…in used in the query: BETWEEN or IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… atomically, not just for new connections as in MySQL. This is very helpful for measurement as…MySQL. The end result might be that you end up limiting the number of results returned by the query, by using a LIMIT clause…not much considering the fact that tmp_tbl_size variable is set to 32M on the …
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…in total execution time. By the way, the slow query logs in the stock MySQL server are extremely limited…in the FROM clause…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…in the future, a redundant node. Work on problems of any size. Set processing is massively parallel . In…. If you have any limitation in resources in a cluster (cpu, memory, …, but right now only MySQL storage nodes are supported. … JOIN, and a WHERE clause that uses an IN clause: — INPUT SQL: select …
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 3
October 19, 2009 By 25 Comments
… In part 1 we talked about boosting performance with memcached on top of MySQL, in … where you can add a where clause and search individual columns, and a ton…As load, number of threads, and data size increases how does performance differ or … the server until it hit its limit and fell over dead. It…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… for each join in a query, MariaDB introduces another variable to control the size of the buffer ‘join_buffer_space_limit‘. This variable… with no WHERE clause The SQL used for this test together with its EXPLAIN output as returned by MySQL 5.5 is… workload. In this test Hash join is not ideal because you have a highly selective where clause that reduces the size of the…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5… new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…mysql> select * from gt limit 5; +——-+——————————————+ | i | c | …in set (0.01 sec) As you can see I’m using ORDER BY NULL clause as otherwise MySQL…mysql> set max_heap_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> set tmp_table_size…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…limitation would often have tables with lots of single-column indexes on columns which commonly appeared in their WHERE clauses… there isn’t likely to be a one-size-fits-all silver bullet solution. For example, one…=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE…
