indexing question

I'm a bit unsure about a topic after reading the ASA 9 documentation: 
automatically generated indexes with respect to primary keys and foreign keys 
constraints.  The documentation suggests that ASA 9 engine automatically indexes 
both primary and foreign key columns upon creation of the constraints.  In the 
past we have practiced creation of foreign key column indexes manually, but now 
I am thinking this practice may now be counter-productive in terms of now 
forcing the database to maintain two indexes on the same set of 'foreign key' 
columns.

Am I correct to assume that these manual indexes we create are redundant and 
degrading our database's performance?

Anyone have any comments on the topic?

Phil
0
Phil
12/18/2003 2:10:53 PM
sybase.sqlanywhere.general 32637 articles. 22 followers. Follow

6 Replies
412 Views

Similar Articles

[PageSpeed] 53
Get it on Google Play
Get it on Apple App Store

Yeah, pretty much, re: "degrading performance". A redundant index has
a cost but no benefit.

Breck

On 18 Dec 2003 06:10:53 -0800, Phil <phil@nospamthanks.com> wrote:

>I'm a bit unsure about a topic after reading the ASA 9 documentation: 
>automatically generated indexes with respect to primary keys and foreign keys 
>constraints.  The documentation suggests that ASA 9 engine automatically indexes 
>both primary and foreign key columns upon creation of the constraints.  In the 
>past we have practiced creation of foreign key column indexes manually, but now 
>I am thinking this practice may now be counter-productive in terms of now 
>forcing the database to maintain two indexes on the same set of 'foreign key' 
>columns.
>
>Am I correct to assume that these manual indexes we create are redundant and 
>degrading our database's performance?
>
>Anyone have any comments on the topic?
>
>Phil

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com
0
Breck
12/18/2003 2:29:14 PM
"Phil" <phil@nospamthanks.com> ???????/???????? ? ???????? ?????????:
news:3fe1b56d$1@forums-1-dub...

> I'm a bit unsure about a topic after reading the ASA 9 documentation:
> automatically generated indexes with respect to primary keys and foreign
keys

   In ASA, primary and foreign keys ARE indexes (since day one, I think).

> constraints.  The documentation suggests that ASA 9 engine automatically
indexes
> both primary and foreign key columns upon creation of the constraints.  In
the
> past we have practiced creation of foreign key column indexes manually,
but now
> I am thinking this practice may now be counter-productive in terms of now
> forcing the database to maintain two indexes on the same set of 'foreign
key'
> columns.

   Additional indexes on PK and FK probably were of some help in SA 5.5
days, but are useless now. Here is an answer from Robert Waywell:

-----------------------------------------------
These indexes were useful in 5.5.x due to a difference in the PK-FK index
structure. In 5.5.x FK references were stored in the PK structure which
bulked up the index structure, particularly in cases of large fanout. In
that case having a separate index over the the same columns as the PK was
beneficial since it gave us an alternate index with less bulk to work with.

FK references are still handled via an index, but they are no longer part of
the PK index structure.
-----------------------------------------------

> Am I correct to assume that these manual indexes we create are redundant
and
> degrading our database's performance?

   Yes, and server will complain about Duplicate Index when it encounters
something useless for it.

Dmitri.


0
Dim
12/18/2003 2:50:32 PM
Hi,

Is it possible **avoid** this FK implicit index creation  ?
I think that often it could be needless .....
If i remember well, MS SqlServer require an explicit index on FKeys .....

Thanks.
Nicola

