1CREATE DATABASE db; 2USE db; 3TRUNCATE TABLE performance_schema.prepared_statements_instances ; 4TRUNCATE TABLE performance_schema.events_statements_history_long ; 5CREATE TABLE t1 (a INT NOT NULL); 6INSERT INTO t1 VALUES (4), (8), (11), (32), (80); 7PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; 8SET @table = 't1'; 9SET @s = CONCAT('SELECT * FROM ', @table); 10PREPARE st2 FROM @s; 11PREPARE st3 FROM 'INSERT INTO t1 SELECT * FROM t1 WHERE a<=?'; 12PREPARE st4 FROM 13'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a'; 14SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 15STATEMENT_NAME st1 16SQL_TEXT SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse 17COUNT_REPREPARE 0 18COUNT_EXECUTE 0 19SUM_ROWS_SENT 0 20SUM_SELECT_SCAN 0 21SUM_NO_INDEX_USED 0 22STATEMENT_NAME st2 23SQL_TEXT SELECT * FROM t1 24COUNT_REPREPARE 0 25COUNT_EXECUTE 0 26SUM_ROWS_SENT 0 27SUM_SELECT_SCAN 0 28SUM_NO_INDEX_USED 0 29STATEMENT_NAME st3 30SQL_TEXT INSERT INTO t1 SELECT * FROM t1 WHERE a<=? 31COUNT_REPREPARE 0 32COUNT_EXECUTE 0 33SUM_ROWS_SENT 0 34SUM_SELECT_SCAN 0 35SUM_NO_INDEX_USED 0 36STATEMENT_NAME st4 37SQL_TEXT (SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a 38COUNT_REPREPARE 0 39COUNT_EXECUTE 0 40SUM_ROWS_SENT 0 41SUM_SELECT_SCAN 0 42SUM_NO_INDEX_USED 0 43SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' ; 44EVENT_NAME statement/sql/truncate 45SQL_TEXT TRUNCATE TABLE performance_schema.events_statements_history_long 46OBJECT_TYPE NULL 47OBJECT_SCHEMA NULL 48OBJECT_NAME NULL 49EVENT_NAME statement/sql/create_table 50SQL_TEXT CREATE TABLE t1 (a INT NOT NULL) 51OBJECT_TYPE NULL 52OBJECT_SCHEMA NULL 53OBJECT_NAME NULL 54EVENT_NAME statement/sql/insert 55SQL_TEXT INSERT INTO t1 VALUES (4), (8), (11), (32), (80) 56OBJECT_TYPE NULL 57OBJECT_SCHEMA NULL 58OBJECT_NAME NULL 59EVENT_NAME statement/sql/prepare_sql 60SQL_TEXT PREPARE st1 FROM ... 61OBJECT_TYPE NULL 62OBJECT_SCHEMA NULL 63OBJECT_NAME NULL 64EVENT_NAME statement/sql/set_option 65SQL_TEXT SET @table = 't1' 66OBJECT_TYPE NULL 67OBJECT_SCHEMA NULL 68OBJECT_NAME NULL 69EVENT_NAME statement/sql/set_option 70SQL_TEXT SET @s = CONCAT('SELECT * FROM ', @table) 71OBJECT_TYPE NULL 72OBJECT_SCHEMA NULL 73OBJECT_NAME NULL 74EVENT_NAME statement/sql/prepare_sql 75SQL_TEXT PREPARE st2 FROM @s 76OBJECT_TYPE NULL 77OBJECT_SCHEMA NULL 78OBJECT_NAME NULL 79EVENT_NAME statement/sql/prepare_sql 80SQL_TEXT PREPARE st3 FROM ... 81OBJECT_TYPE NULL 82OBJECT_SCHEMA NULL 83OBJECT_NAME NULL 84EVENT_NAME statement/sql/prepare_sql 85SQL_TEXT PREPARE st4 FROM ... 86OBJECT_TYPE NULL 87OBJECT_SCHEMA NULL 88OBJECT_NAME NULL 89EVENT_NAME statement/sql/select 90SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances 91OBJECT_TYPE NULL 92OBJECT_SCHEMA NULL 93OBJECT_NAME NULL 94SET @a = 3; 95SET @b = 4; 96EXECUTE st1 USING @a, @b; 97hypotenuse 985 99EXECUTE st2; 100a 1014 1028 10311 10432 10580 106SET @c=3; 107EXECUTE st3 using @c; 108EXECUTE st4; 109a 1104 1118 11211 11314 11418 11521 11632 11742 11880 11990 120SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 121STATEMENT_NAME st1 122SQL_TEXT SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse 123COUNT_REPREPARE 0 124COUNT_EXECUTE 1 125SUM_ROWS_SENT 1 126SUM_SELECT_SCAN 0 127SUM_NO_INDEX_USED 0 128STATEMENT_NAME st2 129SQL_TEXT SELECT * FROM t1 130COUNT_REPREPARE 0 131COUNT_EXECUTE 1 132SUM_ROWS_SENT 5 133SUM_SELECT_SCAN 1 134SUM_NO_INDEX_USED 1 135STATEMENT_NAME st3 136SQL_TEXT INSERT INTO t1 SELECT * FROM t1 WHERE a<=? 137COUNT_REPREPARE 0 138COUNT_EXECUTE 1 139SUM_ROWS_SENT 0 140SUM_SELECT_SCAN 1 141SUM_NO_INDEX_USED 1 142STATEMENT_NAME st4 143SQL_TEXT (SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a 144COUNT_REPREPARE 0 145COUNT_EXECUTE 1 146SUM_ROWS_SENT 10 147SUM_SELECT_SCAN 3 148SUM_NO_INDEX_USED 1 149SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' ; 150EVENT_NAME statement/sql/truncate 151SQL_TEXT TRUNCATE TABLE performance_schema.events_statements_history_long 152OBJECT_TYPE NULL 153OBJECT_SCHEMA NULL 154OBJECT_NAME NULL 155EVENT_NAME statement/sql/create_table 156SQL_TEXT CREATE TABLE t1 (a INT NOT NULL) 157OBJECT_TYPE NULL 158OBJECT_SCHEMA NULL 159OBJECT_NAME NULL 160EVENT_NAME statement/sql/insert 161SQL_TEXT INSERT INTO t1 VALUES (4), (8), (11), (32), (80) 162OBJECT_TYPE NULL 163OBJECT_SCHEMA NULL 164OBJECT_NAME NULL 165EVENT_NAME statement/sql/prepare_sql 166SQL_TEXT PREPARE st1 FROM ... 167OBJECT_TYPE NULL 168OBJECT_SCHEMA NULL 169OBJECT_NAME NULL 170EVENT_NAME statement/sql/set_option 171SQL_TEXT SET @table = 't1' 172OBJECT_TYPE NULL 173OBJECT_SCHEMA NULL 174OBJECT_NAME NULL 175EVENT_NAME statement/sql/set_option 176SQL_TEXT SET @s = CONCAT('SELECT * FROM ', @table) 177OBJECT_TYPE NULL 178OBJECT_SCHEMA NULL 179OBJECT_NAME NULL 180EVENT_NAME statement/sql/prepare_sql 181SQL_TEXT PREPARE st2 FROM @s 182OBJECT_TYPE NULL 183OBJECT_SCHEMA NULL 184OBJECT_NAME NULL 185EVENT_NAME statement/sql/prepare_sql 186SQL_TEXT PREPARE st3 FROM ... 187OBJECT_TYPE NULL 188OBJECT_SCHEMA NULL 189OBJECT_NAME NULL 190EVENT_NAME statement/sql/prepare_sql 191SQL_TEXT PREPARE st4 FROM ... 192OBJECT_TYPE NULL 193OBJECT_SCHEMA NULL 194OBJECT_NAME NULL 195EVENT_NAME statement/sql/select 196SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances 197OBJECT_TYPE NULL 198OBJECT_SCHEMA NULL 199OBJECT_NAME NULL 200EVENT_NAME statement/sql/select 201SQL_TEXT SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' 202OBJECT_TYPE NULL 203OBJECT_SCHEMA NULL 204OBJECT_NAME NULL 205EVENT_NAME statement/sql/set_option 206SQL_TEXT SET @a = 3 207OBJECT_TYPE NULL 208OBJECT_SCHEMA NULL 209OBJECT_NAME NULL 210EVENT_NAME statement/sql/set_option 211SQL_TEXT SET @b = 4 212OBJECT_TYPE NULL 213OBJECT_SCHEMA NULL 214OBJECT_NAME NULL 215EVENT_NAME statement/sql/execute_sql 216SQL_TEXT EXECUTE st1 USING @a, @b 217OBJECT_TYPE NULL 218OBJECT_SCHEMA NULL 219OBJECT_NAME NULL 220EVENT_NAME statement/sql/execute_sql 221SQL_TEXT EXECUTE st2 222OBJECT_TYPE NULL 223OBJECT_SCHEMA NULL 224OBJECT_NAME NULL 225EVENT_NAME statement/sql/set_option 226SQL_TEXT SET @c=3 227OBJECT_TYPE NULL 228OBJECT_SCHEMA NULL 229OBJECT_NAME NULL 230EVENT_NAME statement/sql/execute_sql 231SQL_TEXT EXECUTE st3 using @c 232OBJECT_TYPE NULL 233OBJECT_SCHEMA NULL 234OBJECT_NAME NULL 235EVENT_NAME statement/sql/execute_sql 236SQL_TEXT EXECUTE st4 237OBJECT_TYPE NULL 238OBJECT_SCHEMA NULL 239OBJECT_NAME NULL 240EVENT_NAME statement/sql/select 241SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances 242OBJECT_TYPE NULL 243OBJECT_SCHEMA NULL 244OBJECT_NAME NULL 245TRUNCATE TABLE performance_schema.prepared_statements_instances ; 246SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 247STATEMENT_NAME st1 248SQL_TEXT SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse 249COUNT_REPREPARE 0 250COUNT_EXECUTE 0 251SUM_ROWS_SENT 0 252SUM_SELECT_SCAN 0 253SUM_NO_INDEX_USED 0 254STATEMENT_NAME st2 255SQL_TEXT SELECT * FROM t1 256COUNT_REPREPARE 0 257COUNT_EXECUTE 0 258SUM_ROWS_SENT 0 259SUM_SELECT_SCAN 0 260SUM_NO_INDEX_USED 0 261STATEMENT_NAME st3 262SQL_TEXT INSERT INTO t1 SELECT * FROM t1 WHERE a<=? 263COUNT_REPREPARE 0 264COUNT_EXECUTE 0 265SUM_ROWS_SENT 0 266SUM_SELECT_SCAN 0 267SUM_NO_INDEX_USED 0 268STATEMENT_NAME st4 269SQL_TEXT (SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a 270COUNT_REPREPARE 0 271COUNT_EXECUTE 0 272SUM_ROWS_SENT 0 273SUM_SELECT_SCAN 0 274SUM_NO_INDEX_USED 0 275DEALLOCATE PREPARE st1; 276DEALLOCATE PREPARE st2; 277DEALLOCATE PREPARE st3; 278DEALLOCATE PREPARE st4; 279DROP TABLE t1; 280SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 281SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' ; 282EVENT_NAME statement/sql/truncate 283SQL_TEXT TRUNCATE TABLE performance_schema.events_statements_history_long 284OBJECT_TYPE NULL 285OBJECT_SCHEMA NULL 286OBJECT_NAME NULL 287EVENT_NAME statement/sql/create_table 288SQL_TEXT CREATE TABLE t1 (a INT NOT NULL) 289OBJECT_TYPE NULL 290OBJECT_SCHEMA NULL 291OBJECT_NAME NULL 292EVENT_NAME statement/sql/insert 293SQL_TEXT INSERT INTO t1 VALUES (4), (8), (11), (32), (80) 294OBJECT_TYPE NULL 295OBJECT_SCHEMA NULL 296OBJECT_NAME NULL 297EVENT_NAME statement/sql/prepare_sql 298SQL_TEXT PREPARE st1 FROM ... 299OBJECT_TYPE NULL 300OBJECT_SCHEMA NULL 301OBJECT_NAME NULL 302EVENT_NAME statement/sql/set_option 303SQL_TEXT SET @table = 't1' 304OBJECT_TYPE NULL 305OBJECT_SCHEMA NULL 306OBJECT_NAME NULL 307EVENT_NAME statement/sql/set_option 308SQL_TEXT SET @s = CONCAT('SELECT * FROM ', @table) 309OBJECT_TYPE NULL 310OBJECT_SCHEMA NULL 311OBJECT_NAME NULL 312EVENT_NAME statement/sql/prepare_sql 313SQL_TEXT PREPARE st2 FROM @s 314OBJECT_TYPE NULL 315OBJECT_SCHEMA NULL 316OBJECT_NAME NULL 317EVENT_NAME statement/sql/prepare_sql 318SQL_TEXT PREPARE st3 FROM ... 319OBJECT_TYPE NULL 320OBJECT_SCHEMA NULL 321OBJECT_NAME NULL 322EVENT_NAME statement/sql/prepare_sql 323SQL_TEXT PREPARE st4 FROM ... 324OBJECT_TYPE NULL 325OBJECT_SCHEMA NULL 326OBJECT_NAME NULL 327EVENT_NAME statement/sql/select 328SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances 329OBJECT_TYPE NULL 330OBJECT_SCHEMA NULL 331OBJECT_NAME NULL 332EVENT_NAME statement/sql/select 333SQL_TEXT SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' 334OBJECT_TYPE NULL 335OBJECT_SCHEMA NULL 336OBJECT_NAME NULL 337EVENT_NAME statement/sql/set_option 338SQL_TEXT SET @a = 3 339OBJECT_TYPE NULL 340OBJECT_SCHEMA NULL 341OBJECT_NAME NULL 342EVENT_NAME statement/sql/set_option 343SQL_TEXT SET @b = 4 344OBJECT_TYPE NULL 345OBJECT_SCHEMA NULL 346OBJECT_NAME NULL 347EVENT_NAME statement/sql/execute_sql 348SQL_TEXT EXECUTE st1 USING @a, @b 349OBJECT_TYPE NULL 350OBJECT_SCHEMA NULL 351OBJECT_NAME NULL 352EVENT_NAME statement/sql/execute_sql 353SQL_TEXT EXECUTE st2 354OBJECT_TYPE NULL 355OBJECT_SCHEMA NULL 356OBJECT_NAME NULL 357EVENT_NAME statement/sql/set_option 358SQL_TEXT SET @c=3 359OBJECT_TYPE NULL 360OBJECT_SCHEMA NULL 361OBJECT_NAME NULL 362EVENT_NAME statement/sql/execute_sql 363SQL_TEXT EXECUTE st3 using @c 364OBJECT_TYPE NULL 365OBJECT_SCHEMA NULL 366OBJECT_NAME NULL 367EVENT_NAME statement/sql/execute_sql 368SQL_TEXT EXECUTE st4 369OBJECT_TYPE NULL 370OBJECT_SCHEMA NULL 371OBJECT_NAME NULL 372EVENT_NAME statement/sql/select 373SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances 374OBJECT_TYPE NULL 375OBJECT_SCHEMA NULL 376OBJECT_NAME NULL 377EVENT_NAME statement/sql/select 378SQL_TEXT SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' 379OBJECT_TYPE NULL 380OBJECT_SCHEMA NULL 381OBJECT_NAME NULL 382EVENT_NAME statement/sql/truncate 383SQL_TEXT TRUNCATE TABLE performance_schema.prepared_statements_instances 384OBJECT_TYPE NULL 385OBJECT_SCHEMA NULL 386OBJECT_NAME NULL 387EVENT_NAME statement/sql/select 388SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances 389OBJECT_TYPE NULL 390OBJECT_SCHEMA NULL 391OBJECT_NAME NULL 392EVENT_NAME statement/sql/dealloc_sql 393SQL_TEXT DEALLOCATE PREPARE st1 394OBJECT_TYPE NULL 395OBJECT_SCHEMA NULL 396OBJECT_NAME NULL 397EVENT_NAME statement/sql/dealloc_sql 398SQL_TEXT DEALLOCATE PREPARE st2 399OBJECT_TYPE NULL 400OBJECT_SCHEMA NULL 401OBJECT_NAME NULL 402EVENT_NAME statement/sql/dealloc_sql 403SQL_TEXT DEALLOCATE PREPARE st3 404OBJECT_TYPE NULL 405OBJECT_SCHEMA NULL 406OBJECT_NAME NULL 407EVENT_NAME statement/sql/dealloc_sql 408SQL_TEXT DEALLOCATE PREPARE st4 409OBJECT_TYPE NULL 410OBJECT_SCHEMA NULL 411OBJECT_NAME NULL 412EVENT_NAME statement/sql/drop_table 413SQL_TEXT DROP TABLE t1 414OBJECT_TYPE NULL 415OBJECT_SCHEMA NULL 416OBJECT_NAME NULL 417EVENT_NAME statement/sql/select 418SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances 419OBJECT_TYPE NULL 420OBJECT_SCHEMA NULL 421OBJECT_NAME NULL 422TRUNCATE TABLE performance_schema.events_statements_history_long ; 423UPDATE performance_schema.setup_consumers SET ENABLED = 'NO'; 424CREATE TABLE t1 (a INT NOT NULL); 425INSERT INTO t1 VALUES (4), (8), (11), (32), (80); 426PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; 427SET @table = 't1'; 428SET @s = CONCAT('SELECT * FROM ', @table); 429PREPARE st2 FROM @s; 430PREPARE st3 FROM 'INSERT INTO t1 SELECT * FROM t1 WHERE a<=?'; 431PREPARE st4 FROM 432'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a'; 433SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 434SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' ; 435EVENT_NAME statement/sql/truncate 436SQL_TEXT TRUNCATE TABLE performance_schema.events_statements_history_long 437OBJECT_TYPE NULL 438OBJECT_SCHEMA NULL 439OBJECT_NAME NULL 440SET @a = 3; 441SET @b = 4; 442EXECUTE st1 USING @a, @b; 443hypotenuse 4445 445EXECUTE st2; 446a 4474 4488 44911 45032 45180 452SET @c=3; 453EXECUTE st3 using @c; 454EXECUTE st4; 455a 4564 4578 45811 45914 46018 46121 46232 46342 46480 46590 466SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 467SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' ; 468EVENT_NAME statement/sql/truncate 469SQL_TEXT TRUNCATE TABLE performance_schema.events_statements_history_long 470OBJECT_TYPE NULL 471OBJECT_SCHEMA NULL 472OBJECT_NAME NULL 473DEALLOCATE PREPARE st1; 474DEALLOCATE PREPARE st2; 475DEALLOCATE PREPARE st3; 476DEALLOCATE PREPARE st4; 477DROP TABLE t1; 478SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 479SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' ; 480EVENT_NAME statement/sql/truncate 481SQL_TEXT TRUNCATE TABLE performance_schema.events_statements_history_long 482OBJECT_TYPE NULL 483OBJECT_SCHEMA NULL 484OBJECT_NAME NULL 485TRUNCATE TABLE performance_schema.events_statements_history_long ; 486UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'; 487PREPARE st FROM 'SELECT SUM(1000 + ?) AS total'; 488SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 489STATEMENT_NAME st 490SQL_TEXT SELECT SUM(1000 + ?) AS total 491COUNT_REPREPARE 0 492COUNT_EXECUTE 0 493SUM_ROWS_SENT 0 494SUM_SELECT_SCAN 0 495SUM_NO_INDEX_USED 0 496SET @d=100; 497EXECUTE st USING @d; 498total 4991100 500SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 501STATEMENT_NAME st 502SQL_TEXT SELECT SUM(1000 + ?) AS total 503COUNT_REPREPARE 0 504COUNT_EXECUTE 1 505SUM_ROWS_SENT 1 506SUM_SELECT_SCAN 0 507SUM_NO_INDEX_USED 0 508SET @d = @d + 100; 509EXECUTE st USING @d; 510total 5111200 512SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 513STATEMENT_NAME st 514SQL_TEXT SELECT SUM(1000 + ?) AS total 515COUNT_REPREPARE 0 516COUNT_EXECUTE 2 517SUM_ROWS_SENT 2 518SUM_SELECT_SCAN 0 519SUM_NO_INDEX_USED 0 520SET @d = @d + 100; 521EXECUTE st USING @d; 522total 5231300 524SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 525STATEMENT_NAME st 526SQL_TEXT SELECT SUM(1000 + ?) AS total 527COUNT_REPREPARE 0 528COUNT_EXECUTE 3 529SUM_ROWS_SENT 3 530SUM_SELECT_SCAN 0 531SUM_NO_INDEX_USED 0 532SET @d = @d + 100; 533EXECUTE st USING @d; 534total 5351400 536SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 537STATEMENT_NAME st 538SQL_TEXT SELECT SUM(1000 + ?) AS total 539COUNT_REPREPARE 0 540COUNT_EXECUTE 4 541SUM_ROWS_SENT 4 542SUM_SELECT_SCAN 0 543SUM_NO_INDEX_USED 0 544SET @d = @d + 100; 545EXECUTE st USING @d; 546total 5471500 548SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 549STATEMENT_NAME st 550SQL_TEXT SELECT SUM(1000 + ?) AS total 551COUNT_REPREPARE 0 552COUNT_EXECUTE 5 553SUM_ROWS_SENT 5 554SUM_SELECT_SCAN 0 555SUM_NO_INDEX_USED 0 556SET @d = @d + 100; 557EXECUTE st USING @d; 558total 5591600 560SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 561STATEMENT_NAME st 562SQL_TEXT SELECT SUM(1000 + ?) AS total 563COUNT_REPREPARE 0 564COUNT_EXECUTE 6 565SUM_ROWS_SENT 6 566SUM_SELECT_SCAN 0 567SUM_NO_INDEX_USED 0 568TRUNCATE TABLE performance_schema.prepared_statements_instances ; 569SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 570STATEMENT_NAME st 571SQL_TEXT SELECT SUM(1000 + ?) AS total 572COUNT_REPREPARE 0 573COUNT_EXECUTE 0 574SUM_ROWS_SENT 0 575SUM_SELECT_SCAN 0 576SUM_NO_INDEX_USED 0 577UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' 578WHERE NAME like "statement/sql/execute%"; 579SET @d=3274; 580EXECUTE st USING @d; 581total 5824274 583SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 584STATEMENT_NAME st 585SQL_TEXT SELECT SUM(1000 + ?) AS total 586COUNT_REPREPARE 0 587COUNT_EXECUTE 0 588SUM_ROWS_SENT 0 589SUM_SELECT_SCAN 0 590SUM_NO_INDEX_USED 0 591UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' 592 WHERE NAME like "statement/sql/prepare%"; 593DEALLOCATE PREPARE st; 594SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 595UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' 596 WHERE NAME like "statement/sql/prepare%"; 597UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' 598WHERE NAME like "statement/sql/execute%"; 599CREATE TABLE tab( 600Id INT, 601name CHAR(10), 602age INT 603); 604INSERT INTO tab VALUES(1,"Nakshatr",25),(2,"chanda",24),(3,"tejas",78); 605PREPARE st FROM 'SELECT * FROM tab'; 606SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 607STATEMENT_NAME st 608SQL_TEXT SELECT * FROM tab 609COUNT_REPREPARE 0 610COUNT_EXECUTE 0 611SUM_ROWS_SENT 0 612SUM_SELECT_SCAN 0 613SUM_NO_INDEX_USED 0 614EXECUTE st; 615Id name age 6161 Nakshatr 25 6172 chanda 24 6183 tejas 78 619SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 620STATEMENT_NAME st 621SQL_TEXT SELECT * FROM tab 622COUNT_REPREPARE 0 623COUNT_EXECUTE 1 624SUM_ROWS_SENT 3 625SUM_SELECT_SCAN 1 626SUM_NO_INDEX_USED 1 627ALTER TABLE tab DROP COLUMN age; 628EXECUTE st; 629Id name 6301 Nakshatr 6312 chanda 6323 tejas 633SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 634STATEMENT_NAME st 635SQL_TEXT SELECT * FROM tab 636COUNT_REPREPARE 1 637COUNT_EXECUTE 2 638SUM_ROWS_SENT 6 639SUM_SELECT_SCAN 2 640SUM_NO_INDEX_USED 2 641ALTER TABLE tab ADD COLUMN age INT NULL; 642EXECUTE st; 643Id name age 6441 Nakshatr NULL 6452 chanda NULL 6463 tejas NULL 647SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 648STATEMENT_NAME st 649SQL_TEXT SELECT * FROM tab 650COUNT_REPREPARE 2 651COUNT_EXECUTE 3 652SUM_ROWS_SENT 9 653SUM_SELECT_SCAN 3 654SUM_NO_INDEX_USED 3 655TRUNCATE TABLE performance_schema.prepared_statements_instances ; 656SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 657STATEMENT_NAME st 658SQL_TEXT SELECT * FROM tab 659COUNT_REPREPARE 0 660COUNT_EXECUTE 0 661SUM_ROWS_SENT 0 662SUM_SELECT_SCAN 0 663SUM_NO_INDEX_USED 0 664DEALLOCATE PREPARE st; 665SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ; 666TRUNCATE TABLE performance_schema.prepared_statements_instances ; 667TRUNCATE TABLE performance_schema.events_statements_history_long ; 668DROP TABLE tab; 669DROP DATABASE db; 670