Quicker retrievement

[xxmp]

Hello
I have these database
countries
->country_id
->country_name
->language
counties
->county_id
->county_name
->language
towns
->town_id
->town_name
->language
locations
->location_id
->location_name
->language
streets
->street_id
->street_name
->language
and i have users that select country,county,town,location and optional street at a selected language and i want to show the show the informations of address quicker than joining all these tables how i can do this? Using index that has all the informations?
Thank you very much

[LastMitch]

and i have users that select country,county,town,location and optional street at a selected language and i want to show the show the informations of address quicker than joining all these tables how i can do this?

Maybe I misunderstood your question. You want to fetch the data quicker by using a more efficient query than the one you have now?

what is your query? Post your query.

[xxmp]

The query i have now is:

SELECT * FROM htlsite.thecountries
JOIN htlsite.thecounties
ON htlsite.thecountries.country_id=1 AND htlsite.thecounties.county_id=1 AND htlsite.thecountries.language='en' AND htlsite.thecountries.language=htlsite.thecounties.language
JOIN htlsite.thetowns
ON htlsite.thetowns.town_id=1 AND htlsite.thetowns.language=htlsite.thecountries.language
JOIN htlsite.thelocations
ON htlsite.thelocations.location_id=1 AND htlsite.thelocations.language=htlsite.thetowns.language
LEFT JOIN htlsite.thestreets
ON htlsite.thestreets.street_id=1 AND htlsite.thestreets.language=htlsite.thecountries.language

but i do not have any indexes for now. Will be better to change the shema of databases or use indexes?

[LastMitch]

but i do not have any indexes for now. Will be better to change the shema of databases or use indexes?

I would index it. You have to understand the query doesn't really affect how fast you fetch the data.

[xxmp]

Do you think i should change the schema? I am adding countries, counties, towns,locations and streets on demand

[LastMitch]

Do you think i should change the schema? I am adding countries, counties, towns,locations and streets on demand

Why? Why do you want to change th schema when you have everything in place? The only column that all the table have is language. So the query is fine. Index does take alot of space but if the tables you provided is all the table you have ... then it's fine.

[diafol]

Indexing will help when you select specific data, usually based on your 'where' or 'having' clauses. If you're not filtering by any of those fields, then I don't think that indexing will necessarily help you.

Your query looks really odd though. When you join related tables, you usually join on the common field. Example:

SELECT c1.continent, r.region, c2.country FROM continent AS c1 
    INNER JOIN regions AS r 
        ON c1.continent_id = r.continent_id 
            INNER JOIN countries AS c2 
                ON r.region_id = c2.region_id
    ORDER BY continent, region, country

[xxmp]

Thank you very much for your help