# Query Analyzer (QueryAnalyzer)

`QueryAnalyzer` is a utility class for analyzing SQLite query performance and providing optimization suggestions. It helps developers identify inefficient queries, detect full table scans, analyze index usage, and suggest indexes for complex queries.

> Compatible with better-sqlite3 v11.8.1 and SQLite 3.48.0

## Features

1. **Query Execution Plan Analysis**: Parses SQLite EXPLAIN QUERY PLAN output to identify potential performance issues
2. **Index Usage Detection**: Checks if queries are using available indexes and identifies index scans vs table scans
3. **Performance Suggestions**: Generates optimization suggestions based on query structure and table structure
4. **Execution Time Measurement**: Measures query execution time to help identify slow queries
5. **Index Suggestions**: Automatically suggests indexes based on query patterns

## Basic Usage

### Analyzing SQL Queries

```typescript
import { QueryAnalyzer } from 'quicklite';
import Database from 'better-sqlite3';

const db = new Database('myapp.db');

// Analyze a simple query
const analysis = QueryAnalyzer.analyze(
  db,
  'SELECT * FROM users WHERE age > 30'
);

console.log('Query:', analysis.sql);
console.log('Execution time:', analysis.executionTime, 'ms');
console.log('Query plan:');
analysis.queryPlan.forEach(p => {
  console.log(`- ${p.detail}`);
});
console.log('Suggestions:');
analysis.suggestions.forEach(s => {
  console.log(`- ${s}`);
});

// Analyze a parameterized query
const paramQueryAnalysis = QueryAnalyzer.analyze(
  db,
  'SELECT * FROM users WHERE email = ?',
  ['user@example.com']
);
```

### Getting Index Suggestions

```typescript
// Get index suggestions for a complex query
const indexSuggestions = QueryAnalyzer.suggestIndices(
  db,
  `SELECT u.name, o.product, SUM(o.amount) as total
   FROM users u 
   JOIN orders o ON u.id = o.user_id 
   WHERE u.age > 30 
   GROUP BY u.id, o.product
   ORDER BY total DESC`
);

console.log('Suggested indexes:');
indexSuggestions.forEach(s => {
  console.log(`- ${s}`);
});
```

## API Reference

### QueryAnalyzer Class

#### Static Methods

- **analyze(db: Database, sql: string, params?: any[]): QueryAnalysisResult**
  
  Analyzes an SQL query and returns detailed analysis results.
  
  Parameters:
  - `db`: Better-SQLite3 database instance
  - `sql`: SQL query to analyze
  - `params` (optional): Query parameters
  
  Returns: A `QueryAnalysisResult` object containing query plan, execution time, and optimization suggestions

- **analyzePlan(queryPlan: QueryPlan[], sql: string): string[]**
  
  Generates optimization suggestions based on a query plan.
  
  Parameters:
  - `queryPlan`: Array of query plan items
  - `sql`: SQL query
  
  Returns: Array of suggestion strings

- **extractTableNames(sql: string): string[]**
  
  Extracts table names from an SQL query.
  
  Parameters:
  - `sql`: SQL query
  
  Returns: Array of table names

- **suggestIndices(db: Database, sql: string): string[]**
  
  Suggests possible indices for a given SQL query.
  
  Parameters:
  - `db`: Better-SQLite3 database instance
  - `sql`: SQL query
  
  Returns: Array of index suggestion strings

### Type Definitions

```typescript
// Query plan item
interface QueryPlan {
  id: number;
  parentId: number | null;
  detail: string;
}

// Query analysis result
interface QueryAnalysisResult {
  sql: string;
  queryPlan: QueryPlan[];
  executionTime: number;
  suggestions: string[];
}
```

## Types of Performance Suggestions

QueryAnalyzer can provide various types of performance suggestions, including but not limited to:

1. **Missing Index Suggestions**: When a query contains WHERE, JOIN, or ORDER BY clauses without corresponding indexes
2. **Full Table Scan Warnings**: When a query needs to scan an entire table instead of using an index
3. **Temporary Table Alerts**: When a query needs to create temporary tables for sorting or grouping
4. **Compound Index Suggestions**: When multiple conditions in a query could be optimized with a single compound index
5. **Query Restructuring Recommendations**: Recommending potentially more efficient query patterns

## Best Practices

1. **Regularly Analyze Critical Queries**: Use QueryAnalyzer to regularly check the most frequently used and complex queries in your application
2. **Verify Index Effectiveness**: After adding suggested indexes, analyze the query again to confirm performance improvements
3. **Use During Development**: Catch and solve performance issues early, not when they're already in production
4. **Balance Index Count**: Adding indexes improves query speed but increases write overhead and storage space - find the right balance

## Example Usage Scenarios

### Optimizing Data Report Queries

```typescript
// Analyze a complex reporting query
const reportQueryAnalysis = QueryAnalyzer.analyze(
  db,
  `SELECT 
     u.department,
     COUNT(DISTINCT u.id) as userCount,
     SUM(o.amount) as totalAmount
   FROM users u
   LEFT JOIN orders o ON u.id = o.userId
   WHERE u.joinDate > ?
   GROUP BY u.department
   ORDER BY totalAmount DESC`,
  [Date.now() - 90 * 24 * 60 * 60 * 1000] // Last 90 days
);

// Apply suggested indexes
reportQueryAnalysis.suggestions
  .filter(s => s.includes('Consider creating an index on'))
  .forEach(suggestion => {
    const match = suggestion.match(/on\s+(\w+)\.(\w+)/);
    if (match) {
      const [_, table, column] = match;
      const indexName = `idx_${table}_${column}`;
      db.exec(`CREATE INDEX IF NOT EXISTS ${indexName} ON ${table}(${column})`);
      console.log(`Created index: ${indexName}`);
    }
  });
```

### Monitoring Application Query Performance

```typescript
// Integrate query monitoring in your application
function monitorQuery(db, sql, params = []) {
  const analysis = QueryAnalyzer.analyze(db, sql, params);
  
  // Log queries that take more than 100ms
  if (analysis.executionTime > 100) {
    logger.warn(`Slow query detected: ${sql}`);
    logger.warn(`Execution time: ${analysis.executionTime}ms`);
    logger.warn(`Optimization suggestions: ${analysis.suggestions.join('\n')}`);
  }
  
  return db.prepare(sql).all(params);
}
```