Indexing a longtext field

Question

I would like to run an index on a longtext field using:

CREATE INDEX post_meta ON wp_postmeta (meta_value(8));

There are currently ~1 million records.

Questions:

1. Will creating this index affect the data in any way whatsover? Drop leading 0's or anyhting like that?
2. Is there any reason NOT to do this? There are many rows with content greater than 8 characters, but I frequently query on a type of entry that is 8 or less.

Answer 1

1. Creating tHe index will not affect the content

2. Index on TEXT is not useful because it only index first few characters. Most query with condition such as LIKE '%word%' cannot benefit from the index. Use FULLTEXT index with query 'MATCH (column) AGAINST ('keyword')' instead.

Answer 2

Well if your search criteria always be stable for 8 or less characters i.e. starting from char 1 to 8 from long text field meta_value, then you may add a new column saying meta_key varchar(12) and add index to it. Remove index on meta_value long text. Now whenever you insert a row add first eight characters to meta_key. In this case your selects on varchar with 8 characters would be faster.