lib/ecto_mysql_extras/queries/table_indexes_size.ex

defmodule EctoMySQLExtras.TableIndexesSize do
  @moduledoc """
  Query the total size of indexes for each table in the Ecto Repo database.
  Primary indexes are not included since InnoDB uses it as the clustered index,
  the size represents the table itself.

  Data is retrieved from the `information_schema` database and the `tables` table.
  """
  @behaviour EctoMySQLExtras

  def info do
    %{
      title:
        "Total size of all the indexes on each table (excluding PRIMARY indexes), descending by size",
      order_by: [index_size: :DESC],
      args: [:table],
      columns: [
        %{name: :schema, type: :string},
        %{name: :name, type: :string},
        %{name: :engine, type: :string},
        %{name: :index_size, type: :bytes}
      ]
    }
  end

  def query(args \\ []) do
    where_table =
      if args[:table] do
        "TABLE_NAME = '#{args[:table]}'"
      else
        "TABLE_NAME IS NOT NULL"
      end

    """
    /* ECTO_MYSQL_EXTRAS: #{info().title} */

    SELECT
      TABLE_SCHEMA AS `schema`,
      TABLE_NAME AS `name`,
      ENGINE AS `engine`,
      INDEX_LENGTH AS `index_size`
    FROM information_schema.tables
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_TYPE <> 'VIEW'
    AND #{where_table}
    ORDER BY `index_size` DESC;
    """
  end
end