MYSQL - Why does this SELECT get slower the further down the table?
FIRST QUERY

SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 0,1;


Now, look back at the EXPLAIN plan for it:

EXPLAIN SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 0,1;
1   SIMPLE  wp_users    index   NULL    PRIMARY 8   NULL    1


How many rows do the Query Optimizer See? 1

SECOND QUERY

SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 170003,1;


Now, look back at the EXPLAIN plan for it:

EXPLAIN SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 170003,1;
1   SIMPLE  wp_users    ALL NULL    NULL    NULL    NULL    175862  Using filesort


How many rows do the Query Optimizer See? 175862

When the Query Optimizer sees too many rows to have to process, it decide to "throw indexes under the bus". MySQL will then do a full table scan, as indicated by the expression ALL in the EXPLAIN plan. The filesort occurs because you had ORDER BY ID.

SUGGESTION

Usually, it is recommended to retrieve rows by cursors. Personally, I hate using cursors. I have an alternative that is a little unorthodox. I would create a temp table with all the email addresses, treating it like an array but accessing it like a table.

Here is that paradigm

DELIMITER $$
DROP PROCEDURE IF EXISTS ProcessEmail;
CREATE PROCEDURE ProcessEmail()
BEGIN

    DROP TABLE IF EXISTS EmailArray; 
    CREATE TABLE EmailArray
    (
        ndx INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        user_email varchar(100) NOT NULL DEFAULT ''
    ) ENGINE=MEMORY;
    INSERT INTO EmailArray (user_email) SELECT user_email FROM wp_users;

    SET @ndxnext = 0;
    SELECT COUNT(1) INTO @ndxlast FROM IDArray;
    WHILE @ndxnext &lt; @ndxlast DO
      SET @ndxnext = @ndxnext + 1;
      SELECT user_email INTO @GivenEmail FROM EmailArray WHERE ndx = @ndxnext;
      ...
      ... Process whatever you need with the @GivenEmail 
      ...
    END WHILE;

    DROP TABLE IF EXISTS EmailArray; 
END;
$$
DELIMITER ;


GIVE IT A TRY !!!