What is a good index for my query for quick results?

Question

I have a query that pulls out a lot of tables, but it is slow and I want to improve it.

SELECT ph.phone_call_id AS id, ph.call_subject AS callSubject, ac.account_name AS accountName, ph.trigger_on AS triggerOn, ph.isAppointment, ph.status, ind.name AS industry, cc.call_code_name AS callCode, ac.account_id FROM phone_calls AS ph INNER JOIN accounts AS ac ON ph.account_id = ac.account_id INNER JOIN industries AS ind ON ind.industry_id = ac.industry_id INNER JOIN call_codes AS cc ON ph.call_code_id = cc.call_code_id WHERE ac.status = 1 AND ph.status = 1 AND ph.owner_id = 1 AND ac.do_not_call = 0 AND ph.trigger_on BETWEEN '2012-10-09 00:00:00' AND '2013-04-09 23:59:59' LIMIT 0,25 

Please note that the phone_Calls table contains about 4.5 million entries and 300 KB accounts.

I now have an index

 ph.trigger_on ph.owner_id ph.status ac.status ac.do_not_call ac.account_id 

this is what i get when i explain the request

Answer 1

It is important to remember that the column that makes the quality index has a high degree of uniqueness. I'm not sure how many status values ​​are on your system, but this is not a good index if there are only 3 possible states in your table.

It would also be helpful if your indexes were in columns that are central to the join operation that you are preforming. In this case, ph.account_id , ac.account_id , ind.industry_id , ac.industry_id , ph.call_code_id , ph.owner_id and cc.call_code_id all look like good indexes (if they are not indexes yet). Please note: if in any of your tables there are many indexes (more than five), this can slow down your queries. In addition, if any of your tables has a large number of records or deletions generated on them (as compared to the number of reads), then indexing these tables is very unreasonable, because each index file is rewritten much more often on tables that have many write operations previously generated on them. In my experience, indexing is more a form of art than a science, so you will need to do some experimentation to find what is best for your system. Experiment offline, if possible, to find out what gives the best boost, given your data set. Good luck.

Answer 2

Like the others mentioned above, if your INDEX columns are 0 and 1 bit, then you do not need an index, and id auto-increment should work better for the main index. I assume that phone_call_id is INT and unique? Is this an auto increment field?

MySQL uses an internal auto-increment index index, so for tables like this, it's nice to have one.

If you publish your schema structure using SHOW CREATE TABLE accounts; SHOW CREATE TABLE phone_calls; etc. we can help you.

I would take a database - since it is only 300 thousand - and load it locally, and then add an auto-increment column, if it is not phone_call_id, delete all other EXCEPT indices: phone_call_id, account_id, industry_id and call_code_id.

To help you more, I need to know what the other table structure is, especially how wide and the number of rows are.