SQLITE : Optimize ORDER BY Query

Question

All,

I am iOS developer. Currently we have stored 2.5 lacks data in database. And we have implemented search functionality on that. Below is the query that we are using.

select CustomerMaster.CustomerName ,CustomerMaster.CustomerNumber,
    CallActivityList.CallActivityID,CallActivityList.CustomerID,CallActivityList.UserID,
    CallActivityList.ActivityType,CallActivityList.Objective,CallActivityList.Result,
    CallActivityList.Comments,CallActivityList.CreatedDate,CallActivityList.UpdateDate,
    CallActivityList.CallDate,CallActivityList.OrderID,CallActivityList.SalesPerson,
    CallActivityList.GratisProduct,CallActivityList.CallActivityDeviceID,
    CallActivityList.IsExported,CallActivityList.isDeleted,CallActivityList.TerritoryID,
    CallActivityList.TerritoryName,CallActivityList.Hours,UserMaster.UserName,
    (FirstName ||' '||LastName) as UserNameFull,UserMaster.TerritoryID as UserTerritory 
from
    CallActivityList
    inner join CustomerMaster
        ON CustomerMaster.DeviceCustomerID = CallActivityList.CustomerID
    inner Join UserMaster
        On UserMaster.UserID = CallActivityList.UserID 
where
    (CustomerMaster.CustomerName like '%T%'  or
    CustomerMaster.CustomerNumber like '%T%'  or
    CallActivityList.ActivityType like '%T%' or
    CallActivityList.TerritoryName like '%T%' or
    CallActivityList.SalesPerson like '%T%'  ) 
    and  CallActivityList.IsExported!='2' and CallActivityList.isDeleted != '1'
order by
    CustomerMaster.CustomerName
limit 50 offset 0

Without using 'order by' The query is returning result in 0.5 second. But when i am attaching 'order by', Time is increasing to 2 seconds.

I have tried indexing but it is not making any noticeable change. Any one please help. If we are not going through Query then how can we do it fast.

Thanks in advance.

Olivier Jacot-Descombes · Accepted Answer · 2016-04-21 15:56:42Z

0

This is due to the the limit. Without ORDER BY only 50 records have to be processed and any 50 will be returned. With ORDER BY all the records have to be processed in order to determine which ones are the first 50 (in order).

The problem is that the ORDER BY is performed on a joined table. Otherise you could apply the limit on the main table (I assume it is the CallActivityList) first and then join.

SELECT ...
FROM
    (SELECT ... FROM CallActivityList ORDER BY ... LIMIT 50 OFFSET 0) AS CAL
    INNER JOIN CustomerMaster ON ...
    INNER JOIN UserMaster ON ...
    ORDER BY ...

This would reduce the costs for joining the tables. If this is not possible, try at least to join CallActivityList with CustomerMaster. Apply the limit to those and finally join with UserMaster.

SELECT ...
FROM
    (SELECT ...
     FROM
         CallActivityList 
         INNER JOIN CustomerMaster ON ...
     ORDER BY CustomerMaster.CustomerName
     LIMIT 50 OFFSET 0) AS ActCust
    INNER JOIN UserMaster ON ...
ORDER BY ...

Also, in order to make the ordering unambiguous, I would include more columns into the order by, like call date and call id. Otherwise this could result in a inconsistent paging.

edited Apr 21, 2016 at 15:56

answered Apr 21, 2016 at 15:34

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nikh1414 Over a year ago

That i know, If you can help me to optimize this query with 'order by'.

Collectives™ on Stack Overflow

SQLITE : Optimize ORDER BY Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related