I am newer to setting up MYSQL queries. I have come up with a query that has a sub query, in a sub query to get the desired results. However, I am looking to speed the query up on my database as right now it is taking around 5 seconds to pull the information and do the work. Any help would be appreciated:
SELECT AVG( Total ) AS Average
FROM (
SELECT Total, ID, MIN( DATE )
FROM (
SELECT SUM( Amount ) AS Total, `TransactionDate` AS DATE, `ClientId` AS ID
FROM `transactions`
WHERE `ClientId` <> ''
GROUP BY `InvoiceId`
ORDER BY DATE ASC
) AS Invoice
GROUP BY ID
) AS AllInvoices
The query needs to find all transactions on a given invoice (unfortunately this is stored as one number in the database already), then group them and get the earliest invoice for each client, then average the totals. Looking to get the average first visit total in the end.
Unfortunately I can't provide all table information, it is imported from a third party and I am not at liberty to fully describe it out. For the little bit that I can share:
Field Type NULL KEY Default
Id varchar(900) NO MUL NULL
ClientId varchar(1000) YES NULL
TransactionDate datetime YES NULL
Amount decimal(15,4) YES NULL
The Transaction table currently has 241613 total rows. This could be more or less at any given point in time. These rows don't only contain what a customer paid, but also potentially salary payments and the like included.
However, The only index on table is with the individual transaction IDs themselves, not a value that I am currently pulling.
MYSQL Information:
innodb_version 5.5.43
protocol_version 10
slave_type_conversions
version 5.5.43-0ubuntu0.14.04.1
version_comment (Ubuntu)
version_compile_machine x86_64
version_compile_os debian-linux-gnu