MySQLGenius

A AI powered MySQL dashboard and build to help you optimize your database for maximum performance, inspired by PgHero.

At-a-glance server health, top slow queries, most expensive queries, and index alerts.

SELECT queries exceeding the configured threshold, captured via ActiveSupport notifications and Redis.

Top queries from performance_schema sorted by total time, with call counts, avg/max time, and rows examined.

Server health: version, connections, InnoDB buffer pool, and query activity with AI-powered diagnostics.

View row counts, data size, index size, fragmentation, and a visual size chart for every table.

Find redundant indexes whose columns are a left-prefix of another index, with ready-to-run DROP INDEX statements.

Build queries visually or write raw SQL. Optional AI assistant generates queries from plain English descriptions.

Schema review that finds anti-patterns -- missing primary keys, nullable foreign keys, inappropriate column types, and more.

• Visual Query Builder -- point-and-click query construction with column selection, type-aware filters, and ordering
• Safe SQL Execution -- read-only enforcement, blocked tables, masked sensitive columns, row limits, query timeouts
• EXPLAIN Analysis -- run EXPLAIN on any query and view the execution plan
• AI Query Suggestions -- describe what you want in plain English, get SQL back (optional, any OpenAI-compatible API)
• AI Query Optimization -- get actionable optimization suggestions from EXPLAIN output (optional)
• Slow Query Monitoring -- captures slow SELECT queries via ActiveSupport notifications and Redis
• Duplicate Index Detection -- finds redundant indexes whose columns are a left-prefix of another index
• Table Size Dashboard -- view row counts, data size, index size, and fragmentation for all tables
• Audit Logging -- logs all query executions, rejections, and errors
• MariaDB Support -- automatically detects MariaDB and uses appropriate timeout syntax
• Self-contained UI -- no external CSS/JS dependencies, works with any Rails layout
• Zero jQuery -- pure vanilla JavaScript frontend

• Rails 5.2+
• Ruby 2.6+
• MySQL or MariaDB
• Redis (optional, for slow query monitoring)

Add to your Gemfile:

gem "mysql_genius"

Or from GitHub:

gem "mysql_genius", github: "antarr/mysql_genius"

Then run:

bundle install

In config/routes.rb:

Rails.application.routes.draw do
  mount MysqlGenius::Engine, at: "/mysql_genius"
end

To restrict access at the route level:

# Using a session constraint
constraints ->(req) { req.session[:admin] } do
  mount MysqlGenius::Engine, at: "/mysql_genius"
end

# Or using Devise
authenticate :user, ->(u) { u.admin? } do
  mount MysqlGenius::Engine, at: "/mysql_genius"
end

Create config/initializers/mysql_genius.rb:

MysqlGenius.configure do |config|
  # --- Authentication ---
  # Lambda that receives the controller instance. Return true to allow access.
  # Default: allows everyone. Use route constraints for most cases.
  config.authenticate = ->(controller) { true }

  # To use current_user or other app helpers, inherit from ApplicationController:
  # config.base_controller = "ApplicationController"
  # config.authenticate = ->(controller) { controller.current_user&.admin? }

  # --- Tables ---
  # Tables featured at the top of the visual builder dropdown (optional)
  config.featured_tables = %w[users posts comments]

  # Tables blocked from querying (defaults: sessions, schema_migrations, ar_internal_metadata)
  config.blocked_tables += %w[oauth_tokens api_keys]

  # Column patterns to redact with [REDACTED] in results (case-insensitive substring match)
  config.masked_column_patterns = %w[password secret digest token ssn]

  # Default columns checked in the visual builder per table (optional).
  # When empty for a table, all columns are checked by default.
  config.default_columns = {
    "users" => %w[id name email created_at],
    "posts" => %w[id title user_id published_at]
  }

  # --- Query Safety ---
  config.max_row_limit = 1000       # Hard cap on rows returned
  config.default_row_limit = 25     # Default when no limit specified
  config.query_timeout_ms = 30_000  # 30 second timeout (uses MariaDB or MySQL hints)

  # --- Slow Query Monitoring ---
  # Requires Redis. Set to nil to disable.
  config.redis_url = ENV["REDIS_URL"].presence || "redis://127.0.0.1:6379/0"
  config.slow_query_threshold_ms = 250

  # --- Audit Logging ---
  # Set to nil to disable. Logs query executions, rejections, and errors.
  config.audit_logger = Logger.new(Rails.root.join("log", "mysql_genius.log"))
