Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    3

    Question Unanswered: Impossible WHERE in explain plan is incorrect

    I am running into anissue with a table I've loaded for months with no problems. There are 4,301,290,034 records in the LoanMonth table and each new month I insert approximately 40 million more. This last month I loaded the data as usual with no errors and the total record counts are correct. When I attempt to query the table for the most recently added data I get an empty set. When I check the explain plan I get the message "Impossible WHERE noticed after reading const tables " which doesn't make sense because I can query on any of the previous months data. I have recreated the table and indexes as well as reloaded the most recent month's data, but I am still getting the same empty set problem when I query for the last set of data I loaded. I have also verified that the data I am loading doesn't have any hidden, non-printable, or special characters.

    Please take a look at the queries and their results below and let me know if anyone has any ideas of what could be causing this problem.
    Thanks,
    Scott

    Version 5.0.75-0ubuntu10.3-log

    Code:
    mysql> desc LoanMonth;
    +-----------+----------------------+------+-----+---------+-------+
    | Field     | Type                 | Null | Key | Default | Extra |
    +-----------+----------------------+------+-----+---------+-------+
    | LoanId    | int(10) unsigned     | NO   | PRI | NULL    |       |
    | AsOfMonth | smallint(5) unsigned | NO   | PRI | NULL    |       |
    +-----------+----------------------+------+-----+---------+-------+
    
    mysql> show indexes from LoanMonth;
    +-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table     | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | LoanMonth |          0 | PRIMARY   |            1 | LoanId      | A         |   138751291 |     NULL | NULL   |      | BTREE      |         |
    | LoanMonth |          0 | PRIMARY   |            2 | AsOfMonth   | A         |  4301290034 |     NULL | NULL   |      | BTREE      |         |
    | LoanMonth |          1 | LOAM_1_UK |            1 | AsOfMonth   | A         |         268 |     NULL | NULL   |      | BTREE      |         |
    | LoanMonth |          1 | LOAM_1_UK |            2 | LoanId      | A         |  4301290034 |     NULL | NULL   |      | BTREE      |         |
    +-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    Code:
    mysql> select AsOfMonth, count(*) 
             from LoanMonth 
            where AsOfMonth = 376 
         group by AsOfMonth;
    +-----------+----------+
    | AsOfMonth | count(*) |
    +-----------+----------+
    |       376 | 39193907 |
    +-----------+----------+
    
    mysql> select AsOfMonth, count(*) 
             from LoanMonth 
            where AsOfMonth >= 376 
         group by AsOfMonth;
    +-----------+----------+
    | AsOfMonth | count(*) |
    +-----------+----------+
    |       376 | 39193907 |
    |       377 | 39009879 |
    +-----------+----------+
    
    mysql> select AsOfMonth, count(*) 
            from LoanMonth 
           where AsOfMonth >= 377 
        group by AsOfMonth;
    +-----------+----------+
    | AsOfMonth | count(*) |
    +-----------+----------+
    |       377 | 39009879 |
    +-----------+----------+
    
    mysql> explain 
          select AsOfMonth, count(*) 
            from LoanMonth 
           where AsOfMonth >= 377 
        group by AsOfMonth;;
    +----+-------------+-----------+-------+---------------+-----------+---------+------+----------+--------------------------+
    | id | select_type | table     | type  | possible_keys | key       | key_len | ref  | rows     | Extra                    |
    +----+-------------+-----------+-------+---------------+-----------+---------+------+----------+--------------------------+
    |  1 | SIMPLE      | LoanMonth | range | LOAM_1_UK     | LOAM_1_UK | 2       | NULL | 85595562 | Using where; Using index |
    +----+-------------+-----------+-------+---------------+-----------+---------+------+----------+--------------------------+
    
    mysql> select AsOfMonth, count(*) 
             from LoanMonth 
            where AsOfMonth = 377 
         group by AsOfMonth;
    Empty set (0.00 sec)
    
    mysql> explain 
           select AsOfMonth, count(*) 
             from LoanMonth 
            where AsOfMonth = 377 
         group by AsOfMonth;
    +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
    +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
    |  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
    +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think you've exceeded the capacity of your loanId

    the signed range for INTEGER is -2147483648 to 2147483647, while the unsigned range is 0 to 4294967295
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    3
    Thanks for the quick response. I checked the maximum LoanId value in my table and found that it is 2,147,483,598. This is close to the maximum signed integer value of 2,147,483,647 but not quite greater. I am going to change the datatype to be safe (which will take hours), but I'm not sure that is the problem since I haven't actually gone over the max value yet.
    The primary key on the table is a combination of LoanId and AsOfMonth which is how I can have 4 billion rows with a max LoanId of 2 billion. I have 268 distinct values of AsOfMonth, from 109 to 377.
    Is there any more information I can provide that would be helpful?

    Thanks again for your input,
    Scott

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh yeah, i see it now, i'm sorry, i completely overlooked your compound PK

    and i also don't know what the "impossible WHERE clause" means
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2011
    Posts
    3
    No problem. The "impossible WHERE" is generated by the explain plan when the where clause is always false and will never return rows.

    For example:
    Code:
    SELECT * FROM dummy_table WHERE 1=0;
    From the MySQL docs:

    • Impossible WHERE noticed after reading const tables


    MySQL has read all const (and system) tables and noticed that the WHERE clause is always false.

    The annoying part of this is that this query returns data:
    Code:
      select AsOfMonth, count(*) 
        from LoanMonth 
       where AsOfMonth >= 377 
    group by AsOfMonth;
    +-----------+----------+
    | AsOfMonth | count(*) |
    +-----------+----------+
    |       377 | 39009879 |
    +-----------+----------+
    And this one doesn't:
    Code:
     
      select AsOfMonth, count(*) 
        from LoanMonth 
       where AsOfMonth = 377 
    group by AsOfMonth;
    Empty set (0.00 sec)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •