Mysql – Indexing a LONGTEXT column

database-designindexMySQL

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:

  1. Will creating this index affect the data in any way whatsover, for example, droping 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.

Best Answer

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

Related Question