… one of the two indexes was unique or a foreign key. This is the output of our tool when run on the…`) # Column types: # `col2` int(11) default null # `col3` varchar(200) default null # To remove this duplicate index, execute: ALTER TABLE `test`.`test` DROP INDEX… (`id`), # Column types: # `col2` int(11) default null # `id` int(11) not null auto_increment # To shorten this duplicate clustered index, execute: ALTER…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
… need to?). I get up to speed a lot faster and can contribute to the Development process a lot more effectively when … between columns/tables based on names, tables without indexes, columns flagged ‘nullable’ and ‘must be unique’ (woops!), single column tables, incrementing column names …
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
…-case decision. There are other drawbacks to using temporary tables in this way, for example when using replication, a temporary table could… tuple syntax to look up values against multiple columns in a compound index, then the order would matter, and it would have to match the order of columns in the index. Example…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…is a single row (well, the PRIMARY KEY index entry for that row). This means typically … moving the ‘joined’ column to the users_groups table so we don’t need to update the parent …to replicate your data to a distributed system requires tradeoffs. Most of us are used to the tradeoffs we take when…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… in how well it can deal with primary key appended to the index column, especially in such case as you’re suggesting. In… expensive than having another index on (vendor_id,order_id) in addition to index on (vendor_id) alone. The cases when you really should worry about performance impact of extending index is when you increase…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… when you query AGE=18 with single column BTREE index MySQL will dive into the index to find first matching row and when will continue scanning index… the case when Index merge works much better than multiple column indexes. This is in case you’re using OR between the columns. In this…
Post: How number of columns affects performance ?
September 28, 2009 By 12 Comments
… the start when it comes to MyISAM. The common workaround working with such wide tables is to use covering indexes. I added one to t99v1… stored in BTREE index very similarly to how indexes are stored. Summary: Beware of dynamic row format tables with many columns they might bite you with surprise slowdown. MyISAM is much faster than Innodb when it comes to in memory…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
…: Duplicate indexes apply to indexes of the same time. It may make sense to have indexes of different types to created on the same column(s) – perfect… of columns in 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…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… to show what difference large BETWEEN can make. Conclusion: For benchmarked queries we can see Multiple Column index beats Index Merge in all cases when such index can be used. It is also worth to watchout a MySQL may decide not to do Index…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… query groups by the same column as its sort column. You can optimize by adding the grouping column to your index, so all rows in… need to sort by another column, it must collect the interim result set and sort it as a separate step. This is when… system: define an index with all columns in the predicate, add columns to avoid sorting per query, and add further columns to cover the select-list…
