MYSQL Multiple table optimization for advanced search I have a users table where there is the basic information of each user:

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `account_type` varchar(10) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(500) NOT NULL,
  `registration_date` timestamp NULL DEFAULT NULL,
  `account_status` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8


On the other hand, I have a `user_data' table in which we put every other data:

CREATE TABLE `user_data` (
  `data_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `value` text,
  PRIMARY KEY (`data_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1


Basically, the data is stored as a name => value

I fetch the data in the following fashion:

SELECT email, ud_desc.value as description, ud_lang.value as language
    FROM users
        JOIN user_data as ud_desc
        ON ud_desc.user_id = users.user_id
            AND ud_desc.name = 'description'
        JOIN user_data as ud_lang
        ON ud_lang.user_id = users.user_id
            AND ud_lang.name = 'language'


I made that second table this way because there is a ton of different data for each user, certain depending on their users.account_type.
Also my client keeps adding, removing and changing different kinds of data all the time.
So I needed something quite flexible and this is the best idea I came up with so far, from a developer point of view. Because I fear that this so called "best idea" is just a faint dream and will be too slow on the long term.

Knowing that I might have to perform LIKE and MATCH AGAINST queries on the large volume of data that user_data will hold.

Also, you might take note that I am not very familiar with indexes. But at this point, I have no idea what is the most efficient, given that the data is fetch based on two columns user_data.user_id and user_data.name.

Should I make both of those columns (user_id and name) indexes? Should user_id be a foreign key (for the users table JOIN)?

Is there a way to improve either my database model or my queries?

I thank you all in advance. I am a bit lost at the moment and need some fresh points of view on this matter. Don't hesitate to tell me if you need more details.
Don't use MyISAM, use InnoDB.  http://mysql.rjweb.org/doc.php/myisam2innodb
EAV is inefficient, especially when you normalize either of the columns.  http://mysql.rjweb.org/doc.php/eav  And read more about Entity-Attribute-Value schemas in stackoverflow.com
You are missing some critical indexes that are critical to performance.  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
At this point, FOREIGN KEYs are the least of the issues.