asked on

does this query can be optimized? how?

last try on solving a query from the slow log file its seem indexed but the extra doesn't look so good its a word press query ( I tried to get it answered before...)
thats why im not even sure if its smart to change it

# Query_time: 1.990343  Lock_time: 0.000782 Rows_sent: 5  Rows_examined: 3252
SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1  AND ( wp_term_relationships.term_taxonomy_id IN (3) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 5



results from expalin:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp_posts	index	PRIMARY,type_status_date	PRIMARY	8	NULL	814	Using where; Using temporary; Using filesort
1	SIMPLE	wp_term_relationships	eq_ref	PRIMARY,term_taxonomy_id	PRIMARY	16	chicagw9_wrd10.wp_posts.ID,const	1	Using index

Open in new window

ASKER CERTIFIED SOLUTION

Swindle

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Swindle

And my statement was made assuming that you've already worked on the indexes and at least wp_posts.ID and wp_term_relationships.object_id both have appropriate indexes on them (clustered if at all possible)

Nura111

ASKER

No I didnt thats my question what need to be done and how

Swindle

It has been a while since I've used MySQL, so I won't be able to offer many specifics on how to create an index, but if you have an index on both of the following columns, your query will be much more efficient:

wp_posts.ID
wp_term_relationships.object_id

Nura111

ASKER

how can i check if i have an index there?

Swindle

I will have to defer to someone else to answer that question. I am not familiar enough with MySQL to be able to assist. These links are about all I can do for you:

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
http://dev.mysql.com/doc/refman/5.0/en/show-index.html
http://dev.mysql.com/doc/refman/5.0/en/create-index.html