Skip to main content

2. Configuration

PawSQL Advisor provides a project-level configuration page for input, output, data sources and runtime settings.

./assets/plugin-config.png

2.1 Optimization Settings​

  • Input Type: SQL file or Mapper file
    • SQL files, native SQL file with ; as delimiter
    • Mapper files, extract SQL from MyBatis mapper files and permutate all possible SQL combinations

Mapper file input is important for capturing all possible SQL combinations during application development. Note: Unused SQLs may be generated from poorly written mapper files, leading to useless index recommendations.

  • Language of Output: The language used in recommendations, Chinese or English

  • Enable SQL Rewriting: Whether to enable SQL rewriting optimization. Disable to only get index recommendations when unable to modify SQL easily.

  • Enable What-If Validation: Whether to validate the performance of recommended optimizations. By enabling this option, PawSQL provides explain plans and costs before and after optimization for SQLs.

  • Execute SQL for Validation: Whether to execute SQL to get actual execution time instead of relying on imperfect EXPLAIN information.

Cautions: Please be careful when you enable What-If Validation on production databases, as it will create and drop recommended indexes which may impact application performance.

2.2 Database Settings​

Database connection is used to:

  1. Retrieve database objects like tables, views, columns, indexes as context of SQL optimization
  2. Perform What-If optimization validation

Settings for database include:

  • Database Type: MySQL, PostgreSQL, openGauss, Oracle, MariaDB, KingbaseES
  • Host : the host address which host the database instance,
  • Port: the port of database instance for PawSQL Advisor to connect to
  • Username: the user name for PawSQL Advisor to connect to database instance
  • Password: the password for PawSQL Advisor to connect to database instance
  • Default Database: the default database for PawSQL Advisor to connect to
  • Database/Schema List: List of databases (MySQL) or schemas (PostgreSQL/openGauss). Their tables, views, indexes and stats will be used for optimization.

2.3 Index Advisor Settings​

  • Deduplicate /w Existing Indexes: Whether to deduplicate recommended indexes covered with existing ones. Enable if you only want to add new indexes without dropping existing ones. Disable to replace existing indexes with recommended ones.
  • Recommend Covering Indexes: Whether to recommend covering indexes that can satisfy queries purely from the index without accessing the table. Can significantly improve performance but also increase index size by including non-query columns.
  • Maximum Columns in Covering Indexes: Maximum number of columns to include in a covering index.
  • Maximum Columns in Recommended Indexes: Maximum number of columns in a regular index, to control index size and the depth of an index tree.
  • Maximum Indexes per Table: Threshold for warning on excessive indexes per table.