Search code examples
mysqlperformanceinnodb

MySQL used ALL Type while searching on Primary Key

My table schema:

This is my Table Schema

My above table has ~10L data. While using EXPLAIN, it shows as,

enter image description here

From this, type shows ALL, Extra shows Using where and rows not in O(1). But, for searching on primary key, the type should be const, rows be in O(1) ?? I can't able to figure out the issue, which results in slowing the queries.

Solution

  • Your id field is varchar while you pass the value you are looking for as a number.

    This means that mysql has to perform an implicit data conversion and will not be able to use the index for looking up the value:

    For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:

    SELECT * FROM tbl_name WHERE str_col=1;

    The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'.

    Either convert your id field to number or pass the value as string.