Just curious, I never care to use table index before.

when setting up table, just set primary key and done.

Is index really speed up table? Cause up till now i didn't notice the
difference between using and not using one.

Thanks
Erge

In article <443c50b7@forums-1-dub>, ergeoce (AT) gmail (DOT) com says...
An index *can* increase the speed dramatically ... if the situation can take advantage of it.

The "situation" means

Consider some thousands of customers who buy things.

You'd have a table customers with

customer_id integer default autoincremnet primary key

and a table oders with

order_id integer default autoincrement primary key.

Now you link those by adding a column to the orders to track them

fk_customer_id integer not null references (customers)

Now, ASA automatically creates an index on orders.fk_customer_id ... why?

There are at least 2 question you want to pose tot he db.

"I have an order, who's the customer?" The answer to this is to search customers.customer_id ... becuase this is the primary key, it's fast.

"I have a customer, what are his orders?" Ths answer now scans the entire orders table for orders.fk_customer_id = customers.customer_id.

Could you imagen how slow this could become if there where no index on orders.fk_customer_id? This is why ASA automatically generates an index on foreign keys.

Understanding this makes clear: Whenever you have questions to the db which must be answered by scanning through a table you know that this scanning can greatly benefit from an index on that column.

However, using an index on a column "sex" does not give a real benefit because it usually only has two values, so in principle 50% of the table are hits. An index would not improve this situation.

An index should have many different values to be of any value. The exact ratio where it is not longer of use is something the query optimiser has to answer when it executes a query ... or any real
cracks in here ...

Howerver, any index consumes some processing power because it must be maintained. The situation gets worse if there are lots of insert / update / deletes in the underlying table.

Each of those actions requires the index to be reorganised. So, too many indices will probably improve some queries, but slow down some DDL commands.

If you are not quite sure whether your situation could benefit from an index, ASA offers the Index Assistent which can help you determine the need for additional indices.


HTH,

Martin Baur

MindPower, IT-Services

Martin Baur wrote:
Martin, great summary of indexes!

Erge, please always include the version and build number of SQLAnywhere
that you are using (e.g. use the command "dbeng9 -v" for SQLAnywhere 9.x).

The only correction I offer is that the tool is called the "Index
Consultant". It was originally offered in 9.0.0. See the online docs
for more information.

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

There isn't much to add after Martin's comment, but reducing the explanation
to a single SELECT statement might help too:

Let us assume we have a table "shares" with columns "entryid", "customerid"
and "share". The table has 10 000 000 rows.

Now you want to find someone who has 12 459 shares (not very real question,
but let us assume I want to know). You issue a statement:

SELECT * FROM shares WHERE share = 12 459;

what does the database engine do? Looks into the .db file and goes through
EVERY OF 10 000 000 ROWS and on each row, it compares - is share 12 459? If
yes, the row is added to the result set, if not, the database goes onto the
next row. This is called a full table scan and be really a pain in the ass
(can take approx. 10-15 or more minutes to complete).

An INDEX is a database object that is created with a column or concatenation
of columns and it's purpose is to keep POINTERS to table rows sorted by this
column.

So we create an INDEX upon column "share" in table "shares" - for 10 000 000
rows, the creation of the index will take a fine while. But after it's
completed, we have it - 10 000 000 of sorted pointers to rows in the
original table, sorted by the "shares" column.

The index is created - now we issue the very same SELECT statement as
before:

SELECT * FROM shares WHERE share = 12 459;

The optimizer takes a look at the statement and sees that the returned rows
depend heavily (and only) on "share" column... so it checks if there's an
index on that column - and yes, there is!

Imagine yourself to search for "share = 12 459" rows in a large book (aka
table) with 10 000 000 lines. You would have to go through every line and
check if share = 12 459, that's checking 10 000 000 lines, what would be a
lifelong amusement....

Now, imagine that you have another, smaller book (aka index) which is ALWAYS
perfectly in shape with the original, large book. The smaller book contains
shares only, but sorted!! So you will find the 12 459 very quickly, you will
check the pointer (the page in large book which contains the entire row with
share 12 459 and add that row from large book to result paper) and you will
have absolute guarancy that no other rows with such share exist.

So the optimizer sees that there is an index on the "share" column - and
using logics compared to human logics, it derives the result set almost
immediately using that index (uncomparably faster than before).

