Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [btree]

BTree is a Binary Tree. Use this tag only for questions pertaining to indexes implemented on a binary tree. If you have a question about hierarchy data use the tag "hierarchy".

66 questions
20
votes
3 answers

How is it possible for Hash Index not to be faster than Btree for equality lookups?

For every version of Postgres that supported hash indexing, there is a warning or note that hash indexes are "similar or slower" or "not better" than btree indexes, at least up to version 8.3. From the docs: Version 7.2: Note: Because of the…
Sampson Crowley
  • 315
  • 1
  • 3
  • 9
14
votes
2 answers

Does mysql use B-tree,B+tree or both?

I did some search on the matter and I found out that Mysql uses B+Tree index, but when I run "show index" the index type that I get is Btree. And I found in this article that Mysql uses both Btree and B+tree. If it is true that it uses both; why is…
Noussa Smiley
  • 143
  • 1
  • 1
  • 4
11
votes
1 answer

How to enter records with keys to an initially empty B+ tree?

Show the result of entering the records with keys in the order (1, 2, 3, 4, 5) to an initially empty B+ –tree of order m = 3. In case of overflow, split the node and do not re-distribute keys to neighbors. Is it possible to enter the records…
Revolucion for Monica
  • 677
  • 2
  • 11
  • 27
8
votes
2 answers

PostgreSQL, integer arrays, index for equality

I have a huge list of integer arrays (300,000,000 records) stored in Postgres 9.2 DB. I want to efficiently search these records for an exact match (equality only). I have heard of the intarray module and the corresponding gist-gin indexes. I would…
Alexandros
  • 1,022
  • 4
  • 12
  • 23
8
votes
2 answers

Index seek to specific multi-column key, then get some rows in lexicographical order

Consider the following sample table with a multi-column index: create table BigNumbers ( col1 tinyint not null, col2 tinyint not null, col3 tinyint not null, index IX_BigNumbers clustered (col1, col2, col3) ) DECLARE @n INT =…
John
  • 775
  • 3
  • 17
7
votes
4 answers

Benefits of BTREE in MySQL

What are the pros and cons of using a BTREE index in MySQL, regarding query speed, disk storage and memory usage? Does BTREE provide easier iteration in increasing order ? What kind of queries would benefit from a BTREE ? Are there any…
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
6
votes
1 answer

Character varying index overhead & length limit

I have a unique constraint on a character varying column that will mostly have lengths of approximately 600 but could get to 10 million. I have read about B-Trees, but I can't determine how much disk space the index will consume or if such…
user32234
5
votes
1 answer

Walking a BTREE index as far as possible in MySQL

Suppose one has a column of words on which one builds a BTREE index: CREATE TABLE myTable ( words VARCHAR(25), INDEX USING BTREE (words) ); LOAD DATA LOCAL INFILE '/usr/share/dict/words' INTO TABLE myTable (words); And now one wants to find…
eggyal
  • 331
  • 2
  • 8
5
votes
3 answers

Optimizing DELETE Query on MySQL MEMORY Table

I run a large forum which maintains a MySQL database for backend data storage. The 'session' table tracks logged in users & guests. It is currently about 100k records, so not really that large. However, this session table is showing up in the slow…
Dave Drager
  • 163
  • 1
  • 6
5
votes
4 answers

How many B-tree indexes do I need to support lookups by any subset of n columns?

First, some background ideas Generally, an SQL database which supports multi-column B-tree indexes also supports doing a lookup by a subset of the columns in the index if and only if they're the first columns in the index. For example, if I have an…
Mark Amery
  • 1,106
  • 3
  • 14
  • 24
5
votes
1 answer

Best index for jsonb in Postgres

We have a table containing around 500k rows. The database table is supposed to grow to million of records. This is how the table looks like: CREATE TABLE public.influencers ( id integer NOT NULL DEFAULT nextval('influencers_id_seq'::regclass), …
borjagvo
  • 205
  • 4
  • 6
5
votes
1 answer

What would be the maximum number of records that can be indexed with a three levels B-tree ? B+ tree?

I am learning dynamic tree-structure organizations and how to design databases. Consider a DBMS with the following characteristics : file pages with size 2048 bytes pointers of 12 bytes page header of 56 bytes A secondary index is defined on a…
Revolucion for Monica
  • 677
  • 2
  • 11
  • 27
5
votes
2 answers

Performance for MySQL InnoDB B+Tree index with many duplicate values

I'm trying to diagnose seemingly random performance issues with our database server. Below is a simplified scenario, hopefully generic enough to serve as a useful future reference for anyone looking for the same answer. Suppose I have a (MySQL 5.6…
Reid
  • 153
  • 4
4
votes
2 answers

Is it possible to expose the max depth of the B-Tree behind a rowstore index or a way to see how many nodes were traversed for a particular query?

I'm personally interested in data structures and with knowing that rowstore indexes are stored with a B-Tree behind them, I'm always curious in trying to visualize what those B-Trees look like for a given table/index. Is there a way to correlate or…
J.D.
  • 40,776
  • 12
  • 62
  • 141
4
votes
1 answer

How do database operations (write, update, alter) on particular cell in a table are written to disk without overwriting entire file?

When I want to write to or alter particular cell of an excel file/table using python I'll use pandas read_csv then alter value in particular cell and write back to file with to_csv. But writing back to file seems to be overwriting the entire file…
Optic_Ray
  • 43
  • 3
1
2 3 4 5