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