Which Module or Build In function using this SQL? It slow me down!!!

[explorer1979]

Hi all,

I ask the hosting send me the slow mysql queries log, find it used over 267 second to finished
Who know where it from?

# Query_time: 267 Lock_time: 0 Rows_sent: 20 Rows_examined: 205113
select c.coupon_amount, c.coupon_code, c.coupon_id, et.sent_firstname, et.sent_lastname, et.customer_id_sent, et.emailed_to, et.date_sent, crt.redeem_date, c.coupon_id
from coupons c
left join coupon_redeem_track crt
on c.coupon_id= crt.coupon_id, coupon_email_track et
where c.coupon_id = et.coupon_id
and c.coupon_type = 'G'
order by date_sent desc limit 0, 20;



Here are my installed Module List

How Did You Hear About Us
Confirm Email Address Entry
Airparcel Shipping
MultiGeoZone MultiTable Shipping
Super Orders
Edit Orders
Cross Sell
Cross Sell Advanced
Image Handler
Ceon Back In Stock Notifications
Sales Report
Print Invoice Popup
Update Products Quantity in Category Listing
Easy Populate
WorldPay
Stock Quantity Report
News and Article Manager & Optional Sideboxes
Report Cheaper Price
Recently Viewed Items
Blank Sidebox Mod
Shipping Estimator Sidebox
Google Language Selection
Google reCAPTCHA
Admin login as customer
Export Shipping Information

Thank you all guys who can help on this.

[explorer1979]

And I am using phpmyadmin see the MySQL

table coupons, row total right now 173,096
table coupon_email_track, row total right now 203,539
table coupon_redeem_track, row total right now 13,574

Do it safe to del some records in it?

[Ajeh]

In phpMyAdmin, can you do an optimize on each of those 3 tables one by one?

Does this make any change on the slow query time?

[gjh42]

To find out what file is generating the query, and thus what feature it comes from, search in admin > Tools > Deelopers Toolkit for a distinctive bit of the SQL, like
left join coupon_redeem_track crt

Paste this in the lower left box, select all files and search. This will show the file and line number where the query is built.

[explorer1979]

In phpMyAdmin, can you do an optimize on each of those 3 tables one by one?

Does this make any change on the slow query time?

Yes, in phpmyadmin, it already optimize and no overhead on it right side.

Just it records very big, over 300000 totoal.

On a RAID 1/5 SATA 7200 250/320G HDD ...

[explorer1979]

To find out what file is generating the query, and thus what feature it comes from, search in admin > Tools > Deelopers Toolkit for a distinctive bit of the SQL, like
left join coupon_redeem_track crt

Paste this in the lower left box, select all files and search. This will show the file and line number where the query is built.

gjh42,

Thank you very much of this hints, I will try this see I am luck or not.

[explorer1979]

Hi All,

Using developer tool enter "left join coupon_redeem_track crt"

Nothing find.

But I find this

admin/gv_sent.php

Line #80 : $gv_query_raw = "select c.coupon_amount, c.coupon_code, c.coupon_id, et.sent_firstname, et.sent_lastname, et.customer_id_sent, et.emailed_to, et.date_sent, crt.redeem_date, c.coupon_id


Any other idea on this?

[gjh42]

What does the rest of that file look like? If the text you first searched for is built from pieces by the file, the search will not find it as such. Does the file have any notes or comments that indicate it was modified from stock? Is its date in your file manager different from other files?