Mysql With Innodb: How To Avoid Using Count?

So I have a table which I populated with ~700K entries for testing, and it is showing it's pain in MySQL queries. The table in question: CREATE TABLE `trades` ( `tradeId` int(11)

Solution 1:

Put your conditions in a sum(). Like this you get single counts

SELECT SUM(sourceItem = 1 AND destinationItem = 1) AS count1,
       SUM(sourceItem = 1 AND destinationItem = 2) AS count2
FROM trades

To get the complete conditional count do

SELECTSUM(casewhen sourceItem >0and destinationItem >0then2when sourceItem >0or destinationItem >0then1else0end) AS complete_sum
FROM trades

or

SELECT SUM(sourceItem > 0) + sum(destinationItem > 0) AS complete_sum
FROM trades

Solution 2:

As you are always filtering by sourceItem AND destinationItem, adding an index on those two columns should speed this query up by a lot.

ALTER TABLE trades add index (sourceItem, destinationItem);

This way, the counts only need to get the values from the index, which will make your counts much faster.

Solution 3:

Does this work for you? This query would give you the same desired output, with better performance.

SELECT COUNT(*) FROM 
(
SELECT t.sourceItem, t.destinationItem, COUNT(1) AS count
   FROM trades t
   WHERE t.sourceItem IN ( 1, 2 ) AND t.destinationItem IN ( 1, 2, 3 )
GROUPBY t.sourceItem, t.destinationItem 
) as tbl_tmp

You say that you are running this query just to see if you need to display "No Results Found" message. If that is your only requirement, then I feel you just don't need a query at all. There would be other sources that could tell you that you something to display or not! Am I missing something here?