Mysql – Query not returning results if selecting by indexed columns (Mysql 5.6)

innodbMySQLmysql-5.6primary-keyselect

We had a very strange encounter that all of our queries that uses an indexed column in the select wasn't returning any results. On the other hand, if we do select * or on any other non-indexed column then we get the expected results.

For example,

SELECT product_id FROM product where product_id = 1;

doesn't return any results.

But if we do this:

SELECT * FROM product where product_id = 1;

We get the expected results.

The table engine used to be MyISAM, however, when we changed it to InnoDB we started having these issues. We had to change it back to MyISAM because a lot of websites were down because of the above issue.

The server specs:

  • Ubuntu 16.04 LTS
  • Mysql 5.6
  • 27GB RAM
  • 300 GB Free space while using 100 GB

My.cnf:

  1. innodb_log_file_size = 3G
  2. innodb_buffer_pool_instances = 21
  3. key_buffer_size = 500M
  4. innodb_buffer_pool_size = 22G
  5. sort_buffer_size = 512K
  6. read_rnd_buffer_size = 512K
  7. thread_cache_size = 9
  8. thread_cache_size = 9
  9. innodb_file_per_table = 1
  10. innodb_buffer_pool_dump_at_shutdown = 1
  11. innodb_buffer_pool_dump_at_shutdown = 1

We didn't have any errors on the application side and no error logs in Mysql. The server had 6 GB of Free memory so we weren't running out of memory. The above recommendations were made by mysqltunner.pl

Results from Mysqltunner:

[OK] Slow queries: 0%    
[OK] Thread cache hit rate: 93%        
[OK] Table cache hit rate: 95%     
[OK] Open file limit used: 0%       
[OK] InnoDB File per table is activated        
[OK] InnoDB buffer pool / data size: 20.0G/21.3G        
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 3G *     2/20G should be equal 25%        
[OK] InnoDB buffer pool instances: 21       
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.95%        
[OK] InnoDB Write Log efficiency:  93%    
[OK] InnoDB log waits: 0.00% (0 waits / 68 writes)

Edit 1:

  1. Query used to convert MyISAM to InnoDb

    ALTER TABLE `product` ENGINE=InnoDB;

  2. Table Schema:

     CREATE TABLE `product` (
  `product_id` int(11) NOT NULL,
  `product_type` int(11) NOT NULL DEFAULT '1',
  `product_quantity` float NOT NULL DEFAULT '0',
  `product_model` varchar(32) DEFAULT NULL,
  `product_sku` varchar(255) DEFAULT NULL,
  `product_label` tinyint(4) NOT NULL,
  `product_flavor` tinyint(4) NOT NULL,
  `product_new` tinyint(1) NOT NULL,
  `product_sale` tinyint(1) NOT NULL,
  `product_image` varchar(64) DEFAULT NULL,
  `product_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `product` ADD PRIMARY KEY (`products_id`),

-- Added the FullText key when we changed it to InnoDB 
ALTER TABLE `product` ADD FULLTEXT KEY `model_index` (`products_model`);

Best Answer

I wonder what would happen if you ran OPTIMIZE TABLE product; on your MySQL server. OPTIMIZE TABLE will “rebuild” the physical storage of your table, including the indexes.

https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

Related Question