during my mysql database i have got the geonames database, that contains all nations, states and metropolitan areas.

i'm by using this to produce a cascading down menu therefore the user could choose where he's from: country -> condition -> county -> city.

however the primary problem would be that the query will sort through all of the 7 millions rows for the reason that table every time i wish to obtain the listing of children rows, which takes some time 10-just a few seconds.

i question the way i could speed this up: caching? table sights? restructuring table structure in some way?

and many important, how do you do this stuff? exist good lessons you can connect to me?

i appreciate all help and feedback talking about wise methods for handling this problem!

UPDATE: here's my table structure:

CREATE TABLE `geonames_copy` (
  `geoname_id` mediumint(9) NOT NULL,
  `parent_id` mediumint(9) DEFAULT NULL,
  `name` varchar(200) DEFAULT NULL,
  `ascii_name` varchar(200) DEFAULT NULL,
  `alternate_names` varchar(4000) DEFAULT NULL,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `feature_class` char(1) DEFAULT NULL,
  `feature_code` varchar(10) DEFAULT NULL,
  `country_code` varchar(2) DEFAULT NULL,
  `cc2` varchar(60) DEFAULT NULL,
  `admin1_code` varchar(20) DEFAULT NULL,
  `admin2_code` varchar(80) DEFAULT NULL,
  `admin3_code` varchar(20) DEFAULT NULL,
  `admin4_code` varchar(20) DEFAULT NULL,
  `population` bigint(20) DEFAULT NULL,
  `elevation` int(11) DEFAULT NULL,
  `gtopo30` smallint(6) DEFAULT NULL,
  `time_zone` varchar(40) DEFAULT NULL,
  `modification_date` date DEFAULT NULL,
  PRIMARY KEY (`geoname_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

here is the sql query:

            $query = "SELECT geoname_id, name
                    FROM geonames
                    WHERE parent_id = '$geoname_id'
                    AND (feature_class = 'A')";

must i just create index for just two posts: parent_id and have_class?

one question: isnĀ“t it easier to create a catalog with solr rather than using mysql? just one benefit is the fact that im already using solr and the other is it supports full text search. so perhaps it's better and so i dont use both solr and mysql (2 items to be a master at)?

As pointed out, more information could be useful (Sql, database structure).

The AJAX suggestion is a great one, though you might do that without ajax.

Don't perform choose at any time that chooses all the data. This is very slow.

First, populate the only real listing of nations. Permit the user to create a selection out of this list. Following the user chooses a rustic, either via AJAX, or by refreshing the whole page, populate their email list of states for your country only - something similar to (choose condition from geonames where country = @country). Once the user chooses a condition, populate their email list of areas for your country and condition - something similar to (choose country from geonames where country = @country and condition = @condition). Continue in this way for that city.

I am not so acquainted with MySql, however in SqlServer I'd create a catalog on (Country, Condition, County, City) to accelerate this group of queries. I am unsure if MySql would have the ability to accelerate the whole group of queries with this particular index or otherwise.

Obviously, I am making some presumptions about how exactly your computer data is structured here, which means this info might be relevant.

Publish your SQL for any better reply, however in general:

  • Make indexes on fields that you simply do joins/wheres on.
  • Don't use "Choose *" -- only choose the fields you'll need.
  • Hydrate as arrays rather than objects.

Also, when the menu never changes, cache the HTML inside a file. You can even only cache the nationOrcondition HTML, then fetch metropolitan areas via AJAX when they change frequently.

In my opinion that is generally completed with AJAX. At the start, you simply load the nation names, and after the first is selected, you dynamically load the condition names for the reason that country, then repeat for every subdivision next.