I have this sample table on MySQL v.5.7.17 (InnoDB, 5.000 rows):
+-------+----------+
| code1 | code2 |
+-------+----------+
| 4714 | 15895510 |
| 1041 | 10000158 |
| 2866 | 10000000 |
(...)
Code 1 is indexed.
I test with a simple query: SELECT code2 FROM codes WHERE code1=1041
Case 1: I run it on MySQL console and return the correct value 10000158.
Case 2: I run it from PHP (mysqli_query()) and no rows returned (mysqli_num_rows()=0).
If I run the same query but with a different code1 (WHERE code1=2866) I get a correct result: 10000000.
I've been doing a lot of testing and never got the row for code1=1041.
Finally, I run a OPTIMIZE TABLE codes and I get the correct value from code1=1041! I'm surprised!
From the console has always worked. From PHP some records were not displayed. How can OPTIMIZE TABLE solve this problem in PHP? Could it be a corruption in the index and only affect PHP-Mysqli engine?
Thank you!
I would think this is expected (or at least highly probable) behavior. OPTIMIZE TABLE defragments the database and the indexes. If your indexes were corrupted (or even highly fragmented), this command will fix them. My best guess is that your highly fragmented index was timing out during the search for that value and OPTIMIZE TABLE fixed that.
Read this: https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html and also the accepted answer to this question