Vfrdtyky

I have a query that is reading through approximately 2.4m rows of data.

The query itself is running well but the ORDER BY clause is causing performance issues. If I remove the ORDER BY the query takes 0.03 seconds to execute. With the ORDER BY it can take 4.5 to 5 seconds.

Is there anyway I an optimise this query further? Indexes have been added so that isn't a solution.

EDIT 1 -
This query is a shortened version of a much bigger PDO query so I think the join is necessary. You can see the main query at the bottom of this post.

SELECT t.processing_time, t.paymentType, t.status, t.merchantTransactionId, t.paymentBrand, t.amount, t.currency, t.code, t.holder, t.bin, t.last4Digits, t.recurringType, m.name AS merchant, c.name AS channel, concat(UPPER(SUBSTRING(trim(sp.status_description),1,1)), lower(SUBSTRING(trim(sp.status_description),2))) as status_description

FROM transactionsV2 t

JOIN channels c

ON t.entityId = c.uuid

JOIN merchants m

ON m.uuid = c.sender

JOIN status_payments sp 

ON t.code = sp.status_code

JOIN (

    SELECT t.id, t.processing_time FROM transactionsV2 t

    JOIN channels c ON t.entityId = c.uuid

    JOIN merchants m ON m.uuid = c.sender

    WHERE (t.processing_time >= "2018-11-08 00:00:00")

    AND (t.processing_time <= "2018-11-12 23:59:59")

    ORDER BY t.processing_time DESC

    LIMIT 1000

) t2

ON t.id = t2.id

WHERE t.status = 1

$transactions = DB::connection('mysql2')->select(DB::raw("SELECT t.processing_time, t.paymentType, t.status, t.merchantTransactionId, t.paymentBrand, t.amount, t.currency, t.code, t.holder, t.bin, t.last4Digits, t.recurringType, m.name AS merchant, c.name AS channel, concat(UPPER(SUBSTRING(trim(sp.status_description),1,1)), lower(SUBSTRING(trim(sp.status_description),2))) as status_description

FROM transactionsV2 t

JOIN channels c

ON t.entityId = c.uuid

JOIN merchants m

ON m.uuid = c.sender

JOIN status_payments sp 

ON t.code = sp.status_code

JOIN (

    SELECT t.id, t.processing_time FROM transactionsV2 t

    JOIN channels c ON t.entityId = c.uuid

    JOIN merchants m ON m.uuid = c.sender

    WHERE (t.processing_time >= :insTs1)

    AND (t.processing_time <= :insTs2)

    AND (:merchant1 IS NULL OR m.name LIKE :merchant2)

    AND (:channel1 IS NULL OR c.name LIKE :channel2)

    ORDER BY t.processing_time DESC

    LIMIT 1000

) t2

ON t.id = t2.id

WHERE (:status1 IS NULL OR t.status = :status2)

AND (:holder1 IS NULL OR holder LIKE :holder2)

AND (:paymentType1 IS NULL OR t.paymentType IN (".$paymentType."))

AND (:merchantTransactionId1 IS NULL OR merchantTransactionId LIKE :merchantTransactionId2)

AND (:paymentBrand1 IS NULL OR paymentBrand LIKE :paymentBrand2)

AND (:amount1 IS NULL OR amount = :amount2)

AND (:recurringType1 IS NULL OR t.recurringType = :recurringType2)"),

['status1' => $search->searchCriteria['status'],

'status2' => $search->searchCriteria['status'],

'holder1' => $search->searchCriteria['holder'],

'holder2' => '%'.$search->searchCriteria['holder'].'%',

'paymentType1' => $paymentType,

'merchantTransactionId1' => $search->searchCriteria['merchantTransactionId'],

'merchantTransactionId2' => '%'.$search->searchCriteria['merchantTransactionId'].'%',

'paymentBrand1' => $search->searchCriteria['paymentBrand'],

'paymentBrand2' => '%'.$search->searchCriteria['paymentBrand'].'%',

'amount1' => $search->searchCriteria['amount'],

'amount2' => $search->searchCriteria['amount'],

'recurringType1' => $search->searchCriteria['recurringType'],

'recurringType2' => $search->searchCriteria['recurringType'],

'merchant1' => $search->searchCriteria['merchant'],

'merchant2' => '%'.$search->searchCriteria['merchant'].'%',

'channel1' => $search->searchCriteria['channel'],

'channel2' => '%'.$search->searchCriteria['channel'].'%',

'insTs1' => $search->searchCriteria['fromDate'] . ' 00:00:00',

'insTs2' => $search->searchCriteria['toDate'] . ' 23:59:59']);

Perhaps I'm missing something, but I don't see that the subquery requires the joins. Does this suffice?

SELECT t.id, t.processing_time

FROM transactionsV2 t

WHERE t.processing_time >= '2018-11-08' AND

      t.processing_time <= '2018-11-13'

ORDER BY t.processing_time DESC

LIMIT 1000

If so, an index on transactionsV2(processing_time) would help (assuming that it is not a view).

I believe that the subquery is redundant since it is independent subquery and you are doing the join according to a primary key (transactionsV2.id). You can simply use

SELECT t.processing_time, 

       t.paymentType, 

       t.status, 

       t.merchantTransactionId, 

       t.paymentBrand, 

       t.amount, 

       t.currency, 

       t.code, 

       t.holder, 

       t.bin, 

       t.last4Digits, 

       t.recurringType, 

       m.name AS merchant, 

       c.name AS channel, 

       concat(UPPER(SUBSTRING(trim(sp.status_description),1,1)), 

       lower(SUBSTRING(trim(sp.status_description),2))) as status_description,

       row_number() over ()

FROM transactionsV2 t

JOIN channels c ON t.entityId = c.uuid

JOIN merchants m ON m.uuid = c.sender

WHERE (t.processing_time >= "2018-11-08 00:00:00") AND (t.processing_time <= "2018-11-12 23:59:59") and t.status = 1

ORDER BY t.processing_time DESC

LIMIT 1000