StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As written, the query is possibly betraying the actual intent. It seem likely that the condition desired is</p> <pre><code>WHERE ( ( toid='".$mid."' AND fromid='".$fid."' ) OR (toid='".$fid."' AND fromid='".$mid."') ) AND subject != 'something' </code></pre> <p>But, as written, the query will only apply the subject condition with the second (toid and fromid) clause.<br> Note that in the above, the inner parenthesis are extraneous; never the less it is often a good idea to include them to show the intended expression more explicitly.</p> <p>In either case, <strong>this query is a "hard[er]" query to resolve</strong>, owing to the OR clause and to a NOT EQUAL predicate. The OR clause typically causes the server to merge the results from two subquery (although other strategies are possible). The NOT EQUAL predicate cannot be resolved by an index lookup (however a covering index does help, in some cases), for it saves the trip to the main table / other indexes for assessing whether the row at hand satisfies the predicate)</p> <p>Independently from this possible logical problem, adding indexes will multiple keys would help the situation. I'd like to suggest the following:</p> <ul> <li>toid, fromId, subject</li> <li>toid, fromid</li> </ul> <p>The interest of the index that also includes the subject is to allow the query to be resolved with a partial scan of the index rather than having to lookup the subject. This index would be used as a covering index for this query.</p> <p>Beware however that adding indexes decreases performances for INSERT, UPDATE and DELETE operations.</p> <p><strong>Edit</strong>: <em>on the usability of the (toid,fromid, subject) index</em><br> First off, it is acknowledged that we need only one of the suggested indexes, i.e. if we have the (toid, fromid, subject) index, the (toid, fromid) one would be redundant (albeit possibly more efficient if subject was a relatively long column).<br> This said, the fact that the query uses a NOT EQUAL predicate on subject doesn't necessary exclude the use of the subject data in the (toid, fromid, subject) index. The reason for that is that the [not equal] condition on subject can be resolved within the index (not requiring a match/merge or a lookup, i.e. akin to some "covering" logic)</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