We need a way to investigate improvements to sql.

[YeungKC]

I'm recently improving sql performance and would like to do this with data such as execution time, EXPLAIN QUERY PLAN results, etc.

Currently I am doing this by creating CustomVmDatabaseWrapper with the following core code.

  Future<T> logWrapper<T>(Future<T> Function() run, String statement,
      List<Object?>? args, bool logStatements) async {
    Stopwatch? stopwatch;
    if (logStatements) {
      stopwatch = Stopwatch()..start();
    }

    final result = await run();
    stopwatch?.stop();

    if (stopwatch != null && stopwatch.elapsed.inMilliseconds > 10) {
      final list = await vmDatabase.runSelect('EXPLAIN QUERY PLAN $statement', args ?? []);
      final map = list.map((e) => e['detail']!);
      w('''execution time: ${stopwatch.elapsed.inMilliseconds} MS, args: $args, sql:
$statement
EXPLAIN QUERY PLAN RESULT: 
${map.join('\n')}
       ''');
    }

    return result;
  }

My optimization idea is to avoid SCAN keywords appearing in EXPLAIN QUERY PLAN RESULT and, if possible, to avoid temp B Tree as much as possible.

I believe it would make a lot of people happy if we could do out-of-the-box sql analysis of what indexes are suggested to be created based on EXPLAIN QUERY PLAN RESULT, for example.

[simolus3]

Thanks for opening this issue, I think it could be a helpful addition. How would you envision an api or tooling for this? We can provide a wrapper class similar to the one you wrote (so that it's opt-in and there's no code size impact when this isn't used). We can report the most time-consuming queries and their corresponding query plan, but I'm not sure if that alone helps?

Automatically suggesting indices based on a query plan isn't easy for sure, I don't really a have a good idea on how that could look like. Are you aware of any other tool doing that?

[YeungKC]

It is true that automatic indexing suggestions are difficult and almost impossible to generate perfect indexes, but we can provide more information.

My vision is to present the results of EXPLAIN in a more humane way. For example, when SCAN appears, it outputs a warning and outputs the statements that match all the indexes hit. If SEARCH appears, it also indicates whether all indexes are hit, and outputs the corresponding where and index suggestions. Of course, the same behavior is also true for B-TREE.

I believe it is user-friendly enough to help many people.

Btw actually recently followed this logic to improve it and cleaned up some useless indexes.

Hope these ideas of mine can help you.