Indexes and joins

I wanted to learn more about how to work with indexes to avoid joins.

here’s a query log that might be of some help.

<!– 19 queries, 6.369 seconds.

Array
(
[0] => Array
(
[0] => SET NAMES ‘utf8’
[1] => 0.000162839889526
[2] => require_once
)

[1] => Array
(
[0] => SELECT option_value FROM wp_options WHERE option_name = ‘siteurl’
[1] => 0.000220060348511
[2] => is_blog_installed
)

[2] => Array
(
[0] => SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’
[1] => 0.00084400177002
[2] => wp_load_alloptions
)

[3] => Array
(
[0] => SELECT COUNT(*) FROM wp_posts WHERE post_type=’post’ and post_status = ‘publish’
[1] => 0.00156283378601
[2] => include
)

[4] => Array
(
[0] => SELECT * FROM wp_users WHERE user_login = ‘needto’
[1] => 0.000492095947266
[2] => get_userdatabylogin
)

[5] => Array
(
[0] => SELECT meta_key, meta_value FROM wp_usermeta WHERE user_id = 1
[1] => 0.00019097328186
[2] => _fill_user
)

[6] => Array
(
[0] => SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND YEAR(post_date)=’2008′ AND MONTH(post_date)=’3′ AND DAYOFMONTH(post_date)=’24’ AND post_name = ‘your-guide-to-finding-the-best-anti-wrinkle-cream’ AND post_type = ‘post’ ORDER BY post_date DESC
[1] => 0.00049901008606
[2] => get_posts
)

[7] => Array
(
[0] => SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN (‘category’, ‘post_tag’) AND tr.object_id IN (6287) ORDER BY t.name ASC
[1] => 0.000256776809692
[2] => wp_get_object_terms
)

[8] => Array
(
[0] => SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (6287) ORDER BY post_id, meta_key
[1] => 0.000195980072021
[2] => update_postmeta_cache
)

[9] => Array
(
[0] => SELECT DISTINCT YEAR(post_date) AS year, MONTH(post_date) AS month, count(ID) as posts FROM wp_posts WHERE post_type = ‘post’ AND post_status = ‘publish’ GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC
[1] => 0.000327825546265
[2] => wp_get_archives
)

[10] => Array
(
[0] => SELECT option_value FROM wp_options WHERE option_name = ‘justsimple_sortpages’ LIMIT 1
[1] => 0.000197172164917
[2] => get_option
)

[11] => Array
(
[0] => SELECT option_value FROM wp_options WHERE option_name = ‘justsimple_excludepages’ LIMIT 1
[1] => 0.000128984451294
[2] => get_option
)

[12] => Array
(
[0] => SELECT * FROM wp_posts WHERE (post_type = ‘page’ AND post_status = ‘publish’) ORDER BY menu_order, post_title ASC
[1] => 0.000452041625977
[2] => get_pages
)

[13] => Array
(
[0] => SELECT * FROM wp_users WHERE ID = 1356 LIMIT 1
[1] => 0.000250101089478
[2] => get_userdata
)

[14] => Array
(
[0] => SELECT meta_key, meta_value FROM wp_usermeta WHERE user_id = 1356
[1] => 0.00189113616943
[2] => _fill_user
)

[15] => Array
(
[0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy = ‘category’ AND t.term_id = ‘1’ LIMIT 1
[1] => 0.000236034393311
[2] => get_term
)

[16] => Array
(
[0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy = ‘category’ AND t.term_id = ’29’ LIMIT 1
[1] => 0.0002760887146
[2] => get_term
)

[17] => Array
(
[0] => SELECT * FROM wp_comments WHERE comment_post_ID = 6287 AND (comment_approved = ‘1’ OR ( user_id = 1 AND comment_approved = ‘0’ ) ) ORDER BY comment_date
[1] => 0.000255107879639
[2] => comments_template
)

[18] => Array
(
[0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN (‘category’) ORDER BY t.name ASC
[1] => 0.00106191635132
[2] => get_terms
)

)
–>

This is all still pretty new too me and I’m not sure how to implement the right indexes or to know if they are in place already.

So what would be the optimal indexes for a readout like that?

Thanks

P.S if you can point me to more info or a dumbed down version of how you decide what to index that would be great =)