Which SQL is best to use

Question

i have a mysql database with about 60 Million records. i will have to generate report every day.

I have two type of sql statement.

Method1

SELECT TransferStatus as Transfer_Status, count(ID) as Count 
FROM calldetails 
WHERE YEAR(IVRStartTime) = '2015' AND MONTH(IVRStartTime) = '11' AND DAy(IVRStartTime)='05' AND CallTransfer LIKE '163%' 
GROUP BY TransferStatus;

Method2

SELECT TransferStatus as Transfer_Status,  
count(ID) as Count FROM  calldetails 
WHERE  (IVRStartTime BETWEEN '2015-11-05 00.00.00' AND '2015-11-05 23.59.59') 
AND CallTransfer LIKE '163%'  
GROUP BY TransferStatus

I tried both queries both takes approximately 15 minutes to give results. My question is which one is the best to use and is there any other way i can improve the performance.

The first one is not sargable. So even if you add an index on the date column it will still be slow. See this http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable — thepirat000, Nov 09 '15 at 05:48
Why closed? This is not opinion-based, but fact-based. Voting to reopen. — Rick James, Nov 28 '15 at 13:52

David Soussan · Answer 1 · 2015-11-09T07:57:47.367

3

Just use the DATE function to return the date part of your datetime value

SELECT TransferStatus as Transfer_Status,  
count(ID) as Count FROM  calldetails 
WHERE  DATE(IVRStartTime) = DATE('2015-11-05') 
AND CallTransfer LIKE '163%'  
GROUP BY TransferStatus

That will be more efficient for handling the date condition.

You need to use EXPLAIN to see what the execution plan is so that you can optimise it with indexes to speed up the query

edited Nov 09 '15 at 07:57

answered Nov 09 '15 at 05:57

David Soussan

2,698
1
16
19

No. `DATE(IVRStartTime)` is not sargable, so it won't use an index on `IVRStartTime`. – Rick James Nov 28 '15 at 13:54

score 2 · Accepted Answer · answered Nov 09 '15 at 05:47

use BETWEEN best approach

SELECT TransferStatus as Transfer_Status,  
count(ID) as Count FROM  calldetails 
WHERE  (IVRStartTime BETWEEN '2015-11-05 00.00.00' AND '2015-11-05 23.59.59') 
AND CallTransfer LIKE '163%'  
GROUP BY TransferStatus

this one is best because BETWEEN perform fast as compare many where clause

or use index is always be a good habit because :-

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

Tnx. I created the index and tested both sql statement. This one works well , it takes approximately 2 minutes where other one takes approximately 10 minutes to give results. — Sathya Baman, Nov 28 '15 at 15:05

Which SQL is best to use

2 Answers2