Hello, The problem has reappeared again after I had performed OPTIMIZE TABLE last month. I am now able to provide some more details.
The table in question is defined as follows (left out some columns for better readability): CREATE TABLE `t_taskperformed` ( `ID` varchar(41) COLLATE utf8mb4_unicode_ci NOT NULL, `CREATED_TIMESTAMP` datetime NOT NULL, `CREATED_USER` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `DELETED` bit(1) NOT NULL, `MODIFIED_TIMESTAMP` datetime NOT NULL, `MODIFIED_USER` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `TASK_PERFORMED_ENDED` datetime NOT NULL, `TASK_PERFORMED_STARTED` datetime NOT NULL, `FK_TASKPLANNEDBASE_ID` varchar(41) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`ID`), KEY `I_TASKPERFORMED_BASE_AND_CREATED_TIMESTAMP` (`FK_TASKPLANNEDBASE_ID`,`CREATED_TIMESTAMP`), KEY `I_TASKPERFORMED_TASK_PERFORMED_ENDED` (`TASK_PERFORMED_ENDED`), KEY `I_TASKPERFORMED_TASK_PERFORMED_STARTED` (`TASK_PERFORMED_STARTED`) USING BTREE, CONSTRAINT `FK_TASKPERFORMED_BASE_AND_SYNC_ORDER` FOREIGN KEY (`FK_TASKPLANNEDNBASE_ID`) REFERENCES `t_taskplannedbase` (`ID`), CONSTRAINT `FK_TASKPERFORMED_FK_TASKPERFORMED_GROUP_ID` FOREIGN KEY (`FK_TASKPERFORMED_GROUP_ID`) REFERENCES `t_taskperformed_group` (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci The following query returns 191 results which is not correct. As far as I can see, the result is missing records for random dates during the month (currently, some records for March, 8th). SELECT * from t_taskperformed taskperfor0_ inner join t_taskplannedbase taskplanne1_ on taskperfor0_.FK_TASKPLANNEDBASE_ID=taskplanne1_.ID inner join t_module ccmodule2_ on taskplanne1_.FK_MODULE_ID=ccmodule2_.ID where ccmodule2_.MODULE_KEY='MK1' and (taskplanne1_.FK_PERSON_ID in ( /* List of 1900 Person IDs */)) and taskperfor0_.TASK_PERFORMED_STARTED>='2024-03-01 00:00:00' and taskperfor0_.TASK_PERFORMED_STARTED<'2024-04-01 :00:00:00' and taskperfor0_.DELETED=0 order by taskperfor0_.TASK_PERFORMED_STARTED desc Explain Plan: id;select_type;table;type;possible_keys;key;key_len;ref;rows;Extra 1;SIMPLE;taskperfor0_;range;FK_TASKPERFORMED_BASE_AND_SYNC_ORDER,I_TASKPERFORMED_BASE_AND_CREATED_TIMESTAMP,I_TASKPERFORMED_TASK_PERFORMED_STARTED;I_TASKPERFORMED_TASK_PERFORMED_STARTED;5;\N;45398;Using where 1;SIMPLE;taskplanne1_;eq_ref;PRIMARY,FK_TASKPLANNEDBASE_FK_PERSON_ID,FK_TASKPLANNEDBASE_FK_MODULE_ID;PRIMARY;166;taskperfor0_.FK_TASKPLANNEDBASE_ID;1;Using where 1;SIMPLE;ccmodule2_;eq_ref;PRIMARY;PRIMARY;166;taskplanne1_.FK_MODULE_ID;1;Using where The following query, the only difference being the IGNORE INDEX statement, returns 210 results: SELECT * from t_taskperformed taskperfor0_ IGNORE INDEX (I_TASKPERFORMED_TASK_PERFORMED_STARTED) inner join t_taskplannedbase taskplanne1_ on taskperfor0_.FK_TASKPLANNEDBASE_ID=taskplanne1_.ID inner join t_module ccmodule2_ on taskplanne1_.FK_MODULE_ID=ccmodule2_.ID where ccmodule2_.MODULE_KEY='MK1' and (taskplanne1_.FK_PERSON_ID in ( /* List of 1900 Person IDs */)) and taskperfor0_.TASK_PERFORMED_STARTED>='2024-03-01 00:00:00' and taskperfor0_.TASK_PERFORMED_STARTED<'2024-04-01 :00:00:00' and taskperfor0_.DELETED=0 order by taskperfor0_.TASK_PERFORMED_STARTED desc As soon as I select for one specific person, this does not produce different results whether I ignore indexes or not. Explain plan does indicate that the index is not being used. SELECT * from t_taskperformed taskperfor0_ inner join t_taskplannedbase taskplanne1_ on taskperfor0_.FK_TASKPLANNEDBASE_ID=taskplanne1_.ID inner join t_module ccmodule2_ on taskplanne1_.FK_MODULE_ID=ccmodule2_.ID where ccmodule2_.MODULE_KEY='MK1' and (taskplanne1_.FK_PERSON_ID in (/* 1 specific person id */)) and taskperfor0_.TASK_PERFORMED_STARTED>='2024-03-01 00:00:00' and taskperfor0_.TASK_PERFORMED_STARTED<'2024-04-01 :00:00:00' and taskperfor0_.DELETED=0 order by taskperfor0_.TASK_PERFORMED_STARTED desc id;select_type;table;type;possible_keys;key;key_len;ref;rows;Extra 1;SIMPLE;taskplanne1_;ref;PRIMARY,FK_TASKPLANNEDBASE_FK_PERSON_ID,FK_TASKPLANNEDBASE_FK_MODULE_ID;FK_TASKPLANNEDBASE_FK_PERSON_ID;166;const;2;Using index condition; Using temporary; Using filesort 1;SIMPLE;ccmodule2_;eq_ref;PRIMARY;PRIMARY;166;taskplanne1_.FK_MODULE_ID;1;Using where 1;SIMPLE;taskperfor0_;ref;FK_TASKPERFORMED_BASE_AND_SYNC_ORDER,I_TASKPERFORMED_BASE_AND_CREATED_TIMESTAMP;FK_TASKPERFORMED_BASE_AND_SYNC_ORDER;166;taskplanne1_.ID;3;Using where So in summary: - The query produces different results depending on whether the particular index on a date column is being used. - The correct result is the one that contains more results. - OPTIMIZE TABLE solves the problem for a short period of time, i.e. both queries return the same result, but the problem reappears again, after a fairly short period. Regards, Jef _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
