Slow log files and optimize queries issues

Posted on 2011-05-12
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
    LVL 20

    Expert Comment

    by:Muhammad Wasif
    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

    SHOW TABLE STATUS LIKE 'wp_options';

    Author Comment

    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

    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.

    Author Comment

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

    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

    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


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    In Part I (, I introduced you to the powerful WordPress backend, the WordPress administrative Dashboard.  In Part II, I will introduce yo…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and Log…
    The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now