Take the 2-minute tour ×
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.

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
    
what is MySQL version? –  ravnur Jan 14 at 15:39
    
The version is 5.5.33 –  Pat Jan 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 at 23:55

1 Answer 1

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.

Hope this helps!

share|improve this answer
    
Currently I have the RowOrder indexed. The query is running rather fast, even after FLUSH TABLES, restarting the mysql server and using SQL_NO_CACHE. If I remove the FORCE INDEX(RowOrder), it becomes horribly slow.. But I want to understand if it's just coincidence or what is the reason behind all that. –  Pat Jan 14 at 15:09
    
I'm getting type:range on the HugeDataTable, and ref type for the other two. The extra for all three has Using where, but the Table1 is using index as well. –  Pat Jan 14 at 15:14
    
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. –  ddaniel Jan 14 at 15:24
    
But what's weird is that even with the index there are still around 70k rows being referenced. Shouldn't that be the bottleneck here, rather than the ordering of the rows? Or is the explain being inaccurate about the number of rows? –  Pat Jan 14 at 15:28
    
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. What is the cardinality returned by the index? And how many rows are returned by your query in the end? The number of rows order can be judged by the number of rows you get, because the ordering is the very last operation performed. –  ddaniel Jan 14 at 15:41

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.