1,105,625 Community Members

Quicker retrievement

Member Avatar
xxmp
Junior Poster in Training
90 posts since Jan 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Deleted Member
 
0
 

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.

Member Avatar
xxmp
Junior Poster in Training
90 posts since Jan 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Deleted Member
 
0
 

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.

Member Avatar
xxmp
Junior Poster in Training
90 posts since Jan 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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

LastMitch
Deleted Member
 
0
 

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.

Member Avatar
diafol
Where are my eyes?
12,983 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
0
 

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
Member Avatar
xxmp
Junior Poster in Training
90 posts since Jan 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

Thank you very much for your help

Question Answered as of 9 Months Ago by LastMitch and diafol
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: