The tag has no usage guidance.

learn more… | top users | synonyms

5
votes
2answers
2k 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 ...
5
votes
1answer
294 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
4answers
10k 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 ...
4
votes
3answers
2k 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. ...
4
votes
2answers
84 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 ...
3
votes
3answers
223 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 ...
3
votes
1answer
49 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 ...
2
votes
1answer
867 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 ...
2
votes
1answer
330 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 ...
1
vote
1answer
29 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 ...
1
vote
1answer
431 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
81 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 ...
0
votes
2answers
87 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 ...
0
votes
1answer
165 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 ?
0
votes
0answers
47 views

Can we combine Bitmap index with B+ tree index?

If I have a database of employees and expected queries on (salary,gender). Would it be a good idea to have a B+ tree index on salary and Bitmap index on gender at every leaf node of a tree. What are ...
0
votes
1answer
30 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
588 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 ...