…amount of updates to the database, all SELECT queries, even those participating in read-only transactions, …-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-…nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + UPDATE…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… INDEX k on sbtest$I(k) and tables sbtest$I_r10, sbtest$I_r100, sbtest$I_r1000, with roll-up sum for… join_buffer_size = 4M thread_cache_size = 1000 query_cache_size = 0 query_cache_type = 0 ft_min_word_len = 4 #default…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… query_id) AS queries, SUM(cnt) AS cnt -> FROM index_alternatives -> GROUP BY db, tbl, idx -> HAVING COUNT(*) > 1 limit 2; +————————————+—————————————————+——————————————+——+ | idx | alternatives | queries…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… use to decide which columns should come first in an index. This is not specific to MySQL, it’s generally applicable to any database server with b-tree indexes. And there are a bunch of subtleties, but I will… for the sake of simplicity. Let’s start with this query, which returns zero rows but does a full table scan…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… were questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when query can use both…)) FROM T WHERE i=2 AND j=1 Q2 SELECT sum(length(val)) FROM T WHERE i=2 AND j BETWEEN… make. Conclusion: For benchmarked queries we can see Multiple Column index beats Index Merge in all cases when such index can be used. It…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied…
Post: Air traffic queries in MyISAM and Tokutek (TokuDB)
November 5, 2009 By 26 Comments
… proper indexes to execute queries effectively, and there is pain coming with indexes: – load of data is getting slower; – to design proper indexes is… loaded data and less IO operations during query execution. So what indexes we need for queries. To recall you details, the schema is…*1000/totalflights as c3 FROM (SELECT carrier,count(*) as totalflights,sum(if(depdelay>10,1,0)) as ndelayed from ontime where…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
… to write about is queries that execute slower in new MySQL 6.0 version. Query is pretty simple SELECT sum(l_extendedprice * l… of Random I/O we had during execution of query, as MySQL used index to scan rows and then did access to… here is that MySQL is wrong choosing index scan here. If we try to execute query with IGNORE KEY (li_shp_dt…
Post: Feature Idea: Finding columns which query needs to access
November 17, 2006 By 16 Comments
… which columns query needs to access to provide result set as it gives you ideas if you can use covering indexes to… clause, WHERE clause, ORDER BY GROUP BY and HAVING to sum it up, not to mention more complex questions of subselects… clauses such as GROUP BY and ORDER BY. For complex queries especially if they do not refer to the columns with…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… performance of the query with the join in both directions. Explain for query Q1.1: mysql> select straight_join sum(lo_extendedprice*lo… from ( select table_name, index_name, count(*) pages, sum(is_old=’YES’) old, count(*) – sum(is_old=’YES’) hot, sum(number_records) records from… from ( select table_name, index_name, count(*) pages, sum(is_old=’YES’) old, count(*) – sum(is_old=’YES’) hot, sum(number_records) records from…
