StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There are at least 3 reasons why your select isn't using the index</p> <p>1) You used a <code>select *</code>, which includes items not in the index (i.e., <code>invitation_id</code>). That means had it used the index, it would then have to look up the row in the database to fetch the <code>invitation_id</code> value. Had you added <code>invitation_id</code> to the index, it would have used the index. If you had done a <code>select</code> of just <code>invitee_id, inviter_id</code>, it would have used the index.</p> <p>2) The query optimizer decided it would be better to just scan the table rather than scan a range of the index. When the optimizer is trying to decide full table scan or partial index scan, it doesn't do it for your exact query - it wants a plan that works well in general. One which may be run over an over again. Scanning from <code>invitee_id,inviter_id</code> <code>(62,70)</code> to <code>(70,62)</code> is likely only 8 index entries, but if picked randomly out of 50k items, the average distance would be about 17k items. So on average, a single query will access 1/3 of the index (i.e., pull it into memory), then access the page the row is on (see #1) pulling that into memory. Your rows are so small, accessing just one item likely pulls in 680 rows (8k page by 12 bytes for 3 32 bit #'s) which is 1/70th of table - do 100 queries and likely you've pulled the whole index into memory and the whole table - it makes more sense to take a little longer by scanning the table and use 40% less memory to hold bits of other tables. At some point (which seems to be 65k rows) it stops making sense.</p> <p>3) What your question said: you used an OR. An OR expression can't be used to look something up in an index - that is, you can't look up 62 or 70. Instead, it produces a range looking up <code>(62,70)</code>, then scans to get to <code>(70,62)</code> (see #2 why this can be bad).</p> <p>You asked "what's wrong here" - it's that you used the OR, which won't scale. Not only do you need to avoid the type ALL, you need to avoid large type RANGES.</p> <p>I've seen the same issue with other SQL engines and the solution I used was a UNION ALL.</p> <p>Something like</p> <pre><code>SELECT * FROM `invitation` WHERE (invitee_id = 70 AND inviter_id = 62) UNION ALL SELECT * FROM `invitation` WHERE (invitee_id = 62 AND inviter_id = 70) </code></pre> <p>That will make it be done as two queries and merge the results without checking for duplicates.</p> <p>This is much lighter on memory usage and much faster - Just a few pages of the index and the two pages from the table are required and O(log(N)) for each lookup. This is because it's of type const now - your goal was to eliminate the ALL, but switching to a RANGE is nearly as bad to fetch just two rows. Scanning the whole table is O(N) and scanning a RANGE of the index is also O(N) since O(1/3*N) is O(N). In other words, it doesn't scale.</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