An install with a large overall datasize saw MySQL estimate the cardinality of commitID in repository_pathchange as 18, when the real cardinality was probably more like 18 million.
It would be desirable to prevent (or at least detect) these errors, but the best pathway forward isn't clear.
Original Report
We're seeing this query:
# 2016-03-04T19:17:45 KILL 19676457 (Query 42 sec) SELECT commitID, pathID FROM `repository_pathchange` WHERE commitID IN (1474828, 1474829, 1474830, 1474831, 1474832, 1474833, 1474834, 1474836, 1474837, 1474838, 1474839, 1474840, 1474841, 1474842, 1474843, 1474844, 1474845, 1474847, 1474848, 1474849, 1474850, 1474851, 1474852, 1474853, 1474854, 1474855, 1474856, 1474857, 1474858, 1474859, 1474860, 1474862, 1474863, 1474864, 1474865, 1474866, 1474867, 1474868, 1474870, 1474871, 1474872, 1474873, 1474874, 1474875, 1474876, 1474877, 1474878, 1474879, 1474880, 1474881, 1474882, 1474883, 1474884, 1474885, 1474886, 1474887, 1474889, 1474891, 1474892, 1474893, 1474894, 1474895, 1474896, 1474897, 1474898, 1474899, 1474900, 1474901, 1474902, 1474903, 1474904, 1474905, 1474906, 1474907, 1474908, 1474909, 1474910, 1474911, 1474912, 1474914, 1474915, 1474916, 1474917, 1474918, 1474919, 1474920, 1474921, 1474922, 1474923, 1474924, 1474925, 1474926, 1474928, 1474929, 1474930, 1474931, 1474932, 1474933, 1474934, 1474935, 1474936, 1474937, 1474938, 1474939, 1474940, 1474941, 1474942, 1474943, 1474944, 1474945, 1474946, 1474947, 1474948, 1474949, 1474950, 1474951, 1474952, 1474953, 1474955, 1474956, 1474957, 1474958, 1474959, 1474960, 1474961, 1474962, 1474963, 1474964, 1474965, 1474967, 1474968, 1474969, 1474970, 1474971, 1474972, 1474973, 1474974, 1474975, 1474976, 1474977, 1474978, 1474979, 1474980, 1474981, 1474982, 1474983, 1474984, 1474985, 1474986, 1474987, 1474988, 1474989, 1474990, 1474992, 1474993, 1474994, 1474995, 1474996, 1474997, 1474998, 1474999, 1475000, 1475001, 1475002, 1475004, 1475005, 1475006, 1475007, 1475008, 1475009, 1475010, 1475011, 1475013, 1475014, 1475015, 1475016, 1475017, 1475018, 1475019, 1475020, 1475021, 1475022, 1475023, 1475024, 1475025, 1475026, 1475027, 1475028, 1475029, 1475030, 1475031, 1475032, 1475034, 1475035, 1475036, 1475037, 1475038, 1475039, 1475041, 1475042, 1475043, 1475044, 1475045, 1475046, 1475047, 1475048, 1475049, 1475050, 1475052, 1475053, 1475054, 1475055, 1475056, 1475057, 1475058, 1475059, 1475060, 1475061, 1475062, 1475063, 1475064, 1475065, 1475066, 1475067, 1475068, 1475069, 1475070, 1475072, 1475073, 1475074, 1475075, 1475076, 1475077, 1475078, 1475079, 1475080, 1475081, 1475082, 1475083, 1475084, 1475085, 1475086, 1475087, 1475088, 1475089, 1475091, 1475092, 1475093, 1475094, 1475095, 1475096, 1475097, 1475098, 1475099, 1475100, 1475101, 1475102, 1475104, 1475105, 1475106, 1475107, 1475108, 1475109, 1475110, 1475111, 1475112, 1475113, 1475114, 1475115, 1475116, 1475118, 1475119, 1475120, 1475121, 1475122, 1475123, 1475124, 1475125, 1475126, 1475127, 1475128, 1475129, 1475130, 1475131, 1475132, 1475134, 1475135, 1475136, 1475137, 1475138, 1475139, 1475140, 1475141, 1475142, 1475143, 1475144, 1475145, 1475146, 1475147, 1475148, 1475149, 1475150, 1475151, 1475152, 1475153, 1475154, 1475155, 1475156, 1475158, 1475159, 1475160, 1475161, 1475162, 1475163, 1475164, 1475165, 1475166, 1475167, 1475168, 1475169, 1475170, 1475171, 1475172, 1475173, 1475175, 1475180, 1475181, 1475182, 1475184, 1475185, 1475186, 1475187, 1475188, 1475189, 1475190, 1475191, 1475192, 1475193, 1475194, 1475195, 1475196, 1475197, 1475198, 1475199, 1475200, 1475201, 1475202, 1475204, 1475205, 1475206, 1475207, 1475208, 1475209, 1475210, 1475213, 1475214, 1475215, 1475216, 1475217, 1475218, 1475219, 1475220, 1475221, 1475222, 1475223, 1475224, 1475225, 1475226, 1475227, 1475229, 1475230, 1475231, 1475232, 1475233, 1475234, 1475235, 1475236, 1475237, 1475239, 1475240, 1475241, 1475242, 1475243, 1475244, 1475246, 1475248, 1475249, 1475250, 1475251, 1475252, 1475253, 1475254, 1475255, 1475256, 1475257, 1475258, 1475260, 1475261, 1475262, 1475263, 1475265, 1475266, 1475267, 1475268, 1475269, 1475270, 1475271, 1475272, 1475273) AND (isDirect = 1 OR changeType = 10)
take 2+ minutes.
If we add an index:
CREATE TABLE `test_rp` ( `repositoryID` int(10) unsigned NOT NULL, `pathID` int(10) unsigned NOT NULL, `commitID` int(10) unsigned NOT NULL, `targetPathID` int(10) unsigned DEFAULT NULL, `targetCommitID` int(10) unsigned DEFAULT NULL, `changeType` int(10) unsigned NOT NULL, `fileType` int(10) unsigned NOT NULL, `isDirect` tinyint(1) NOT NULL, `commitSequence` int(10) unsigned NOT NULL, PRIMARY KEY (`commitID`,`pathID`), KEY `repositoryID` (`repositoryID`,`pathID`,`commitSequence`), KEY `commitID` (`commitID`,`isDirect`,`changeType`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
5139 rows in set (2 min 2.24 sec)
vs
5139 rows in set (0.02 sec)
then it takes a reasonable amount of time