I have these database
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
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?
SELECT * FROM htlsite.thecountries
ON htlsite.thecountries.country_id=1 AND htlsite.thecounties.county_id=1 AND htlsite.thecountries.language='en' AND htlsite.thecountries.language=htlsite.thecounties.language
ON htlsite.thetowns.town_id=1 AND htlsite.thetowns.language=htlsite.thecountries.language
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?
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.
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