…produce updates in-place (I will use …NOT NULL, c CHAR(120) DEFAULT ” NOT NULL, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX… as TokuDB does time-based …where `id` is sequential, and `hid`,`mid` is low selectivity is not good for fast inserts, but it is suitable for range selects…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…in slow query log as query which does not use indexes, even if it is quite fast: mysql> explain select…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…not other index, also you only can have equality comparison as a search condition WHERE A=5 ORDER BY B will use index…using UUID rather successful in applications which do not need to be optimized for peak performance or the cases when this does not…
Post: Indexes in MySQL
June 2, 2006 By 29 Comments
…does not always make a right decision about indexes usage. Condsider a simple …use index. Execution time: SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1 – 1200 ms SELECT COUNT(SUBNAME) FROM t2 IGNORE INDEX (ID1) WHERE…index access is less than for table scan. So be careful with indexes, they help in not…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
…in the systems memory. I’ve benchmarked simple queries using where clause which covers multiple columns: Q1 SELECT sum(length(val)) FROM T WHERE…not use Index Merge technique for low cardinality table but instead pick to do single index scan. I’m not…
Post: Quickly preloading Innodb tables in the buffer pool
May 1, 2008 By 9 Comments
…load Innodb Table Clustered Index in the buffer pool pretty efficiently by using something like SELECT count(*) FROM tbl WHERE non_index_col=0 This works relatively well (though can be slow for fragmented tables) but it does not preload indexes in…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
…use full index for sorting (note MySQL may not select to use index…not be able to use index because requested order does not line up with the order of data in…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… is very traditional recommendation to normalize your data however it does not always bring good performance. Joins are expensive and you can… IN (SELECT id FROM users WHERE featured=1) Some day this should be fixed however but do not expect it soon. Use Indexes This item was not in…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…not know of the hint in MySQL which would allow forcing using index merge when MySQL does not think it should be used…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…index lookups, if the columns that are being fetched do not belong to the secondary index definition (and hence covering index optimization is not used…Using where So during cold query runs the optimizer would switch to using plan ‘a’, which does not…
