WordPress.org

Forums

Redirection
Queries using 'WHERE...OR' causes full table scans [low priority] (2 posts)

  1. markwhite
    Member
    Posted 1 year ago #

    The indexes are not being utilised on queries like this (from models/redirect.php):

    SELECT @redirection_items.*,@redirection_groups.tracking,@redirection_groups.position AS group_pos,    @redirection_modules.id AS module_id FROM @redirection_items INNER JOIN @redirection_groups ON @redirection_groups.id=@redirection_    items.group_id AND @redirection_groups.status='enabled' INNER JOIN @redirection_modules ON @redirection_modules.id=@redirection_gro    ups.module_id AND @redirection_modules.type=%s WHERE( @redirection_items.regex=1 OR @redirection_items.url=%s)

    Explain:

    |  1 | SIMPLE      | wp_redirection_groups  | ref    | PRIMARY,module_id,status    | status  | 1       | const                                    |    1 | Using where                    |
    |  1 | SIMPLE      | wp_redirection_modules | eq_ref | PRIMARY,type                | PRIMARY | 4       | knd_blog.wp_redirection_groups.module_id |    1 | Using where                    |
    |  1 | SIMPLE      | wp_redirection_items   | ALL    | url,regex,group_idpos,group | NULL    | NULL    | NULL                                     | 1029 | Using where; Using join buffer |

    Rewriting this to use UNIONS avoids this:

    SELECT `@redirection_items.*,@redirection_groups.tracking,@redirection_groups.position AS group_pos, @redirection_modules.id AS module_id FROM @redirection_items INNER JOIN @redirection_groups ON @redirection_groups.id=@redirection_ items.group_id AND @redirection_groups.status='enabled' INNER JOIN @redirection_modules ON @redirection_modules.id=@redirection_gro ups.module_id AND @redirection_modules.type=%s WHERE @redirection_items.regex=1

    UNION
    SELECT
    @redirection_items.*,@redirection_groups.tracking,@redirection_groups.position AS group_pos, @redirection_modules.id AS module_id FROM @redirection_items INNER JOIN @redirection_groups ON @redirection_groups.id=@redirection_ items.group_id AND @redirection_groups.status='enabled' INNER JOIN @redirection_modules ON @redirection_modules.id=@redirection_gro ups.module_id AND @redirection_modules.type=%s WHERE @redirection_items.url=%s
    Explain:

    1 PRIMARY wp_redirection_items ref url,group_idpos,group url 602 const 1 Using where
    1 PRIMARY wp_redirection_groups eq_ref PRIMARY,module_id,status PRIMARY 4 knd_blog.wp_redirection_items.group_id 1 Using where
    1 PRIMARY wp_redirection_modules eq_ref PRIMARY,type PRIMARY 4 knd_blog.wp_redirection_groups.module_id 1 Using where
    2 UNION wp_redirection_items ref regex,group_idpos,group regex 4 const 1
    2 UNION wp_redirection_groups eq_ref PRIMARY,module_id,status PRIMARY 4 knd_blog.wp_redirection_items.group_id 1 Using where
    2 UNION wp_redirection_modules eq_ref PRIMARY,type PRIMARY 4 knd_blog.wp_redirection_groups.module_id 1 Using where
    NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL`

    There are other WHERE...OR in use so the above is just an example. It's not a performance killer by any means, so not a high priority issue.

    http://wordpress.org/extend/plugins/redirection/

  2. markwhite
    Member
    Posted 1 year ago #

    Formatting did not work as expected. No preview or edit button, so that's just how it is...

Topic Closed

This topic has been closed to new replies.

About this Plugin

  • Redirection
  • Frequently Asked Questions
  • Support Threads
  • Reviews

About this Topic

Tags

No tags yet.