Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question

2 Answers 2

  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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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