Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
Bob Dietrich
MySQL issues with composite indexes
March 17, 2012 2:19 PM
Answer

Bob Dietrich

Rank: Regular Member

Posts: 221

Join Date: May 15, 2005

Recent Posts

In Liferay 6.0/6.1 multi-column indexes are used in various places (twice in DLFileEntry). This can cause problems depending on the database engine and character set.

In particular, MySQL 5 has a limit on multi-column index sizes.The limit is 1000 bytes for MyISAM and 767 for InnoDB. This would not seem to be a problem when combining a 255 character title field with groupid and folderid (plus separator characters; see DLFileEntry). However, when you use UTF-8 for the database, MySQL saves each character using 3 bytes. This means the title field alone uses 765 bytes of the 767 for InnoDB, and adding the other two fields to the index exceeds the limit. And the title field in this case maps directly to the UI.

Anyone else noticed this issue? Thanks.
Hitoshi Ozawa
RE: MySQL issues with composite indexes
March 18, 2012 4:05 AM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7949

Join Date: March 23, 2010

Recent Posts

MySQL 5 has a limit on multi-column index sizes.The limit is 1000 bytes for MyISAM and 767 for InnoDB


You mean "Index key prefixes can be up to 767 bytes"

http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

MySQL saves each character using 3 bytes


Each character may be up to 3 bytes.
Bob Dietrich
RE: MySQL issues with composite indexes
March 18, 2012 9:45 AM
Answer

Bob Dietrich

Rank: Regular Member

Posts: 221

Join Date: May 15, 2005

Recent Posts

Being left to draw my own conclusions, does this mean you don't think there is a problem?
Hitoshi Ozawa
RE: MySQL issues with composite indexes
March 18, 2012 2:01 PM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7949

Join Date: March 23, 2010

Recent Posts

I have several systems up.
Just to say that I haven't encountered any problems so far.

Has anybody had problems with the coposite indexes?