Indexing a LONGTEXT column

I would like to set up an index for a LONGTEXT column using:

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

There are currently ~1 million rows in the relevant table.

Questions:

• Will creating this index affect the data in any way whatsover, for example, droping leading 0's or anyhting like that?

• 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.

I was fighting a similar problem today with some slow SQL queries generated by WooCommerce in WordPress and I managed to resolve the issue by creating the following two indexes:

alter table wpn0_posts add index woo_index1 (post_type, post_status, id);

alter table wpn0_postmeta add index woo_index2 (post_id, meta_key, meta_value(8));

In my case, however, the query was always looking for IDs in the longtext field:


WHERE meta_value = '15358'....

That is why the index works in my case for my specific queries. I hope this helps you :)

Edit: You may also check the following question I posted today and I managed to optimize my query by adding indexes:

How to optimize this specific MySQL query which examines 2.84M rows and 29.49k InnoDB distinct pages

StackExchange Database Administrators Q#33185, answer score: 4