1create table t0(a int); 2insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 3create table t1(a int); 4insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; 5create table t2 ( 6a int, 7b int, 8key (a) 9); 10insert into t2 select A.a*1000 + B.a, A.a*1000 + B.a from t0 A, t1 B; 11# 12# Try an UPDATE that uses filesort: 13# 14explain 15update t2 set b=b+1 order by b limit 5; 16id select_type table type possible_keys key key_len ref rows Extra 171 SIMPLE t2 ALL NULL NULL NULL NULL 10000 Using filesort 18explain format=json 19update t2 set b=b+1 order by b limit 5; 20EXPLAIN 21{ 22 "query_block": { 23 "select_id": 1, 24 "filesort": { 25 "table": { 26 "update": 1, 27 "table_name": "t2", 28 "access_type": "ALL", 29 "rows": 10000 30 } 31 } 32 } 33} 34analyze format=json 35update t2 set b=b+1 order by b limit 5; 36ANALYZE 37{ 38 "query_block": { 39 "select_id": 1, 40 "r_total_time_ms": "REPLACED", 41 "filesort": { 42 "r_loops": 1, 43 "r_total_time_ms": "REPLACED", 44 "r_limit": 5, 45 "r_used_priority_queue": true, 46 "r_output_rows": 6, 47 "r_sort_mode": "sort_key,rowid", 48 "table": { 49 "update": 1, 50 "table_name": "t2", 51 "access_type": "ALL", 52 "rows": 10000, 53 "r_rows": 10000, 54 "r_filtered": 100, 55 "r_total_time_ms": "REPLACED" 56 } 57 } 58 } 59} 60# 61# Try an UPDATE that uses buffering: 62# 63explain 64update t2 set a=a+1 where a<10; 65id select_type table type possible_keys key key_len ref rows Extra 661 SIMPLE t2 range a a 5 NULL 9 Using where; Using buffer 67explain format=json 68update t2 set a=a+1 where a<10; 69EXPLAIN 70{ 71 "query_block": { 72 "select_id": 1, 73 "buffer": { 74 "table": { 75 "update": 1, 76 "table_name": "t2", 77 "access_type": "range", 78 "possible_keys": ["a"], 79 "key": "a", 80 "key_length": "5", 81 "used_key_parts": ["a"], 82 "rows": 9, 83 "attached_condition": "t2.a < 10" 84 } 85 } 86 } 87} 88analyze format=json 89update t2 set a=a+1 where a<10; 90ANALYZE 91{ 92 "query_block": { 93 "select_id": 1, 94 "r_total_time_ms": "REPLACED", 95 "buffer": { 96 "table": { 97 "update": 1, 98 "table_name": "t2", 99 "access_type": "range", 100 "possible_keys": ["a"], 101 "key": "a", 102 "key_length": "5", 103 "used_key_parts": ["a"], 104 "rows": 9, 105 "r_rows": 10, 106 "r_filtered": 100, 107 "r_total_time_ms": "REPLACED", 108 "attached_condition": "t2.a < 10" 109 } 110 } 111 } 112} 113# 114# Try a DELETE that uses filesort: 115# 116explain 117delete from t2 order by b limit 5; 118id select_type table type possible_keys key key_len ref rows Extra 1191 SIMPLE t2 ALL NULL NULL NULL NULL 10000 Using filesort 120explain format=json 121delete from t2 order by b limit 5; 122EXPLAIN 123{ 124 "query_block": { 125 "select_id": 1, 126 "filesort": { 127 "table": { 128 "delete": 1, 129 "table_name": "t2", 130 "access_type": "ALL", 131 "rows": 10000 132 } 133 } 134 } 135} 136analyze format=json 137delete from t2 order by b limit 5; 138ANALYZE 139{ 140 "query_block": { 141 "select_id": 1, 142 "r_total_time_ms": "REPLACED", 143 "filesort": { 144 "r_loops": 1, 145 "r_total_time_ms": "REPLACED", 146 "r_used_priority_queue": false, 147 "r_output_rows": 10000, 148 "r_buffer_size": "REPLACED", 149 "r_sort_mode": "sort_key,rowid", 150 "table": { 151 "delete": 1, 152 "table_name": "t2", 153 "access_type": "ALL", 154 "rows": 10000, 155 "r_rows": 10000, 156 "r_filtered": 100, 157 "r_total_time_ms": "REPLACED" 158 } 159 } 160 } 161} 162# 163# Try a SELECT with QEP in form: filesort { tmp_table { join } } 164# 165explain 166select * from t0,t2 where t2.a=t0.a order by t2.b limit 4; 167id select_type table type possible_keys key key_len ref rows Extra 1681 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where; Using temporary; Using filesort 1691 SIMPLE t2 ref a a 5 test.t0.a 1 170explain format=json 171select * from t0,t2 where t2.a=t0.a order by t2.b limit 4; 172EXPLAIN 173{ 174 "query_block": { 175 "select_id": 1, 176 "filesort": { 177 "sort_key": "t2.b", 178 "temporary_table": { 179 "table": { 180 "table_name": "t0", 181 "access_type": "ALL", 182 "rows": 10, 183 "filtered": 100, 184 "attached_condition": "t0.a is not null" 185 }, 186 "table": { 187 "table_name": "t2", 188 "access_type": "ref", 189 "possible_keys": ["a"], 190 "key": "a", 191 "key_length": "5", 192 "used_key_parts": ["a"], 193 "ref": ["test.t0.a"], 194 "rows": 1, 195 "filtered": 100 196 } 197 } 198 } 199 } 200} 201analyze format=json 202select * from t0,t2 where t2.a=t0.a order by t2.b limit 4; 203ANALYZE 204{ 205 "query_block": { 206 "select_id": 1, 207 "r_loops": 1, 208 "r_total_time_ms": "REPLACED", 209 "filesort": { 210 "sort_key": "t2.b", 211 "r_loops": 1, 212 "r_total_time_ms": "REPLACED", 213 "r_limit": 4, 214 "r_used_priority_queue": true, 215 "r_output_rows": 4, 216 "r_sort_mode": "sort_key,rowid", 217 "temporary_table": { 218 "table": { 219 "table_name": "t0", 220 "access_type": "ALL", 221 "r_loops": 1, 222 "rows": 10, 223 "r_rows": 10, 224 "r_table_time_ms": "REPLACED", 225 "r_other_time_ms": "REPLACED", 226 "filtered": 100, 227 "r_filtered": 100, 228 "attached_condition": "t0.a is not null" 229 }, 230 "table": { 231 "table_name": "t2", 232 "access_type": "ref", 233 "possible_keys": ["a"], 234 "key": "a", 235 "key_length": "5", 236 "used_key_parts": ["a"], 237 "ref": ["test.t0.a"], 238 "r_loops": 10, 239 "rows": 1, 240 "r_rows": 0.4, 241 "r_table_time_ms": "REPLACED", 242 "r_other_time_ms": "REPLACED", 243 "filtered": 100, 244 "r_filtered": 100 245 } 246 } 247 } 248 } 249} 250# 251# Try a SELECT with QEP in form: join { filesort { table0 }, table2 } 252# 253explain 254select * from t0,t2 where t2.a=t0.a order by t0.a limit 4; 255id select_type table type possible_keys key key_len ref rows Extra 2561 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where; Using filesort 2571 SIMPLE t2 ref a a 5 test.t0.a 1 258explain format=json 259select * from t0,t2 where t2.a=t0.a order by t0.a limit 4; 260EXPLAIN 261{ 262 "query_block": { 263 "select_id": 1, 264 "read_sorted_file": { 265 "filesort": { 266 "sort_key": "t0.a", 267 "table": { 268 "table_name": "t0", 269 "access_type": "ALL", 270 "rows": 10, 271 "filtered": 100, 272 "attached_condition": "t0.a is not null" 273 } 274 } 275 }, 276 "table": { 277 "table_name": "t2", 278 "access_type": "ref", 279 "possible_keys": ["a"], 280 "key": "a", 281 "key_length": "5", 282 "used_key_parts": ["a"], 283 "ref": ["test.t0.a"], 284 "rows": 1, 285 "filtered": 100 286 } 287 } 288} 289analyze format=json 290select * from t0,t2 where t2.a=t0.a order by t0.a limit 4; 291ANALYZE 292{ 293 "query_block": { 294 "select_id": 1, 295 "r_loops": 1, 296 "r_total_time_ms": "REPLACED", 297 "read_sorted_file": { 298 "r_rows": 10, 299 "filesort": { 300 "sort_key": "t0.a", 301 "r_loops": 1, 302 "r_total_time_ms": "REPLACED", 303 "r_used_priority_queue": false, 304 "r_output_rows": 10, 305 "r_buffer_size": "REPLACED", 306 "r_sort_mode": "sort_key,addon_fields", 307 "table": { 308 "table_name": "t0", 309 "access_type": "ALL", 310 "r_loops": 1, 311 "rows": 10, 312 "r_rows": 10, 313 "r_table_time_ms": "REPLACED", 314 "r_other_time_ms": "REPLACED", 315 "filtered": 100, 316 "r_filtered": 100, 317 "attached_condition": "t0.a is not null" 318 } 319 } 320 }, 321 "table": { 322 "table_name": "t2", 323 "access_type": "ref", 324 "possible_keys": ["a"], 325 "key": "a", 326 "key_length": "5", 327 "used_key_parts": ["a"], 328 "ref": ["test.t0.a"], 329 "r_loops": 10, 330 "rows": 1, 331 "r_rows": 0.4, 332 "r_table_time_ms": "REPLACED", 333 "r_other_time_ms": "REPLACED", 334 "filtered": 100, 335 "r_filtered": 100 336 } 337 } 338} 339drop table t2; 340create table t2 ( 341a int, 342b int, 343c int 344); 345insert into t2 346select 347a.a+10*b.a+100*c.a, 348b.a+10*c.a, 349c.a 350from t0 a, t0 b, t0 c; 351analyze format=json 352select MAX(b) from t2 where mod(a,2)=0 group by c; 353ANALYZE 354{ 355 "query_block": { 356 "select_id": 1, 357 "r_loops": 1, 358 "r_total_time_ms": "REPLACED", 359 "filesort": { 360 "sort_key": "t2.c", 361 "r_loops": 1, 362 "r_total_time_ms": "REPLACED", 363 "r_used_priority_queue": false, 364 "r_output_rows": 10, 365 "r_buffer_size": "REPLACED", 366 "r_sort_mode": "sort_key,rowid", 367 "temporary_table": { 368 "table": { 369 "table_name": "t2", 370 "access_type": "ALL", 371 "r_loops": 1, 372 "rows": 1000, 373 "r_rows": 1000, 374 "r_table_time_ms": "REPLACED", 375 "r_other_time_ms": "REPLACED", 376 "filtered": 100, 377 "r_filtered": 50, 378 "attached_condition": "t2.a MOD 2 = 0" 379 } 380 } 381 } 382 } 383} 384drop table t2; 385# 386# MDEV-8282: crash in filesort() with simple ordered delete 387# 388create table t3(a int) engine=innodb; 389delete from t3 order by a; 390# EXPLAIN thinks it will use delete_all_rows(): 391explain 392delete from t3 order by a; 393id select_type table type possible_keys key key_len ref rows Extra 3941 SIMPLE NULL NULL NULL NULL NULL NULL 1 Deleting all rows 395# ANALYZE shows that delete_all_rows() didn't work and we deleted rows 396# one-by-one: 397analyze 398delete from t3 order by a; 399id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 4001 SIMPLE t3 ALL NULL NULL NULL NULL 1 0.00 100.00 100.00 Using filesort 401drop table t3; 402# 403# A test for duplicate_removal() 404# 405create table t3 (a int, b int); 406insert into t3 select a, 123 from t0; 407analyze format=json 408select distinct max(t3.b) Q from t0, t3 where t0.a=t3.a group by t0.a order by null; 409ANALYZE 410{ 411 "query_block": { 412 "select_id": 1, 413 "r_loops": 1, 414 "r_total_time_ms": "REPLACED", 415 "duplicate_removal": { 416 "temporary_table": { 417 "table": { 418 "table_name": "t0", 419 "access_type": "ALL", 420 "r_loops": 1, 421 "rows": 10, 422 "r_rows": 10, 423 "r_table_time_ms": "REPLACED", 424 "r_other_time_ms": "REPLACED", 425 "filtered": 100, 426 "r_filtered": 100 427 }, 428 "block-nl-join": { 429 "table": { 430 "table_name": "t3", 431 "access_type": "ALL", 432 "r_loops": 1, 433 "rows": 10, 434 "r_rows": 10, 435 "r_table_time_ms": "REPLACED", 436 "r_other_time_ms": "REPLACED", 437 "filtered": 100, 438 "r_filtered": 100 439 }, 440 "buffer_type": "flat", 441 "buffer_size": "65", 442 "join_type": "BNL", 443 "attached_condition": "t3.a = t0.a", 444 "r_filtered": 10 445 } 446 } 447 } 448 } 449} 450# 451# A query with two filesort calls: 452# - first is needed to do group-by-group grouping to calculate COUNT(DISTINCT) 453# - the second is need to produce ORDER BY. 454# (see MDEV-7836 for description of the query plan) 455create table t5 (a int , b int) ; 456create table t6 like t5 ; 457create table t7 like t5 ; 458insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7), 459(2, -1), (3, 10); 460insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1); 461insert into t7 values (3, 3), (2, 2), (1, 1); 462# TODO: This ANALYZE output doesn't make it clear what is used for what. 463analyze format=json 464select count(distinct t5.b) as sum from t5, t6 465where t5.a=t6.a and t6.b > 0 and t5.a <= 5 466group by t5.a order by sum limit 1; 467ANALYZE 468{ 469 "query_block": { 470 "select_id": 1, 471 "r_loops": 1, 472 "r_total_time_ms": "REPLACED", 473 "filesort": { 474 "sort_key": "count(distinct t5.b)", 475 "r_loops": 1, 476 "r_total_time_ms": "REPLACED", 477 "r_limit": 1, 478 "r_used_priority_queue": true, 479 "r_output_rows": 2, 480 "r_sort_mode": "sort_key,rowid", 481 "temporary_table": { 482 "filesort": { 483 "sort_key": "t5.a", 484 "r_loops": 1, 485 "r_total_time_ms": "REPLACED", 486 "r_used_priority_queue": false, 487 "r_output_rows": 6, 488 "r_buffer_size": "REPLACED", 489 "r_sort_mode": "sort_key,rowid", 490 "temporary_table": { 491 "table": { 492 "table_name": "t6", 493 "access_type": "ALL", 494 "r_loops": 1, 495 "rows": 5, 496 "r_rows": 5, 497 "r_table_time_ms": "REPLACED", 498 "r_other_time_ms": "REPLACED", 499 "filtered": 100, 500 "r_filtered": 80, 501 "attached_condition": "t6.b > 0 and t6.a <= 5" 502 }, 503 "block-nl-join": { 504 "table": { 505 "table_name": "t5", 506 "access_type": "ALL", 507 "r_loops": 1, 508 "rows": 7, 509 "r_rows": 7, 510 "r_table_time_ms": "REPLACED", 511 "r_other_time_ms": "REPLACED", 512 "filtered": 100, 513 "r_filtered": 100 514 }, 515 "buffer_type": "flat", 516 "buffer_size": "119", 517 "join_type": "BNL", 518 "attached_condition": "t5.a = t6.a", 519 "r_filtered": 21.42857143 520 } 521 } 522 } 523 } 524 } 525 } 526} 527explain format=json 528select count(distinct t5.b) as sum from t5, t6 529where t5.a=t6.a and t6.b > 0 and t5.a <= 5 530group by t5.a order by sum limit 1; 531EXPLAIN 532{ 533 "query_block": { 534 "select_id": 1, 535 "filesort": { 536 "sort_key": "count(distinct t5.b)", 537 "temporary_table": { 538 "filesort": { 539 "sort_key": "t5.a", 540 "temporary_table": { 541 "table": { 542 "table_name": "t6", 543 "access_type": "ALL", 544 "rows": 5, 545 "filtered": 100, 546 "attached_condition": "t6.b > 0 and t6.a <= 5" 547 }, 548 "block-nl-join": { 549 "table": { 550 "table_name": "t5", 551 "access_type": "ALL", 552 "rows": 7, 553 "filtered": 100 554 }, 555 "buffer_type": "flat", 556 "buffer_size": "119", 557 "join_type": "BNL", 558 "attached_condition": "t5.a = t6.a" 559 } 560 } 561 } 562 } 563 } 564 } 565} 566drop table t5,t6,t7; 567drop table t3; 568# 569# Tabular ANALYZE must get its data from execution tracker (and not from 570# the query plan) 571# 572CREATE TABLE t2( 573col1 int, 574col2 int, 575UNIQUE INDEX idx (col1, col2)) engine=myisam; 576INSERT INTO t2(col1, col2) VALUES 577(1,20),(2,19),(3,18),(4,17),(5,16),(6,15),(7,14),(8,13),(9,12),(10,11), 578(11,10),(12,9),(13,8),(14,7),(15,6),(16,5),(17,4),(18,3),(19,2),(20,1); 579flush status; 580explain 581select col1 f1, col2 f2, col1 f3 from t2 group by f1; 582id select_type table type possible_keys key key_len ref rows Extra 5831 SIMPLE t2 range NULL idx 5 NULL 7 Using index for group-by 584analyze 585select col1 f1, col2 f2, col1 f3 from t2 group by f1; 586id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 5871 SIMPLE t2 range NULL idx 5 NULL 7 20.00 100.00 100.00 Using index for group-by 588analyze format=json 589select col1 f1, col2 f2, col1 f3 from t2 group by f1; 590ANALYZE 591{ 592 "query_block": { 593 "select_id": 1, 594 "r_loops": 1, 595 "r_total_time_ms": "REPLACED", 596 "table": { 597 "table_name": "t2", 598 "access_type": "range", 599 "key": "idx", 600 "key_length": "5", 601 "used_key_parts": ["col1"], 602 "r_loops": 1, 603 "rows": 7, 604 "r_rows": 20, 605 "r_table_time_ms": "REPLACED", 606 "r_other_time_ms": "REPLACED", 607 "filtered": 100, 608 "r_filtered": 100, 609 "using_index_for_group_by": true 610 } 611 } 612} 613drop table t2; 614drop table t0,t1; 615