"Dim" <NOdimSPAM@mail15.com> ha scritto nel messaggio
news:3fe1c081@forums-2-dub...
> "Phil" <phil@nospamthanks.com> ???????/???????? ? ???????? ?????????:
> news:3fe1b56d$1@forums-1-dub...
>
> > I'm a bit unsure about a topic after reading the ASA 9 documentation:
> > automatically generated indexes with respect to primary keys and foreign
> keys
>
>    In ASA, primary and foreign keys ARE indexes (since day one, I think).
>
> > constraints.  The documentation suggests that ASA 9 engine automatically
> indexes
> > both primary and foreign key columns upon creation of the constraints.
In
> the
> > past we have practiced creation of foreign key column indexes manually,
> but now
> > I am thinking this practice may now be counter-productive in terms of
now
> > forcing the database to maintain two indexes on the same set of 'foreign
> key'
> > columns.
>
>    Additional indexes on PK and FK probably were of some help in SA 5.5
> days, but are useless now. Here is an answer from Robert Waywell:
>
> -----------------------------------------------
> These indexes were useful in 5.5.x due to a difference in the PK-FK index
> structure. In 5.5.x FK references were stored in the PK structure which
> bulked up the index structure, particularly in cases of large fanout. In
> that case having a separate index over the the same columns as the PK was
> beneficial since it gave us an alternate index with less bulk to work
with.
>
> FK references are still handled via an index, but they are no longer part
of
> the PK index structure.
> -----------------------------------------------
>
> > Am I correct to assume that these manual indexes we create are redundant
> and
> > degrading our database's performance?
>
>    Yes, and server will complain about Duplicate Index when it encounters
> something useless for it.
>
> Dmitri.
>
>


0
Nicola
12/18/2003 4:10:56 PM
Nicola Cisternino wrote:
> Hi,
> 
> Is it possible **avoid** this FK implicit index creation  ?
> I think that often it could be needless .....
> If i remember well, MS SqlServer require an explicit index on FKeys .....
> 
> Thanks.
> Nicola
> 
> "Dim" <NOdimSPAM@mail15.com> ha scritto nel messaggio
> news:3fe1c081@forums-2-dub...
> 
>>"Phil" <phil@nospamthanks.com> ???????/???????? ? ???????? ?????????:
>>news:3fe1b56d$1@forums-1-dub...
>>
>>
>>>I'm a bit unsure about a topic after reading the ASA 9 documentation:
>>>automatically generated indexes with respect to primary keys and foreign
>>
>>keys
>>
>>   In ASA, primary and foreign keys ARE indexes (since day one, I think).
>>
>>
>>>constraints.  The documentation suggests that ASA 9 engine automatically
>>
>>indexes
>>
>>>both primary and foreign key columns upon creation of the constraints.
> 
> In
> 
>>the
>>
>>>past we have practiced creation of foreign key column indexes manually,
>>
>>but now
>>
>>>I am thinking this practice may now be counter-productive in terms of
> 
> now
> 
>>>forcing the database to maintain two indexes on the same set of 'foreign
>>
>>key'
>>
>>>columns.
>>
>>   Additional indexes on PK and FK probably were of some help in SA 5.5
>>days, but are useless now. Here is an answer from Robert Waywell:
>>
>>-----------------------------------------------
>>These indexes were useful in 5.5.x due to a difference in the PK-FK index
>>structure. In 5.5.x FK references were stored in the PK structure which
>>bulked up the index structure, particularly in cases of large fanout. In
>>that case having a separate index over the the same columns as the PK was
>>beneficial since it gave us an alternate index with less bulk to work
> 
> with.
> 
>>FK references are still handled via an index, but they are no longer part
> 
> of
> 
>>the PK index structure.
>>-----------------------------------------------
>>
>>
>>>Am I correct to assume that these manual indexes we create are redundant
>>
>>and
>>
>>>degrading our database's performance?
>>
>>   Yes, and server will complain about Duplicate Index when it encounters
>>something useless for it.
>>
>>Dmitri.
>>
>>
> 

This is the whole issue we have.

Our program is supported on 3 platforms, Oracle, ASA, and MS SQL Stupid and we 
try to be consistent with our design on all three platforms, but if some vendors 
give you an index on fk columns while others don't, then we need to maintain 3 
different versions of our database schema, which makes it a schema maintenance 
and upgrade nightmare.

