Index on wp_termmeta

  • Hello,

    Is there any way to index wp_termmeta on mysql using innodb?

    I have a website that uses taxonomy, i have over 400k rows on termmeta table, and when i update it my server cpu goes very high, causing the site to break.

    Thanks!

Viewing 6 replies - 1 through 6 (of 6 total)
  • Moderator Steven Stern (sterndata)

    (@sterndata)

    Volunteer Forum Moderator

    It’s probably best to make all your tables InnoDB. That’s the current default.

    See https://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-into-innodb

    Before you do that, use a plugin like “wp-optimize” to remove unneeded rows from that large table.

    Thread Starter mariusmrs

    (@mariusmrs)

    Hello,

    I have the tables innodb at the moment.

    I see that innodb doesn’t support fulltext index. Will fulltext index help me if i need to search in meta_value? Because is a longtext row?

    Moderator Steven Stern (sterndata)

    (@sterndata)

    Volunteer Forum Moderator

    No idea. But if you create a WP site now, all tables are InnoDB. So all core features work with it.

    Hi, @mariusmrs
    You can organize your search on meta fields using a word-indexed approach (it means without MySQL indexes at all). It also can give you more possibilities like real relevance ordering etc.

    Thread Starter mariusmrs

    (@mariusmrs)

    Hey @epsiloncool , thanks for your answer.
    I don’t know how to do this ??
    Can you give me more details please?

    @mariusmrs
    Well, the common idea is to get all the textual data that you need to search on, split them onto words (or part of the words) and then put all them to well-organized MySQL tables called “Word-based Search Index”.

    Then, you could have the optimized MySQL query, which can effectively scan for given words (or sub-words) within this search index.

    Thus you don’t need to create complex MySQL queries even if you have a very complex data organization (for example, if you want to search in metadata, in taxonomies, in file contents, etc) and at the same time, you can get faster search and justify relevance calculation.

    This is an idea. If you are interested, you can use my free plugin WP Fulltext Search or any other related plugin which is using the same principle. I can give you more information about this if you like.

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Index on wp_termmeta’ is closed to new replies.