newbie question: performance of large table

Hi,

Thank you for taking the time to read this. This is a rather general
database performance question I think. But since I'm running WAMP I thought
it would be best to ask in this group. Perhaps there are specific MySQL
abilitites that I could use.

I've downloaded the GeoDataSource Cities Database Free Edition and extracted
the cities and countries in two seperate MyISAM tables.

So now I have a city table with roughly 2,1 million records and a country
table of roughly 260 records. I want to use AJAX to retrieve cities in a
search field on my website after the user has selected a country from a
dropdown box. What this AJAX search field does is suggest, say the top 20
records, that match the characters the user has typed so far.

This is the table info:

CREATE TABLE `country` (
`id` int unsigned NOT NULL auto_increment,
`fips` char(2) NOT NULL default '',
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `city` (
`id` int unsigned NOT NULL auto_increment,
`country_id` int unsigned NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

What I use is something similar to:

$sql = 'SELECT name FROM city WHERE country_id = ' . $country_id . ' AND
name LIKE "' . $city . '%" LIMIT 20';

It works. But as you might have guessed, it's rather slow. My questions are:

What measurements can I take to improve search performance and still keep
the database's relational model scalable?

I thought of creating a table of cities for each available country. But my
gutfeeling tells me this is not good normilisation / relational model
practice. I've also read of indexing tables, but I am not sure how to use
this properly.

Please advice.My guess is that there must be a lot of other things here I
can do to speed things up. Thank you in advance for your suggestions!

As a side note: I would rather use a city table only has the 'important /
major' cities of countries, for obvious performance and usability reasons.
So if anybody knows where to get that information (preferably at no cost),
please inform me about it. Much appreciated.

Cheers
A.

Re: newbie question: performance of large table

"amygdala" schreef in bericht
news:44ed5f66$0$2024$9a622dc7@news.kpnplanet.nl...
> Hi,
>
> Thank you for taking the time to read this. This is a rather general
> database performance question I think. But since I'm running WAMP I
> thought it would be best to ask in this group. Perhaps there are specific
> MySQL abilitites that I could use.
>
> I've downloaded the GeoDataSource Cities Database Free Edition and
> extracted the cities and countries in two seperate MyISAM tables.
>
> So now I have a city table with roughly 2,1 million records and a country
> table of roughly 260 records. I want to use AJAX to retrieve cities in a
> search field on my website after the user has selected a country from a
> dropdown box. What this AJAX search field does is suggest, say the top 20
> records, that match the characters the user has typed so far.
>
> This is the table info:
>
> CREATE TABLE `country` (
> `id` int unsigned NOT NULL auto_increment,
> `fips` char(2) NOT NULL default '',
> `name` varchar(255) NOT NULL default '',
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> CREATE TABLE `city` (
> `id` int unsigned NOT NULL auto_increment,
> `country_id` int unsigned NOT NULL default '0',
> `name` varchar(255) NOT NULL default '',
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> What I use is something similar to:
>
> $sql = 'SELECT name FROM city WHERE country_id = ' . $country_id . ' AND
> name LIKE "' . $city . '%" LIMIT 20';
>
> It works. But as you might have guessed, it's rather slow. My questions
> are:
>
> What measurements can I take to improve search performance and still keep
> the database's relational model scalable?
>
> I thought of creating a table of cities for each available country. But my
> gutfeeling tells me this is not good normilisation / relational model
> practice. I've also read of indexing tables, but I am not sure how to use
> this properly.
>
> Please advice.My guess is that there must be a lot of other things here I
> can do to speed things up. Thank you in advance for your suggestions!
>
> As a side note: I would rather use a city table only has the 'important /
> major' cities of countries, for obvious performance and usability reasons.
> So if anybody knows where to get that information (preferably at no cost),
> please inform me about it. Much appreciated.
>
> Cheers
> A.
>

In the meantime I read up on indexing a little and came up with:

CREATE TABLE `city` (
`id` int(10) unsigned NOT NULL auto_increment,
`country_id` int(10) unsigned NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `city` (`country_id`,`name`(1))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Which already improved performance drastically! That's great! Any other
suggestions on performance improvement are still much appreciated! Thanks in
advance.

Re: newbie question: performance of large table

My gut feeling is to go down the table of cities for each country with
an index for name.

amygdala wrote:
> Hi,
>
> Thank you for taking the time to read this. This is a rather general
> database performance question I think. But since I'm running WAMP I thought
> it would be best to ask in this group. Perhaps there are specific MySQL
> abilitites that I could use.
>
> I've downloaded the GeoDataSource Cities Database Free Edition and extracted
> the cities and countries in two seperate MyISAM tables.
>
> So now I have a city table with roughly 2,1 million records and a country
> table of roughly 260 records. I want to use AJAX to retrieve cities in a
> search field on my website after the user has selected a country from a
> dropdown box. What this AJAX search field does is suggest, say the top 20
> records, that match the characters the user has typed so far.
>
> This is the table info:
>
> CREATE TABLE `country` (
> `id` int unsigned NOT NULL auto_increment,
> `fips` char(2) NOT NULL default '',
> `name` varchar(255) NOT NULL default '',
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> CREATE TABLE `city` (
> `id` int unsigned NOT NULL auto_increment,
> `country_id` int unsigned NOT NULL default '0',
> `name` varchar(255) NOT NULL default '',
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> What I use is something similar to:
>
> $sql = 'SELECT name FROM city WHERE country_id = ' . $country_id . ' AND
> name LIKE "' . $city . '%" LIMIT 20';
>
> It works. But as you might have guessed, it's rather slow. My questions are:
>
> What measurements can I take to improve search performance and still keep
> the database's relational model scalable?
>
> I thought of creating a table of cities for each available country. But my
> gutfeeling tells me this is not good normilisation / relational model
> practice. I've also read of indexing tables, but I am not sure how to use
> this properly.
>
> Please advice.My guess is that there must be a lot of other things here I
> can do to speed things up. Thank you in advance for your suggestions!
>
> As a side note: I would rather use a city table only has the 'important /
> major' cities of countries, for obvious performance and usability reasons.
> So if anybody knows where to get that information (preferably at no cost),
> please inform me about it. Much appreciated.
>
> Cheers
> A.