CODEWITHSUNDEEP
mysqloptimizationselectindexing
Roman
Roman

Reputation: 131228

Do indexes interfere with each other in MySQL?

I have a table with 3 columns. This table contains many raws (millions). When I select rows from the table I frequently use the following where clauses:

where column2=value1 and column3=value2
where column1=value

To speed up the select query I want to declare column1 and column2 as indexes. My questions is if declaring the second column as an index will not reduce the positive effect of declaring the first column as index.

I also would like to ask if declaring the second column as index will speed up the queries of this type: where column2=value1 and column3=value2.

ADDED The column1, column2, and column3 are entity, attribute, value. It's very general. As entities I use person, movies, cities, countries and so on. Attributes are things like: "located in", "date of birth", "produced by".

Upvotes: 0

Views: 126

Answers (2)

aefxx
aefxx

Reputation: 25279

Seems like neither of your plans are going to work. Based on both of the where clauses I would suggest having the primary key on column1 and a second index column2,column3. This would speed up both of your queries.

Upvotes: 0

Theo
Theo

Reputation: 132922

You should create indexes that support your queries. In this case you want to create an index on column2,column3 together (not two separate indexes, but one index for the combination of columns) to support the first query, and another on column1 to support the second query. More generally, if a query uses a set of columns, adding an index for all those columns will speed it up (although there are many exceptions, of course).

An index on column2 would speed up the query column2=value1 and column1=value2, and so would an index on column2,column3 (the important thing is that column2 is the first column in the index).

When working with indexes the EXPLAIN keyword is very useful. Prefix your queries with EXPLAIN (e.g. EXPLAIN SELECT * FROM table) to get a description of how the database is going to perform your query. It will tell you if it's going to use an index, and in that case which.

Upvotes: 4

Related Questions