StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Could you make your sample table and EXPLAIN results match? Because, obviously it is not a same situation and we don't know if you maybe made a mistake in abstracting your real query only by looking at the provided EXPLAIN results. If you don't want to show too much of a structure then reverse it and create the quoted table structure and provide EXPLAIN result on that (maybe you will catch the problem that way).</p> <p>Now one thing is certain - sorting is using <a href="http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/" rel="nofollow noreferrer">filesort</a>, which is bad.</p> <p>To simplify (we'll come back to it) - compound indexes useful for sorting need to have the sort field in front. </p> <p>Example idx(ID, Start)</p> <pre><code>ID Start 1 5 8 8 10 25 2 3 9 10 40 41 42 42 ... </code></pre> <p>In the above example the index is not of much help for sorting if you don't have where condition in which ID is limited to only one value.</p> <p><strong>But</strong>, this exception is important since you have single row selectivity on one or both id fields.</p> <p>So from your indexes the only indexes that have start at the beginning are</p> <pre><code>start_index: [start, first_user_id, second_user_id] test_3_index: [start,stop,first_user_id,second_user_id] </code></pre> <p>Mysql ignores the index </p> <pre><code>start_index: [start, first_user_id, second_user_id] </code></pre> <p>because it has better choices in terms of selectivity - it would need to do an index scan with this index and it has indexes that will allow it to do index intersect jumping directly to (unsorted) results. It expects better selectivity from the intersect and selectivity drives the planer.</p> <p>Once the result is obtained mysql should realize that it could use another index to sort the results, but it seems that it can not see how cheap that would be.</p> <p>So to help the planer you could create an index that will capitalize on your single value selectivity with index such as:</p> <pre><code>two_ids_with_sort: [first_user_id, second_user_id, start] </code></pre> <p>I assume that above would work very well on your second query where you have conditions on both id's giving you access to presorted start record pointers. The following query should do the same for the first query:</p> <pre><code>one_id_with_sort: [first_user_id, start] </code></pre> <p>Only if you end up with a lot of records in the result sets I would look into indexing it further.</p> <p>There are two paths there a) adding the field stop to the end of the index b) creating two more similar indexes with stop instead of start (index intersect could be used there and wider range of queries could benefit from it)</p> <p>But do test all of the above theories.</p> <p>Couple of general suggestions </p> <ul> <li>write your conditions in most selective manner first</li> <li>when testing indexes start with single column indexes first and then expand to compound indexes (for example for sorting on start I would add index only on start)</li> <li>too many indexes are not so good in mysql as the query planer is not able to quickly run through all the possible combinations and can not properly estimate costs of all the operations (so it cuts corners and the best index combination and plan might be left out)</li> <li>therefore test indexes with <code>USE INDEX (index1) FOR ORDER BY</code> in your select to gauge a benefit for a certain index over planer, see more <a href="http://dev.mysql.com/doc/refman/5.1/en/index-hints.html" rel="nofollow noreferrer">here</a> (esp FORCE option; also - aim to leave only the useful indexes and see if planer will be able to utilize them then, if not, as a last resort only, force the indexes in your queries for which performance is crucial. keep in mind that this is a bad practice in terms of administration and design).</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