How to Use MySQL Index Columns

How to use MySQL index columns?

Primary

The primary key is - as the name suggests - the main key of a table and should be a column which is commonly used to select the rows of this table. The primary key is always a unique key (unique identifier). The primary key is not limited to one column, for example in reference tables (many-to-many) it often makes sense to have a primary key including two or more columns.

Unique

A unique index makes sure your DBMS doesn't accept duplicate entries for this column. You ask 'Foreign keys?' NO! That would not be useful since foreign keys are per definition prone to be duplicates, (one-to-many, many-to-many).

Index

Additional indexes can be placed on columns which are often used for SELECTS (and JOINS) which is often the case for foreign keys. In many cases SELECT (and JOIN) queries will be faster, if the foreign keys are indexed.

Note however that - as SquareCog has clarified - Indexes get updated on any modifications to the data, so yes, adding more indexes can lead to degradation in INSERT/UPDATE performance. If indexes didn't get updated, you would get different information depending on whether the optimizer decided to run your query on an index or the raw table -- a highly undesirable situation.

This means, you should carefully assess the usage of indices. One thing is sure on the basis of that: Unused indices have to be avoided, resp. removed!

How to see indexes for a database or table in MySQL?

To see the index for a specific table use SHOW INDEX:

SHOW INDEX FROM yourtable;

To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

Removing the where clause will show you all indexes in all schemas.

Understanding multiple column indexes in MySQL query

Good question.

Indexes work left to right, so your WHERE criteria would use the index. The sort would also utilize the index in this case (execution plan below).

From the manual:

The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause. The following queries use the index to resolve the ORDER BY part:

SELECT * FROM t1
WHERE key_part1=constant
ORDER BY key_part2;

If you had a single column index (accountid), a filesort would be used instead. Therefore, your query does benefit from that index.

---

Two Column Index

create table t1 (
  accountid tinyint,
  logindate date);

create index idx on t1 (accountid, logindate);

insert into t1 values (1, '2012-09-05'), (2, '2012-09-09'), (3, '2012-09-04'), 
    (1, '2012-09-01'), (1, '2012-09-26'), (2, '2012-05-16'), 
    (1, '2012-09-01'), (3, '2012-10-19'), (1, '2012-03-01')

Execution Plan

ID  SELECT_TYPE  TABLE  TYPE  POSSIBLE_KEYS  KEY  KEY_LEN  REF   ROWS  FILTERED  EXTRA
1   SIMPLE       t1     ref   idx            idx  2        const 5     100       Using where; Using index

Single Column Index

create table t1 (
  accountid tinyint,
  logindate date);

create index idx on t1 (accountid);

insert into t1 values (1, '2012-09-05'), (2, '2012-09-09'), (3, '2012-09-04'), 
    (1, '2012-09-01'), (1, '2012-09-26'), (2, '2012-05-16'), (1, '2012-09-01'), 
    (3, '2012-10-19'), (1, '2012-03-01')

Execution Plan

ID  SELECT_TYPE  TABLE  TYPE   POSSIBLE_KEYS  KEY  KEY_LEN  REF   ROWS  FILTERED  EXTRA
1   SIMPLE       t1     range  idx            idx  2              5     100       Using where; Using filesort

Best way to create Database index on MySQL

According to the type of queries you want to use, I would say the most recommended way to create the index is:

CREATE INDEX my_index1 on table(field1); 
CREATE INDEX my_index2 on table(field2); 
CREATE INDEX my_index3 on table(field3); 
CREATE INDEX my_index4 on table(field4); 

Then you will create 4 different index that can be used independently, otherwise you will create a composite index.

Why one single index won't work here?

Because, with only one single index with multiple fields, your query only will apply the index if you are using the fields on the query from left to right strictly. I put some examples:

EXPLAIN SELECT * FROM table WHERE field2=value2 AND field1=value1;

This query, will apply the index for the fields field1, and field2. Why? Because you are using the two most left fields from the created index.

You can see it on the explain field possible_keys with the value my_index.

However the next example:

EXPLAIN SELECT * FROM table WHERE field3=value3 AND field4=value4;

Won't apply any index, because you are going directly to call methods from the most right.

You can see it on the explain field possible_keys with the value null.

And just as the last example:

EXPLAIN SELECT * FROM table WHERE field1=value1 AND field4=value4;

This query, only apply the index for field1, but not for field4. The reason? is not used the other field2 and field3 in between.

You can see it on the explain field possible_keys equal to my_index and field extras with the value Using index, using where.

You can find more information about composite index here:

http://www.mysqltutorial.org/mysql-index/mysql-composite-index/

How does a multi-column index work in MySQL?

Think of a MySQL "composite" index this way.

Concatenate all the columns in the index together, then build an BTree index on that 'single' string.

