CODEWITHSUNDEEP
mysqlsqloptimization
Sas
Sas

Reputation: 2503

Optimize query to return row count

I am trying to get latest date for each item.

For e.g.

Say I have a table with vendor, item, shipment Date. I am retrieving latest date for each item shipped by all vendors.

Data:

|Vendor|item   |shipmentDate|
|HP    |laptop | 2014/07/29 |
|HP    |laptop | 2014/06/28 |
|Apple |tablet | 2014/05/05 |
|Apple |tablet | 2014/06/28 |

Get latest date query:

SELECT Vendor, item, MAX(shipmentDate) 
FROM table 
GROUP BY Vendor, item;

Result:

|Vendor|item   |shipmentDate|
|HP    |laptop | 2014/07/29 |
|Apple |tablet | 2014/06/28 |

Everything is fine. However, my actual table would yield more than 50,000 records for above query. So I am trying to execute the query in a batch from my JAVA application. Thus, I am trying to retrieve row count of data:

SELECT COUNT(*)
FROM (SELECT Vendor, item, MAX(shipmentDate) 
    FROM table 
    GROUP BY Vendor, item) T;

Row Count query takes: 00:04:47 same amount of time as the other query: 00:04:43. Looking to find a way to optimize the row count query. Any help would be appreciated.

Upvotes: 2

Views: 82

Answers (4)

Jonathan Amend
Jonathan Amend

Reputation: 12815

You can use MySQL's non-standard SQL_CALC_FOUND_ROWS option for this.

First query:

SELECT SQL_CALC_FOUND_ROWS Vendor, item, MAX(shipmentDate) 
FROM table 
GROUP BY Vendor, item;

Second query:

SELECT FOUND_ROWS();

Upvotes: 1

xxx
xxx

Reputation: 189

I would suggest to run only the query 

    SELECT Vendor, item, MAX(shipmentDate) 
    FROM table 
    GROUP BY Vendor, item

and put the result in a ResultSet (if you are using Java), meaning store them in a Table like data structure (cache them) and them using the in-built methods of the API, count the number of rows in the ResultSet or table.

This will be faster than running the query twice.

Upvotes: 0

Adrian Nasui
Adrian Nasui

Reputation: 1095

I only want to suggest using DISTINCT to get the count, maybe it yields better results:

select count(Vendor) from
(select distinct Vendor, item
from  table)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Try doing this:

select count(distinct vendor, item)
from table;

This might be faster with an index on table(vendor, item). I am not 100% if MySQL will take advantage of the index for this query.

Upvotes: 2

Related Questions