does this query can be optimized? how?

Nura111
Nura111 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented:
The query looks pretty good as is.  There's not much you're going to be able to do to optimize that.  The only change I would make is to change:

AND wp_term_relationships.term_taxonomy_id IN (3)

to:

AND wp_term_relationships.term_taxonomy_id = 3

I'm not sure in MySQL if that's even a performance gain or not however.
Swindle

Commented:
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

Author

Commented:
No I didnt thats my question what need to be done and how
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Register
Swindle

Commented:
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

Author

Commented:
how can i check if i have an index there?
Swindle

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial