WordPress.org

Ready to get started?Download WordPress

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

About this Topic

Tags

No tags yet.