How to Check the Performance of MySQL Indexing

Is there any way to check the performance of mysql Indexing

Following query will tell you whether query uses index or not:

EXPLAIN EXTENDED SELECT col1, col2, col3, COUNT(1) 
FROM table_name 
WHERE col1 = val 
GROUP BY col1 
ORDER BY col2;

SHOW WARNINGS;

You can add covering index for best performance.

For covering index you add columns used in where clauses first then columns used in group by the columns used in order by and then columns used in select.

e.g. for above query you can add covering index KEY(col1, col2, col3)

*Note Adding more indexes will slow down your insert queries.

Good indexing practices for performance in mysql databases

4- for querys which find rows in date ranges.

Usually there is something else in the WHERE, say

 WHERE x = 123
   AND Entrada BETWEEN ... AND ...

I that case this is optimal: INDEX(x, Entrada)

`CheckOut` tinyint(1) DEFAULT NULL
ADD KEY `ix_Bookings_CheckOut` (`CheckOut`),

It is rarely useful to index a "flag". However, a composite index (as above) may be useful.
Why are most columns NULLable? For "booleans", simply use 0 and 1 and DEFAULT to whichever one is appropriate. Use NULL for "don't know", "optional", "not yet supplied", etc.
6- Also have indexed some Bool columns which I query together with another index column.

Then have a composite index. And be sure to say b=1 not b<>0, since <> does not optimize as well.

It's fine to query by this unique indexed 50chars string? It's not too long to work as index? Will work as fast now as with 50millions register?

If the dataset becomes bigger than RAM, there is a performance problem with "random" indexes. Your example should be fine. (Personally, I think 50 chars is excessive.) And such a 'hash' should probably be CHARACTER SET ascii and perhaps with COLLATE ascii_bin instead of a case-folding version.

And "task.completed==True, task.userID==user.id" os probably best indexed with a "composite" INDEX(userID, completed) in either order.
Yes, indexes in datetime columns do some work when comparing with <, <=, >, >= operators? Strings can also be compared, though I do not see any likely columns for string comparisions other than with =.
50M rows is large, but not "huge". Composite indexes are often important for large tables.

MySQL indexes performance on huge tables

For this query:

SELECT MAX(YEAR(p.birthdate)) as max_year, wi.department as department
FROM person p INNER JOIN
     works_in wi
     ON wi.person_id = p.id
WHERE p.birthdate IS NOT NULL
GROUP BY wi.department;

The best indexes are: person(birthdate, id) and works_in(person_id, department). These are covering indexes for the query and save the extra cost of reading data pages.

By the way, unless a lot of persons have NULL birthdates (i.e. there are departments where everyone has a NULL birthdate), the query is basically equivalent to:

SELECT MAX(YEAR(p.birthdate)) as max_year, wi.department as department
FROM person p INNER JOIN
     works_in wi
     ON wi.person_id = p.id
GROUP BY wi.department;

For this, the best indexes are person(id, birthdate) and works_in(person_id, department).

EDIT:

I cannot think of an easy way to solve the problem. One solution is more powerful hardware.

If you really need this information quickly, then additional work is needed.

One approach is to add a maximum birth date to the departments table, and add triggers. For works_in, you need triggers for update, insert, and delete. For persons, only update (presumably the insert and delete would be handled by works_in). This saves the final group by, which should be a big savings.

A simpler approach is to add a maximum birth date just to works_in. However, you will still need a final aggregation, and that might be expensive.

How to determine query performance on basis of Index(a,b)

To determine which query will be theoretically faster/slower you can look into how database index works: it is essentially tree build from values from a column first, then appended with values from b column

Some notes:

Looking for exact value is faster neither looking for range (like > 0), just because you need to scan less data.

It means that query will perform faster for conditions, where both variable compared to const:

Select * from user where a = 0 AND b = 0

Second type of query is first column in index has const comparison and second one is range, as again you reduce number of scans (you don't need to scan Bs for A != 0):

Select * from user where a = 0 AND b > 0

Next query partially scans A range (this is why it is slower than 1/2), but can quite quickly match b:

Select * from user where a > 0 AND b = 0

Last one is where you have OR, basically you do 2 separate scans and then merge results (or just perform full scan):

Select * from user where a = 0 OR b = 0

mysql index for speed

You should try index on (mode, instruction_id), in that order.

The reasoning behind that index is that it creates an index like this

mode  instruction_id
A     1
A     3
A     4
A     5
A     10
A     11
B     2
B     8
B     12
B     13
B     14
C     6
C     7
C     9
C     15
C     16
C     17

If you search for mode B the sql server can search the index with a binary search on mode until it finds the first B, then it can simply output the next n rows. This would be really fast, about 22 compares for 4M rows.

Always use ORDER BY if you expect the result to be ordered, regardless of how the data is stored. The query engine might choose a query plan that output the rows in a different order than the order of the PK (maybe not in such simple cases as this, but in general).

MySQL composite index column order & performance

The rows in EXPLAIN is just an estimate of the number of rows that MySQL believes it must examine to produce the result.

I remembered reading one article from Peter Zaitsev of Percona that this number could be very inaccurate. So you can not simply compare the query efficiency based on this number.

I agree with you that the first index will produce better result in normal scenarios.

You should have noticed that the type column in the first EXPLAIN is ref while index for the second. ref is usually better than a index scan. As you mentioned, if both keys exists, MySQL prefer the first one.

Find out usage statistics of MySQL indices?

NOTE: This answer is no longer valid as of 5.5.3!

See https://stackoverflow.com/a/43205887/1251127

Original answer below.

Currently, MySQL does not offer statistics on index usage.

One way to generate those stats on your own would be to log all queries (Be careful of the extra i/o here) and parse it out. Percona has a nice tool for this, pt-index-usage.

How to properly increase MySQL performance by indexing

Try removing order by and do your sorting in your application logic.

Hope it can minimize your query load.