[Last Call] Learn how to a build a cloud-first strategyRegister Now


Slow log files and optimize queries issues

Posted on 2011-05-12
Medium Priority
Last Modified: 2012-06-27
does an optimize database will have an empty slow log files?
right one i have a few queries at MySQL log file that seem to take more time than I wanted but its examines only a short number of rows most of them are 0 or 1
how should i address the optimize queries as such?

foe exm:
# Thu May 12 16:02:12 2011
# Query_time: 1.072932  Lock_time: 0.000092 Rows_sent: 0  Rows_examined: 1

UPDATE `wp_options` SET `option_value` = '1305237731' WHERE `option_name` = '_transient_doing_cron'

Open in new window

Question by:Nura111
  • 5
  • 2
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 35753246
It looks like option_name is indexed.

Are you one a shared server? What is the size of table 'wp_options'? Post the results of the following query


Author Comment

ID: 35755632
yes its a shared server
do you mind telling me how can you see its indexed?
and also my question is in general this is just one of the few queries that appear in the slow log file which doesnt seem to go on a lot of rows so im not really sure what to do with those queries.

the results:

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
wp_options	MyISAM	10	Dynamic	194	1882	396032	281474976710655	20480	30788	1809	2011-05-09 11:35:42	2011-05-13 10:25:45	NULL	utf8_general_ci	NULL

Open in new window

LVL 20

Accepted Solution

Muhammad Wasif earned 2000 total points
ID: 35755808
Rows_examined tells how many rows the query has scanned to locate the desired one.

Your table has just 194 rows. This might be due to load on the server at that time that it took more than 1 sec. Do you see this query frequently?

Post some other queries as well.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 35755866
yes this one is repeating in the slow log but for different databases

thank you I attached some more queries. they are all  from different database so the tables  size can change. so please write if you need more information.
I have different wp websites on the same one.

# Fri May 13 07:01:03 2011
# Query_time: 6.370423  Lock_time: 0.000141 Rows_sent: 3  Rows_examined: 813
use chicagw9_wrd10;
SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY wp_posts.post_date DESC LIMIT 0, 3

# Fri May 13 07:15:35 2011
# Query_time: 1.983450  Lock_time: 0.000122 Rows_sent: 3  Rows_examined: 654

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY wp_posts.post_date DESC LIMIT 0, 3

# Fri May 13 07:21:26 2011
# Query_time: 1.186763  Lock_time: 0.000120 Rows_sent: 1  Rows_examined: 7
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (1) ORDER BY t.name ASC

# Fri May 13 07:43:25 2011
# Query_time: 1.080922  Lock_time: 0.000801 Rows_sent: 1  Rows_examined: 1

SELECT * FROM wp_comments  WHERE comment_approved = '1' ORDER BY comment_date_gmt DESC LIMIT 5

Open in new window


Author Comment

ID: 35757945
another ex with the result of EXPLAIN is

SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (15)

results :

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp_postmeta	ref	post_id	post_id	8	const	8

Open in new window


Author Comment

ID: 35771150
Please advise, I have a lot of queries like that  that I dont know if to try optimize because it doesn't seem like there is a way to optimize ot try something else, if you can please response on the queries I posted
Thank you!

Author Comment

ID: 35772291

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video teaches users how to migrate an existing Wordpress website to a new domain.
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question