MySQL INDEX Varchar Field 500 for urls

Question

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?

Answer 1

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