[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Optimize query

Posted on 2011-05-12
16
Medium Priority
?
541 Views
Last Modified: 2012-05-11
Hi. im having a hard time trying to understand the results of  the next query.
its have something to do with (word press plugin ManageWP Worker)
in the slow log files its seem to repeat several time and thats why im trying to optimize it,
im adding the query and the results from the EXPLAIN in phpmyadmin (the tables name in the table coloumn  is not tables I have in the database so thats also a reason than im confused with that) that
Im really new at this so..

The results:

id      select_type      table      type      possible_keys      key      key_len      ref      rows      Extra
1      PRIMARY      <derived2>      ALL      NULL      NULL      NULL      NULL      374      Using temporary; Using filesort
1      PRIMARY      p      eq_ref      PRIMARY      PRIMARY      8      rp.postid      1       
1      PRIMARY      u      eq_ref      PRIMARY      PRIMARY      8      chicagw9_wrd6.p.post_author      1       
1      PRIMARY      tr      ref      PRIMARY      PRIMARY      8      chicagw9_wrd6.p.ID      2      Using index
1      PRIMARY      tt      eq_ref      PRIMARY      PRIMARY      8      chicagw9_wrd6.tr.term_taxonomy_id      1       
1      PRIMARY      t      eq_ref      PRIMARY      PRIMARY      8      chicagw9_wrd6.tt.term_id      1       
2      DERIVED      wp_recently_popular      range      index_all      index_all      4      NULL      2026      Using where; Using index; Using temporary; Using f...
SELECT
  `rp`.`hits` AS hits,
  `rp`.`postid` AS `post_id`,
  `p`.`post_title` AS `post_title`,
  `p`.`post_date` AS `post_date`,
  `u`.`display_name` AS `display_name`,
  `u`.`user_url` AS `user_url`,
  `t`.`name` AS `category`
  FROM (
  SELECT 
  COUNT(`post_id`) AS `hits`,
  MIN(`ts`) AS `ts`,
  MIN(`user_type`) AS `user_type`,
  MIN(`post_id`) AS `postid`
  FROM 	wp_recently_popular
  WHERE   `ts` > (CURRENT_TIMESTAMP() - INTERVAL 1 MONTH)
  GROUP 	BY `post_id`
  ORDER	BY `hits` DESC
  ) AS `rp`
  LEFT JOIN wp_posts AS `p` ON `rp`.`postid` = `p`.`ID`
  LEFT JOIN wp_users AS `u` ON `p`.`post_author` = `u`.`ID`
  LEFT JOIN wp_term_relationships AS `tr` ON `p`.`ID` = `tr`.`object_id`
  LEFT JOIN wp_term_taxonomy AS `tt` ON `tr`.`term_taxonomy_id` = `tt`.`term_taxonomy_id`
  LEFT JOIN wp_terms AS `t` ON `tt`.`term_id` = `t`.`term_id`
  WHERE 1
  GROUP BY `rp`.`postid` 
  ORDER BY `rp`.`hits` DESC, `rp`.`ts` DESC
  LIMIT 5

Open in new window

0
Comment
Question by:Nura111
  • 9
  • 7
16 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35750143
Hi.

We will take a look and help.  It looks like you are well on your way as you knew to post EXPLAIN plan.  That will help.  
Before analyzing that, couple notes:
+"WHERE 1" << remove that
+"ORDER BY `hits` DESC" in `rp` derived table << likely causing unnecessary poor performance as you sort the outer query by hits again, so redundant.

Kevin
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35750181
SELECT
  COUNT(`post_id`) AS `hits`,
  MIN(`ts`) AS `ts`,
  MIN(`user_type`) AS `user_type`,
  MIN(`post_id`) AS `postid`
  FROM       wp_recently_popular
  WHERE   `ts` > (CURRENT_TIMESTAMP() - INTERVAL 1 MONTH)  
  GROUP       BY `post_id`
  ORDER      BY `hits` DESC

+As mentioned, remove the last bit ordering the rows here.
+MIN(`post_id`) AS `postid` should be `post_id` since you are grouping by this column
+You don't use `user_type` in the other query, so remove MIN(`user_type`) AS `user_type` so it is not having to lookup that value for no reason.
+Consider putting an index on the `ts` field if you consistently do filters based on a date/time range
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35750218
Sorry, one more observation.  You are grouping by post id on the outer query after already aggregating in the inner query.  See if you can eliminate this by finding the sources of multiple matches in other tables, i.e., try to get to 1-to-1 joins since you are not doing any aggregates in the other query.  This works in MySQL, but in other database system would have failed telling you that you have columns in select not in the group by.  

I suspect one area is wp_posts as it is likely you want the post that not only fits `rp`.`postid` = `p`.`ID` but also `rp`.`ts` = p`.`post_date`.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Nura111
ID: 35750239
ok thank you but as for now I removed the plug n that was doing that to check if it causing the problem and it seem that wasn't the problem(ill will change it according to you advise later on) so im trying to deal with other query i think maybe will be the case:
i also not sure I fully understand whats this query does because the score its tring to order by is not an existing column on this table (wp_posts)
(again this is in wp site if you are familiar)
and also im not sure i followed the query function right


SELECT ID, post_title, 
  MATCH (post_title, post_content) AGAINST ('\r\n							\r\n								mug shot edward hogan courtesy hamilton county justice center\r\n							\r\n						\r\n					\r\n				\r\n			\r\n		\r\n	\r\n\r\n	\r\n\r\n	\r\n		police make arrest deadly home invasionofficers arrested edward hogan avondale booked hamilton county justice center murder charge saturdayon robbed victim apartment cottonwood drive springfield township neighbor shot killed suspects derrick thomas officers robbery suspects charged murder person died result crime committingthirtyfiveyearold hogan arraigned court monday morningterrance arnold antwan isome facing murder charges connection case\r\n	\r\n\r\n\r\n			\r\n		\r\n	\r\n	\r\n	\r\n ') AS score
  FROM wp_posts
  WHERE MATCH (post_title, post_content) AGAINST ('\r\n							\r\n								mug shot edward hogan courtesy hamilton county justice center\r\n							\r\n						\r\n					\r\n				\r\n			\r\n		\r\n	\r\n\r\n	\r\n\r\n	\r\n		police make arrest deadly home invasionofficers arrested edward hogan avondale booked hamilton county justice center murder charge saturdayon robbed victim apartment cottonwood drive springfield township neighbor shot killed suspects derrick thomas officers robbery suspects charged murder person died result crime committingthirtyfiveyearold hogan arraigned court monday morningterrance arnold antwan isome facing murder charges connection case\r\n	\r\n\r\n\r\n			\r\n		\r\n	\r\n	\r\n	\r\n ' IN BOOLEAN MODE) 
  AND post_status = 'publish'
  AND post_date <= '2011-05-12 15:07:11'			
  AND post_type = 'post'
  ORDER BY score DESC
  LIMIT 50

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35750302
Look at the end, the alias for the first MATCH(...) AGAINST(...) is AS score.  Since the ORDER BY clause is evaluated after the SELECT clause it knows what the alias correlates to.  Once you have looked at my other suggestions, please post back, so we can help you there.
0
 

Author Comment

by:Nura111
ID: 35750394
I'm sorry Im not sure I understand. how can we do ORDER BY on a column that is not exist in the table?
and the other thing is that you are saying that instead of the MATCH it can be an AS? is it better for the query time?
0
 

Author Comment

by:Nura111
ID: 35750432
its seem to me like it doing twice the same thing
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35750606
It is, but it isn't.  The first MATCH in the WHERE is to filter rows.  It is optimized version as it uses 'IN BOOLEAN MODE'.  Later the one in the SELECT is to display the degree to which the row matches, i.e., SCORE.  The value of the expression is given the alias "score".  Alias can be used in ORDER BY and so yes you can order by a column not in original table as long as it is defined during the SELECT portion of your query.  This is really a different question, so note for future interaction with other Experts it is usually better to stay focused on one topic in thread as to not confuse original question; however, I was willing to answer.  Just a heads up. ;)
0
 

Author Comment

by:Nura111
ID: 35750742
k thanks, so do you think there is a way to optimize this one?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35750790
Which one, the original one in the question body?  If the new one, you will need to do similar as you did before to find out.  Use EXPLAIN and find if you are missing any appropriate indexing.  If the latter query, additionally, you will have to take into account you are doing Full-Text Search which will take up some overhead.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

And you need to determine what is appropriate amount of time, i.e., how long does it run now and what is your expectation.
Keeping in mind that you may not be able to do anything with query, but may have to look to other factors like increased hardware resources.  See this great article on MySQL query optimization:
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html
0
 

Author Comment

by:Nura111
ID: 35750814
the problem is that because he isnt finding the text in the datbase i cant get results from the EXPLAIN
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35751625
EXPLAIN SELECT ID, post_title, 
  MATCH (post_title, post_content) AGAINST ('sometext') AS score
  FROM wp_posts
  WHERE MATCH (post_title, post_content) AGAINST ('sometext' IN BOOLEAN MODE) 
  AND post_status = 'publish'
  AND post_date <= '2011-05-12 15:07:11'			
  AND post_type = 'post'
  ORDER BY score DESC
  LIMIT 50

Open in new window


Post the results.  It doesn't make sense for EXPLAIN not to work.  I just verified on my system and got results just fine regardless of matching rows or not.  In addition, note that you can use FULLTEXT search option without having explicitly defined a FULLTEXT key; however, as you will see in the documentation this can be slower.  Therefore, that may be the route of slowness if you are experiencing any.
0
 

Author Comment

by:Nura111
ID: 35755654
#1191 - Can't find FULLTEXT index matching the column list
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35755999
Then that is your issue, you don't have fulltext indexing on those columns.  Fix that before trying to optimize.
0
 

Author Comment

by:Nura111
ID: 35756881
Im sorry I not really sure how do it can you please give me more details?
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 35773011
Please see the manual page referenced here http:#a35750790
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this article, we’ll look at how to deploy ProxySQL.
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…
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
Course of the Month18 days, 19 hours left to enroll

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