Need Advice Re: Mysql DB Structure

Pig of a database, need help simplifing down

[2kreative]

Hey guys looking for a bit of advice on how to structure a database to reduce queries and output results faster.

Quick Overview of current structure (simplified down)

Advertisers { id, Business Name, Address, County, Phone, Category Code } (Approx 80,000 rows)
Categories { id, Category Code, Name } (Approx 900 rows)

Results are filtered by Category Code & by County

My initial idea is to take the 80,000 rows from the main table. Make a table for each county and sort them out that way. Max counties would be 32. Anyone have any better ideas?

Thanks,

T

[justageek]

Do you have an example of your query or queries?

JAG

[2kreative]

Yes thanks,

SELECT COUNT(Business_Name) FROM Advertisers WHERE Sub_Category_Code LIKE '$category' AND County_Code LIKE '$coCode'

That query is looped for each County_Code to produce sort of an overall results page.

[justageek]

You really don't have that many rows so the queries should be fairly fast. The thought you have on breaking up the main db will certainly help and is the right way to go.

I have to wonder why you have a 'like' in the query when you are searching for an exact value? If you added indexes with the codes separate and one with the codes together, and changed the query to the one below, you'd cover all the queries for the codes and it should be very fast.

SELECT COUNT(Business_Name) FROM Advertisers WHERE Sub_Category_Code = '$category' AND County_Code = '$coCode'

JAG

[2kreative]

Thanks for your reply JAG, I'll go and read up on indexes now and see what I can come up with. I just wanted a little bit of feedback to see if I was approaching the situation correctly.

[justageek]

I'll go and read up on indexes now and see what I can come up with

Good deal!

JAG