I am relatively new with PHP and have a problem using the SELECT function. While accessing a 66,000 record database, I am attempting to read CITY, STATE, AND ZIP. The file has as one of its indexes CITY STATE ZIP. However, I have noticed that the read is very slow when there are many identical CITY STATE with various zipcodes, and rapid when only a few. How could this be? An indexed search should immediately retrieve the correct CITY STATE and ZIP as requested in the search. Does one have to use special code to force an indexed search or does <PHP> SELECT * from FILENAME where CITY ='$City' and STATE = '$State and ZIP = '$Zip'"; </PHP> default to a sequential search. Please explain what is happening here. I would greatly appreciate a solution to this problem. Thank you.
-
-
your tables structures is not normal, but i'm not judge you that you are wrong, but actually this give you a problem don't you.
you need to "normalize" your table structure (i'm not good enough, but i suggest you to learn this from books, net or friends maybe).
---------
table state : state_id, state_name (primary key state_id)
table city : city_id, state_id, city_name (primary key city_id, state_id)
table zip : zip_number, city_id (primaty key zip_number)
select state.state_id, state_name, city.city_id, city_name, zip_number from state, city, zip where state.state_id = city.state_id and city.city_id = zip.city_id and state.state_id = $sid and city.city_id = $cid and zip_number = $zn;
with this shema so you can get how much city on a state, and etc,
select state_name, count(city_id) from state,city where state.state_id = city.state_id group by state.state_id;
Comment