MYSQL where in Query takes too long

Hello,

I am trying to run a query to determine how many users I have in each state.

I have a table with all the zips in the USA and a table with all my users with a field for zip code:

So I run the following query:

SELECT COUNT( * )
FROM profile_fields_data
WHERE pf_zip_code
IN (

SELECT zip_code
FROM zips
WHERE state =  "CA"
)

My profile fields table has 4200 records and my zipcode table has 33,000 records and when i run this query it takes 51 seconds which is absurd!!!

If I run just this query
SELECT zip_code
FROM zips
WHERE state =  "CA"  

It takes milliseconds - how can I fix this, as I plan to run 50 queries to list out all 50 states.
LVL 1
neilsavAsked:
Who is Participating?
 
johanntagleConnect With a Mentor Commented:
Your original query just did a count so that's what I rewrote to a join.  Okay so I re-read your question and you said at the top that what you really want is to know how many users are on each state.  So it can be like this:

SELECT z.state, COUNT( p.* )
FROM zips z  
JOIN profile_fields_data p
ON p.pf_zip_code=z.zip_code
group by z.state;
0
 
johanntagleCommented:
Make sure  profile_fields_data.pf_zip_code and zips.zip_code and zips.state are indexed.  Then use a join instead of an IN subquery:

SELECT COUNT( p.* )
FROM profile_fields_data p
JOIN zips z
ON p.pf_zip_code=z.zip_code
WHERE z.state='CA';

By the way, especially if your tables are of innodb, it's better to count(p.primary_column) than p.*
0
 
neilsavAuthor Commented:
Using a join doesn't help me that just returns the amount of matches in the two tables.

I need to know what zipcodes are from particular state that is why i am using where in
0
 
neilsavAuthor Commented:
Perfect!  Thanks!
0
All Courses

From novice to tech pro — start learning today.