2

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
  • 2
    @maurice check covering indexes - that are the ones where you do not index only the conditions and orders, but the select clause too to skip reading the actual row. But if you use InnoDB, having enough memory you can keep everything in RAM, because the table is equal to its Primary Key (an index too). The buffer pool is used for this so just allocate enough memory for that. – jkavalik Feb 5 '16 at 7:45
  • 1
    The question is good. It would be much better if you added the SHOW CREATE TABLE ... output for both tables. – ypercubeᵀᴹ Feb 6 '16 at 10:59
  • @jkavalik I believe covering indexes was the term I needed, and can now research further. The database I'm using is InnoDB, so I'm going to do some reading into that as well. – maurice Feb 8 '16 at 20:13
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.