title: Optimize query for joining key/value pair tables in MySQL
body: <p>There are several topics I have tried and my own trial and errors but I can't get the following query to speed up. The query works fine but is just horribly slow :)</p>
  
  <p>The topics I have read and tried:</p>
  
  <p><a href="https://stackoverflow.com/questions/28697569/need-help-for-optimizing-mysql-query-with-few-joins">Need Help for Optimizing MySQL query with few joins</a></p>
  
  <p><a href="https://stackoverflow.com/questions/48532673/mysql-select-from-key-value-pairs-table">MySQL select from key/value pairs table</a></p>
  
  <p><a href="https://stackoverflow.com/questions/2367651/inner-joining-two-tables-based-on-all-key-value-pairs-matching-exactly">Inner Joining two tables based on all &quot;Key/Value&quot; Pairs matching exactly</a></p>
  
  <p><a href="https://stackoverflow.com/questions/45279797/merging-the-result-of-two-inner-joins-of-the-same-table-into-one-single-temporar">Merging the result of two INNER JOINS of the same table into one single temporary table in SQL Server</a></p>
  
  <p>Here is the query I have:</p>
  
  <pre><code>SELECT `users`.`id`,`users`.`name`,`users`.`email`
  FROM `test_users` AS `users`
  LEFT JOIN `test_fields_values` AS `fields_values` ON
  `fields_values`.`item_id` = `users`.`id`
  INNER JOIN `test_fields_values` AS `fields_values_lastname` ON        
  `users`.`id` = `fields_values_lastname`.`item_id`
  WHERE 
  (`fields_values`.`field_id` IS NOT NULL) AND 
  (`fields_values_lastname`.`field_id` = 13)
  GROUP BY `users`.`id`
  ORDER BY `fields_values_lastname`.`value`
  </code></pre>
  
  <p>The tables look as follows. 
  The user table:</p>
  
  <pre><code>id | name   | email
  --------------------------------
  1  | Myself | myself@example.com
  </code></pre>
  
  <p>The fields_values table, instead of the actual value I put in the name of the field to make it easier to understand I hope.</p>
  
  <pre><code>field_id | item_id | value
  ------------------------------------------------------------
  4        | 1       | Address
  5        | 1       | Phone
  6        | 1       | City
  7        | 1       | Zipcode
  8        | 1       | Email
  9        | 1       | Country
  10       | 1       | Lastname
  11       | 1       | H
  12       | 1       | I
  13       | 1       | J
  14       | 1       | K
  15       | 1       | L
  16       | 1       | M
  17       | 1       | N
  </code></pre>
  
  <p>The <code>item_id</code> field in the <code>fields_values</code> table matches the <code>id</code> field in the <code>users</code> table.</p>
  
  <p>My goal is the following:</p>
  
  <p>The <code>user</code> table has about 5500 entries in it and the <code>field_values</code> table has 13 entries for each user in it. What I am currently doing is getting a list of users ordered by lastname and show them in a list. This list has a filter, so the user list can be filtered on Country/City and a general search. </p>
  
  <p>The filters apply on the <code>value</code> field fo the <code>fields_values</code> table. So I can get all users living in Belgium for example.</p>
  
  <p>For each filter I add another innerJoin and so the query becomes like this:</p>
  
  <pre><code>SELECT `users`.`id`,`users`.`name`,`users`.`email`
  FROM `test_users` AS `users`
  LEFT JOIN `test_fields_values` AS `fields_values` ON
  `fields_values`.`item_id` = `users`.`id`
  INNER JOIN `test_fields_values` AS `fields_values_lastname` ON        
  `users`.`id` = `fields_values_lastname`.`item_id`
  INNER JOIN `test_fields_values` AS `fields_values_country` ON  
  `users`.`id` = `fields_values_country`.`item_id`
  WHERE 
  (
  (`fields_values`.`field_id` IS NOT NULL) AND 
  (`fields_values_lastname`.`field_id` = 13)) AND 
  (`fields_values_country`.`value` = 'belgium')
  GROUP BY `users`.`id`
  ORDER BY `fields_values_lastname`.`value`
  </code></pre>
  
  <p>Even without the filters applied the list takes like 2,5 minutes to load and <code>EXPLAIN</code> shows me that it retrieved 73979 records, which is approximately the total of 5500 * 13.</p>
  
  <p>Any hints/tips/improvements are welcome. Thank you.</p>
  
  <p>Edit:</p>
  
  <p><code>users</code> table</p>
  
  <pre><code>CREATE TABLE `test_users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL DEFAULT '',
  `username` VARCHAR(150) NOT NULL DEFAULT '',
  `email` VARCHAR(100) NOT NULL DEFAULT '',
  `password` VARCHAR(100) NOT NULL DEFAULT '',
  `usertype` VARCHAR(25) NOT NULL DEFAULT '',
  `block` TINYINT(4) NOT NULL DEFAULT '0',
  `sendEmail` TINYINT(4) NULL DEFAULT '0',
  `registerDate` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lastvisitDate` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `activation` VARCHAR(100) NOT NULL DEFAULT '',
  `params` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `usertype` (`usertype`),
  INDEX `idx_name` (`name`),
  INDEX `idx_block` (`block`),
  INDEX `username` (`username`),
  INDEX `email` (`email`)
  )
  COLLATE='utf8_general_ci';
  </code></pre>
  
  <p><code>fields_values</code> table</p>
  
  <pre><code>CREATE TABLE `test_fields_values` (
  `field_id` INT(10) UNSIGNED NOT NULL,
  `item_id` VARCHAR(255) NOT NULL COMMENT 'Allow references to items which have strings as ids, eg. none db systems.' COLLATE 'utf8mb4_unicode_ci',
  `value` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
  INDEX `idx_field_id` (`field_id`),
  INDEX `idx_item_id` (`item_id`(191))
  )
  COLLATE='utf8mb4_unicode_ci'
  ENGINE=InnoDB
  </code></pre>
  
  <p>Hereby the explain:</p>
  
  <pre><code>id  | select_type | table                    | type     | possible_keys              | key            | key_len | ref                                        | rows    | extra
  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  "1" | "SIMPLE"    | "fields_values_lastname" | "ref"    | "idx_field_id,idx_item_id" | "idx_field_id" | "4"     | "const"                                    | "3513"  | "Using temporary; Using filesort"
  "1" | "SIMPLE"    | "users"                  | "eq_ref" | "PRIMARY"                  | "PRIMARY"      | "4"     | "cms_inter.fields_values_lastname.item_id" | "1"     | "Using where"
  "1" | "SIMPLE"    | "fields_values"          | "ALL"    | "id_id,idx_item_id"        | NULL           | NULL    | NULL                                       | "73979" | "Using where; Using join buffer (flat, BNL join)"
  </code></pre>