2 more
source | link

In any system, this will probably be beneficial, probably optimal:

INDEX(username, last_modified) -- in this order

Please provide EXPLAIN SELECT... on both database versions so we can better identify what the difference might be. And provide SHOW CREATE TABLE so we can see what indexes, datatypes, engine, etc, you have.

The index I recommend

  • Is a 'composite' index.
  • Is not the same as two single-column indexes.
  • Deliberately starts with the column tested via =.
  • Ignores the <> as not being useful.
  • Includes one 'range' (on last_modified).
  • It should, but may not consume the ORDER BY, thereby allowing it to efficiently consume the LIMIT. Without reaching all the way to the LIMIT, it is destined to scan the entire table, thereby taking a 'long' time.

A side note: UUIDs (such as what app_instance_id seems to be) are notoriously inefficient when indexed in large tables. For this query, I am hoping that very few of the rows have that particular value.

To further investigate the "why", answer these questions:

  • What percentage of the table contains username='joebloggs'. and
  • What percentage of the table contains last_modified>'2018-08-20 20:14:51.394'

If either of these is more than say, 20%, then the Optimizer wisely chose to ignore the corresponding index. It could be that the statistics on one was better than on the other. Let's see SHOW INDEXES FROM event.

Cookbook for devising good indexes.

Try to avoid index hints -- they may help today, but hurt tomorrow (with different constants).

In any system, this will probably be beneficial, probably optimal:

INDEX(username, last_modified) -- in this order

Please provide EXPLAIN SELECT... on both database versions so we can better identify what the difference might be. And provide SHOW CREATE TABLE so we can see what indexes, datatypes, etc, you have.

The index I recommend

  • Is a 'composite' index.
  • Is not the same as two single-column indexes.
  • Deliberately starts with the column tested via =.
  • Ignores the <> as not being useful.
  • Includes one 'range' (on last_modified).
  • It should, but may not consume the ORDER BY, thereby allowing it to efficiently consume the LIMIT. Without reaching all the way to the LIMIT, it is destined to scan the entire table, thereby taking a 'long' time.

A side note: UUIDs (such as what app_instance_id seems to be) are notoriously inefficient when indexed in large tables. For this query, I am hoping that very few of the rows have that particular value.

In any system, this will probably be beneficial, probably optimal:

INDEX(username, last_modified) -- in this order

Please provide SHOW CREATE TABLE so we can see what indexes, datatypes, engine, etc, you have.

The index I recommend

  • Is a 'composite' index.
  • Is not the same as two single-column indexes.
  • Deliberately starts with the column tested via =.
  • Ignores the <> as not being useful.
  • Includes one 'range' (on last_modified).
  • It should, but may not consume the ORDER BY, thereby allowing it to efficiently consume the LIMIT. Without reaching all the way to the LIMIT, it is destined to scan the entire table, thereby taking a 'long' time.

A side note: UUIDs (such as what app_instance_id seems to be) are notoriously inefficient when indexed in large tables. For this query, I am hoping that very few of the rows have that particular value.

To further investigate the "why", answer these questions:

  • What percentage of the table contains username='joebloggs'. and
  • What percentage of the table contains last_modified>'2018-08-20 20:14:51.394'

If either of these is more than say, 20%, then the Optimizer wisely chose to ignore the corresponding index. It could be that the statistics on one was better than on the other. Let's see SHOW INDEXES FROM event.

Cookbook for devising good indexes.

Try to avoid index hints -- they may help today, but hurt tomorrow (with different constants).

1
source | link

In any system, this will probably be beneficial, probably optimal:

INDEX(username, last_modified) -- in this order

Please provide EXPLAIN SELECT... on both database versions so we can better identify what the difference might be. And provide SHOW CREATE TABLE so we can see what indexes, datatypes, etc, you have.

The index I recommend

  • Is a 'composite' index.
  • Is not the same as two single-column indexes.
  • Deliberately starts with the column tested via =.
  • Ignores the <> as not being useful.
  • Includes one 'range' (on last_modified).
  • It should, but may not consume the ORDER BY, thereby allowing it to efficiently consume the LIMIT. Without reaching all the way to the LIMIT, it is destined to scan the entire table, thereby taking a 'long' time.

A side note: UUIDs (such as what app_instance_id seems to be) are notoriously inefficient when indexed in large tables. For this query, I am hoping that very few of the rows have that particular value.