… to follow up with more SSB tests at a later date. I also promised more low concurrency SSB tests when Peter… significantly during the hot run, as hash indexes are faster than a b-tree index. Also accessing pages from the buffer pool… data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… applying cluster transactions. Therefore, it keeps reads more up to date without needing to use wsrep_causal_reads. It minimizes the… translates into number of unique key lookups into the certification index, which is a hash table. A small fc_limit does however keep the certification index smaller in memory. It keeps the certification interval small, which…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…_date DATE NOT NULL, ADD INDEX itm_prd_id__and__itm_order_date (itm_prd_id, itm_order_date); UPDATE items_ordered SET itm_order_date = DATE… Extra: Using where; Using index This query uses 7 bytes of `itm_prd_id__and__itm_order_date` index – 4 bytes is `itm_prd_id` and 3 bytes is `itm_order_date` (DATE type uses 3…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
…NULL, update_time DATETIME NOT NULL, …. INDEX `uid` (uid, update_time), INDEX `bar` (some_other_columns) …. ) ENGINE=InnoDB… (root@localhost) [test]> select update_date FROM foo order by update_date LIMIT 5; +———————+ | update_date | +———————+ | 0024-06-21 …
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… to find the different indexes and types. mysql (information_schema) > select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as i INNER… employees/salaries, id 18, flags 1, columns 7, indexes 2, appr.rows 2844513 [...] INDEX: name PRIMARY, id 26, fields 2/6, uniq… pages 6120 FIELDS: emp_no from_date DB_TRX_ID DB_ROLL_PTR salary to_date INDEX: name emp_no, id 27, fields…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… common for a date range to also need to be ordered. The question is how can one have optimized indexes and sorting in such a scenario, since inequality ends index usage. Actually in this case index on (A,B…: We have a database that has about 400GB of indexes. The indexes can’t fit in memory anymore. How does this affect…
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
… Descending indexes ? Most typical case is when you want to order by two colums in different directions: … ORDER BY price ASC, date DESC LIMIT 10 If you have indexed on (price,date) in ascending order you will not… needed. If you would be able to build index on price ASC, date DESC the same query could retrive data in aready…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… the all_indexes view: mysql> select * from all_indexes\G … *************************** 33. row *************************** TABLE_SCHEMA: sakila TABLE_NAME: rental INDEX_NAME: rental_date NON_UNIQUE: 0 COLUMN_CNT: 3 COLUMN_NAMES: rental_date,inventory_id,customer_id index_total_pages: 27 index_total_size: 442368 … Now we just need to update our droppable_indexes view…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes | | queries | | tables | +———————–+ … | 1262633894049058504 | mpb_wordpress.wp_posts.type_status_date | 2 | 15 | 30.0000 | | 7675136724153707161…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
…(‘html’,'video’,'mp3′,’image’,'pdf’,'other’) NOT NULL, `message_day` date NOT NULL, `mod_is` tinyint(3) unsigned NOT NULL default… deletes are rare). And let’s take index domain_id table: art/link_out104, index: domain_id, space id: 12, root page… this is in XtraDB/InnoDB plugin – fast index creation. With this feature, InnoDB creates indexes by sort, so page fill factor should…
