June 19, 2013

Post: Is Synchronous Replication right for your app?

May 14, 2013 By 18 Comments

…a single row (well, the PRIMARY KEY index entry for that row).  This means typically on a single Innodb… This is not implemented for full BEGIN … COMMIT multi-statement transactions since it cannot be assumed …Example 2, above, how above moving the ‘joined’ column to the users_groups table so we don…

Filed Under: MySQL, Percona Software, XtraDB Cluster Tagged With: , , , , , ,

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

… brings. Multi Range Read With traditional secondary index lookups, if the columns that are being fetched do not belong to the secondary index definition… that traditional index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index record to make a lookup in the PK. Both these lookups are performed in a single call…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: Multi Column indexes vs Index Merge

September 19, 2009 By 31 Comments

… see index merge indeed improves performance compared to single index only but it is by far better to use multi column indexes. But the problems with Index… combined index in this case. In case you’re using OR between columns – single column indexes are required for index merge to work and combined indexes can…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: Multi-Column IN clause - Unexpected MySQL Issue

April 4, 2008 By 21 Comments

… of urls. To save on indexes instead of using URL we index CRC32 of the URL which allows… feature. I should not be surprised though as multi-column in is not the most used MySQL feature… we could simply rewrite query using more standard single column IN clause: mysql> explain SELECT url FROM 106pages….

Filed Under: Insight for Developers Tagged With: ,

Post: 3 ways MySQL uses indexes

September 12, 2009 By 28 Comments

… column in explain plan to see how many index parts are actually used for row lookup. Very common problem I see is multi column indexes… uses single index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index merge” Using Index…

Filed Under: Insight for Developers Tagged With:

Comment: How to find wrong indexing with glance view

August 22, 2008 By 27 Comments

… drop a few columns from that index without losing any performance. And there’s the problem of mysql choosing single column indexes where multi-column indexes are present, and wasting memory and disk space. I still think that single column indexes make more sense…

Filed Under: Insight for DBAs Tagged With:

Post: On Character Sets and Disappearing Tables

June 21, 2012 By 1 Comment

…(32) not null, fkto1 char(32) not null primary key, index(fkfrom1), index(fkfrom2), foreign key (fkto1) references dos1(fkfrom3) ) engine=innodb…-referenced column from INT to SMALLINT, but it will allow us to change from a multi-byte character column to a single-byte character column…

Filed Under: Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

… need to create an *InnoDB* table with a single VARCHAR column named “value”. OK, sounds easy enough: mysql: … case of GEN_CLUST_INDEX causing me issues, I tried adding a second column to the table which …This may or may not be the case with multi-byte character sets or with ideographic languages such…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

…to specify single page size for Innodb table, while data pages and pages for different indexes may …for statement duration for Inserts with auto-increment column. This starts giving you problems if you … for full statement duration. The problem with multi value inserts however is the following – even …

Filed Under: Events and Announcements Tagged With:

Comment: Why InnoDB index cardinality varies strangely

September 28, 2009 By 5 Comments

… is much worse than it is sounds. For single-column indexes we get variations. For multi-column indexes we have problems. Lets consider CREATE TABLE T…. In their opinion the behavior is inherent, as sampling of indexes happen independently. I guess, expectations of optimizer are: 1) x1…

Filed Under: Insight for DBAs, MySQL Tagged With:
« Older Posts