MySQL Forums
Forum List  »  Performance

Re: Mysql indexes weird behavior
Posted by: Rick James
Date: May 03, 2015 06:21PM

> I have columsn c1, c2, c3, c4 with indexes

Do you mean
    INDEX(c1),
    INDEX(c2),
    INDEX(c3),
    INDEX(c4)
or do you mean a composite index:
    INDEX(c1,c2,c3,c4)
?

[/code]
select c1,c2 from table1 where c1=a and c2=b and c3 = d
[/code]
Needs
    INDEX(c1,c2,c3)
with the columns in any order.

    select c5 from table1 where c5>a and c1=b
needs, in _this_ order,
    INDEX(c1, c5)

31 indexes is too many. I suspect the optimizer is throwing up its hands.

With either of the composite indexes I mentioned, you would not longer need
    INDEX(c1)
because it is a prefix of them, hence redundant.

If you want to discuss speed differences, please provide
EXPLAIN SELECT ...
and
SHOW CREATE TABLE

Here's my cookbook on building the optimal index from a SELECT:
http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Options: ReplyQuote


Subject
Views
Written By
Posted
1273
May 02, 2015 02:17AM
Re: Mysql indexes weird behavior
591
May 03, 2015 06:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.