Why is the SELECT timeline different though the WHERE clause & EXPLAIN plan are same? Server version:         Amazon AWS: 5.6.19-log MySQL Community Server (GPL)

mysql&gt; select VERSION_NO from bb_table where UPDATE_STAMP &gt; '0' and SID='M02147'  limit 1 ;
Empty set (0.01 sec)

mysql&gt; select FILE_DATA from bb_table where UPDATE_STAMP &gt; '0' and SID='M02147'  limit 1 ;
Empty set (8.05 sec)


mysql&gt; EXPLAIN select VERSION_NO from bb_table where UPDATE_STAMP &gt; '0' and SID='M02147'  limit 1 ;
+----+-------------+----------------+------+----------------------+------+---------+------+------+-------------+
| id | select_type | table          | type | possible_keys        | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------------+------+----------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | bb_version_mas | ALL  | UPDATE_STAMP_SID     | NULL | NULL    | NULL | 2555 | Using where |
+----+-------------+----------------+------+----------------------+------+---------+------+------+-------------+
1 row in set, 2 warnings (0.00 sec)


mysql&gt; EXPLAIN select FILE_DATA from bb_table where UPDATE_STAMP &gt; '0' and SID='M02147'  limit 1 ;
+----+-------------+----------------+------+----------------------+------+---------+------+------+-------------+
| id | select_type | table          | type | possible_keys        | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------------+------+----------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | bb_version_mas | ALL  | UPDATE_STAMP_SID     | NULL | NULL    | NULL | 2555 | Using where |
+----+-------------+----------------+------+----------------------+------+---------+------+------+-------------+
1 row in set, 2 warnings (0.00 sec)

VERSION_NO     :varchar(50), NO INDEX
FILE_DATA      :longtext, NO INDEX
WHERE CLAUSE   :SAME FOR BOTH 


If the WHERE clause is processed first, the time for these SELECTs should be the same but that is not the case here. Why is there a huge difference in timelines?

What difference does it make that the selected column is longtext when the WHERE clause is not satisfied anyway?

Caching is disabled. There are no rows satisfying the WHERE clause. I am wondering why longtext data has to be considered while the result set is just 0 rows?
Without more information, we can only guess why these two almost identical queries run with so much different efficiency (about 500x).

But we can make a few guesses based on the facts:


There is no index on SID and UPDATE_STAMP so both queries do a table scan. 
The difference must lie in the mediumtext type. 
This difference in speed suggests that the first query can do the table scan relatively quickly (the table is small) and find that no rows satisfy the condition. But the second query is very slow compared to the first one. My guess is that the mediumtext column has some long texts in it. As text and long varchar column values are not stored in the table/index pages but in different locations, this would explain the difference. In the 2nd query, the engine fetches these values as well while running the full table scan (so many more pages have to be read from disk) and thus the slow performance.


A suggestion to improve efficiency. Find first the primary key of the wanted row, using a subquery, then fetch the whole row:

select file_data 
from bb_table 
where pk = ( select pk            -- where pk is the PRIMARY KEY
             from bb_table
             where update_stamp &gt; '0' 
             limit 1
           ) ;