Better Query to Select Count Where

Question

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.

Do you have an index on clientid and callerid? Is your data well-formed enough to use = instead of LIKE? — grossvogel
– grossvogel, Commented Apr 16, 2012 at 23:46
MySQL doesn't do so well with millions of records, try archiving. — Jason Foglia
– Jason Foglia, Commented Apr 16, 2012 at 23:54
What is the primary key for aaa_db? What are the columns types for callerid and clientid columns? — Marcus Adams
– Marcus Adams, Commented Apr 16, 2012 at 23:58
Jason -- mySQL does just fine with millions of records, as long as the table is indexed correctly. — O. Jones
– O. Jones, Commented Apr 17, 2012 at 0:23

Marcus Adams · Accepted Answer · 2012-04-17 00:00:46Z

4

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.

answered Apr 17, 2012 at 0:00

Marcus Adams

54k9 gold badges98 silver badges146 bronze badges

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

2 Comments

O. Jones Over a year ago

Marcus has it right. But also figure out why you're using LIKE to match the callerid column, and use = instead if you possibly can. I don't think it's relevant that you're hosted at GoDaddy, unless they restrict your mySQL instance to a very small amount of memory.

thevoipman Over a year ago

Hello, my indexes for aaa_db is callerid and clientid to unique... Godaddy has a new hosting package that has unlimited everything, and even 600 concurrent connections on it's hosting packages. However, just running this line, I'm getting my accounts threatened to be shutdown.

Mike Purcell · Accepted Answer · 2012-04-17 00:06:57Z

1

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.

answered Apr 17, 2012 at 0:06

Mike Purcell

20k10 gold badges55 silver badges94 bronze badges

5 Comments

Marcus Adams Over a year ago

+1 Good point about the indexes being used from left to right. It is something to consider. The order of the index doesn't matter here with regard to how they're ordered in the query, or = vs. LIKE since the LIKE value is a constant (no wildcard), however, the column with the highest cardinality (most uniqueness) should be first in the index (to improve performance). If there is a chance that wildcards might be used in the LIKE, then clientID shold definitely be first in the index so that it can always be used. Ranges invalidate further use of the index, so ranges should be right most.

Mike Purcell Over a year ago

@MarcusAdams: Thanks for the input. It seems to me that he has the callerid setup as a non-int datatype (probably varchar), which in my experience translates to slower queries, especially if there is an int column available to query against. So are you saying that given the disparate datatypes that the order still wouldn't matter?

Marcus Adams Over a year ago

Mike, now you've stumped me. That's one I don't know. I always thought cardinality outweighed type :)

Mike Purcell Over a year ago

I agree, with same datatypes, but I believe the general practice is to always try to use int over non-int when possible.

thevoipman Over a year ago

Wow, learning something new every day. Great job guys. Thank you so much for this.

Bohemian · Accepted Answer · 2012-04-17 00:23:14Z

1

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'

answered Apr 17, 2012 at 0:23

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

2 Comments

Marcus Adams Over a year ago

I agree. This might be the way to go. Test it out. The performance gain here would come from a small index, which is read faster and consumes less memory.

thevoipman Over a year ago

Thanks for this. I will try this and see if Godaddy say anything.

Collectives™ on Stack Overflow

Better Query to Select Count Where

3 Answers 3

2 Comments

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related