How do I profile slow queries using database/sql with MySQL?

Profiling slow queries in Go using the database/sql package with MySQL can help you identify performance bottlenecks efficiently. By carefully logging and analyzing the queries that take too long to execute, you can optimize them for better performance. Here’s how you can achieve that.

package main

import (
    "database/sql"
    "fmt"
    "log"
    "time"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // Open a connection to the MySQL database
    db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Enable query profiling
    _, err = db.Exec("SET profiling = 1")
    if err != nil {
        log.Fatal(err)
    }

    // Example query
    start := time.Now()
    rows, err := db.Query("SELECT * FROM your_table WHERE some_column = ?", "some_value")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    
    // Process the rows...
    
    fmt.Printf("Query executed in: %s\n", time.Since(start))

    // Get profiling results
    var queryID int
    var duration time.Duration
    err = db.QueryRow("SHOW PROFILES").Scan(&queryID, &duration)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Query ID: %d, Duration: %v\n", queryID, duration)
}