Why calling a query as a view takes lots of extra time to be executed?

user_16834766 0
Viewed 94

Here is my query: (it takes 0.01 sec) to be executed:

SELECT `pos_transactions`.`id`, 
       `psps`.`name` psp_or_club_name, 
        businesses.discount, 
        shop_name, 
        businesses.city_id, 
       `amount`,
       `transaction_date`, 
       `psp_id` , 
       `business_id`, 
       'pos' as `name`, 
       `daily_percentage`
FROM `pos_transactions`       
JOIN `businesses` 
  ON `business_id` = `businesses`.`id`
JOIN `business_sub_categories` 
  ON `businesses`.`sub_category_id` = `business_sub_categories`.`id`
LEFT JOIN `psps`  
  ON `psp_id` = `psps`.`id`

UNION ALL

SELECT `club_transactions`.`id`, 
       `clubs`.`name_fa`,
        psp_or_club_name, 
        businesses.discount,
        shop_name, 
        businesses.city_id,
       `amount`,
       `transaction_date`,
       `club_transactions`.`club_id` as `psp_id` ,
       `business_id`, 
       'club' as `name`, 
       `daily_percentage` 
FROM `club_transactions`
JOIN `businesses` 
  ON `business_id` = `businesses`.`id`
JOIN `business_sub_categories` 
  ON `businesses`.`sub_category_id` = `business_sub_categories`.`id`
LEFT JOIN `clubs`  
  ON `club_transactions`.`club_id` = `clubs`.`id`
where `club_transactions`.`status` = "paid"

I need to make a view of it and them call it in different places. So I create the view this way:

CREATE VIEW myview AS ( <query above> )

And then use it like this:

SELECT * FROM myview

but it takes 3 sec to be executed. Why really? And how can I optimize it?

And here is the result of EXPLAIN:

enter image description here

sql-view mysql sql performance
3 Answers

  • 3 sec for 280K rows -- Not bad.

  • 0.01 sec for 280K rows -- sounds like the Query cache is turned on and it looked up the result without actually evaluating it. Re-time it after adding SQL_NO_CACHE immediately after each SELECT.

  • The first SELECT has no filtering, so it is obligated to fetch 280K rows.

  • pos_transactions and club_transactions seem to be nearly identical. I suggest you combine them into a single transactions table. That will probably help the Optimizer do a better job.

To discuss further, please provide SHOW CREATE TABLE for each table, the table sizes.

The explanation

The 0.01 sec is bogus! The UI appended LIMIT 24 onto the query before running it. That let it stop before doing all the work, hence ran quite fast.

user_17668310

One potential cause is the query cache, try disabling it to see if it solves the issue.
Another cause would be that the view optimization is leading to using temp tables which causes slowness.

user_26281370

Make sure you've index on

  • pos_transactions.business_id
  • businesses.sub_category_id
  • pos_transactions.psp_id
  • club_transactions.business_id
  • club_transactions.status

If you've indexes. You can try running ANALYZE TABLE table_names on tables to update table statistics

user_45202740
Related