Bug #617 | Queries mysteriously seem to stop using an Index / slow performance | ||
---|---|---|---|
Submitted: | 6 Jun 2003 18:27 | Modified: | 9 Jun 2003 5:41 |
Reporter: | [ name withheld ] | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 3.23.52, 4.0.12 | OS: | Linux (Linux/Redhat 8.0) |
Assigned to: | CPU Architecture: | Any |
[6 Jun 2003 18:27]
[ name withheld ]
[9 Jun 2003 5:41]
Alexander Keremidarski
Sorry, but the bug system is not the appropriate forum for asking support questions. Your problem is not the result of a bug. For a list of more appropriate places to ask for help using MySQL products, please visit http://www.mysql.com/support/ Thank you for your interest in MySQL. What you describe looks more like case for MySQL and Server tunning and application optimization. If you can provide repeatable test case we can treat it as a bug, otherwise we don't have much choice, but to rule it out of Bugs Database. No matter if it is bug or not please try to repeat it with MySQL from our binary distribution - RPM as you are with RedHat.
[29 Sep 2010 2:26]
Jason Garrett
I've seen this issue roughly 4 times this year, often over 4-8 weeks between occurances. Each time, the solution is to stop and start the Windows Service. I've upgraded the MySQL Server twice to see if it resolved the issue, but it has reoccured since, the most recent was today. I am using 5.1.48-community-log, running on Windows Server 2k3 Advanced. The query will run correctly, and return in 2-3 seconds typically. At some seemingly random time which appears to be related to the uptime and use of the server, we get a Support call from a user saying that the report is failing to complete. When we check the query plan (using EXPLAIN), it appears to not be using the index correctly. We see that a possible key exists, but it is not used, thus making the query very slow! An snippet example of an EXPLAIN is below. Note the 'key' is empty, and 'rows' is very large, being the number of rows in the table. id: 1 select_type: SIMPLE table: dockets1 type: eq_ref possible_keys: PRIMARY key: key_len: 4 ref: slim.archivedlogs1.docketID rows: 1626160 Extra: Using where Below shows the normal EXPLAIN for the query, as it is for weeks prior to the event, and immediately after the MySQL service is restarted. Looks like this issue is more serious than just poor query writing. explain SELECT `voyagelots1`.`branchID`, `voyages1`.`voyageCode`, IF(`voyagelotting1`.`lot` IS NOT NULL, `voyagelotting1`.`lot`, `voyageloadouts1`.`lot`) AS lot, IF(`voyagelotting1`.`lot` IS NOT NULL, `voyageloadouts1`.`lot`, NULL) AS containerNumber, `voyagelots1`.`dischargePort`, `voyagelots1`.`consignee`, `voyageloadouts1`.`hatch`, `voyageloadouts1`.`place`, `dockets1`.`docketNumber`, `dockets1`.`forestSource`, `dockets1`.`forestCompartment`, `dockets1`.`forestStand`, `dockets1`.`loggingOrganisation`, `voyagelots1`.`comments`, `voyagelots1`.`commentsOperator`, `voyagelots1`.`operatorRef1`, `voyagelots1`.`operatorRef2`, `specifications1`.`species`, `specifications1`.`grade`, `specifications1`.`_length`, sum(`archivedlogs1`.`volume`) AS `totalVolume`, sum(`archivedlogs1`.`weight`) AS `totalWeight`, count(*) AS `totalCount` FROM ( ( ( ( ( `slim`.`archivedlogs` `archivedlogs1` INNER JOIN `slim`.`specifications` `specifications1` ON `archivedlogs1`.`specificationID`=`specifications1`.`specificationID` ) INNER JOIN `slim`.`dockets` `dockets1` ON `archivedlogs1`.`docketID`=`dockets1`.`docketID` ) INNER JOIN `slim`.`voyageloadouts` `voyageloadouts1` ON `archivedlogs1`.`voyageLoadOutID`=`voyageloadouts1`.`voyageLoadOutID` ) INNER JOIN `slim`.`voyagelots` `voyagelots1` ON (`voyageloadouts1`.`voyageID`=`voyagelots1`.`voyageID`) AND (`voyageloadouts1`.`branchID`=`voyagelots1`.`branchID`) AND (`voyageloadouts1`.`lot`=`voyagelots1`.`lot`) ) INNER JOIN `slim`.`voyages` `voyages1` ON `voyagelots1`.`voyageID`=`voyages1`.`voyageID` ) LEFT OUTER JOIN ( `slim`.`voyagelotting` `voyagelotting1` INNER JOIN `slim`.`voyagelotcontainersassign` `voyagelotcontainersassign1` ON `voyagelotting1`.`voyageLottingID`=`voyagelotcontainersassign1`.`voyageLottingID` ) ON `voyagelots1`.`voyageLotID`=`voyagelotcontainersassign1`.`voyageLotID` WHERE `voyagelots1`.`branchID`=4 AND `voyages1`.`voyageCode`='10316' AND `specifications1`.`supplierCode`='RNZ' GROUP BY branchID, voyageCode, lot, species, grade, containerNumber, docketNumber, _length, hatch, place\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: voyages1 type: const possible_keys: PRIMARY,idx_voyage_voyageCode key: idx_voyage_voyageCode key_len: 53 ref: const rows: 1 Extra: Using index; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: voyagelots1 type: ref possible_keys: idx_voyageLots_loadOut key: idx_voyageLots_loadOut key_len: 8 ref: const,const rows: 22 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: voyageloadouts1 type: ref possible_keys: PRIMARY,idx_voyageLoadOuts_loadOut key: idx_voyageLoadOuts_loadOut key_len: 40 ref: const,const,slim.voyagelots1.lot rows: 1 Extra: Using index *************************** 4. row *************************** id: 1 select_type: SIMPLE table: voyagelotting1 type: index possible_keys: PRIMARY key: idx_voyageLotting_lot key_len: 41 ref: NULL rows: 373 Extra: Using index *************************** 5. row *************************** id: 1 select_type: SIMPLE table: voyagelotcontainersassign1 type: ref possible_keys: idx_ids key: idx_ids key_len: 10 ref: slim.voyagelotting1.voyageLottingID,slim.voyagelots1.voyageLotID rows: 1 Extra: Using index *************************** 6. row *************************** id: 1 select_type: SIMPLE table: archivedlogs1 type: ref possible_keys: idx_archivedlogs_voyageLoadOutID,idx_archivedlogs_docketID,idx_specificationid key: idx_archivedlogs_voyageLoadOutID key_len: 5 ref: slim.voyageloadouts1.voyageLoadOutID rows: 663 Extra: Using where *************************** 7. row *************************** id: 1 select_type: SIMPLE table: dockets1 type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: slim.archivedlogs1.docketID rows: 1 Extra: Using where *************************** 8. row *************************** id: 1 select_type: SIMPLE table: specifications1 type: eq_ref possible_keys: PRIMARY,idx_specifications_specification key: PRIMARY key_len: 4 ref: slim.archivedlogs1.specificationID rows: 1 Extra: Using where 8 rows in set (0.00 sec)