Can I use an index to optimize select clause, versus where clause?

Question

I'm not sure if this question has been answered, because I'm not sure of the proper wording to describe what I need. So here it is:

I am trying to optimize a database that is running very slowly, hinged on one central table, with 128k records (set to accept about 10x that many in the next couple months), and 24 columns. 90+% of the time a query only needs to return a particular subset of 4 of those columns, and that query needs to be as fast as possible, because it happens constantly.

My understanding is that given enough RAM (our db server has plenty of extra right now), indexes all will be kept in RAM, and any query that only requires fields present in the index will be able to complete without performing a disk read at all, thereby speeding the query significantly.

Is this assumption accurate? And if so, must they all be in the same multi-column index in order for that to work, or would separate per-column indices work just as well? Is there a different method I should be using, since I really want to optimize the selecting and not the filtering part of the query?

The 4 crucial columns are id, person_type, person_id (a foreign key, used in conjunction with person_type), and full_name. The typical use cases are:

1. The where clause only specifies one or more values for id.
2. The where clause specifies person_id then person_type.
3. The where clause specifies person_type then a LIKE condition for full_name.

In each of the above cases, all 4 columns are specified in the SELECT clause. A few sample queries:

SELECT contacts.full_name, contacts.person_type, contacts.person_id
FROM `contacts`
WHERE `contacts`.`person_type` = 'Usage::User'
AND `contacts`.`person_id` IN (388,389)

SELECT `contacts`.* FROM `contacts`
WHERE (person_type="Usage::Profile" AND full_name LIKE "%Foo%")

SELECT `crm_connections`.*, `contacts`.`full_name`, `contacts`.`person_type`, `contacts`.`person_id`
FROM `crm_connections`
INNER JOIN `contacts` ON `contacts`.`person_id` = `crm_connections`.`id`
AND `contacts`.`person_type` = 'Crm::Connection'
WHERE `crm_connections`.`active` = 1
AND (contacts.full_name LIKE '%Foo%')
LIMIT 1

Answer 1

Query 1:

INDEX(person_type, person_id) -- in that order
INDEX(person_type, person_id, full_name) -- to be "covering"

Query 2 ("covering" is not practical because of *):

INDEX(person_type, full_name) -- in that order

Query 3: Before making suggestions here, please explain why you have a LIMIT without an ORDER BY.

See also my cookbook on making indexes.