I am building a multilingual web application.
Urls stored in database like:
id_url bigint(18)
id_map int(11)
id_entity bigint(18)
active tinyint(1)
lang varchar(8)
path varchar(500)
path_ids varchar(50)
full_path_ids varchar(50)
created_at datetime
My question is about path indexing. I need to make search only LIKE '$str%. I don't need LIKE '%$str'.
Index is : idx_url_path BTREE No No path (255) 22 A No
It automatically degraded to 255.
Any idea on how to get things done?
From: http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html
By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.
So, assuming you are using the InnoDB engine, you can't index 500 characters unless you enable the innodb_large_prefix configuration option, and your table is using DYNAMIC and COMPRESSED row formats:
innodb_large_prefix: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_large_prefix
DYNAMIC row format: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_dynamic_row_format
COMPRESSED row format: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_compressed_row_format