18.18 Solving problems with no matching rows

If you have a complicated query with many tables 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. 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 find 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. Take the file up in a editor, remove some insert lines (if there are too many of these) and add your select statement last in 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.