Talk:Database index

This article is rated C-class on Wikipedia's content assessment scale. It is of interest to the following WikiProjects:

Computing This article is within the scope of WikiProject Computing, a collaborative effort to improve the coverage of computers, computing, and information technology on Wikipedia. If you would like to participate, please visit the project page, where you can join the discussion and see a list of open tasks.ComputingWikipedia:WikiProject ComputingTemplate:WikiProject ComputingComputing ??? This article has not yet received a rating on the project's importance scale. This article has been automatically rated by a bot or other tool because one or more other projects use this class. Please ensure the assessment is correct before removing the |auto= parameter. Databases (inactive) This article is within the scope of WikiProject Databases, a project which is currently considered to be inactive.DatabasesWikipedia:WikiProject DatabasesTemplate:WikiProject DatabasesDatabases

Text or other creative content from this version of Index (computer science) was copied or moved into Index (database) with this edit on 15:37, 11 January 2012. The former page's history now serves to provide attribution for that content in the latter page, and it must not be deleted as long as the latter page exists.

I propose this page be moved to "Database index", to avoid the awkward parenthesized subject and because this is a familiar enough unambiguous term. Consent/dissent? Deco 22:18, 9 August 2005 (UTC)Reply

I disagree. Index is an appropriate name for the page, just as "Query (database)" would be for a DB query. I rarely refer to a database index by the term "Database Index", I just call it an index. --Rob 20:02, 26 October 2005 (UTC)Reply

Also disagree. Awkward parenthesized subjects are a way of life here at Wikpedia. :) Turnstep 23:21, 26 October 2005 (UTC)Reply

Ok I'm an expert, what is wrong with this article? I'm going to remove the expert tag and I hope that anyone who puts it back can leave a note as to what is wrong so it can be corrected. Triddle 14:07, 8 October 2005 (UTC)Reply

Hi expert, this is the non-expert. Frankly, the text is too poor (even for me). The email example is too specific of a certain implementation and a fulltext index could solve the problem. Also, it doesn't say anything about multiple indexes, binary indexes, etc. I'm sure there are more index types with different behaviors, but, again, I’m not an expert, so I don't know. :)

Thanks for the comments, those are all good observations. For reference a more proper tag is {{expand}}. I do have one rebuttal though, hopefully if someone comes along to expand this article they can integrate some of this info. First, I don't think the performance of a full-text index is going to match the performance of the reverse trick; add to that the fact that not all databases support full-text indexing out of the box and this trick is pretty useful. Thanks for the comments, I'm sure they will come in useful in the future. Triddle 01:20, 10 October 2005 (UTC)Reply

Expert at what? Relational databases?

"...without having to search every row in a database table every time said table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records."

Many kinds of data structures can be indexed, not only RDB.

Articles for Wikipedia obviously can't write anyone. 2001:1AE9:212:DC05:0:0:0:D (talk) 10:00, 21 June 2023 (UTC)Reply

