WordPress.org

Ready to get started?Download WordPress

Forums

Mysql optimization (2 posts)

  1. derbov
    Member
    Posted 6 years ago #

    You are crazy people. Had you seen how many mysql queries you make???

    This is result of print_r($wpdb->queries);

    Array
    (
    [0] => Array
    (
    [0] => SET NAMES 'utf8'
    [1] => 0.000146150588989
    [2] => require_once
    )

    [1] => Array
    (
    [0] => SELECT option_value FROM wp_options WHERE option_name = 'siteurl'
    [1] => 0.000427007675171
    [2] => is_blog_installed
    )

    [2] => Array
    (
    [0] => SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
    [1] => 0.00160598754883
    [2] => wp_load_alloptions
    )

    [3] => Array
    (
    [0] => SELECT option_value FROM wp_options WHERE option_name = 'rurumo' LIMIT 1
    [1] => 0.000524997711182
    [2] => get_option
    )

    [4] => Array
    (
    [0] => SELECT option_value FROM wp_options WHERE option_name = '' LIMIT 1
    [1] => 0.000396966934204
    [2] => get_option
    )

    [5] => Array
    (
    [0] => SELECT * FROM wp_users WHERE user_login = 'admin'
    [1] => 0.000432014465332
    [2] => get_userdatabylogin
    )

    [6] => Array
    (
    [0] => SELECT meta_key, meta_value FROM wp_usermeta WHERE user_id = 1
    [1] => 0.000334024429321
    [2] => _fill_user
    )

    [7] => Array
    (
    [0] => SELECT option_value FROM wp_options WHERE option_name = 'widget_categories' LIMIT 1
    [1] => 0.000420808792114
    [2] => get_option
    )

    [8] => Array
    (
    [0] => SELECT option_value FROM wp_options WHERE option_name = 'widget_text' LIMIT 1
    [1] => 0.000258922576904
    [2] => get_option
    )

    [9] => Array
    (
    [0] => SELECT option_value FROM wp_options WHERE option_name = 'widget_rss' LIMIT 1
    [1] => 0.00025486946106
    [2] => get_option
    )

    [10] => Array
    (
    [0] => SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name = 'users' AND (post_type = 'page' OR post_type = 'attachment')
    [1] => 0.00124716758728
    [2] => get_page_by_path
    )

    [11] => Array
    (
    [0] => SELECT * FROM wp_posts WHERE ID = 7 LIMIT 1
    [1] => 0.00133109092712
    [2] => get_post
    )

    [12] => Array
    (
    [0] => SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND (ID = '7') AND wp_posts.post_type = 'page' ORDER BY wp_posts.post_date DESC
    [1] => 0.000814199447632
    [2] => get_posts
    )

    [13] => Array
    (
    [0] => 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') AND tr.object_id IN (7) ORDER BY t.name ASC
    [1] => 0.00364112854004
    [2] => wp_get_object_terms
    )

    [14] => Array
    (
    [0] => SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (7) ORDER BY post_id, meta_key
    [1] => 0.00215697288513
    [2] => update_postmeta_cache
    )

    [15] => Array
    (
    [0] => SELECT value FROM wp_flickr WHERE name='image_viewer'
    [1] => 0.00187587738037
    [2] => getSetting
    )

    [16] => Array
    (
    [0] => 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.term_id <> 1 ) ORDER BY t.name ASC
    [1] => 0.0026650428772
    [2] => get_terms
    )

    [17] => Array
    (
    [0] => SELECT ID
    FROM wp_users
    WHERE display_name = ''
    [1] => 0.00161123275757
    [2] => pf_name_to_id
    )

    [18] => Array
    (
    [0] => SELECT COUNT(ID)
    FROM wp_users
    [1] => 0.0884850025177
    [2] => pf_user_count
    )

    [19] => Array
    (
    [0] => SELECT * FROM wp_users
    LIMIT 0, 10

    [1] => 0.000290155410767
    [2] => pf_uids
    )

    [20] => Array
    (
    [0] => SELECT * FROM wp_posts WHERE (post_type = 'page' AND post_status = 'publish') ORDER BY ID ASC
    [1] => 0.00166606903076
    [2] => get_pages
    )

    [21] => Array
    (
    [0] => 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.term_id <> 1 ) ORDER BY t.name ASC
    [1] => 0.0354290008545
    [2] => get_terms
    )

    [22] => Array
    (
    [0] => SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish' AND post_date_gmt < '2008-06-15 09:46:12' ORDER BY post_date DESC LIMIT 0, 5
    [1] => 0.0149819850922
    [2] => require_once
    )

    [23] => Array
    (
    [0] => SELECT * FROM wp_posts WHERE ID = 6 LIMIT 1
    [1] => 0.000416994094849
    [2] => get_post
    )

    [24] => Array
    (
    [0] => SELECT * FROM wp_posts WHERE ID = 5 LIMIT 1
    [1] => 0.00043511390686
    [2] => get_post
    )

    [25] => Array
    (
    [0] => SELECT * FROM wp_posts WHERE ID = 4 LIMIT 1
    [1] => 0.000416040420532
    [2] => get_post
    )

    [26] => Array
    (
    [0] => SELECT * FROM wp_posts WHERE ID = 1 LIMIT 1
    [1] => 0.000483989715576
    [2] => get_post
    )

    [27] => Array
    (
    [0] => SELECT p.ID, c.comment_ID, c.comment_content, u.display_name, c.comment_author, c.comment_author_url, p.post_title
    FROM wp_comments c
    LEFT JOIN wp_posts p ON (p.ID=c.comment_post_ID)
    LEFT JOIN wp_users u ON (c.comment_author=u.user_login )
    WHERE p.post_status IN ('publish','static') AND post_password ='' AND comment_approved = '1' ORDER BY comment_ID DESC LIMIT 5
    [1] => 0.00112700462341
    [2] => mdv_recent_comments
    )

    [28] => Array
    (
    [0] => SELECT * , IF (DATE_ADD(link_updated, INTERVAL 120 MINUTE) >= NOW(), 1,0) as recently_updated , UNIX_TIMESTAMP(link_updated) AS link_updated_f FROM wp_links WHERE 1=1 AND link_visible = 'Y' ORDER BY wp_links.link_id ASC
    [1] => 0.00215697288513
    [2] => get_bookmarks
    )

    Main lags in php-mysql applications are accured when you are running queries. Time for transfer query and time for getting the resukt back to script is much more then parsing results in php.

    Had you seen book "mysql for dummies"? it look like yours "WordPress for Dummies" if you dont know.

    So, lets look on queries.

    0 is ok ofcourse :)

    1-4,7-9 why not to run it in one single query? (sum of times is 0.00577259063721)

    12: "..WHERE 1=1.." %)

    this was only page view. if we look on category:

    cool function i think is "get_post"

    "SELECT * FROM wp_posts WHERE (post_type = 'page' AND post_status = 'publish') ORDER BY ID ASC"

    "SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish' AND post_date_gmt < '2008-06-15 10:02:47' ORDER BY post_date DESC LIMIT 0, 5"

    then you are running "SELECT * FROM wp_posts WHERE ID = %N LIMIT 1" this data can be transfered in of thouse two queries.

    make it better and not so much queries :)
    maybe you`ll need to use more JOIN statments.

    electronically yours, Derbov A.V.

  2. derbov
    Member
    Posted 6 years ago #

    чо чо пыщ пыщ! )

Topic Closed

This topic has been closed to new replies.

About this Topic