end

MySQLGenius supports AI-powered query suggestions and optimization via any OpenAI-compatible API, including OpenAI, Azure OpenAI, Ollama Cloud, and local Ollama instances.

MysqlGenius.configure do |config|
  # --- Option A: OpenAI ---
  config.ai_endpoint = "https://api.openai.com/v1/chat/completions"
  config.ai_api_key = ENV["OPENAI_API_KEY"]
  config.ai_model = "gpt-4o"
  config.ai_auth_style = :bearer

  # --- Option B: Azure OpenAI ---
  config.ai_endpoint = ENV["AZURE_OPENAI_ENDPOINT"]  # Your deployment URL
  config.ai_api_key = ENV["AZURE_OPENAI_API_KEY"]
  config.ai_auth_style = :api_key                     # Default, uses api-key header

  # --- Option C: Ollama Cloud ---
  config.ai_endpoint = "https://api.ollama.com/v1/chat/completions"
  config.ai_api_key = ENV["OLLAMA_API_KEY"]
  config.ai_model = "gemma3:27b"
  config.ai_auth_style = :bearer

  # --- Option D: Local Ollama ---
  config.ai_endpoint = "http://localhost:11434/v1/chat/completions"
  config.ai_api_key = "ollama"  # Any non-empty string
  config.ai_model = "llama3"
  config.ai_auth_style = :bearer

  # --- Option E: Custom client ---
  # Any callable that accepts messages: and temperature: kwargs
  # and returns an OpenAI-compatible response hash.
  config.ai_client = ->(messages:, temperature:) {
    MyAiService.chat(messages, temperature: temperature)
  }

  # --- Domain Context ---
  # Helps the AI understand your schema and generate better queries.
  config.ai_system_context = <<~CONTEXT
    This is an e-commerce database.
    - `users` stores customer accounts. Primary key is `id`.
    - `orders` tracks purchases. Linked to users via `user_id`.
    - `products` contains the product catalog.
    - Soft-deleted records have `deleted_at IS NOT NULL`.
  CONTEXT
end

When AI is not configured, the AI Assistant panel and optimization buttons are hidden automatically.

Visit /mysql_genius in your browser. The dashboard loads automatically with an overview of your database health.

The default landing page shows server health cards, top 5 slow queries, top 5 most expensive queries (from performance_schema), and index alert badges for duplicate and unused indexes. Each section links to its detailed tab.

Combines the visual query builder and raw SQL editor in one tab. Toggle between Visual mode (point-and-click with column selection, filters, and ordering) and SQL mode (raw SQL with optional AI assistant). Generated SQL syncs between modes.

• Slow Queries -- slow SELECT queries captured from your application in real time, with Explain and Optimize actions
• Query Stats -- top queries from performance_schema sorted by total time, avg time, calls, or rows examined
• Server -- connections, InnoDB buffer pool, query activity, with AI-powered diagnostics
• Table Sizes -- row counts, data size, index size, fragmentation for all tables
• Unused Indexes -- indexes with zero reads since server restart
• Duplicate Indexes -- redundant indexes with ready-to-run DROP statements

Tested against:

git clone https://github.com/antarr/mysql_genius.git
cd mysql_genius
bin/setup
bundle exec rspec

To test against a specific Rails version:

RAILS_VERSION=6.1 bundle update && bundle exec rspec

Bug reports and pull requests are welcome on GitHub at https://github.com/antarr/mysql_genius.

The gem is available as open source under the terms of the MIT License.