Retrive data quicker

[xxmp]

I have these templates:
temp1(id,country_id,county_id,town_id,location_id,street_id)
country(country_id, language,country_name)
county(county_id, language,county_name)
town(town_id, language,town_name)
location(location_id, language,location_name)
street(street_id, language,street_name)

For now i am joining all these for the selected language to find the names of temp1 id's location. Is there a better way to retrieve the informations? Would be better to make a table
newtable(id,country_name,county_name,town_name,location_name,street_name,language) or make any index or view or something else?
Thank you very much

[rubberman]

The key to speed in joining tables in any relational database (such as mysql) are indices. If you do frequent queries on a specific join, then I recommend using a view and perform the query on that.

[rch1231]

Hello,

Rubberman is correct and what he is trying to tell you is you need the ID fields (first field of each table) to be a key field and built with a unique index (normally also set to auto increment).
The when you join the fields the lookups will use the index and be much faster.

Creating your tables should look something like this:

CREATE TABLE `country` (
  `country_id` int(10) NOT NULL auto_increment,
  `language` varchar(25) default NULL,
  `country_name` varchar(25) default NULL,
  PRIMARY KEY  (`ID`)
);