… that any query can be optimized quickly no matter how bad the pruning heuristics works. With heuristics, things can go arbitrarily bad, that’s why the choice of higher search depth was … search depth based on some syntactic properties and knowledge of indexes. The problem is that it is very hard to predict…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… being fetched do not belong to the secondary index definition (and hence covering index optimization is not used), then primary key lookups… traditional index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index record… a look at what are the good and bad query execution plans. a. Bad Plan id select_type table type possible_keys…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
…. Duplicate keys are bad so once you find them get rid of them. Note: Duplicate indexes apply to indexes of the same time… index is significant, index (A,B) is not duplicate to index (B,A) So now what are Redundant indexes when ? I call redundant indexes BTREE indexes which are prefix of other index, for…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
… 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…. And the last point of the post – if you are badly missing some features in MySQL, InnoDB, InnoDB-plugin, XtraDB, XtraBackup…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
… database_name/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL… at you, Baron) was adverse to trusting INNODB_SYS_INDEXES from some bad experiences with it, and suggested the Innodb Table monitor… based on the .frm table definition and removes the existing index in Innodb, which might not be desirable, but at least…
Post: Redundant index is not always bad
August 28, 2007 By 7 Comments
… year ago Peter wrote about redundant indexes and mentioned sometimes it is good to leave two indexes, even one is first part… we use for such queries is covering index, which store all needed columns in the index, and there is no need to… – 470sec (Here I limited available memory to fit only one index, that shows how can degrade performance if we balance on…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web… to use index for further restriction and than using file sort, rather than using index for sorting and doing non-index based filtering… of values of given seller_id, so it is badly selective using index scan is much better idea. Until MySQL is able…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… rows are retrived. Notice Using Where. There are even more bad news. Full rows will need to be read to check… be done only by reading data from the index. MySQL can ether read index only for all rows, in this case you… cases when it is technically possible. For multiple key part indexes MySQL will only be able to use multiple keyparts if…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… seems reasonable based on the structure of a B-tree index, so let’s assume anyway: Hard-to-prove assumption #3… interesting question that we haven’t really addressed yet. How bad is bad? This is where we return to the notion of… that is large enough to hold your working set — the index blocks that are frequently used. How large is that? This…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… this one… > 1. Using mysql_* functions directly This is probably bad but I do not like > solutions proposed by original article… that developers should consider when deciding when / how to create indexes. Foreign key references may add processing time to inserts, updates… remember > we’re here to help. Partial agreement: “Use Indexes smartly.” Indexing columns that have very low cardinality will often be a…
