StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First, create an index on <code>HugeFileTable.file_last_access</code>.</p> <p>Then try the following query:</p> <pre><code>SELECT r.range_name, COUNT(t.file_last_access) as FileCount FROM `DateRanges` r JOIN `HugeFileTable` t ON (t.file_last_access BETWEEN CURDATE() + INTERVAL r.range_start DAY AND CURDATE() + INTERVAL r.range_end DAY) GROUP BY r.range_name; </code></pre> <p>Here's the <code>EXPLAIN</code> plan that I got when I tried this query on MySQL 5.0.75 (edited down for brevity):</p> <pre><code>+-------+-------+------------------+----------------------------------------------+ | table | type | key | Extra | +-------+-------+------------------+----------------------------------------------+ | t | index | file_last_access | Using index; Using temporary; Using filesort | | r | ALL | NULL | Using where | +-------+-------+------------------+----------------------------------------------+ </code></pre> <p>It's still not going to perform very well. By using <code>GROUP BY</code>, the query incurs a temporary table, which may be expensive. Not much you can do about that.</p> <p>But at least this query eliminates the Cartesian product that you had in your original query.</p> <hr> <p><strong>update:</strong> Here's another query that uses a correlated subquery but I have eliminated the <code>GROUP BY</code>.</p> <pre><code>SELECT r.range_name, (SELECT COUNT(*) FROM `HugeFileTable` t WHERE t.file_last_access BETWEEN CURDATE() - INTERVAL r.range_end DAY AND CURDATE() - INTERVAL r.range_start DAY ) as FileCount FROM `DateRanges` r; </code></pre> <p>The <code>EXPLAIN</code> plan shows no temporary table or filesort (at least with the trivial amount of rows I have in my test tables):</p> <pre><code>+----+--------------------+-------+-------+------------------+--------------------------+ | id | select_type | table | type | key | Extra | +----+--------------------+-------+-------+------------------+--------------------------+ | 1 | PRIMARY | r | ALL | NULL | | | 2 | DEPENDENT SUBQUERY | t | index | file_last_access | Using where; Using index | +----+--------------------+-------+-------+------------------+--------------------------+ </code></pre> <p>Try this query on your data set and see if it performs better.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload