WordPress.org

Support

Support » Plugins and Hacks » Redirection » Queries using 'WHERE…OR' causes full table scans [low priority]

Queries using 'WHERE…OR' causes full table scans [low priority]

  • 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/

Viewing 1 replies (of 1 total)
Viewing 1 replies (of 1 total)
  • The topic ‘Queries using 'WHERE…OR' causes full table scans [low priority]’ is closed to new replies.
Skip to toolbar