Some related comments:

Once you understand that analogy, you can see why the cardinality of the individual columns does not matter. That is, the order of the columns in a composite index does not matter for performance. The order does matter, depending on what the query asks for.

INDEX(a,b) is likely to be useful for these:

 WHERE a = 123
 WHERE b = 5 AND a = 678

But cannot be used for

 WHERE b = 5

(Note on my use of "concatenate". Since it is not really practical to concatenate floats, dates, signed integers, strings with odd collations, etc as if they were just bytes, I do not mean that InnoDB literally concatenates the bytes together.)

MySql, SELECT * FROM with Indexed columns

There is part answer for your question.

https://stackoverflow.com/a/3211164/2957840

But also, maybe you should consider partitioning your table:
https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

How do I get MySQL to use an INDEX for view query?

How do you get MySQL to use an index for a view query? The short answer, provide an index that MySQL can use.

In this case, the optimum index is likely a "covering" index:

... ON highscores (player, happened_in, score)

It's likely that MySQL will use that index, and the EXPLAIN will show: "Using index" due to the WHERE player = 24 (an equality predicate on the leading column in the index. The GROUP BY happened_id (the second column in the index), may allow MySQL to optimize that using the index to avoid a sort operation. Including the score column in the index will allow the query to satisfied entirely from the index, without having to visit (lookup) the data pages referenced by the index.

That's the quick answer. The longer answer is that MySQL is very unlikely to use an index with leading column of happened_id for the view query.

---

Why the view causes a performance issue

One of the issues you have with the MySQL view is that MySQL does not "push" the predicate from the outer query down into the view query.

Your outer query specifies WHERE happened_in = 2006. The MySQL optimizer does not consider the predicate when it runs the inner "view query". That query for the view gets executed separately, before the outer query. The resultset from the execution of that query get "materialized"; that is, the results are stored as an intermediate MyISAM table. (MySQL calls it a "derived table", and that name they use makes sense, when you understand the operations that MysQL performs.)

The bottom line is that the index you have defined on happened_in is not being used by MySQL when it rusn the query that forms the view definition.

After the intermediate "derived table" is created, THEN the outer query is executed, using that "derived table" as a rowsource. It's when that outer query runs that the happened_in = 2006 predicate is evaluated.

Note that all of the rows from the view query are stored, which (in your case) is a row for EVERY value of happened_in, not just the one you specify an equality predicate on in the outer query.

The way that view queries are processed may be "unexpected" by some, and this is one reason that using "views" in MySQL can lead to performance problems, as compared to the way view queries are processed by other relational databases.

---

Improving performance of the view query with a suitable covering index

Given your view definition and your query, about the best you are going to get would be a "Using index" access method for the view query. To get that, you'd need a covering index, e.g.

... ON highscores (player, happened_in, score).

That's likely to be the most beneficial index (performance wise) for your existing view definition and your existing query. The player column is the leading column because you have an equality predicate on that column in the view query. The happened_in column is next, because you've got a GROUP BY operation on that column, and MySQL is going to be able to use this index to optimize the GROUP BY operation. We also include the score column, because that is the only other column referenced in your query. That makes the index a "covering" index, because MySQL can satisfy that query directly from index pages, without a need to visit any pages in the underlying table. And that's as good as we're going to get out of that query plan: "Using index" with no "Using filesort".

---

Compare performance to standalone query with no derived table

You could compare the execution plan for your query against the view vs. an equivalent standalone query:

SELECT player
     , MAX(score) AS highest_score
     , happened_in
 FROM highscores
WHERE player = 24
  AND happened_in = 2006
GROUP
   BY player
    , happened_in

The standalone query can also make use of a covering index e.g.

... ON highscores (player, happened_in, score)

but without a need to materialize an intermediate MyISAM table.

---

I am not sure that any of the previous provides a direct answer to the question you were asking.

Q: How do I get MySQL to use an INDEX for view query?

A: Define a suitable INDEX that the view query can use.

The short answer is provide a "covering index" (index includes all columns referenced in the view query). The leading columns in that index should be the columns that are referenced with equality predicates (in your case, the column player would be a leading column because you have a player = 24 predicate in the query. Also, the columns referenced in the GROUP BY should be leading columns in the index, which allows MySQL to optimize the GROUP BY operation, by making use of the index rather than using a sort operation.

The key point here is that the view query is basically a standalone query; the results from that query get stored in an intermediate "derived" table (a MyISAM table that gets created when a query against the view gets run.

Using views in MySQL is not necessarily a "bad idea", but I would strongly caution those who choose to use views within MySQL to be AWARE of how MySQL processes queries that reference those views. And the way MySQL processes view queries differs (significantly) from the way view queries are handled by other databases (e.g. Oracle, SQL Server).

---

Related Topics