title: Slow search query with a one to many join
body: <p>My problem is a slow search query with a one-to-many relationship between the tables. My tables look like this.</p>
  
  <p>Table Assignment</p>
  
  <pre><code>CREATE TABLE `Assignment` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ProjectId` int(10) unsigned NOT NULL,
  `AssignmentTypeId` smallint(5) unsigned NOT NULL,
  `AssignmentNumber` varchar(30) NOT NULL,
  `AssignmentNumberExternal` varchar(50) DEFAULT NULL,
  `DateStart` datetime DEFAULT NULL,
  `DateEnd` datetime DEFAULT NULL,
  `DateDeadline` datetime DEFAULT NULL,
  `DateCreated` datetime DEFAULT NULL,
  `Deleted` datetime DEFAULT NULL,
  `Lat` double DEFAULT NULL,
  `Lon` double DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `idx_assignment_assignment_type_id` (`AssignmentTypeId`),
  KEY `idx_assignment_assignment_number` (`AssignmentNumber`),
  KEY `idx_assignment_assignment_number_external` 
  (`AssignmentNumberExternal`)
  ) ENGINE=InnoDB AUTO_INCREMENT=5280 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>Table ExtraFields</p>
  
  <pre><code>CREATE TABLE `ExtraFields` (
  `assignment_id` int(10) unsigned NOT NULL,
  `name` varchar(30) NOT NULL,
  `value` text,
  PRIMARY KEY (`assignment_id`,`name`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>My search query</p>
  
  <pre><code>SELECT 
  `Assignment`.`Id`, COL_5_72, COL_5_73, COL_5_74, COL_5_75, COL_5_76, 
  COL_5_77 FROM ( 
  SELECT 
  `Assignment`.`Id`, 
  `Assignment`.`AssignmentNumber` AS COL_5_72,
  `Assignment`.`AssignmentNumberExternal` AS COL_5_73 ,
  `AssignmentType`.`Name` AS COL_5_74,
  `Assignment`.`DateStart` AS COL_5_75,
  `Assignment`.`DateEnd` AS COL_5_76,
  `Assignment`.`DateDeadline` AS COL_5_77 FROM `Assignment`
  CASE WHEN `ExtraField`.`Name` = "WorkDistrict" THEN 
  `ExtraField`.`Value` end as COL_5_78 FROM `Assignment`
      LEFT JOIN `ExtraFields` as `ExtraField` on 
  `ExtraField`.`assignment_id` = `Assignment`.`Id`            
  WHERE `Assignment`.`Deleted` IS NULL -- Assignment should not be removed.
  AND (1=1) -- Add assignment filters.
  ) AS q1 
  GROUP BY `Assignment`.`Id` 
  HAVING 1 = 1 
  AND COL_5_78 LIKE '%Amsterdam East%'
  ORDER BY COL_5_72 ASC, COL_5_73 ASC;
  </code></pre>
  
  <p>When the table is only around 3500 records my query takes a couple of seconds to execute and return the results.</p>
  
  <p>What is a better way to search in the related data? Should I just add a JSON field to the Assignment table and use the MySQL 5.7 Json query features? Or did I made a mistake in designing my database?</p>