Optimize query for joining key/value pair tables in MySQL

Question

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 :)

The topics I have read and tried:

Need Help for Optimizing MySQL query with few joins

MySQL select from key/value pairs table

Inner Joining two tables based on all "Key/Value" Pairs matching exactly

Merging the result of two INNER JOINS of the same table into one single temporary table in SQL Server

Here is the query I have:

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`

The tables look as follows. The user table:

id | name   | email
--------------------------------
1  | Myself | myself@example.com

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.

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

The item_id field in the fields_values table matches the id field in the users table.

My goal is the following:

The user table has about 5500 entries in it and the field_values 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.

The filters apply on the value field fo the fields_values table. So I can get all users living in Belgium for example.

For each filter I add another innerJoin and so the query becomes like this:

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`

Even without the filters applied the list takes like 2,5 minutes to load and EXPLAIN shows me that it retrieved 73979 records, which is approximately the total of 5500 * 13.

Any hints/tips/improvements are welcome. Thank you.

Edit:

users table

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';

fields_values table

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

Hereby the explain:

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)"