… to follow up with more SSB tests at a later date. I also promised more low concurrency SSB tests when Peter… time when the database is hot. I am only speculating (and I can investigate further), but I believe the adaptive hash index… 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
…. Therefore, it keeps reads more up to date without needing to use wsrep_causal_reads…. small fc_limit does however keep the certification index smaller in memory. It keeps the certification … expensive replication conflicts more likely and therefore time-consuming to the application. How to tell…
Comment: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… to be on the extremes, e.g. all-zero dates or dates like you mentioned. Probably not too hard to find them… Percona Toolkit tool could iterate through all tables, look for indexed date/time columns, and check the first and last to see if…
Post: Building Indexes by Sorting In Innodb (AKA Fast Index Creation)
June 19, 2012 By 2 Comments
… MySQL 5.5 promptly, yet it has not happen to date. Here is example of gains you can expect (courtesy of… in this table is using “fast_index_creation=0″ which allows to disable fast index creation in Percona Server and force complete… even for such small table there is possible to improve index creation time 2x by using large buffer. Also we can see…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
…time DATETIME NOT NULL, …. INDEX `uid` (uid, update_time), INDEX `bar` (some_other_columns) …. ) ENGINE=InnoDB; When he ran this query: SELECT MIN(update_time…) [test]> select update_date FROM foo order by update_date LIMIT 5; +———————+ | update_date | +———————+ | 0024…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…). There were the times when when it would make a difference though. Q: Would you recommend creating an index in every table… scanning data in index order MySQL gets data in already sorted order and looks at “one group at the time“, computing aggregate… concerns or tricks for selecting using some date ranges? or between dates? or after a date? A: Date comparisons work very similar to other…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…: 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… blocks and on a large table can take quite some time. All the normal tricks and tips about doing a long…
Comment: Multi Column indexes vs Index Merge
October 20, 2009 By 31 Comments
… latitude, longitude, date_time, vehicle_id and a few more columns. I can’t seem to find a good index combination for a query bringing back around 7 days of results for a vehicle. Tried (date_time,vehicle_id) and MYSQL won’t use it, (date_time) and its bringing back thousands of…
Comment: MyISAM concurrent insert
August 23, 2011 By 12 Comments
… time issues (the tables are Primary 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…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… this one: which queries sometimes use different indexes, and what fraction of the time is each index chosen? SELECT iu.query_id, CONCAT… | 35 | 70.0000 | | 1262633894049058504 | mpb_wordpress.wp_posts.type_status_date | 2 | 15 | 30.0000 | | 7675136724153707161 | mpb_wordpress.wp_comments.comment… a much more skewed index usage: 2.4% of the time it uses one index, and the rest of the time it uses another…
