CODEWITHSUNDEEP
mysqlcount
thevoipman
thevoipman

Reputation: 1833

Better Query to Select Count Where

I'm hosted at Godaddy and they are giving me a fit with a query that I have which is understandable. I am wondering if there is a better way to go about rewriting this query I have to make it resource friendly:

SELECT count(*) as count
FROM 'aaa_db'
WHERE 'callerid' LIKE '16602158641' AND 'clientid'='41'

I have over 1 million rows, and have duplicates therefore I wrote a small script to output the duplicates and delete them rather than changing tables etc.

Please help.

Upvotes: 0

Views: 1591

Answers (3)

Bohemian
Bohemian

Reputation: 425033

I like Marcus' answer, but I would first try a single index on just callerid, because with values like 16602158641, how many rows can there be that match out of a million? Not very many, so the single index could match or exceed performance of the double index.

Also, remove LIKE and use =:

SELECT count(*) as count
FROM aaa_db
WHERE callerid = '16602158641' -- equals instead of like
AND clientid = '41'

Upvotes: 1

Mike Purcell
Mike Purcell

Reputation: 19979

The first thing I would try, is to ensure you have an index on the clientid column, then rewrite your query to look for clientid first, this should remove rows from consideration speeding up your query. Also, as Marcus stated, adding a multi-column index will be faster, but remember to add it as (clientid, callerid) as mysql reads indices from left to right.

SELECT count(*) as count
FROM aaa_db
WHERE clientid = 41 and callerid LIKE '16602158641';

Notice I removed the quotes from the clientid value, as it should be an int datatype, if it is not, converting to int datatype should also be faster. Also, I am not sure why you are doing a LIKE without the wildcard operator, if you could change that to an = that will also help.

Upvotes: 1

Marcus Adams
Marcus Adams

Reputation: 53830

Having separate indexes on clientid and callerid causes MySQL to use just one index, or in some cases, attempt an index merge (MySQL 5.0+). Neither of these are as efficient as having a multi-column index.

Creating a multi-column index on both callerid and clientid columns will relieve CPU and disk IO resources (no table scan), however it will increase both disk storage and RAM usage. I guess you could give it a shot and see if Godaddy prefers that over the other.

Upvotes: 4

Related Questions