Need Advice Re: Mysql DB Structure

Forum Moderators: open

Message Too Old, No Replies

Need Advice Re: Mysql DB Structure

Pig of a database, need help simplifing down

2kreative

2:07 pm on Apr 10, 2007 (gmt 0)

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,

justageek

11:52 pm on Apr 10, 2007 (gmt 0)

Do you have an example of your query or queries?

JAG

2kreative

10:46 am on Apr 11, 2007 (gmt 0)

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

2:07 pm on Apr 11, 2007 (gmt 0)

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

10:28 am on Apr 12, 2007 (gmt 0)

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

2:53 pm on Apr 12, 2007 (gmt 0)

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

Good deal!

JAG