Link to home
Start Free TrialLog in
Avatar of Nura111

asked on

optimize query -

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 .

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

Avatar of Walter Ritzel
Walter Ritzel
Flag of Brazil image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Nura111


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
Avatar of Nura111


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

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.
Avatar of Nura111


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
That's a good idea.
Avatar of Nura111


whats a good idea?
Open a new question for the second query.