StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<ul> <li><blockquote> <p>where (dmd1.FKrevID - dmd2.FKrevID) < rw.k</p> </blockquote> <p>The most damaging about this statement is the less than operator <code><</code> not the arithmetic. B-trees cannot use this and forces a full table scan every time, any time. Gory details why this true: <a href="http://explainextended.com/2010/05/19/things-sql-needs-determining-range-cardinality/" rel="nofollow">http://explainextended.com/2010/05/19/things-sql-needs-determining-range-cardinality/</a></p></li> <li><p>I doubt your <code>CASE</code> statement can be optimized by the backend and <code><></code> operator suffers from the same problem as above. I would think about ways to join with <code>=</code> operators, perhaps breaking up the query and using <code>UNION</code> statements so you can always use indexes.</p></li> <li><p>Your not using <code>EXPLAIN</code>. You need to start using it to optimize queries. You have no idea what indexes are being used and what are not, or if your condition is selective enough where they would even be helpful (if its not very selective see the last point) <a href="http://dev.mysql.com/doc/refman/5.0/en/explain.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/explain.html</a></p></li> <li><p>Since this a data mining application you have a great opportunity to use temp tables of intermediate values. Since the data is probably dumped at periodic intervals (or maybe even only once!) it is easy to rebuild the long running temp table every so often without running the risk of data corruption (Or it may just not matter since you looking for aggregate patterns.) </p> <p>I have taken queries that were running over 60 minutes and reduced them to less than 100 ms (instant) by building temp tables that cached the hard stuff. If you are not able to use any of the ideas above, this is probably the lowest lying fruit. Take all the 'hard stuff' - case joins and non equality joins and do it one place. Then add an index to your temp table :-) The trick is to make it general enough that you can query the temp table so you still have flexibility to ask different questions. </p></li> </ul>

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