(Can you tell I've had more than a few questions about these lately?)

1. SQL Standard Examples, or even pseudocode.
2. Effects of indices on standard SQL operations, not just select.
3. Implementations of indices on various database systems

Dunno if I inadvertently missed some of these topics. --Rob 23:35, 26 October 2005 (UTC)Reply

May I also suggest that we at least mention the reason for using'indexes' vs. 'indices'. Though perhaps a minor linguistics/usage distinction, it comes up in IT dept. discussions and matters for things like keyword searches. Rob uses 'indices' in this discussion area, for example, but the entry itself always uses 'indexes'. -- jorvis

• JA: The disambig page for Index gives the standard usage for the two different plurals. Probably this page needs to be brought into line with that. Jon Awbrey 14:58, 15 March 2006 (UTC)Reply

• JA: Oops, that's what I get for trying to work before coffee, I thought the article was using "index" as a synonym for "key". It now seems to be using it for a separate table data structure containing keys (= indices), like an index at the back of a book, in which case "indexes" is correct. Jon Awbrey 15:08, 15 March 2006 (UTC)Reply

I'd also like to suggest that there be some diagrams to accompany examples. — Preceding unsigned comment added by 69.143.93.39 (talk) 16:45, 3 November 2013 (UTC)Reply

The current content of Bitmap index is sparse and much of the page space is occupied by an illustrative data grid. The content would/could appropriately sit as a section of this article; if expansion takes place, Template:main could be used to cross-reference, a solution I prefer over a bare see-also link (currently included here). --User:Ceyockey (talk to me) 19:01, 21 December 2006 (UTC)Reply

If no objections by Saturday, 5/19/2007, I will merge Bitmap index into this article. SqlPac 04:07, 17 May 2007 (UTC)Reply

Bitmap Index deserves to have its own pages. User:oaf2 May 26 2007

The bitmap index article can remain mostly as is, but the In-memory bitmaps section should probably be documented here, because it's an access strategy for any kind of index, not an index storage format. But currently there's nowhere to put it, until this article doesn't document other access strategies. -- intgr [talk] 06:38, 13 October 2009 (UTC)Reply

The article dense index was created earlier today (21 Dec) and is not wikified or categorized. It seems that the content would make a reasonable section in this article, better than a stand alone stub article awaiting clean-up. Regards --User:Ceyockey (talk to me) 19:04, 21 December 2006 (UTC)Reply

Testing to see if i should trust the in for on wikepia. Denecia —Preceding unsigned comment added by 190.59.154.206 (talk) 00:08, 11 March 2010 (UTC)Reply

yes, it'll be nice to merge the dense index and sparse index into this one. we could also add a picture like this one : http://www.e-student.si/data/thumb/6/69/Indeksi_gostota.png/500px-Indeksi_gostota.png to ilusstrate the meaning of both. Stdazi 14:32, 15 April 2007 (UTC)Reply

If no objections by Saturday, 5/19/2007, I will merge Bitmap index into this article. SqlPac 04:07, 17 May 2007 (UTC)Reply

I'm a bit disappointed that this article contains no information on the actual structure and implementation of database indexes, which is a deep but accessible subject. I'll look at adding this unless it's hiding someplace else. Dcoetzee 20:13, 11 May 2007 (UTC)Reply

Excellent suggestion. I know that SQL Server uses B-Tree (or is it B+ Tree?) nonclustered indexes. A discussion of clustered vs. nonclustered indexes, and even so-called "covering indexes", would be appropriate as well. SqlPac 04:07, 17 May 2007 (UTC)Reply

Yes it does, without an example just blabbers some stuff useless. I will get back to fix it.

~rAGU (talk)

How is that coming? —Preceding unsigned comment added by 72.181.253.68 (talk) 06:10, 7 February 2009 (UTC)Reply

I wouldn't go so far as to say it sucks, but it does need more inline citations! — Preceding unsigned comment added by 197.215.247.13 (talk) 01:12, 12 March 2018 (UTC)Reply

HOW COME??? this article uses the terminology TABLE when referring to databases? Indexed Databases are just as easily made of FILES/RECORDS/FIELDS as they are TABLES/ROWS/COLUMNS. My RELATIONAL, ISAM/NoSQL/Embedded Perl Databases are composed of (1) Fixed-length record (no CR/LF) Flat File (.dat extension) databases containing text fields, (2) indexed by binary SDBM files (.pag & .dir file extensions) databases of key/values pairs (tied to program hash tables), for persistent, instantaneous, random access indexing, where the KEY is one or more fields and or partial fields contained within the Flat File records, and the VALUE is the byte offset location of the record relative to either TOP OF FILE (positive integer to 2 Gigabytes) or END OF FILE (negative integer to 2 Gigabytes). The byte offset is used for setting the FILE POINTER before performing READ/WRITE operations. Supported with the "public domain" SDBM databases are: Unique Primary Keys, Unique Alternate Keys, and Alternate Keys with Duplicates. --Anonymous

The article currently describes "clusterED" indexes where I believe that the right term would be "clusterING" indexes: There doesn't have to be anything special about the clustering index inself, but it is affecting how table data should be stored. Thus, clusterING is more appropriate.

Also, the "clusterING" term is used in the following publications, all of which are rather notable database resources:

• Database Systems - The complete book by Garcia-Molina, Ullman & Widom
• Database Tuning by Shasha & Bonnet.
• The DB2 manual

Troels Arvin (talk) 22:58, 22 February 2009 (UTC)Reply

Dictionary.com lists the plural form of index as either indexes or indices. I see this article uses both. I think that one form or the other needs to be picked and used exclusively. Might even be worth noting the multiple plural forms.—NMajdan•talk 14:42, 12 October 2009 (UTC)Reply

"Indices" is only used in 2 places in the article (one of which is a title in a source) so it's obvious that "indexes" has the dominance here. I have changed the article. -- intgr [talk] 06:09, 13 October 2009 (UTC)Reply

"Indices" is the correct plural in the context of computing. "Indexes" is essentially an Americanisation. "Indexes" is correct when used as a verb (He indexes), but the plural form of the noun "index" is "indices". --The nell 87 (talk) 08:13, 6 October 2010 (UTC)Reply

Pretty much every dictionary lists "indexes" as a correct plural form, so you're outright wrong. Whether it's "an Americanisation" or not is irrelevant (sounds like a misguided attmept at discrediting American English?). More importantly, "indexes" is more familiar to people who have seen the word "index", especially to foreign language speakers, so that's what this article should be using. -- intgr [talk] 09:07, 6 October 2010 (UTC)Reply

For me as a non-native speaker its awkward to read indexes. I have looked up English and German dictionaries. Indexes seems to be present in both languages as plural of index. Still it sounds awkward. I prefer 'indices' when I'm talking about database indices, in both languages. Bistjaanodo (talk) 19:43, 17 November 2012 (UTC)Reply

• http://www.iso.org/iso/publications_and_e-products/a-z_subject_index.htm

--222.64.22.74 (talk) 02:26, 8 April 2010 (UTC)Reply

Mainfile does NOT to be sorted ... (Ullman, Principles of Database Systems) —Preceding unsigned comment added by 92.193.5.33 (talk) 10:38, 20 November 2010 (UTC) --41.238.77.233 (talk) 23:42, 16 April 2012 (UTC)--41.238.77.233 (talk) 23:42, 16 April 2012 (UTC)Reply

Sorry for my english. Why this article says nothing about practical limitations of the indices implementations? For example is it possible to index the string fields with varying length? What the limit if yes? If i have field with a string say 65536 unicode symbols length, will be it indexable practically? And how about arbitrary binary data? Are they indexable ever? And if i have the table with 100 fields, may i create the index for each field. Do expose dbms any limit on this? And although this is not about the indices directly, but is highly related, suppose the query asks dbms for something with 'where fx="blagjptmjgdaj"' and fx is non-indexed, what behavior of dbms is? Linear fullsearch with implicit creating of the indexon this field or whithout or anything else? And so forth. Idea for speeding up the location of records by B+ tree indices is understandable in principle, but it is very desirable, that this article also would mention those complications and their solutions in real indexing systems. Maybe some expert, who understood my writings, have the time and willing, will add about this. I think it will be very useful. 77.52.154.70 (talk) 08:26, 14 May 2013 (UTC)Reply

Note that most of these questions (particularly limitations) are very database-specific and are answered in the respective database software manual. Wikipedia does not aim to be a manual, but tries to give a general overview of the topic. So I'm not surprised that they aren't covered here.

> suppose the query asks dbms for something with 'where fx="blagjptmjgdaj"' and fx is non-indexed, what behavior of dbms is? Linear fullsearch with implicit creating of the indexon this field or whithout or anything else?

I believe this is answered in the article under "Applications and limitations":

Consider the following SQL statement: SELECT first_name FROM people WHERE last_name = 'Smith';. To process this statement without an index the database software must look at the last_name column on every row in the table (this is known as a full table scan). With an index the database simply follows the B-tree data structure until the Smith entry has been found; this is much less computationally expensive than a full table scan

-- intgr [talk] 13:06, 14 May 2013 (UTC)Reply

Link to Finnish article should be removed, because the article is about subject headings, not computers. — Preceding unsigned comment added by 195.148.239.230 (talk) 10:54, 27 August 2014 (UTC)Reply

Removed. -- intgr [talk] 12:00, 27 August 2014 (UTC)Reply

I noticed that https://en.wikipedia.org/wiki/Key had a link to Key (computing), which redirects to this wikipedia entry. But the second paragraph in this entry said: "An index is a copy of selected columns of data from a table, called a database key or simply key, that can be searched very efficiently", which is a VERY misleading definition of "key". I have changed that text and the link on the Key page, but the redirection from https://en.wikipedia.org/wiki/Key_(computing) should go to https://en.wikipedia.org/wiki/Unique_key -- NOT https://en.wikipedia.org/wiki/Database_index -- and I don't know how to fix that. Can someone help with that? -- DBooth (talk) 20:14, 5 February 2021 (UTC)Reply