# Engine Smart Selection

+ **`session.tidb_isolation_read_engines`**: Available engines that support read operations
+ **`ALTER TABLE ... SET TIFLASH REPLICA {replica_count}`**: Enable the learner replication for table
+ **`ALTER TABLE ... SET TIFLASH REPLICA 0`**: Turn off the learner replication for table
+ Cost Based Optimizer (`CBO`) will automatically choose a better engine to execute query

```
tidb> SELECT @@tidb_isolation_read_engines;
+-------------------------------+
| @@tidb_isolation_read_engines |
+-------------------------------+
| tikv,tiflash,tidb             |
+-------------------------------+
1 row in set (0.00 sec)
```
```
+------------------------------------------------------+------------+-------------------+---------------+
| id                                                   | estRows    | task              | access object | 
+------------------------------------------------------+------------+-------------------+---------------+
| Sort_10                                              | 3.20       | root              |               |
| └─Projection_12                                      | 3.20       | root              |               |
|   └─TableReader_62                                   | 3.20       | root              |               |
|     └─ExchangeSender_61                              | 3.20       | ~!@batchCop[tiflash]@!~ |               |
|       └─Projection_57                                | 3.20       | ~!@batchCop[tiflash]@!~ |               |
|         └─HashAgg_58                                 | 3.20       | ~!@batchCop[tiflash]@!~ |               |
|           └─ExchangeReceiver_60                      | 3.20       | ~!@batchCop[tiflash]@!~ |               |
|             └─ExchangeSender_59                      | 3.20       | ~!@batchCop[tiflash]@!~ |               |
|               └─HashAgg_17                           | 3.20       | ~!@batchCop[tiflash]@!~ |               |
|                 └─Projection_67                      | 1677721.60 | ~!@batchCop[tiflash]@!~ |               |
|                   └─HashJoin_56                      | 1677721.60 | ~!@batchCop[tiflash]@!~ |               |
|                     ├─ExchangeReceiver_34(Build)     | 1677721.60 | ~!@batchCop[tiflash]@!~ |               |
|                     │ └─ExchangeSender_33            | 1677721.60 | ~!@batchCop[tiflash]@!~ |               |  
|                     │   └─Selection_32               | 1677721.60 | ~!@batchCop[tiflash]@!~ |               |
|                     │     └─TableFullScan_31         | 2097152.00 | ~!@batchCop[tiflash]@!~ | table:t5      |     
|                     └─TableFullScan_35(Probe)        | 2097152.00 | ~!@batchCop[tiflash]@!~ | table:t2      |
+------------------------------------------------------+------------+-------------------+---------------+
```
------------------------------------------------------------

Class demo scripts:

https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/07-demo-tiflash-olap-01-show.sql

Together with TiKV, they took their respective strengths to make the TiDB cluster truly HTAP, a NewSQL database that supports hybrid transaction and analytic processing.

Higher number of replicas is good for load balancing and fast-failover with more space consumptions.

Once TiFlash replica are enabled, it is recommended to use the CBO (cost-based optimizer) to automatically select a higher-performing storage engine to serve the query operation. Of course, you can also use hints. For hints, please refer to `https://docs.pingcap.com/zh/tidb/v6.0/use-tiflash` .

Use the following statement to get a table with TiFlash Learner enabled in your data:

```
SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = db_name;
```

Before TiFlash is enabled:
```
tidb> SELECT avg(t5.id), t5.id2, count(*) 
    -> FROM test.t5_nonclustered t5 JOIN test.t2_nonclustered t2
    -> ON t5.id = t2.id
    -> WHERE t5.id > t5.id2
    -> GROUP BY id2
    -> ORDER BY id2;
+--------------------------+---------------------+----------+
| avg(t5.id)               | id2                 | count(*) |
+--------------------------+---------------------+----------+
| 6499304087950836113.4308 | 2305843009213693953 |   524007 |
| 6499304087950836114.4308 | 2305843009213693954 |   524007 |
| 6499304087950836115.4308 | 2305843009213693955 |   524007 |
| 6499304087950836116.4308 | 2305843009213693956 |   524007 |
+--------------------------+---------------------+----------+
4 rows in set (2.21 sec)
```

When TiFlash is enabled:
```
tidb> SELECT avg(t5.id), t5.id2, count(*) 
    -> FROM test.t5_nonclustered t5 JOIN test.t2_nonclustered t2
    -> ON t5.id = t2.id
    -> WHERE t5.id > t5.id2
    -> GROUP BY id2
    -> ORDER BY id2;
+--------------------------+---------------------+----------+
| avg(t5.id)               | id2                 | count(*) |
+--------------------------+---------------------+----------+
| 6499304087950836113.4308 | 2305843009213693953 |   524007 |
| 6499304087950836114.4308 | 2305843009213693954 |   524007 |
| 6499304087950836115.4308 | 2305843009213693955 |   524007 |
| 6499304087950836116.4308 | 2305843009213693956 |   524007 |
+--------------------------+---------------------+----------+
4 rows in set (0.48 sec)
```