Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Make your voice heard. Take the 2019 Developer Survey now

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".

2
votes
2answers
100 views

Containment check tstzrange @> timestamptz not using btree or gist index

Schema: Column | Type ----------------------+-------------------------- id | integer event_id | integer ...
0
votes
2answers
68 views

How to understand Composite Primary Key when it comes to storage (in Mysql)?

I can image how rows will be arranged in a single database page as primary key order (example an auto-increment key) after reading this post. But I wonder how it will in the case of composite primary ...
-4
votes
1answer
26 views

About the Index in MYSQL [closed]

I have a question about indexes in MYSQL. I have a table, DIM_BANK. The primary key is the column ID_BANK. MySQL automatically created the an index for this with the following attributes - BTREE (...
6
votes
2answers
668 views

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 ...
1
vote
1answer
91 views

Choosing index fillfactor for large, highly inserted table

We have a table that will hold records on the 108 order. ╔══════════╦═════╦══════╦══════════════════════════╗ ║ id ║ a ║ b ║ ...many other columns... ║ ╠══════════╬═════╬══════╬════════════...
1
vote
2answers
961 views

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 ...
0
votes
1answer
253 views

postgres 9.6 - jsonb & indexing - poor performances

On a table (used by a django model) I'm using jsonb column data to store arbitrary data fetched from a webservice: abs=# \d data_importer_rawdata; Table "...
1
vote
1answer
51 views

How can we foresee (and configure) B-Tree and R-Tree indexes width and depth in PostgreSQL? [closed]

The B-Tree and the R-Tree are two similar index structures that PostgreSQL offers in its implementation. Both structures can have at most m children per node and as other tree structures, children ...
2
votes
0answers
70 views

Sequential scans instead of index with a PostgreSQL conditional index

I have the following PostgreSQL table, in it I store millions of rows. I'm effectively trying to use it as a work queue, large lumps of JSON go in (status=queued) and get processed (status=processed)....
1
vote
1answer
149 views

PostgreSQL covering index on large table as alternative to CLUSTER command

I'm looking for an alternative to executing the CLUSTER command on large tables to keep data efficiently organized on disk, since it requires an ACCESS EXCLUSIVE lock on tables being clustered, making ...
0
votes
0answers
453 views

How to create index on mediumtext data type field should be use B+tree Indexes?

I have a MySQL table Engine InnoDB with a mediumtext data type field: messages mediumtext NOT NULL should be use B+tree Indexes?
4
votes
4answers
202 views

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 ...
-1
votes
1answer
63 views

Postgresql extension pageinspect - mean bt_metap

I am using Postgresql extension pageinspect. Could someone tell me the meaning of these columns: magic, version, root, level, fastroot, fastlevel of the bt_metap function. This information is not ...
0
votes
1answer
78 views

Can you use multiple types of indexes in one composite index

I've built a covering index for a pretty big query which has already halved the select time. Now, all the columns are just INT(11)'s so there isn't a problem there but one of the columns is a VARCHAR(...
3
votes
1answer
62 views

Query results not as expected - Jsonb Postgres

We've created the following table: CREATE TABLE public.influencers ( id integer NOT NULL DEFAULT nextval('influencers_id_seq'::regclass), location jsonb, gender text COLLATE pg_catalog."...
1
vote
1answer
889 views

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 ...
1
vote
0answers
46 views

Measuring 'data items accessed' by index technique to retrieve item (PostgreSQL)?

In the example I have attached, a btree is being used to search for the number 19. With PostgreSQL execution plan, buffers read/hit are being shown. Which would they be in the example I have attached? ...
5
votes
1answer
788 views

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 ...
11
votes
1answer
286 views

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 ...
0
votes
0answers
50 views

Btree - Full index scan

I have a multicolumn btree index on columns (A,B) of the table: R(A,B,C,D) I ask the following query Select * From R Where B=10; If i want to use the index, then postgresql will do a full index ...
0
votes
1answer
88 views

Multi-column btree index & INLJ

I have three tables: FILM(ID,TITLE) ACTOR(ID,NAME,GENDER) CAST(ACTOR_ID,FILM_ID,ROLE) Foreign keys: ACTOR_ID, FILM_ID And I have created a btree on columns (ACTOR_ID,FILM_ID) of CAST table. Could ...
3
votes
2answers
436 views

MySQL - Optimal indexing for a lookup table. HASH index, BTREE index or composite PK?

Let's say I have a very large lookup table that looks like this: CREATE TABLE `MyLookup` ( `FKToTableA` bigint(20) NOT NULL, `FKToTableB` bigint(20) NOT NULL, `Count` bigint(20) ...
0
votes
2answers
93 views

Creating Compound Indexes on Many Flags

So, according to this article, low-cardinality indexes may negatively impact performance: http://www.ibm.com/developerworks/data/library/techarticle/dm-1309cardinal/ Let's say you have 15-20 yes/no ...
1
vote
1answer
110 views

XML database for linux

I want to be able to store multiple xml objects in a database. But I don't want to just serialize the object and dump it out. Rather I would like to be able to access the database representation of ...
0
votes
2answers
175 views

Clustered Index Misunderstanding

I've read in couple of articles that clustered index is as the same as table, or if you read using clustered index, you have the whole record. Some say, clustered index stores the whole records in ...
5
votes
2answers
326 views

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 ...
4
votes
1answer
191 views

B Tree and B+Tree differences

I'm studying B+ Tree and B Tree and I would like to understand two things about it, if someone can clarify it to me I would appreciate: Why can I store more search keys on an B+ Tree Index? My guess ...
4
votes
3answers
823 views

How is combined index implemented in SQL Server

I understand how single indexed column works in SQL Server and how it is implemented using balanced trees. There are plenty of interesting videos on YouTube on this topic. However I don't understand ...
0
votes
1answer
48 views

Record scan speed, column orientated databases and SSDs

I'm learning about column orientated databases. I have just watched a YouTube video on column orientated databases by Arnaldo from MIT, who suggests an interesting motivation for column orientated ...
-1
votes
1answer
1k views

Btree index and Bit Map Index [duplicate]

Please clarify the difference between BitMap Index and Btree index. I am using mysql version 5.5.37-0ubuntu0.12.04.1. I can able to create Btree index but unable to create the same, Please find the ...
2
votes
1answer
2k views

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 ...
0
votes
1answer
299 views

How does InnoDB store tables in ibdata file? [closed]

There are two tables and their default engine is InnoDB. I want know the following What's inside the ibdata file ? How does InnoDB store these two tables ?
5
votes
2answers
3k views

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 ...
1
vote
1answer
1k views

what is the structure of a Mysql multi-column index? [closed]

For example: CREATE TABLE test( col_1 INT, col_2 INT, col_3 VARCHAR(11), col_4 VARCHAR(11) )ENGINE = INNODB; CREATE INDEX mul ON test(col_2,col_3,col_4); Sorry,my English is not good,I mean ...
1
vote
1answer
87 views

When is the index actually created?

I have noticed that even on huge tables with millions of records when I do a CREATE INDEX the index is created immediately (I get the console back in nsecs). Why is this? Doesn't it create the B-Tree ...
2
votes
1answer
649 views

In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index

Let's assume that I have table data: CREATE TABLE `test`.`data` ( `row_id` INT NOT NULL , `data_id` INT NOT NULL , PRIMARY KEY (`row_id`) ); Now, let's assume that data_id has unique values. Is ...
5
votes
1answer
323 views

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 ...
4
votes
3answers
4k views

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. ...
6
votes
4answers
15k views

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 ...