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

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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