optimize query -

Nura111
Nura111 used Ask the Experts™
on
im trying to optimize queries on a wp database:
attched us the query from the slow log file and the results from a EXPLAIN on the query in .
phpmyadmin.

the site is on a shared host with other wp sites.


# Mon May 16 03:56:33 2011
# Query_time: 1.742733  Lock_time: 0.000106 Rows_sent: 1  Rows_examined: 1
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') AND t.slug = 'sports' ORDER BY t.name ASC

Explain results:
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	const	PRIMARY,slug	slug	602	const	1	 
1	SIMPLE	tt	const	term_id_taxonomy,taxonomy	term_id_taxonomy	106	const,const	1

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Software Engineer
Commented:
I'm not seeing something slow on this query. I have tested the query and explain plan in my wordpress test database and the results were strictly the same. The query returns in less than 0.0020 seconds.

Author

Commented:
so why is it in my slow query log? what am i suppose to do about it? any good reference to optimize queried and reading the results from explain I know the basic only

Author

Commented:
Thats my biggest problem there is so much queries there in my slow log file i dont know which one is there for others reason and which one i need to try to optimize
here is another ex
EXPLAIN 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


id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp_term_relationships	ref	PRIMARY,term_taxonomy_id	term_taxonomy_id	8	const	3	Using temporary; Using filesort
1	SIMPLE	wp_posts	eq_ref	PRIMARY,type_status_date	PRIMARY	8	chicagw9_wrd3.wp_term_relationships.object_id	1	Using where

Open in new window

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.

Walter RitzelSenior Software Engineer

Commented:
Extracted from the mysql manuals.

The slow query log consists of all SQL statements that took more than long_query_time seconds to execute. The time to acquire the initial table locks is not counted as execution time. mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might be different from execution order. The minimum and default values of long_query_time are 1 and 10, respectively.

To enable the slow query log, start mysqld with the --log-slow-queries[=file_name] option.

If no file_name value is given for --log-slow-queries, the default name is host_name-slow.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process a slow query log file using the mysqldumpslow command to summarize the queries that appear in the log.

So, this not necessary means the query is slow, means the query is running more the threashold determined in the parameter.

And, as far as I was able to see, the indexes are being used.

Author

Commented:
the second query
so what can  I do about it? it said in the extra that its  Using temporary; Using filesort which i read that is not good
also how can it be that the rows is 3 and 1 and on th log file it said that 1416 rows exmined.
maybe i will write this query as a new question
Walter RitzelSenior Software Engineer

Commented:
That's a good idea.

Author

Commented:
whats a good idea?
Walter RitzelSenior Software Engineer

Commented:
Open a new question for the second query.

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