2

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

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

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.