-- 1 sec mysql> SELECT * FROM bigtable; ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded mysql> SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM bigtable; ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded Server Side Statement Timeout 【 Optimizer Hint をつかう 】 【SETで指定する】
sys.statement_analysis \G *************************** 1. row *************************** View: statement_analysis Create View: CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `statement_analysis` AS select `sys`.`format_statement`(`performance_schema`.`events_statements_summary_by_digest`.`DIGEST_TEXT`) AS `query`,`performance_schema`.`events_statements_summary_by_digest`.`SCHEMA_NAME` AS `db`,if(((`performance_schema`.`events_statements_summary_by_digest`.`SUM_NO_GOOD_INDEX_USED` > 0) or (`performance_schema`.`events_statements_summary_by_digest`.`SUM_NO_INDEX_USED` > 0)),'*','') AS `full_scan`,`performance_schema`.`events_statements_summary_by_digest`.`COUNT_STAR` AS `exec_count`,`performance_schema`.`events_statements_summary_by_digest`.`SUM_ERRORS` AS `err_count`,`performance_schema`.`events_statements_summary_by_digest`.`SUM_WARNINGS` AS `warn_count`,`sys`.`format_time`(`performance_schema`.`events_statements_summary_by_digest`.`SUM_TIMER_WAIT`) AS `total_latency`,`sys`.`format_time`(`performance_schema`.`events_statements_summary_by_digest`.`MAX_TIMER_WAIT`) AS `max_latency`,`sys`.`format_time`(`performance_schema`.`events_statements_summary_by_digest`.`AVG_TIMER_WAIT`) AS `avg_latency`,`sys`.`format_time`(`performance_schema`.`events_statements_summary_by_digest`.`SUM_LOCK_TIME`) AS `lock_latency`,`performance_schema`.`events_statements_summary_by_digest`.`SUM_ROWS_SENT` AS `rows_sent`,round(ifnull((`performance_schema`.`events_statements_summary_by_digest`.`SUM_ROWS_SENT` / nullif(`performance_schema`.`events_statements_summary_by_digest`.`COUNT_STAR`,0)),0),0) AS `rows_sent_avg` <snip>
• デフォルトでは無効化されているため、有効化が必要 -- 戻すときは、ENABLED を NO に mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES’ WHERE NAME LIKE 'stage/innodb/alter%’; mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES’ WHERE NAME LIKE '%stages%';
`orders`.`deleted_at` IS NULL AND `orders`.`client_id` = 12345 AND (contracted_cd REGEXP '^000-[0-9]+$’) ORDER BY CAST(replace(contracted_cd,'000-','') as SIGNED) desc LIMIT 1 \G 1 row in set (2.50 sec) mysql> SELECT `orders`.contracted_cd FROM `orders` WHERE `orders`.`deleted_at` IS NULL AND `orders`.`client_id` = 12345 AND (contracted_cd REGEXP '^000-[0-9]+$’); +---------------+ | contracted_cd | +---------------+ | 000-10000 | | 000-20000 | ~~ | 000-9000 | +---------------+ ハイフンより後ろの 数値で降順ソート
FROM fts_tbl WHERE data LIKE ‘%ANDPAD%’ と同じ mysql> SELECT * FROM fts_tbl WHERE MATCH(data) AGAINST ('ANDPAD' IN BOOLEAN MODE); +----+----------------------------------------------------------------------------------------+ | pk | data | +----+----------------------------------------------------------------------------------------+ | 2 | 施工管理といえばANDPAD。施工状況をどこでも確認できるから、もう工期に遅れない。 | | 3 | 【シェアNo.1】施工管理アプリ| ANDPAD(アンドパッド) | +----+----------------------------------------------------------------------------------------+