StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You should have used Postgres, lol. A simple query like this should not take more than some tens of seconds provided you have enough RAM to avoid disk thrashing.</p> <p>Anyway. </p> <p>=> Is the problem in the SELECT or the INSERT ?</p> <p>(run the SELECT alone on a test server and time it).</p> <p>=> Is your query disk bound or CPU bound ?</p> <p>Launch it on a test server and check vmstat output. If it is CPU bound, skip this. If it is disk bound, check the working set size (ie the size of your database). If the working set is smaller than your RAM, it should not be disk bound. You can force loading of a table in the OS cache prior to executing a query by launching a dummy select like SELECT sum( some column ) FROM table. This can be useful if a query selects many rows in random order from a table which is not cached in RAM... you trigger a sequential scan of the table, which loads it in cache, then random access is much faster. With some trickery you can also cache indexes (or just tar your database directory to >/dev/null, lol).</p> <p>Of course, adding more RAM could help (but you need to check if the query is killing the disk or the CPU first). Or telling MySQL to use more of your RAM in the configuration (key_buffer, etc). </p> <p>If you are making millions of random HDD seeks you are in PAIN.</p> <p>=> OK now the query</p> <p>FIRST, ANALYZE your tables.</p> <blockquote> <p>LEFT JOIN shift_positions ON su.shift_position_id = shift_positions.id WHERE shift_positions.level = 1</p> </blockquote> <p>WHY do you LEFT JOIN and then add a WHERE on it ? The LEFT makes no sense. If there is no row in shift_positions, LEFT JOIN will generate a NULL, and the WHERE will reject it.</p> <p>Solution : use JOIN instead of LEFT JOIN and move (level=1) in the JOIN ON() condition.</p> <p>While you're at it, also get rid of the other LEFT JOIN (replace by JOIN) unless you are really interested in all those NULLs ? (I guess you are not).</p> <p>Now you probably can get rid of the subselect.</p> <p>Next.</p> <blockquote> <p>WHERE TIME(t.created) BETWEEN shift_times.start AND shift_times.end)</p> </blockquote> <p>This is not indexable, cause you have a function TIME() in the condition (use Postgres, lol). Lets look at it :</p> <blockquote> <p>JOIN shift_times ON (shifts.id = shift_times.shift_id AND shift_times.dow = DAYOFWEEK(t.created) AND TIME(t.created) BETWEEN shift_times.start AND shift_times.end)</p> </blockquote> <p>Ideally you would like to have a multicolumn index on shift_times(shift_id, DAYOFWEEK(t.created),TIME(t.created)) so this JOIN can be indexed.</p> <p>Solution : add columns 'day','time' to shift_times, containing DAYOFWEEK(t.created),TIME(t.created), filled with correct values using a trigger firing on INSERT or UPDATE.</p> <p>Now create multicolumn index on (shift_id,day,time)</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