Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table that isn't very large row wise (in the 100ks range), but that contains a lot of raw data that is very large in size. Despite having a relatively little number of rows, it's around 1.5GB.

So it's quite important to know if MySQL loads the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY and indexes when performing the query, and the rest of the columns at the very end?

An example query:

SELECT HugeDataTable.*, Table2.Name
FROM Table1
LEFT JOIN Table2 ON Table1.`ID` = Table2.`Table1ID` 
LEFT JOIN HugeDataTable FORCE INDEX(RowOrder) ON Table2.`ID` = HugeDataTable.`Table2ID` 
WHERE HugeDataTable.Category = 5
AND HugeDataTable.RowOrder >10000 AND HugeDataTable.ID <> "h4324h534"
ORDER BY HugeDataTable.`RowOrder` DESC LIMIT 18 ;

Using Explain SELECT I've managed to find that MySQL scans around 70k rows per query. The query is rather fast, but I'm not sure if it's due to row caching, as I can't simulate a heavy load on the server.

So, my question is, will the columns containing the large raw data be loaded after the query limits the result to 18 rows, and thus loading only the little raw data needed in the end?

Or will they be loaded before the limit, and so 70k rows, which are around 1GB's worth of data be loaded before the limit? And if it's the latter case, what can be made to prevent such a thing, since the server only has 1GBs of RAM.

Edit: I've added the EXPLAIN.

id  select_type  table          type    possible_keys                       key               key_len  ref                           rows   Extra                     
1   SIMPLE       HugeDataTable  range   Table2ID,Category,RowOrder          RowOrder          9                                      49591  Using where               
1   SIMPLE       Table2         eq_ref  PRIMARY                             PRIMARY           10       const,HugeDataTable.Table2ID  1      Using where; Using index  
1   SIMPLE       Table1         ref     PRIMARY                             PRIMARY           2        Table2.Table1ID               1    
share|improve this question
    
The version is 5.5.33 – Pat Jan 14 '14 at 15:41
    
Don't use SELECT <tablename>.* or SELECT *. Those both explicitly advise the engine to retrieve all columns. This is an anti-pattern that is to be avoided, as it breaks all sort of performance and interface guidelines. Always explicitly name all columns of a SELECT (except possibly when referencing all columns of a directly referenced subquery). – Pieter Geerkens Sep 7 '14 at 23:55
up vote 2 down vote accepted

Because it is missing, I would suppose that your query execution plan shows a full-scan on your raw-data table. If this is the case, then your entire rows will be loaded into memory (including raw columns), which obviously is not ok.

The easiest way to avoid this would be to create an index on the filtering columns, so that the query will scan the index prior, and only if the row is a candidate for your result-set, will reach the table and load it into memory.

From comment discussion:

The query performs fast mostly because of the index (thus the slow performance when removing it) ...but your rows, are still loaded entirely. As you thought, only some of the rows get into the memory, but this does not mean that only parts of them are loaded. The trick here is that the index points only to those rows that are relevant. You're using the index with a ">" operator, which causes a Range Scan, so it is possible that more rows than you need/get are referenced.

You could try to apply the 18 limit earlier, rather at the end of the query. Or you could make a new table with only the raw column and a foreign key to the first table. Link the result from the first table just at the very end of the query. Remember though, if you want to order the rows, and perform the ORDER BY after you join the two tables, it will still perform the sorting with the raw data loaded into memory.

Try to sort the rows of the main table before joining the raw table. If the main table is leading the join, then you should get the rows sorted after the join too.

share|improve this answer
    
Yes, that's what I've done. I managed to speed up the query from 1-2 seconds to 0.1-0.2 seconds. – Pat Jan 15 '14 at 13:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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