Speeding up work with huge SQL tables

On my website I am experimenting with tables that are extremely big. One table 'items' has about 2 million records and the second one 'items_parents' about 6 millions. Surprisingly I need to join those tables. When I try for exa开发者_StackOverflow社区mple this SQL command in MySQL:

SELECT * 
FROM  `items_parent` 
JOIN items ON items_parent.id = items.id
WHERE items_parent.parent = 4706421

It takes about 7 seconds, which is simple too long for a website. On both tables I have double indexes (of type BTREE) on 1. ID and name - on 2. ID and parent. I dont know much about using the indexes to be honest, but its better to have two common indexes right? In the first one the name is unique, maybe that could help somehow? Or the indexes are ok? In that case what can I improve?

Thank you very much

Suggest you look at the explain plan to see if the indexes are being used.

First, try putting a multi-column index on the items_parent table, making sure the parent column is listed first and then the id column listed second.

Also, if you don't need all the columns from both tables, do not do "Select *". At a minimum here, you are getting an additional column from each table for the columns you are joining on. Basically, the same data in 2 columns, which is a waste of time and bandwidth.

I'm having a hard time finding a good article on "covered indices", but you can check this one out: http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/

The basic idea is that if you have an index on a table on 1 or 2 columns, but you're SELECTing more data than just those columns, your query is going to have to perform another table scan just to pull the data from the table.

A covered index will include the other columns you want in the initial seek, greatly improving performance.