I have these templates:
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

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.


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,
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.