Question: I have problem with mysql request. Currently it runs approximately 700-1300ms (it's definitely too slow). Table has about 1mil rows.

```
Select * 
  from `numbers` 
 where `code` = ? 
   and `id` not in (?) 
   and `open` = ? 
   and `views` >= ? 
   and `last_visit` >= ? 
   and `last_visit` <= ? 
 order 
    by `views` desc
 limit 24

```

Fields:

```
"id"   => mediumint, primary, unique
"code" => smallint, not unique, unsigned, not null
"open" => tinyint, unsigned, not null
"views" => smallint, unsigned, not null

```

How can i get more performance with this request? Or try something else, like cache results? Thanks!

Answer: 
I created a test table and filled it with 1 million rows. I used random data.

Here's the EXPLAIN for your query with no indexes defined in the table. You didn't say you had any indexes, so I assumed you had none.

```
           id: 1
  select_type: SIMPLE
        table: numbers
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: NULL
         rows: 523516
     filtered: 0.04
        Extra: Using where; Using filesort

```

The estimated rows examined is about 50% of the 1 million rows. Also the result must be sorted, indicated by "Using filesort." Both of these contribute to the query being expensive.

I created the following index:

```
alter table numbers add key bk1 (code,open,views,last_visit);

```

The reason for the order of these columns in this index is:

* First the `code` and `open` columns that are referenced in equality conditions.
* Next the `views` column because it's the order you want for the result, so reading the index in this order will eliminate the need to do filesort.
* Last\_visit is also helpful, maybe the storage engine can do some pre-filtering (this is called index condition pushdown).

There's no need to add `id` to the index, because InnoDB secondary indexes implicitly have the primary key appended to the end of the list of columns.

Now the EXPLAIN for the same query is as follows:

```
           id: 1
  select_type: SIMPLE
        table: numbers
   partitions: NULL
         type: range
possible_keys: PRIMARY,bk1
          key: bk1
      key_len: 10
          ref: NULL
         rows: 527
     filtered: 5.56
        Extra: Using where; Backward index scan; Using index

```

Notice it's no longer doing a filesort, because it's able to read the table in index order. 

The backward index scan note is a new thing in MySQL 8.0. If you use an earlier version of MySQL, you may not see this.

Also the number of rows examined is reduced by a factor of 1000. Your results may vary.
Are you using the right indexes?

Create a composite index with the fields you filter:

```
CREATE INDEX index_name 
ON numbers(id, code, open, last_visit, views);

```

And you can execute an `Explain` query to check that the query is using the index:

```
Explain Select * from `numbers` ...

```