Query Run time with Inner Join

Question

I tried running a query with an inner join in Sequel Pro to get the most recent records/invoices using this:

SELECT tt.Hotel_Property, tt.Preferred_Hotel_Status
FROM hotel_detail tt
INNER JOIN
    (SELECT Hotel_Property, MAX(STR_TO_DATE (`Invoice_Date`, '%m/%d/%Y')) AS MaxDateTime
    FROM hotel_detail
    GROUP BY Hotel_Property) groupedtt 
ON tt.Hotel_Property = groupedtt.Hotel_Property 
AND tt.Invoice_Date = groupedtt.MaxDateTime

But it's running the query for a long time and I'm not sure if it'll actually execute (cancelled it after waiting 14 mins). I know it's a lot of data to work through but wondered if anyone had suggestions to make it run faster?

*Ideally I want one record for each hotel property giving the most recent invoice date and the status associated with that max invoice

Thanks!

On a sidenote: `Invoice_Date` is a string? Why? And you make it a date (`MaxDateTime`), but compare this date again with the string `Invoice_Date`? This doesn't look good to me. — Thorsten Kettner, Mar 06 '18 at 14:43
Performance questions should include `EXPLAIN ANALYZE` and some information about table size, index, current time performance, desire time, etc. `Slow` is a relative term and we need a real value to compare. [**MySQL**](http://dba.stackexchange.com/questions/15371/how-do-i-get-the-execution-plan-for-a-view) and [**How do I obtain a Query Execution Plan?**](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) — Juan Carlos Oropeza, Mar 06 '18 at 14:49
You should read https://use-the-index-luke.com/ to learn how use the index to improve performance. — Juan Carlos Oropeza, Mar 06 '18 at 14:53

score 0 · Answer 1 · answered Mar 06 '18 at 14:46

From my knowledge sequel pro uses MySQL so you might no be able to use analytical functions but try the following:

SELECT 
Hotel_Property
, Preferred_Hotel_Status
, MAX(STR_TO_DATE (`Invoice_Date`, '%m/%d/%Y')) OVER(PARTITION BY hotel_property) AS MaxDateTime
FROM hotel_detail

If this doesn't work then I'd suggest running the query in 'chunks' based on the date. So maybe run a day at a time by employing a WHERE clause. I.E.:

SELECT tt.Hotel_Property, tt.Preferred_Hotel_Status
FROM hotel_detail tt
INNER JOIN
    (SELECT Hotel_Property, MAX(STR_TO_DATE (`Invoice_Date`, '%m/%d/%Y')) AS MaxDateTime
    FROM hotel_detail
WHERE DATE = "the_date_you_want_to_run"
    GROUP BY Hotel_Property) groupedtt 
ON tt.Hotel_Property = groupedtt.Hotel_Property 
AND tt.Invoice_Date = groupedtt.MaxDateTime 
WHERE DATE = "the_date_you_want_to_run"

Then you can either look at the results for different days separately, or simply INSERT them into a new table where you can perform more analysis.

score 0 · Answer 2 · answered Mar 06 '18 at 14:50

0

Try using a correlated subquery:

select hd.*
from hotel_detail hd
where str_to_date(hd.invoice_date, '%m/%d/%Y') =
          (select max(str_to_date(hd2.invoice_date, '%m/%d/%Y'))
           from hotel_detail hd2
           where hd2.hotel_property = hd.hotel_property
          );

This can take advantage of an index on hotel_detail(hotel_property, invoice_date). The index would be more effective if you stored the date properly using the native SQL format of date or datetime.

answered Mar 06 '18 at 14:50

Gordon Linoff

1,242,037
58
646
786

2

because OP is using `str_to_date` I dont think and index on that field would help. But as you suggest, saving the date as date or datetime would. – Juan Carlos Oropeza Mar 06 '18 at 14:52
@JuanCarlosOropeza . . . It has a minor help, because the subquery can be satisfied entirely using the index ("the index covers the subquery"). It would be much better if the column were stored correctly. – Gordon Linoff Mar 06 '18 at 22:44

Query Run time with Inner Join

2 Answers2