December 14th, 2012, 02:38 AM
Mysql Inconsistency Issue Faced In Production Server
Yesterday, i came across a problem which let me to scratch every hair of mine.
My application got stuck due to a query :
select * from tbl_name where fld_name ='xyz';
While executing this query even in db it use to wait for around 10 seconds though the table contains only 10 entries and then results with mysql connection disconnected.
But when i execute select * from tbl_name; it provides rapid results wit no issues.
Even if i try with some other value like select * from tbl_name where fld_name ='pqr'; then also it executes but it fails only for a specific value 'xyz'.
This issue was faced in production server. Initially i created a new database and connected my application to it.
Can someone let me know what is wrong?
December 14th, 2012, 11:06 AM
Was there an index on the table for column fld_name? If so, it may have gone bad and been causing negative things to happen when accessed.
There's no reason to have an index on a table with only 10 records, as a full table scan will not take any longer than an index scan.