GNU Info

(mysql.info)No matching rows

---

Prev: Deleting from related tables Up: Query Issues

---

Enter node , (file) or (file)node

Solving Problems with No Matching Rows
--------------------------------------

If you have a complicated query that has many tables and that doesn't
return any rows, you should use the following procedure to find out what
is wrong with your query:

  1. Test the query with `EXPLAIN' and check if you can find something
     that is obviously wrong. Note: `EXPLAIN'.

  2. Select only those fields that are used in the `WHERE' clause.

  3. Remove one table at a time from the query until it returns some
     rows.  If the tables are big, it's a good idea to use `LIMIT 10'
     with the query.

  4. Do a `SELECT' for the column that should have matched a row against
     the table that was last removed from the query.

  5. If you are comparing `FLOAT' or `DOUBLE' columns with numbers that
     have decimals, you can't use `='!  This problem is common in most
     computer languages because floating-point values are not exact
     values:

          mysql> SELECT * FROM table_name WHERE float_column=3.5;
             ->
          mysql> SELECT * FROM table_name WHERE float_column between 3.45 and 3.55;

     In most cases, changing the `FLOAT' to a `DOUBLE' will fix this!

  6. If you still can't figure out what's wrong, create a minimal test
     that can be run with `mysql test < query.sql' that shows your
     problems.  You can create a test file with `mysqldump --quick
     database tables > query.sql'. Open the file in an editor, remove
     some insert lines (if there are too many of these), and add your
     select statement at the end of the file.

     Test that you still have your problem by doing:

          shell> mysqladmin create test2
          shell> mysql test2 < query.sql

     Post the test file using `mysqlbug' to <mysql@lists.mysql.com>.