…) NOT NULL default ”, `password` varchar(64) NOT NULL default ”, `dob` date default NULL, `address` varchar(128) NOT NULL default ”, `city` varchar…_INDEX Query: SELECT id FROM $tableName WHERE id = %d Simple but very quick query to retrieve value from PK. READ_PK_RANGE… need an extra lookup to access to data. READ_PK_RANGE_INDEX Query: SELECT count(id) FROM $tableName WHERE id between %d…
Comment: MyISAM concurrent insert
August 23, 2011 By 12 Comments
… Key index ID,Date, and indexed Date, with the Select statements always: Select from table where ID = ###### Order by Date with sometimes AND Date >= 20100101 and Date <= 20110101 (for ranges…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… An automated search for low cardinality indexes wouldn’t have revealed it’s uselessness. For range scans, it’s very easy to lead yourself into a trap where your index can not filter out… where customers have queries that use a BETWEEN on a date, but the window of time it is searching in is…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… on such as seller, price, date added etc. Such configuration often causes serious challenge choosing proper index configuration as it is hard… to use index for further restriction and than using file sort, rather than using index for sorting and doing non-index based filtering… to better take into account column selectivity together with LIMIT range. If there are only few values for given seller_id…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…char(1) NOT NULL, `l_commitdate` date NOT NULL, `l_receiptdate` date NOT NULL, `l_shipinstruct` char… | 1 | SIMPLE | lineitem | range | li_shp_dt_idx | li_shp_dt_idx | 3 | NULL | 8272067 | Using index condition;…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… Created_tmp_tablesblogpercona.commanual datadirblogpercona.commanual date_formatblogpercona.commanual datetime_formatblogpercona.commanual debugblogpercona…..commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select_…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…, so it is important for it to use index – in this case index range scan will be started and query execution stopped as… do SELECT * FROM sites ORDER BY date_created DESC LIMIT 10; I would use index on (date_created) to get result set very… in the list now so index on (category_id, date_created) can’t be used directly. Index on date_created separately would still work…
Comment: 3 ways MySQL uses indexes
July 20, 2010 By 28 Comments
…: “once MySQL runs into the interval range it will not use any further index parts.” WRONG! Triple range lookup: EXPLAIN SELECT * FROM tree WHERE 1 AND tree_type = “2220″ AND date_from = “0000-00-00″ AND date… range lookups with speeds in miliseconds. It’s maybe difficult to make it work the first time, to make the correct index…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…` char(1) DEFAULT NULL, `l_shipDATE` date DEFAULT NULL, `l_commitDATE` date DEFAULT NULL, `l_receiptDATE` date DEFAULT NULL, `l_shipinstruct` char(25… | filtered | Extra | +—-+————-+———-+——-+—————+—————+———+————————-+——-+———-+————-+ | 1 | SIMPLE | supplier | index | PRIMARY | i_s_nationkey | 5 | NULL | 20174 | 100.00 | Using index | | 1 | SIMPLE | lineitem | ref | i… 5.6 and MariaDB 5.5 prefer to do an index range scan on the secondary key o_orderdate which results in…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
… ’0′ ‘Com_create_function’, ’0′ ‘Com_create_index‘, ’0′ ‘Com_create_table’, ’31′ ‘Com_dealloc_…’0′ ‘Com_purge’, ’0′ ‘Com_purge_before_date‘, ’0′ ‘Com_rename_table’, ’0′ ‘Com_repair’,…Select_full_join’, ’0′ ‘Select_full_range_join’, ’0′ ‘Select_range‘, ’5683551′ ‘Select_range_check’, ’0′ ‘Select_scan’,…