Anyway, we are going to continue to maintain a manually created index on foreign 
key columns regardless, and put up with the overhead and performance hit on our 
ASA databases (ASA can handle it, as all of our ASA clients maintain small 
databases as compared to clients using the other two vendors db's).

Just for the record, I think the auto-generated indexes are great and wish all 
databases supported the concept, especially considering most vendors that I know 
of auto-create a primary key column index, it would have just made sense to 
extend that to the foreign key constraint, as Sybase did.

Just my $0.02

Phil
0
Phil
12/18/2003 4:40:28 PM
BTW, I have *occasionally* found that creating an index on a PK is very
useful, but these instances are only when the ordering of the columns in a
PK are "really wrong".  I have since written a utility that goes through all
multiple key FK's/PK's and rearranges the columns in the reload.sql file,
but having to go through that is a rathar nasty process, and it has to be
done regularly (every several months or so)

"Dim" <NOdimSPAM@mail15.com> wrote in message news:3fe1c081@forums-2-dub...
> "Phil" <phil@nospamthanks.com> ???????/???????? ? ???????? ?????????:
> news:3fe1b56d$1@forums-1-dub...
>
> > I'm a bit unsure about a topic after reading the ASA 9 documentation:
> > automatically generated indexes with respect to primary keys and foreign
> keys
>
>    In ASA, primary and foreign keys ARE indexes (since day one, I think).
>
> > constraints.  The documentation suggests that ASA 9 engine automatically
> indexes
> > both primary and foreign key columns upon creation of the constraints.
In
> the
> > past we have practiced creation of foreign key column indexes manually,
> but now
> > I am thinking this practice may now be counter-productive in terms of
now
> > forcing the database to maintain two indexes on the same set of 'foreign
> key'
> > columns.
>
>    Additional indexes on PK and FK probably were of some help in SA 5.5
> days, but are useless now. Here is an answer from Robert Waywell:
>
> -----------------------------------------------
> These indexes were useful in 5.5.x due to a difference in the PK-FK index
> structure. In 5.5.x FK references were stored in the PK structure which
> bulked up the index structure, particularly in cases of large fanout. In
> that case having a separate index over the the same columns as the PK was
> beneficial since it gave us an alternate index with less bulk to work
with.
>
> FK references are still handled via an index, but they are no longer part
of
> the PK index structure.
> -----------------------------------------------
>
> > Am I correct to assume that these manual indexes we create are redundant
> and
> > degrading our database's performance?
>
>    Yes, and server will complain about Duplicate Index when it encounters
> something useless for it.
>
> Dmitri.
>
>


0
Erik
12/18/2003 5:56:05 PM
"Nicola Cisternino" <ncister@cointa.it> wrote:

> Is it possible **avoid** this FK implicit index creation  ?

   There is nothing to avoid. There is no additional implicit index on FK,
FK is an index (although a special one) by itself.

> I think that often it could be needless .....
> If i remember well, MS SqlServer require an explicit index on FKeys .....

   MS way of doing things is not always the only right one...

Dmitri.



0
Dim
12/19/2003 7:33:29 AM
Reply:

Similar Artilces:

To Index or not to Index (that is the question...)
I'd like to clear up an assumption I have. If I have a Primary Key Constraint on a Table I assume that I will not need to create an Index on the field(s) of the constraint as the action of creating the primary key constraint produces what is the equivalent of an Index (be the constraint clustered or not). Is this the case? Also I'm looking to Pivot some data, are there any SQL tricks etc. I should be aware of to be able to do this (I've searched the Newsgroups for 'Pivot' but don't understand any of the answers posted - some SQL examples would be gr...

Basic Index question :- nonclusted indexes which are a subset of a clustered index
Can anyone suggest why I would have the following set of indices upon a table: index1 clustered, unique field1,field,field3,fiel4 index2 nonclustered field2,field3, field4 index3 nonclustered field,field2,field4 The table is one into which a lot of inserts are done, so I would have thought that index2 & 3 would be unncessary since they're (1) already in the clustered index and (2) any inserts would triple the amount of index updates that are required... Are these assumptions correct ? Thanks in advance, Steve Hi, if you have a...

To Index or Not To Index or How To Index
Hello, I was wondering if someone knows a better way to explain how different indexes work. Such as, is it better to have an index with int type or char or varchar etc? Is it better to have composite indexes or indexes with individual columns? How does optimizer handle different indexes and what can we do help it? Indexes are very efficient most of the time but they can be quite detrimental to server's performance, if abused. Perhaps you know of a good article about indexing that you could share with us. Or, perhaps Mr. Rob Verschoor would like to issue an article in his very usefu...

MS Index Server, Web Page Searches
Greetings: I have the service set up and running and I'm able to do searches on my website and return the results to a search page. The basics are fine and up and running. Question: I find when I publish the website if the '[x] Make site updateable' checkbox isn't checked the .aspx pages do not show up in a search. Is it true that the site must be published as updateable in order to make the MS Indexer work?  Am I sacrificing security in order to get the pages indexed?  Is there a way to publish the site as not updateable and still have the indexer work?  I...

Questions about Indexing and Using an Indexing POA
Although I have only about 50 users, at least 15 of them have in excess of 100,000 messages in their accounts and the POA (version 7.0.2) is regularly slowing to a crawl. (I just know that plans for revolution are fomenting!) I have embarked on a campaign to reduce these accounts by archiving everything off to get mail accounts down to 3000 or fewer pieces. I have achieved user buy-in, but have worked on only a few users so far. In another closely related thread, it was suggested to me that the PO speed issues relate to broken indexes. And I suspect that given so many messages...

automate form question and general http questions
Dear all, There are lots questions. I appologize at first. I am trying to automate a form submission. in html the form has <select name=p2 size=1 onChange="showimage()"><option value="01">123</option> <input type="radio" value="" checked name="show">001 <input type="radio" value="0" name="show">001 i am wondering can i do it as $ua->post($url,[p2=>'01',show=>'0']); which corresponds to choose option '123' and radio '001'? or...

anim_parrot_logo.imc: .include question + general imcc questions
Hello, While looking at Chromatic's anim_parrot_logo.imc (in examples/sdl), I was wondering why the includes weren't at the same place. Indeed, the source reads: ..sub _main _init() _MAIN() end ..end ..include "library/sdl_types.imc" ..pcc_sub _init prototyped .include "library/sdl.pasm" .pcc_begin_return .pcc_end_return ..end Why does: ..sub _main _init() _MAIN() end ..end ..include "library/sdl_types.imc" ..include "library/sdl.pasm" fails with: error:imcc:parse error, unexpec...

windows ME questions, questions, questions.......
Hi, my next PC will be running Windows ME. The PC will be supplied via my work, so there's no choice here for me (ME?) (I think I would have preferred 98 SE). The harddisk (40GB matrox) will have ME installed, and both the Windows ME set-up files and an image of the initial Harddisk 'on a hidden partition'. Word has it that this partition is not seen by Format nor FDisk. Is this really possible? No Windows CD will be supplied. Seems a new way of MS to encourage working with licensed software only. Anyone familiar with this way of distributing an OS? Will I be able to ...

general question
Hello everyone, I wonder, how many people can access a simple order webform simultaneously without causing a server crash?? And another question for the pro's. How many users can you  manage using the membership and profiles using ASP.NET?? regards Cheers >I wonder, how many people can access a simple order webform simultaneously without causing a server crash??Effectively unlimited in theory if all session state state is handled in viewstate. If handled in memory then memory will constrain. In in SQL State, the capacity of the database. There is also a lim...

Index question
If I have a table with a foreign key into another table, will the optimizer use that as an index for other purposes as well, such as joins to other tables (not the one the foreignkey is referring to) or 'where' clauses. Or do I need to put in an explicit index for those purposes? The reason I'm asking is that a query which uses a join into this table was giving me a sequential scan of this table even though there was an index available in the form of the foreign key. I can't tell if that's just because the optimizer thought this would be faster (since it's ...

Some general questions....
Dear all, I am at the end of my dev cycle, and to be honest my knowlegde about database's is kinda limited. I am using Sybase Sql Anywhere 5.5.04 build 1867. Suppose i get my first customer that buys the product (PB app). 1. Do i just make a copy of our db and place at customer's site ? Or can i extract some creating scripts from the existing db, that will create the db (tables) at their site ? How do i do this ? 2. All our data is company dependant, so a client can have multiple companies, maybe i can write a small pb application that reads in txt files (extr...

General Questions
hi all I am having trouble finding answer for the following questions if anyone knows any answers from these questions please do reply as soon as possible! thanks 1. What is the PFC Application Manager and how is used in PFC applications? And, describe the details of the relationship of the PowerBuilder Application Object to the PFC Application Manager. 2. Describe the steps required to create a MDI application using PFC from scratch in sequence and where appropriate indicate why the sequence is necessary. (At least, one complete sentence per step.) Where and how c...

general question
Hello In my window i have 4 dw which have a master/detail - relation top down. All dw have tabular style and are not editable. On doubleclicking a row I open a window which shows this dw in freeform style. I'm not sure if it is better to pass a reference of the dw to the window and to change the values (I think that doesn't meet the criteria of data encapsulation) or to copy the rows of the dw into another dw, pass it into the window, there copy it to the freeform dw and do the same at returning from the window (=much more to code). thanks for ideas Martin, ...

general question
Hi i am allitle bit confused. is the asp.net SDK differenet from the .Net framework sdk...or is it part of the .Netframe work sdk...... Another question, i am using the MS VS.net family member (VB.net) which says version 2002 on the package, is there a new VS.net family member (VB.net) out. Hello, there is now the VS.NET 2003, a more updated version of VS.nET 2002. you have the .NET framework sdk, where you can run asp.net scripts. regards.Bilal Hadiar, MCP, MCTS, MCPD, MCTMicrosoft MVP - Telerik MVP hello, sorry i forgot to tell you about this website, it helps you unders...

Web resources about - indexing question - sybase.sqlanywhere.general

Latent semantic indexing - Wikipedia, the free encyclopedia
in the early 1970s, to a contingency table built from word counts in documents. Called Latent Semantic Indexing because of its ability to correlate ...

Under the Hood: Indexing and Ranking in Graph Search - Facebook
Facebook Engineering hat eine Notiz mit dem Titel Under the Hood: Indexing and Ranking in Graph Search geschrieben. Du kannst den vollständigen ...

Bing Indexing Faster? - Flickr - Photo Sharing!
www.seroundtable.com/archives/021690.html

English Google App-Indexing office-hours hangout - YouTube
Join us for a *Google office hours hangout about App Indexing* App Indexing connects your website with your smartphone app, so that the app can ...

Apple confirms its ‘Applebot’ is indexing the web for Siri and Spotlight
... … Siri mostly uses Bing and Wolfram Alpha for its web searches, so one could ask the question why Apple would need to do its own indexing of ...

Trends in Wine: Out-Innovated By Beer, Under-Indexing With Hispanics
Is wine losing its buzz? Lately the category has been out-innovated by brewers, who have flooded the market with new beer flavors, brands and ...

Google Extends App Indexing to iOS
... in Chrome , \"in the coming weeks.\" Wald also offered instructions for developers interested in getting their apps indexed: While app indexing ...

Google's App Indexing speeds up switching to apps from Chrome and Google Search
... in the Google Developers Blog, I yawned. When I read deeper, though, and saw what it can do, I got a bit more excited. Google’s App Indexing ...

Coffee With a Googler: Learn about App Indexing and Search
Posted by Laurence Moroney , Developer Advocate. App Indexing helps you get your mobile app found in Google Search. Once your app is indexed, ...

Re-indexing Magento through the command line
... for a whole host of reasons, for example, if the indexer is timing out or not finishing through the web interface. Magento includes an indexing ...

Resources last updated: 12/4/2015 1:22:18 AM