3 replies to this topic

[Tirus]

Hello all.

I have undertaken to complete a business listings website that was 3/4 completed by the previous web designer but who decided to abandon the project. Right now, when someone conducts a search by specifyig a category name or business name along with the State (this search is for the U.S.), a search is conducted through a table called "listing" where all the information for all the states is stored.

The problem is that this search takes quite a while and I am thinking that it may be due to the fact that so much data is in one table, therefore it takes quite some time to find results. As of now, about 25 states are uploaded, making over 5,000,000 business listings.

Is there a way to code something in asp (the sites coding is already all in asp) so that when a user specifies a state, lets say AZ, instead of looking through the table "listing" for results from AZ, the results are searched for in a table called "AZ".

Basically each state would have its own table and the search would be conducted in the table belonging to that state. Ex, AZ is searched for in table AZ, AL is searched for in table AL etc etc. This would greatly improve the speed of the search as far as I see it.

Can anyone help?

Thanks in advance,

Tirus

P.S. If you would need the original asp coding for the site that specifies how the search is conducted, just let me know.

[Wolfe]

Well, it seems that all you would be able to do to speed up search times is seperate the listing table by state like you said.

So, all you'll need to do is instead of putting all of the listing in one table, put each state's listing in a seperate table.

And the search form just tells it what table to search in..

Should be simple.. except for the whole 5 million table rows part

[Tirus]

Wolfe, on Jul 10 2006, 08:20 AM, said:

Well, it seems that all you would be able to do to speed up search times is seperate the listing table by state like you said.

So, all you'll need to do is instead of putting all of the listing in one table, put each state's listing in a seperate table.

And the search form just tells it what table to search in..

Should be simple.. except for the whole 5 million table rows part

how would I code something to tell the form what table to search in?

also, I read up on indexing in tables that may help my search by indexing the state column. Anyone know anything about that?

[Wolfe]

Well, I'm a PHP guy, but it's a simple process. So, I'm sure you could just convert it or get someone to. It's not hard at all.

For the search query, have the query box a state dropdown box (I'll assume you already have that, since it is the most logical choice). The name of the dropdown box will be the table name.

PHP would be like this:

// The input from the dropdown box's name is state in this case

// Get the form choice they made
$state = $_POST['state'];

$sql = "SELECT * FROM {$state}_listings";

Then you would execute it [as a search, of course.]

So, the listing tables would be like this, in this case:

alabama_listings
arkansas_listings
newyork_listings
etc..

So, basically, you put the form choice into an ASP variable and plug that into the table name in the query.