SQL - Strange issue with SELECT

Question

I have a strange situation with a simple select by column pqth_scan_code from the following table:

table pqth_

Field           Type         Null   Key     Default     Extra   
pqth_id         int(11)      NO     PRI     NULL        auto_increment
pqth_scan_code  varchar(250) NO             NULL    
pqth_info       text         YES            NULL    
pqth_opk        int(11)      NO             999

query 1

This query took 12.7221 seconds to execute

SELECT * FROM `pqth_` WHERE pqth_scan_code = "7900722!30@3#6$EN" 

query 2 This query took took 0.0003 seconds to execute

SELECT * FROM `pqth` WHERE `pqth_id`=27597 

Based on data from table pqth_ I have created the following table, where pqthc_id = pqth_id and pqthc_scan_code=pqth_scan_code

table pqthc

Field           Type         Null   Key     Default     Extra   
pqthc_id        int(11)      NO     PRI     NULL    
pqthc_scan_code tinytext     NO             NULL    

The same query ,query1, on table pqthc took 0.0259 seconds to run

SELECT * FROM `pqthc` WHERE pqthc_scan_code = "7900722!30@3#6$EN"

If I run the following query will took 0.0971 seconds, very strange.

query 3

SELECT * FROM `pqth` WHERE pqth_id = (SELECT pqthc_id From pqthc where pqthc_scan_code = "7900722!30@3#6$EN")

My question is why a SELECT by pqth_scan_code is slow and SELECT by pqth_id is fastest? Both columns are indexed.

For testing please get the export from this link

The same behavior is with MySQL and MariaDB server

Answer 1

SELECT * FROM `pqth_` WHERE pqth_scan_code = "7900722!30@3#6$EN" 

needs INDEX(pqth_scan_code). Period. End of discussion.

SELECT * FROM `pqth` WHERE `pqth_id`=27597 

has a useful index, since a PRIMARY KEY is an index (and it is unique).

SELECT * FROM `pqthc` WHERE pqthc_scan_code = "7900722!30@3#6$EN"

also needs INDEX(pqthc_scan_code). But it may have been faster because (1) the table is smaller, or (2) you ran the query before, thereby caching what was needed in RAM.

Please don't prefix column names with the table name.
Please don't have table names so close to each other that they are hard to distinguish. (pqth and pqthc)

SELECT  *
    FROM  `pqth`
    WHERE  pqth_id = 
      ( SELECT  pqthc_id
            From  pqthc
            where  pqthc_scan_code = "7900722!30@3#6$EN"
      )

The construct IN ( SELECT ... ) is not efficient.

It is rare to have two table with the same PRIMARY KEY; are you sure you meant that?

Use a JOIN instead:

SELECT  a.*
    FROM  `pqth` AS a
    JOIN  pqthc AS c ON a.id = c.id
    where  c.scan_code = "7900722!30@3#6$EN" 

If that is 'correct', then I recommend this 'covering' index:

INDEX(scan_code, id)

instead of the shorter INDEX(scan_code) I previously recommended.

More on indexing.