1

I have mysql 5.5 and table item have around 1-3 milions rows.

CREATE TABLE `item` (
  `Id_item` int(11) NOT NULL DEFAULT '0',
  `item_Kod` varchar(15) DEFAULT NULL,
  `Date1` datetime DEFAULT NULL,
  `Date2` datetime DEFAULT NULL,
  `Date3` datetime DEFAULT NULL,
  `Person1` int(11) DEFAULT NULL,
  `Person2` int(11) DEFAULT NULL,
  `Person3` int(11) DEFAULT NULL, 
  `Deleted` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Id_item`),
  UNIQUE KEY `Index_item_Kod` (`item_Kod`),  
  KEY `Index_Date1` (`Date1`),
  KEY `FK_item_Person2` (`Person2`),
  KEY `Person1` (`Person1`),
  KEY `FK_item_Person3` (`Person3`),
  CONSTRAINT `FK_item_Person2` FOREIGN KEY (`Person2`) REFERENCES `persons` (`Id_Person`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_item_Person1` FOREIGN KEY (`Person1`) REFERENCES `persons` (`Id_Person`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=cp1250;


CREATE TABLE `persons` (
  `Id_Person` int(11) NOT NULL DEFAULT '0',
  `Code` int(11) DEFAULT '0',
  `Title` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`Id_Person`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1250;

and I want optmalize this query...result is about 100-500 rows and query run about 3-4seconds

SELECT 
  * 
FROM 
  `item` AS `item` 
WHERE 
  (
    `item`.`Deleted` = 0 
    AND (
      (
        `item`.`Date1` >= '2020-01-20 00:00:00' 
        AND `item`.`Date1` <= '2021-01-29 23:59:59'
      ) 
      OR (
        `item`.`Date3` >= '2020-01-20 00:00:00' 
        AND `item`.`Date3` <= '2021-01-29 23:59:59'
      ) 
      OR (
        `item`.`Date2` >= '2020-01-20 00:00:00' 
        AND `item`.`Date2` <= '2021-01-29 23:59:59'
      )
    ) 
    AND (
      `item`.`Person1` IN (15, 167, 192, 198, 398, 662) 
      OR `item`.`Person2` IN (15, 167, 192, 198, 398, 662) 
      OR `item`.`Person3` IN (15, 167, 192, 198, 398, 662) 
      OR (
        `item`.`Person1` IS NULL 
        AND `item`.`Person3` IS NULL
      )
    )
  ) 
ORDER BY 
  `item`.`Date1` DESC

Explain return:

id:1
select_type: SIMPLE
table: item
partitions: NULL
type: ALL
possible_keys: Index_Date1,FK_item_Person2,Person1,FK_item_Person3
key: null
key_len: NULL
ref: null
rows: 1198601
extra: Using where; Using filesort

I tried create composition index for Date1-3 or for Person1-3 but with the index query run around 30 seconds.

How I can optimalize query to run fast as possible? Ideal max.5 seconds.

2
  • 1
    Your data isn't normalized and an index isn't going to fix that. Unless the meanings of Date 1-3 and Person 1-3 existing beyond a poor enumeration of a 1 to many relationship, you should create two tables: itemDate, itemPerson and start there. – bbaird Jan 29 at 17:07
  • Won't you need 4 dates or 4 persons tomorrow? – Rick James Jan 30 at 0:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.