…-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct-ranges=0 –oltp-skip-trx=on –oltp-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index…-ranges=0 –oltp-distinct-ranges=0 –oltp-skip-trx=on –oltp-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=1 –oltp-non-index…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… set (0.00 sec) mysql> SELECT count(distinct(population)) FROM Country; +—————————–+ | count(distinct(population)) | +—————————–+ |      … An automated search for low cardinality indexes wouldn’t have revealed it’s uselessness. For range scans, it’s very …
Post: Fix of InnoDB/XtraDB scalability of rollback segment
January 18, 2009 By 9 Comments
…-trx off –oltp-range-size –oltp-point-selects –oltp-simple-ranges –oltp-sum-ranges –oltp-order-ranges –oltp-distinct-ranges –oltp-index-updates –oltp-non-index-updates –oltp…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… i and j having both of them having 1000 of distinct values, independent on each other. I also created couple of… can’t use Index Merge however as it is currently implemented so they would use single index range scan. The 2 indexes however benefits to Q3 because it can only use first keypart of index (j,i…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… remember if indexes are helpful or not depends on index selectivity – how large proportion of rows matches to particular index value or range. What… 10000 distinct value, so range 1..100 selects about 1% of the table. The times for full table scan vs range scan by index… affect index scan/range scan speed dramatically. The rows referenced by indexes also could be located sequentially or require radom IO if index ranges are…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… 5.6 and MariaDB 5.5 prefer to do an index range scan on the secondary key o_orderdate which results in… match approximately 600 rows from the table lineitem for each distinct key value, in this test case D, the joining key… match approximately 5 rows from the table lineitem for each distinct key value. Also the joining key in this test case…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…different diapason of years. SELECT DestCityName, COUNT( DISTINCT OriginCityName) FROM ontime WHERE Year BETWEEN …had to crunch. For query Q8 (1year range) traditional transactional oriented stored engine took …the curtain, they also do not have indexes like InfoBright, but results are impressive. On …
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… separate index on every column in the WHERE clause. Here’s the query plan initially. *************************** 1. row *************************** table: dim1 type: range key… tables will stay in memory, and this is an important distinction. Let’s put ourselves into the mindset of the optimizer…
Post: Get Me Some Query Logs!
November 22, 2012 By 14 Comments
…. This is so you have a distinct file that contains only the specific time range of logs you collected. The restoration of… London, UK on December 3-4 2012: How to Design Indexes, Really Survey of Percona Toolkit If you can make it…
Comment: Should MySQL Extend GROUP BY Syntax ?
August 18, 2007 By 11 Comments
… range which will be using index to access the rows you will get different plan and not result you’re expecting. Regarding DISTINCT…
