1

Server version: Amazon AWS: 5.6.19-log MySQL Community Server (GPL)

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

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


mysql> EXPLAIN select VERSION_NO from bb_table where UPDATE_STAMP > '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> EXPLAIN select FILE_DATA from bb_table where UPDATE_STAMP > '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?

  • 1
    How long are the values in the file_data column? (the values in the other rows, that are not selected, I mean). It would be good if you added the SHOW CREATE TABLE bb_table; as well. – ypercubeᵀᴹ Oct 10 '16 at 19:15
  • 1
    I also wonder how fast this will be select file_data from bb_table where pk = (select pk from bb_table where update_stamp > '0' limit 1); (replace pk with the primary key). – ypercubeᵀᴹ Oct 10 '16 at 19:18
  • @ypercubeᵀᴹ 16: Thank you for your update. above query is working quick but can you explain me why the previous one is taking time. – Uday Oct 11 '16 at 11:37
  • I guess, its fetching each "FILE_DATA" and then taking its respective "UPDATE_STAMP" and "SID" then processing the same against the where clause. FIELD_DATA is long text and avergae row size is 1.6 MB. If this is correct, is there any way to force the query to first fetch where clause fields and then validate against where clause and then get FIELD_DATA ? – Uday Oct 11 '16 at 11:44
  • 1
    Please add the SHOW CREATE TABLE bb_table; output in the question. Also tell us how many rows the table has and how big is its size on disk. – ypercubeᵀᴹ Oct 11 '16 at 15:40
3

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 > '0' 
             limit 1
           ) ;
  • ypercube, perfectly agrees with your update. I am sure your word is correct. But is nt it wrong to fetch long text data along with processing where clause ? this is what my concern is.... – Uday Oct 11 '16 at 16:08
1

(You must provide SHOW CREATE TABLE for questions like this.)

LONGTEXT is the issue. With InnoDB, "large" columns are stored separately from the rest of the columns. If there are 2555 rows (the estimate given by EXPLAIN) in the result, and if FILE_DATA is usually "large", then the query involves at least 2555 extra disk hits. For typical spinning disks, that is an extra 25 seconds. Since you are using AWS, and that implies SSDs (??), I would have expected better than 8 seconds. Possible reasons: More than 2555 rows; FILE_DATA is more than one block (16KB); interference from other users on the same hardware; etc.

But you have a LIMIT 1 without an ORDER BY. (Are you willing to get an arbitrary row??) So, the indexes must be inadequate. (Did we already ask for SHOW CREATE TABLE?)

What you need is is this composite index:

INDEX(SID, UPDATE_STAMP)  -- in that order

That should make both queries very fast. The FILE_DATA one will be proportional to the size of the LONGTEXT. If it is a gigabyte, then you will probably exceed 8 seconds.

  • "then the query involves at least 2555 extra disk hits" - really? I would have expected MySQL to scan "only" the table to find the matching rows, and then head over to the separate storage and fetch the actual data? – a_horse_with_no_name Oct 17 '16 at 21:26
  • @a_horse_with_no_name -- You could be right. It will be tricky to devise a test to prove/disprove that. – Rick James Oct 17 '16 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.