set
infinidb_vtable_mode=2 for run mysql query.
set
infinidb_vtable_mode = n
where n is:
0) a generic, highly compatible row-by-row processing mode. Some WHERE clause components can be processed by InfiniDB, but joins are processed entirely by mysqld using a nested-loop join mechanism
1) (Default) query syntax is evaluated by InfiniDB for compatibility with distributed execution and incompatible queries are rejected. Queries executed in this mode take advantage of distributed execution and typically result in higher performance.
2) auto-switch mode: InfiniDB will attempt to process the query internally, if it cannot, it will automatically switch the query to run in row-by-row mode.
where n is:
0) a generic, highly compatible row-by-row processing mode. Some WHERE clause components can be processed by InfiniDB, but joins are processed entirely by mysqld using a nested-loop join mechanism
1) (Default) query syntax is evaluated by InfiniDB for compatibility with distributed execution and incompatible queries are rejected. Queries executed in this mode take advantage of distributed execution and typically result in higher performance.
2) auto-switch mode: InfiniDB will attempt to process the query internally, if it cannot, it will automatically switch the query to run in row-by-row mode.
With
infinidb_vtable_mode=2, the sub select will be handled within the
MySQL front end and will not have the full benefit of the MPP
architecture. The vtable mode option allows extended syntax support
and can be a viable option for certain types of queries. It's very
possible that MYSQL will run for hours or even days on a query that
involves sub selects against the row counts you posted.
