Support » Requests and Feedback » Prefix the length of indexes

  • Abstract:
    Why doesn’t WP prefix length index “autoload” column, under wp_options table??

    PROBLEM:
    Such query patterns are always slow:

    Count: 124 Time=25.76s (3194s) Lock=1.81s (224s) Rows=515.3 (63893), 3users@19hosts
    SELECT option_name, option_value FROM wp_options WHERE autoload = ‘S’

    FIX:
    1) In almost all cases one can create an “autoload” prefix length index to resolve the issue and speed up the query.

    or

    2) The autoload column can also be shortened instead of doing the prefix lenght index as this column almost never holds values outside of: “YES” or “NO”. So make it a VARCHAR(3) alternatively?

    This problem is near and dear to many users. Believe me I know. This will help many many many WP customers!

    Can WP consider a WP plugin update, sometime soon on this issue?

    Please get back to me.

    [sig moderated as per the Forum Rules]

Viewing 1 replies (of 1 total)
  • example precise queries:
    SELECT option_name, option_value FROM wp_options WHERE autoload = ‘NO’
    SELECT option_name, option_value FROM wp_options WHERE autoload = ‘YES’

    Please note the following based on what is above:
    1) This is just given as a sample from one customer.
    But it is very common issue for causing slow queries to stack up.

    2) For this particular case:
    – The total count of such slow queries in a 8 hour window: 124
    – Average time to completion for this query type: ~25seconds (should be unacceptable to WP).
    – Average lock time: ~2seconds

    3) Problem starts with high concurrency and/or with significant growth of wp_options table.
    If this table is small or the # concurrent queries accessing wp_options is small, then there isn’t any issue.

    — Similarly, a prefixed length “meta_key” index would be nice:
    As you can note, the meta_key column is indexed by default but it indexes the whole length of varchar(255).
    Recommend prefix length on this at say (20).

    Thanks,
    Nadeem Asrar
    [sig moderated as per the Forum Rules]

Viewing 1 replies (of 1 total)
  • The topic ‘Prefix the length of indexes’ is closed to new replies.