Search by key in strings, real?

Question

MySQL

Maybe I didn't build the database correctly at all.
But I went this way) There is a table key - value
Example:
1. Last name - Ivanov
2. First name - Ivan
3. Last name - Petrov
4. First name - Peter
And now I have a search for a person by Last Name and First Name, naturally using LIKE.
THAT is for the surname its own field, for the name its own field.
If to use LIKE on one field. Everything is working. Naturally adding the second key he will not find results. Because the request is like this

SELECT `pid` FROM `promisers_data` as `pd` WHERE 1 and (`key`='family' and `value` LIKE '%Иванов%') and (`key`='name' and `value` LIKE '%Иван%')

What are the ways out of this situation?

Answer 1

SELECT
    pd.pid
  FROM
    promisers_data pd 
    INNER JOIN promisers_data pd2 ON pd.pid = pd2.pid
  WHERE 1
    AND (pd.key='family' AND pd.value LIKE '%Иванов%')
    AND (pd2.key='name' AND pd2.value LIKE '%Иван%')

Answer 2

How is it built for you??? I mean what kind of key, value? Why? The table should simply contain 3 (according to your example) fields, something like this:
id | family | name
1 Ivanov Ivan
2 Petrov Kolya
3 Ivanov Vasily
...
then the normal query will return the desired result

SELECT `id` 
FROM `promisers_data` as `pd` 
WHERE  (`family` LIKE 'Иванов') 
and (`name` LIKE 'Иван')

If you are looking for a surname, then you do not need to look for it in the middle of the surname field, that is, % before the query text is not needed, but in the mind and at the end it is superfluous if you are not looking for all similar names with a different ending. And it will work many times faster, and if you also create indexes for the fields, it will generally fly. Especially if you replace like with equality (=).