Indexing mysql query

Question

I have this query in mysql and I wanted to be indexed, what is the best index composition?

SELECT cometchat.id, cometchat.from, cometchat.to, cometchat.message, cometchat.sent, cometchat.read, cometchat.direction
FROM cometchat
WHERE (
(
cometchat.to =  '30411'
AND cometchat.direction <>2
)
OR (
cometchat.from =  '30411'
AND cometchat.direction <>1
)
)

I've tried these indexes but they didn't work:

ALTER TABLE  `cometchat` ADD INDEX (  `from`,`to`,`direction`)
ALTER TABLE  `cometchat` ADD INDEX (  `from`,`to`)
ALTER TABLE  `cometchat` ADD INDEX (  `to`,`direction`)
ALTER TABLE  `cometchat` ADD INDEX (  `from`,`direction`)

Any help would be much appreciated.

Answer 1

Because of the OR in your where clause, you'd need 2 separate indexes to optimaze selects:

ALTER TABLE  `cometchat` ADD INDEX index1 (  `to`,`direction`)
ALTER TABLE  `cometchat` ADD INDEX index2 (  `from`,`direction`)

I wouldn't bother trying to make these covering indexes--just eat the cost of the point lookup. However, if you want them to be covering you'd need:

   ALTER TABLE  `cometchat` ADD INDEX index1 
          ( `to`,`direction`, `message`, `sent`, `read` );
   ALTER TABLE  `cometchat` ADD INDEX index2 
          (  `from`,`direction`,`message`, `sent`, `read`);

The covering indexes will give best select performance, but it's a lot of extra storage and will add to insert overhead--so I'd only use them if performance with the non-covering ones are inadequate and this is a insert light table.

Also, just some general advice, I'd avoid using MySQL reserved words like 'from' when naming databases, tables, or columns. https://dev.mysql.com/doc/refman/5.5/en/reserved-words.html
When you use a reserved as a name for something you're really asking to run into a buggy query that will result in another SP question ;)

Answer 2

Try using only the following:

ALTER TABLE  `cometchat` ADD INDEX (`direction`)