0

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

3
Contributors
2
Replies
15
Views
4 Years
Discussion Span
Last Post by rch1231
0

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.

0

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`)
);
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.