| Author |
Topic |
|
mynkow
Starting Member
12 Posts |
 Posted - 2008-10-05 : 04:44:07
|
| Hi,I have a table with 3 million records. The table holds name of cities all over the world. I have columns: ID,City,Country_Code, ... etc. I will search within this table using the 3 columns. For ex. to get city by ID or City. Another ex. is to get all cities for one country_code. What indexes I need to create and speed up the searching there?Thank you! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-05 : 04:46:01
|
| Do you currently have clustered index on your table? |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-10-05 : 04:56:12
|
| The basic answer is that you need an index starting with whatever you search on.Search on city, then you need an index that at least starts with city etc. If you want to get it from ID then index ID. For the last one, index country code (maybe).I'm not sure why visakh16 is asking about clustered indexes, put probably you want that on ID. Perhaps if you do not do many updates it would be worth a clustered index on country but you'll have to read up to see if that makes sense for your requirement. |
|
|
|
mynkow
Starting Member
12 Posts |
Posted - 2008-10-05 : 05:04:00
|
| Hi,there will be no updates in that table. Currently I do not have any indexes. I tried to create one but it messed my master table in the MS SQL server and I had to reinstall everything. Can you help me with some code to do this? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-05 : 06:11:03
|
quote:
Originally posted by mynkow
Hi,there will be no updates in that table. Currently I do not have any indexes. I tried to create one but it messed my master table in the MS SQL server and I had to reinstall everything. Can you help me with some code to do this?
i think you need non clustered indexes on City & Country fields.But you need to consider how frequently you will be searching on a field before putting an index. ALso how frequently and what amount of records will be inserted into your table. Having too much indexes on your table will have an impact on insert operations. Also i've heard most cases presence of just non clustered indexes without a clustered index performs better than cases where you've a clustered index being present.for syntax, have a look at CREATE INDEX statement in books onlinehttp://doc.ddart.net/mssql/sql70/create_2.htm |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-05 : 11:20:41
|
quote:
Originally posted by visakh16
quote:
Originally posted by mynkow
Hi,there will be no updates in that table. Currently I do not have any indexes. I tried to create one but it messed my master table in the MS SQL server and I had to reinstall everything. Can you help me with some code to do this?
i think you need non clustered indexes on City & Country fields.But you need to consider how frequently you will be searching on a field before putting an index. ALso how frequently and what nmuch indexes on your table will have an impact on insert operations. Also i've heard most cases presence of just non clustered indexes without a clustered index performs better than cases where you've a clustered index being present.for syntax, have a look at CREATE INDEX statement in books onlinehttp://doc.ddart.net/mssql/sql70/create_2.htm
I would never recommend doing it because without clustered index you table is a heap. There is no way you can defrag heap table. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-05 : 13:26:26
|
quote:
Originally posted by sodeepI would never recommend doing it because without clustered index you table is a heap. There is no way you can defrag heap table.
this was something that was provided to me in support of that statementhttp://www.simple-talk.com/community/blogs/robertchipperfield/archive/2007/12/17/40766.aspx |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-05 : 13:39:50
|
quote:
Originally posted by visakh16
quote:
Originally posted by sodeepI would never recommend doing it because without clustered index you table is a heap. There is no way you can defrag heap table.
this was something that was provided to me in support of that statementhttp://www.simple-talk.com/community/blogs/robertchipperfield/archive/2007/12/17/40766.aspx
The fact that I have mentioned is clearly mentioned in the comment section of that article. |
|
|
|
mynkow
Starting Member
12 Posts |
Posted - 2008-10-06 : 02:39:45
|
| Ok. Let me summarize.1. I will never update/insert/delete from my table.2. I will use the next columns for searching purposes: ID, City, Country_Code (there are other columns but they are not important)3. The table has more than 3 000 000 records.So, what indexes I need for best performance except powerful PC/server? |
|
|
|
nakuvalekar
Starting Member
7 Posts |
Posted - 2008-10-06 : 03:48:58
|
| Creating index is critical for big data table.What are the data types of three fields? If ID field is of int type then create clustered index on ID field.And create non clustered index on Country_Code.You can use Index tunning wizard for creating index.How to use this?1. Run the query in query analyzer(example: SELECT ID, City, Country_ID FROM CITY WHERE ID = 'value')2. Use the index tuning wizard from Query menu.3. It will guider you and gives performance statistics.Hope it will you. |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-10-06 : 06:34:15
|
| If you will never update table then there is no down side to having lots of indexes (assuming you have plenty of hard-disk space). You could create lots of covering indexes, i.e take each full select statement and create an index for it. This will mean that the total query is indexed. If you have SQL server 2005 you could use the "includes" statement in index. Alternatively create indexed Views to capture all your queries. Be sure to use the "no expand" hint or query optimiser will ignore indexed view when running queries and also use "Schemabinding" option. |
|
|
|
mynkow
Starting Member
12 Posts |
Posted - 2008-10-06 : 08:42:38
|
quote:
Originally posted by nakuvalekar
Creating index is critical for big data table.What are the data types of three fields? If ID field is of int type then create clustered index on ID field.And create non clustered index on Country_Code.You can use Index tunning wizard for creating index.How to use this?1. Run the query in query analyzer(example: SELECT ID, City, Country_ID FROM CITY WHERE ID = 'value')2. Use the index tuning wizard from Query menu.3. It will guider you and gives performance statistics.Hope it will you.
ID int(autoincrement)City nvarchar(500) believe or not there is a city in England with name lenght 255 chars ;)Country_Code char(2)If you are creating indexes for these columns how will they look? As I said earlier I messed my master table in SQL server and I do not want to take risk again with my code. The search will be like getting one city by ID or City and all cities grouped by one Country_Code. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-06 : 09:52:48
|
quote:
Originally posted by mynkow
quote:
Originally posted by nakuvalekar
Creating index is critical for big data table.What are the data types of three fields? If ID field is of int type then create clustered index on ID field.And create non clustered index on Country_Code.You can use Index tunning wizard for creating index.How to use this?1. Run the query in query analyzer(example: SELECT ID, City, Country_ID FROM CITY WHERE ID = 'value')2. Use the index tuning wizard from Query menu.3. It will guider you and gives performance statistics.Hope it will you.
ID int(autoincrement)City nvarchar(500) believe or not there is a city in England with name lenght 255 chars ;)Country_Code char(2)If you are creating indexes for these columns how will they look? As I said earlier I messed my master table in SQL server and I do not want to take risk again with my code. The search will be like getting one city by ID or City and all cities grouped by one Country_Code.
which is that city? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-06 : 11:07:43
|
Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch is found in Anglesey, North Wales.Still oly 58 characters.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-06 : 11:10:33
|
A city in Thailand has 163 charactersKrungthepmahanakornamornratanakosinmahintarayutthayamahadilokphopnopparatrajathaniburiromudomrajaniwesmahasatharnamornphimarnavatarnsathitsakkattiyavisanukamprasit
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-06 : 11:12:50
|
http://everything2.com/index.pl?node_id=1534419
E 12°55'05.63"N 56°04'39.26" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-06 : 11:46:51
|
quote:
Originally posted by Peso
Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch is found in Anglesey, North Wales.Still oly 58 characters.
E 12°55'05.63"N 56°04'39.26"
wow! what a name. I had been to England but have never heard of this place at all.Might take a while before i pronounce it correctly and would take much longer to memorise the spelling, if at all i could.  |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-06 : 11:50:46
|
quote:
Originally posted by Peso
http://everything2.com/index.pl?node_id=1534419
E 12°55'05.63"N 56°04'39.26"
Nice link |
|
|
|
mynkow
Starting Member
12 Posts |
Posted - 2008-10-07 : 02:17:07
|
| OMG, please be serious. Help me with the F***n indexes. |
|
|
|
nakuvalekar
Starting Member
7 Posts |
Posted - 2008-10-07 : 05:20:13
|
quote:
Originally posted by mynkow
quote:
Originally posted by nakuvalekar
Creating index is critical for big data table.What are the data types of three fields? If ID field is of int type then create clustered index on ID field.And create non clustered index on Country_Code.You can use Index tunning wizard for creating index.How to use this?1. Run the query in query analyzer(example: SELECT ID, City, Country_ID FROM CITY WHERE ID = 'value')2. Use the index tuning wizard from Query menu.3. It will guider you and gives performance statistics.Hope it will you.
ID int(autoincrement)City nvarchar(500) believe or not there is a city in England with name lenght 255 chars ;)Country_Code char(2)If you are creating indexes for these columns how will they look? As I said earlier I messed my master table in SQL server and I do not want to take risk again with my code. The search will be like getting one city by ID or City and all cities grouped by one Country_Code.
|
|
|
|
Next Page
|
Help me with indexes