That's why indexes are very imporant, but:
1. since they have slight performance disadvantages, they have to exist on
absolte necessary columns only
2. there are syntax watchouts in SELECT statements which often disallow
usage of an index (for example: WHERE lastname LIKE 'Bro%' uses an index on
"lastname" column, but WHERE lastname LIKE '%wn' does not allow usage of the
same index)
3. the performance disadvantages appear from INSERT and DELETE statements,
because during INSERT to a table, the a new pointer to that row has to be
inserted into the index (or all of the indexes when table has more than 1
index) and since there's a small search for WHERE to insert the pointer into
index, because the index ALWAYS must be sorted, it takes a very very very
very very little something longer than an INSERT into a table which does not
have an index. The same applies for DELETE. UPDATEs take even somewhat
longer, because no update exists for indexes, indexes treat updates as
delete-and-then-insert.
4. there's not much real advantage of multicolumn indexes unless you are
sure that your complex WHERE caluse will benefit from then
5. primary and foreign keys automatically create indexes

There's much, much more to say to the beautiful art of indexes (B-TREE
indexes, what's the mechanism of searching, processes of optimizer not
visible to user, mechanisms behind index operations, how are index pages
treated compared to table pages, performance benefit/cost statistics, etc.
etc. etc.) but time is really an essence. I recommend an *excellent* book on
this: Dan Tow's SQL Tuning.

Hope this helped somehat
Pavel

big thanks to all of you...

now I know the *real* power of index

cheers,
Erge


"Pavel Karady" <pavel_ns.ns_karady (AT) ns_kogerusa (DOT) com> wrote

Pavel

You made the statement :
"5. primary and foreign keys automatically create indexes"

I have tables that have a primary key and also a unique index on the same columns as those in the primary key. Occasionally I get "duplicate index" messages when working with some tables, and sometimes I can't drop the unique index - it just hangs, due to locking issues!

If I look in SYSINDEXES I only see the details for the unique index - where do I find the details for the primary key index (if, for instance, I dont have a unique index)?
Based on your statement then I shouldn't need the unique indexes on each of the tables??

Thanks
Paul

Pavel

You made the statement :
"5. primary and foreign keys automatically create indexes"

I have tables that have a primary key and also a unique
index on the same columns as those in the primary key.
Occasionally I get "duplicate index" messages when working
with some tables, and sometimes I can't drop the unique
index - it just hangs, due to locking issues!

If I look in SYSINDEXES I only see the details for the
unique index - where do I find the details for the primary
key index (if, for instance, I dont have a unique index)?
Based on your statement then I shouldn't need the unique
indexes on each of the tables??

Thanks
Paul

On 13 Apr 2006 06:02:11 -0700, paulr wrote:

Any connection that has worked with table X (even SELECT) and not
committed will have a "schema lock" on table X that will block schema
changes on table X... and DROP INDEX qualifies as such.

You can use Block Sniffer to find and drop those connections
http://www.risingroad.com/block_sniffer.html
or use p_drop_other_connections (search this newsgroup for the source
code), or just walk around the department yelling "COMMIT! COMMIT!"

ISQL is the *worst* offender for holding long-lasting locks, not just
schema locks, and it occasionally causes inter-developer strife.

In Jasper the primary key indexes will be more "visible" in the system
catalog tables. In Version 9 and earlier, these indexes certainly
exist but are not recorded in SYSINDEX. In Version 9 the information
is spread across SYSCOLUMN, SYSCONSTRAINT and SYSATTRIBUTE, with the
important clue in V9 and earlier being SYSCOLUMN.pkey = 'Y'.

Exactly right (assuming you are using V7 or later; the situation in
earlier versions is somewhat murkier).

FWIW Foxhound also shows duplicate indexes, with explanations and
suggestions in the context-sensitive Help
http://www.risingroad.com/foxhound_beta_3.html

Breck

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

FWIW, Jasper will include the ability to share physical data amongst
identical indexes, so the server will no longer complain about duplicate
indexes. It may, in fact, be beneficial to go ahead and declare additional
RI constraints on the same set of columns where they make sense.

There are some additional changes w.r.t. indexes and keys that application
designers should find helpful.

-anil

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter (AT) risingroad (DOT) com> wrote in
message news:abis32ho5kgaah6ub9u6q2nqrvtg2lntqs (AT) 4ax (DOT) com...