SQL Queries - nimrody/knowledgebase GitHub Wiki

SQL Editors

General techniques

  • Join two tables based on closest time

    select t1.user_id, t1.time, min(t2.time - t1.time)/1000 as diff_sec
    from nimrod._user_load_time t1 join nimrod._user_load_time t2 on t1.user_id = t2.user_id and t1.package_name = t2.package_name and t1.package_name='com.tmon' and t1.load_scenario = 'PRELOAD_DELAYED' and t2.load_scenario IN ('USER_ENTER_APP_PRELOADED', 'USER_ENTER_APP_NOT_PRELOADED') and t1.time < t2.time
    group by 1,2 having diff_sec < 10 order by 1,2,3 limit 900;

  • Show active transactions in MySQL

    SELECT * FROM information_schema.innodb_trx ORDER BY trx_started

  • Show non-system transactions started 1 second ago (from here)

    SELECT trx.trx_id, trx.trx_started, trx.trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX trx JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON trx.trx_mysql_thread_id = ps.id WHERE trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 1 SECOND AND ps.user != 'system_user'\G

  • Finding sessions using SQL window functions

  • Show table sizes

    SELECT table_name "Table name", lpad(truncate( ( data_length + index_length ) / 1024 / 1024, 2), 5, ' ') "Data Base Size in MB", lpad(format( data_free / 1024 / 1024,2), 5, ' ') "Free Space in MB" FROM information_schema.TABLES where table_schema='activitydb' order by (data_length+index_length) desc;

  • Show schemas sizes

    SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema

  • Innodb monitoring

    show global status like 'Innodb_row%'; show engine innodb status;

⚠️ **GitHub.com Fallback** ⚠️