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.

What is the explain plan for that query?

doublesharp
– doublesharp

2012-11-05 15:24:31 +00:00
Commented Nov 5, 2012 at 15:24 — doublesharp
– doublesharp, Commented Nov 5, 2012 at 15:24

Ray · Accepted Answer · 2012-11-05 16:06:27Z

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 ;)

Plamen Nikolov · Accepted Answer · 2012-11-05 15:26:13Z

0

Try using only the following:

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

answered Nov 5, 2012 at 15:26

Plamen Nikolov

3,0651 gold badge16 silver badges29 bronze badges

Collectives™ on Stack Overflow

Indexing mysql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related