Problem with mysql query

Question

How can I optimize this mysql query:

SELECT * 
FROM messages 
WHERE toid='".$mid."' 
  AND fromid='".$fid."' 
  OR (toid='".$fid."' AND fromid='".$mid."')  
  AND subject != 'something' 
order by id ASC LIMIT 0,5

I need to get messages between users in one page. This query id taking to much time and server resources. Can it be done in some other way?

Thanks.

Answer 1

As zerkms points out, you can't do an index for the subject in this case. Check his suggestion in the comments.

Add one index for both ids:

CREATE INDEX ids_index ON messages (fromid, toid);

and split the query into two.

Answer 2

Add an index on the pair (toid,fromid). That will allow it to use the index to find the relevant messages and speed up your query. Note that indexes on the individual columns would potentially still leave a lot of messages to be scanned, i.e., those messages to/from one of the individuals to someone else. Using the pair will limit the messages found by the index scan to just those between the two individuals.

Answer 3

As written, the query is possibly betraying the actual intent. It seem likely that the condition desired is

WHERE ( ( toid='".$mid."' AND fromid='".$fid."' )
   OR   (toid='".$fid."'  AND fromid='".$mid."') 
      )
  AND subject != 'something' 

But, as written, the query will only apply the subject condition with the second (toid and fromid) clause.
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.

In either case, this query is a "hard[er]" query to resolve, 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)

Independently from this possible logical problem, adding indexes will multiple keys would help the situation. I'd like to suggest the following:

• toid, fromId, subject
• toid, fromid

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.

Beware however that adding indexes decreases performances for INSERT, UPDATE and DELETE operations.

Edit: on the usability of the (toid,fromid, subject) index
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).
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)

Answer 4

Given that you hopefully have some logic to prevent a user sending a message to herself, perhaps this:

WHERE toid IN ($mid, $fid) AND fromid IN ($mid, $fid) AND subject <> 'something'

Put an index on (toid, fromid) and that should be pretty ok, i think.

Answer 5

Thanks! Index on the pair (toid,fromid) is the solution.