There was a very simple query which was taking time inside stored procedure like 20 sec.
If I execute this query directly on the server, it was taking only 13 milli sec.
Using the Full Process List, I found that mysql was adding collation parameter to the query
something like
NAME_CONST(' ',_utf8 COLLATE 'utf8_general_ci')
I found that the columns in my table have different char set. If you change the char set of innodb table, it changes for the columns as well, but the same is not the case with myisam.
Collation causes the full table scan instead of using the index.
Corrected the collation, and now it works as expected.
If I execute this query directly on the server, it was taking only 13 milli sec.
Using the Full Process List, I found that mysql was adding collation parameter to the query
something like
NAME_CONST(' ',_utf8
I found that the columns in my table have different char set. If you change the char set of innodb table, it changes for the columns as well, but the same is not the case with myisam.
Collation causes the full table scan instead of using the index.
Corrected the collation, and now it works as expected.
No comments:
Post a Comment