Databases
StackOverflow2013
Postsdbo
POSlow Execution of MySQL Select Query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSlow Execution of MySQL Select Query
    primarykey
    data
    text
    <p>I have the following query…</p> <pre><code>SELECT DISTINCT * FROM vPAS_Posts_Users WHERE (post_user_id =:id AND post_type != 4) AND post_updated &gt;:updated GROUP BY post_post_id UNION SELECT DISTINCT vPAS_Posts_Users.* FROM PAS_Follow JOIN vPAS_Posts_Users ON ( PAS_Follow.folw_followed_user_id = vPAS_Posts_Users.post_user_id ) WHERE (( PAS_Follow.folw_follower_user_id =:id AND PAS_Follow.folw_deleted = 0 ) OR ( post_type = 4 AND post_passed_on_by = PAS_Follow.folw_follower_user_id AND post_user_id !=:id )) AND post_updated &gt;:updated GROUP BY post_post_id ORDER BY post_posted_date DESC LIMIT :limit </code></pre> <p>Where <code>:id = 7</code>, <code>:updated = 0.0</code> and <code>:limit=40</code> for example</p> <p>My issue is that the query is taking about a minute to return results. Is there anything in this query that I can do to speed up the result?</p> <p>I am using RDS</p> <p>********EDIT*********</p> <p>I was asked to run the query with an EXPLAIN the result is below</p> <p><img src="https://i.stack.imgur.com/BKuMK.png" alt="EXPLAIN Result from Query"></p> <p>********EDIT********** View Definitition</p> <pre><code>CREATE ALGORITHM=UNDEFINED DEFINER=`MySQLUSer`@`%` SQL SECURITY DEFINER VIEW `vPAS_Posts_Users` AS SELECT `PAS_User`.`user_user_id` AS `user_user_id`, `PAS_User`.`user_country` AS `user_country`, `PAS_User`.`user_city` AS `user_city`, `PAS_User`.`user_company` AS `user_company`, `PAS_User`.`user_account_type` AS `user_account_type`, `PAS_User`.`user_account_premium` AS `user_account_premium`, `PAS_User`.`user_sign_up_date` AS `user_sign_up_date`, `PAS_User`.`user_first_name` AS `user_first_name`, `PAS_User`.`user_last_name` AS `user_last_name`, `PAS_User`.`user_avatar_url` AS `user_avatar_url`, `PAS_User`.`user_cover_image_url` AS `user_cover_image_url`, `PAS_User`.`user_bio` AS `user_bio`, `PAS_User`.`user_telephone` AS `user_telephone`, `PAS_User`.`user_dob` AS `user_dob`, `PAS_User`.`user_sector` AS `user_sector`, `PAS_User`.`user_job_type` AS `user_job_type`, `PAS_User`.`user_unique` AS `user_unique`, `PAS_User`.`user_deleted` AS `user_deleted`, `PAS_User`.`user_updated` AS `user_updated`, `PAS_Post`.`post_post_id` AS `post_post_id`, `PAS_Post`.`post_language_id` AS `post_language_id`, `PAS_Post`.`post_type` AS `post_type`, `PAS_Post`.`post_promoted` AS `post_promoted`, `PAS_Post`.`post_user_id` AS `post_user_id`, `PAS_Post`.`post_posted_date` AS `post_posted_date`, `PAS_Post`.`post_latitude` AS `post_latitude`, `PAS_Post`.`post_longitude` AS `post_longitude`, `PAS_Post`.`post_location_name` AS `post_location_name`, `PAS_Post`.`post_text` AS `post_text`, `PAS_Post`.`post_media_url` AS `post_media_url`, `PAS_Post`.`post_image_height` AS `post_image_height`, `PAS_Post`.`post_link` AS `post_link`, `PAS_Post`.`post_link_title` AS `post_link_title`, `PAS_Post`.`post_unique` AS `post_unique`, `PAS_Post`.`post_deleted` AS `post_deleted`, `PAS_Post`.`post_updated` AS `post_updated`, `PAS_Post`.`post_original_post_id` AS `post_original_post_id`, `PAS_Post`.`post_original_type` AS `post_original_type`, `PAS_Post`.`post_passed_on_by` AS `post_passed_on_by`, `PAS_Post`.`post_passed_on_caption` AS `post_passed_on_caption`, `PAS_Post`.`post_passed_on_fullname` AS `post_passed_on_fullname`, `PAS_Post`.`post_passed_on_avatar_url` AS `post_passed_on_avatar_url` FROM (`PAS_User` join `PAS_Post` on((`PAS_User`.`user_user_id` = `PAS_Post`.`post_user_id`))); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJustin Erswell
    1. USJustin Erswell
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSlow Execution of MySQL Select Query
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POSlow Execution of MySQL Select Query
      1. USJustin Erswell
      1. VTBountyStart
    3. VO
      singulars
      1. POSlow Execution of MySQL Select Query
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you tell me why you need distinct *, how many fields exist in this table, I would say restrict it to the fields you want and that can improve the query a bit. Moreover why do you need a group by and a distinct, is there a specific purpose for doing this?
      singulars
      1. POSlow Execution of MySQL Select Query
      1. USskv
    2. COIn a word no I built the query so many times to get the result I wanted that I have not really focused on that, would this be causing an issue?
      singulars
      1. POSlow Execution of MySQL Select Query
      1. USJustin Erswell
    3. COLots of trouble here that may doom your query to poor performance. First, don't use `SELECT *`. CERTAINLY don't use `SELECT DISTINCT *`. Name your columns. Second, what is the purpose of your `GROUP BY` clauses? Where are your aggregative expressions? Read this: http://dev.mysql.com/doc/refman/5.1/en/group-by-extensions.html Then, avoid the nonstandard extensions. Third, `OR` clauses can hammer performance. Fourth, please explain what you're trying to do and disclose your table layouts.
      singulars
      1. POSlow Execution of MySQL Select Query
      1. USO. Jones
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload