MySQL Query – No Quote For String Fields

One of the module in our project, it is experiencing a slow performance. The module will let the user scan the PCB barcode of an item and updated a record on the database. It was originally built on MyISAM table engine but when we changed it to InnoDB, it was showing a drastic change on performance.

When the user scans the barcode, it takes like 800 ms or even 1 second to complete the process. In a production environment, it is such a noticeable delay. Good thing that specific line is not the busiest one.

The process goes like this:

Check the barcode against the database if it is valid.
Update 4 fields on a single record on that table.
Get the previous count on the summary table.
Update the previous count by adding one to it.

According to the profile:

Query #0 | Elapse: 0.0018610954284668 | Query: connect
Query #1 | Elapse: 0.24757719039917 | Query: SELECT `t_lot_number_bre`.* FROM `t_lot_number_bre` WHERE (Scan_Number = 3010083) AND (Read_Check_Flg = 0) LIMIT 1
Query #2 | Elapse: 0.062864065170288 | Query: UPDATE `t_lot_number_bre` SET `Stock_Check_Flg` = ?, `Stock_Check_Date` = ?, `Stock_Check_User_Cd` = ?, `Stock_Check_Name` = ? WHERE (Lot_Number = 'TEST-ESP-0001') AND (Scan_Number = 3010083) AND (Read_Check_Flg = 0) AND (Stock_Check_Flg = 0)
Query #3 | Elapse: 0.0007328987121582 | Query: SELECT `t_lot_number`.* FROM `t_lot_number` WHERE (Lot_Number = 'TEST-ESP-0001') AND (Reading_Date = '2009-10-20 04:57:40') LIMIT 1
Query #4 | Elapse: 0.068100929260254 | Query: UPDATE `t_lot_number` SET `Check_Count` = ? WHERE (Lot_Number = 'TEST-ESP-0001') AND (Reading_Date = '2009-10-20 04:57:40')

Where the longest query is this:

SELECT `t_lot_number_bre`.* FROM `t_lot_number_bre` WHERE (Scan_Number = 3010083) AND (Read_Check_Flg = 0) LIMIT 1

Running an EXPLAIN statement, it shows the my INDEX for Scan_Number was not hit.

And when run, it takes 0.2670 sec on phpMyAdmin. Analyzing the EXPLAIN further, it seems that the query will search the whole table to find the specified record. What the!?!

Scan_Number is an INDEX, therefore it should not scan the whole table. I tried to optimize the table.Then run the query again, but still the problem exists. Then finally, I rewrite the query by putting single quotes around Scan_Number. It is just a wild guess, then I tried to run it.

SELECT `t_lot_number_bre`.* FROM `t_lot_number_bre` WHERE (Scan_Number = '3010083') AND (Read_Check_Flg = 0) LIMIT 1

I was shocked with the result as it took 0.008 seconds to complete. As I looked at the EXPLAIN result, it only looks up one row on the table. Wow!

It turns out that MySQL does not search the Scan_Number properly since the supplied value is an integer, whereas the field was a VARCHAR(20). Looking on the source code on that module, there was a line that cast the barcode value into integer.

$this->_scanNumber = (int)$params['s_bar_code'];

//The solution was to change (int) to (string):

$this->_scanNumber = (string)$params['s_bar_code'];

Another lesson learned for today!

Leave a reply