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
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

A database structure that can improve the speed of queries at the cost of disk space and slower inserts/updates. It stores a copy of one or more columns but structures the data differently to allow for faster access.

-5
votes
1answer
56 views

Can data size be less than index size in sql database?

Recently I came across a question like , Can a SQL Database table contain index size greater than actual data size ? I have gone through few articles and studied bit on this and few experts ...
1
vote
2answers
37 views

Best Indexing for Performance - MySQL

I have a question related performance DB MySQL. I have querying like this with big data inside : SELECT * FROM tableA a INNER JOIN tableB b ON a.id = b.id AND a.code = b.code ...
0
votes
1answer
29 views

Trigram index on all values of a JSONB column

I have a JSONB column that contains a bunch of key/value pairs, no complicated nested structures or arrays in there. The keys are dynamic, and I don't know them in advance. I'm using PostgreSQL 9.6. ...
1
vote
0answers
16 views

Maintenance Plan Failing After Database Collation Change

We currently use maintenance plans to perform index rebuilds each night (we inherited this system). Yesterday, i amended to the database collation to match the instance level collation (...
1
vote
0answers
39 views

Remote harden of transaction 'CREATE INDEX' - when moving an index to a different filegroup

Remote harden of transaction 'CREATE INDEX' (ID 0x0000000082a3e3fc 0000:8d8f7911) started at Aug 14 2018 6:14PM in database 'cola' at LSN (2599205:10500:111) failed. The statement has been ...
0
votes
1answer
20 views

Oracle PK index extremely low performance

There's a table MYHIST containing 42M rows. The table has a primary key column MYHIST_ID NUMBER(22) with a valid index PK_MYHIST on it, and 50 more columns (of types VARCHAR2, NUMBER and DATE), which ...
5
votes
2answers
72 views

Clustered Index 'Seek predicate' and 'predicate' on the same column

I have a clustered index primary key column and I'm doing a range query on it. The problem is the scan only uses the first range part for the seek predicate, leaving the other side of the range as a ...
1
vote
2answers
28 views

Any benefit in running ANALYZE after bulk insert in table with only PRIMARY KEY index in SQLite?

If I create a table in SQLite that has only the PRIMARY KEY index, is there any role for running ANALYZE after a bulk insert, or will that key automatically have been kept up-to-date by necessity? In ...
1
vote
2answers
30 views

Mongodb Products schema with proper indexes

I'm totally new to mongodb with a MYSQL background. I'm working on an e-commerce website with some millions of products So we decided to migrate from MYSQL to mongodb. So far this is the products ...
0
votes
1answer
100 views

Query does not use Indexes

This query runs very slowly (over 2 minutes) because it does full table scans. Why does it not use the indexes I created? EXPLAIN SELECT * FROM race_points t1 LEFT JOIN ( SELECT Name ...
0
votes
1answer
31 views

Postgresql small index for row contains query

I want to store several hundreds of gigabytes of geodata in a postgresql database. I want to query the data on position, time and/or a unique identifier for each object. My table layout is similar to ...
2
votes
1answer
36 views

Does MySQL or MariaDB support GIS KNN Index Queries (k-nearest neighbors)?

Can you do k-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can run a kNN query on the spatial index with <-> SELECT ...
2
votes
1answer
34 views

Multi-column and single-column index on the same column

I have two indexes on the same column: store_deleted_idx (store_id, deleted) store_idx (store_id) DDL: CREATE TABLE `stores_shoppers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `store_id` int(11) ...
0
votes
1answer
20 views

Why does querying the right most index of a composite index does not result in full table scan in postgres?

Consider the following table CREATE TABLE public.my_table ( my_id text COLLATE pg_catalog."default" NOT NULL, my_second_id text COLLATE pg_catalog."default" NOT NULL, CONSTRAINT ...
1
vote
1answer
39 views

Creating index on foreign key column?

I have below table structure where one department can have many employees. Department id dept_name ... Employee id emp_name dept_id(foreign key to department table) .... ...
1
vote
0answers
38 views

MySQL index on BIT column strange behaviour

Using MySQL 5.7 Given the following table: CREATE TABLE `test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `col1` bit(1) DEFAULT b'0', `col2` int(11) DEFAULT NULL, PRIMARY KEY (`id`), ...
-2
votes
2answers
59 views

MS SQL Server differentiate system and user defined indexes using sys.indexes table

I am trying to write a sproc to maintain all the indexes created by me. My indexes are shown in random numbered rows in sys.indexes table, mixed with system indexes. Now I want to write a select ...
1
vote
1answer
38 views

Postgresql create index concurrently

I want to create a concurrently index in postgresql inside a trigger like following code CREATE OR REPLACE FUNCTION hour_production_index_partition(partition_name text) RETURNS VOID AS $BODY$ ...
0
votes
1answer
42 views

MS SQL Server sys.indexes vs sys.sysindexes system table difference

For indexes there are 2 system tables: sys.indexes and sys.sysindexes. I'd like to know what are their differences. Both contain information about indexes then why they are separate? Thanks in ...
0
votes
1answer
25 views

WITH behavior in relation to indexes

I have a question related to behavior of WITH queries. So I have WITH test as ( SELECT id, name, .... FROM test... WHERE ... ) In that auxiliary statement I select a column(s) that ...
0
votes
1answer
28 views

MySQL 5.7 UPDATE slow due to not using index

We have an issue similar to this. Only, the solution there won't work for us as: We don't use multi-table JOINS, but UPDATE … WHERE `userid` IN (<list of 10k ids>); We currently "only" have the ...
1
vote
1answer
47 views

Postgres bypassing index with IN clause with 100+ values?

I came across this on another forum: PG has a limit of 100 for values in IN queries after which the index on said column is not used. For ex: a typical SELECT ... WHERE IN (...) query on the PK ...
1
vote
1answer
36 views

Reclaim Database Wasted Space

In our company we have a test server on which Oracle RDBMS 11g is installed. My question is related to reclaiming wasted space. I ran the following query: select table_name,round((blocks*8),2) "...
0
votes
2answers
28 views

Cannot Check Cluster Index Sql Server

I am just check if my table has Cluster Index or not by running this query Table Design CREATE TABLE [dbo].[postTB]( [postID] [bigint] IDENTITY(1,1) NOT NULL, [slug] [nvarchar](300) NULL, [title] [...
-2
votes
1answer
45 views

Can rebuilding an index create fragmentation?

Its my understanding that when you rebuild an index SQL will sort the pages into order at the end of the data file, growing the data file until all pages are ordered and then drop the old index ...
1
vote
1answer
14 views

Table/Column Character Encoding and Indexes

Does table/column character encoding have any impact on how the MySQL optimizer chooses join indexes? I have a table that have the same columns and indexes on two servers, one server does a eq_ref ...
6
votes
2answers
292 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 ...
3
votes
1answer
32 views

Multi-Column index not used for index-only scan, but partial index is

My question is why a multi-column index is not used for a index only scan, when a partial index with equivalent information (i think) is. The table: CREATE TABLE test ( id INT, descr ...
1
vote
1answer
15 views

Transform/map json object array to primitive for GIN indexing

Say I have a table with a jsonb column that contains data structured like this: { "records": [ { "id": 1, "tag": "address" },{ "id": 1, "tag": "receipt" } ] } ...
1
vote
0answers
29 views

why order by desc and limit query is taking too much time?

Currently, i was trying to make a sql query on my postgresql table, all things was going good unless i sort my query by Price in DESC, after this filter query is taking almost round about 60 sec to ...
2
votes
1answer
26 views

MongoDB Embeded Index

we have a collection in below format: { "_id" : { "a" : 8, "b" : ObjectId("5b23aa9e24826b82a2553ce7") }, "field1" : "value1", "field2" : "value2", "field3" : "...
-1
votes
1answer
35 views

Can I tweak Mysql [buffer?] variables to speed up indexes building?

I have disabled indexes updates with alter table my_table disable keys to speed up inserts Are there variables I can set [for the session] to speed up indexes rebuilding with alter table my_table ...
1
vote
2answers
37 views

Can indexes really decrease performances in a low volume writes DB? [closed]

I'm trying to do a bit of a clean up/review of the database I'm currently working on (which I didn't design, by the way). The DB is 'big' in term of objects (hundreds of tables, hundreds of SP, ...
1
vote
1answer
78 views

Indexing strategy for VARCHAR2 LIKE search

Scenario Database Oracle Database 11g Table Users ID Number, PK CODE Number, Indexed, Unique, Not null FIRST_NAME VARCHAR(100), Not null LAST_NAME VARCHAR(100), Not null EMAIL VARCHAR(50), Not ...
2
votes
1answer
47 views

Function in JOIN condition

I have big dynamically constructed query with FROM main_table t LEFT JOIN table1 t1 ON my_func(t.col1, 1) = t1.col1 ... LEFT JOIN table6 t6 ON my_func(t.col1, 6) = t6.col1 Query is very slow, but if ...
2
votes
1answer
44 views

Creating very quickly an composite partial index on 2 new columns with null values

I'm adding 2 new columns on big table. Those 2 new columns have no default value and no constraint. I also want to add a composite partial index on those 2 new columns. CREATE UNIQUE INDEX ...
5
votes
1answer
69 views

What is an “ACCESS METHOD”?

In the context of the CREATE ACCESS METHOD DDL in PostgreSQL, CREATE ACCESS METHOD name TYPE access_method_type HANDLER handler_function What is a a high level description of what an "access ...
3
votes
2answers
113 views

“Create Unique Index” on big table taking too long time [closed]

I am trying to restore GHTorrent's database dumps (CSV files containing GitHub metadata). The commits table has more than 891 Million rows and the project_commits has more than 5.4 Billion rows. Since ...
1
vote
0answers
29 views

Avoid adding an id-only table for a one-to-many table

I have a table that links user IDs with external identities (one-to-many) and I would like to avoid creating a second table with just IDs. Simplified example: CREATE TABLE identities ( uid ...
0
votes
0answers
14 views

Using SP-Gist or Gist(cube) index to implement kdtree for high dimensional data in Postgresql

I plan to implement kd-tree for high dimensional (100-200 columns) data in Postgresql but I don't know how to start. After I read the documents of SP-Gist and Gist index, I was confused. 1.Sp-GiST ...
3
votes
1answer
41 views

How does indexes on a date field work?

I have a table with many dates, ten years into the future and ten years history. (combined with customer id and more) Most of the reads and writes from/to the database is for records with the ...
1
vote
0answers
16 views

How to make use of indexes with inner join in Postgis?

I got 2 sets of points in 2 separate tables. Table_a got 100k points and table_b got 300k of points. I trying to find nearest points in relation find me any point from table_b that is within 50 meters ...
0
votes
0answers
23 views

Example of Tree Indexing Structures

This presentation goes in depth into some indexing structures but doesn't go into XML indexes (or tree indexes). By tree indexes I don't mean B-trees and the like, which are used for indexing single-...
2
votes
0answers
60 views

Why is Insert-Ignore so expensive in MySQL?

TL;DR: Inserting a large number of rows with no collisions is MUCH faster than (not) re-inserting the same rows, BOTH using the INSERT IGNORE syntax. Why is this? I would assume that the index lookup ...
0
votes
1answer
135 views

How do you index a text column in MySQL?

If I create a table with a text column CREATE TABLE foo ( bar text ); I can't index the bar column. MariaDB [test]> CREATE INDEX foo_bar_idx ON foo ( bar ); ERROR 1170 (42000): BLOB/TEXT column ...
1
vote
1answer
63 views

Generic way to determine if the table needs index (PostgreSQL)

I saw advices like Indexes should not be used on small tables or Index on small table may decrease performance`. Can anybody provide magnitude of table size when creating indices makes sense, besides ...
0
votes
1answer
45 views

Does PostgreSQL index null values? [closed]

If I have a table like so: Food ----------------- name | price | x Let's say I create an index on name & price, but the database has a bunch of null values for both name & price in some of ...
0
votes
0answers
52 views

H2: Drop index which belongs to a constraint

We have a schema "Logger" that contains two tables: LogEntry (PK: Id) LogExceptions (Combined PK:LogEntryId,LineIndex) LogProperties (Combined PK:LogEntryId,PropertyKey) The schema has a foreign key ...
0
votes
0answers
31 views

Warning in Ola Hallengren Index Maintenance Job [duplicate]

I am using Ola Hallengren index optimize SP, I have included command execute and Index optimize SP, I am using SQL Server project(DACPAC) to manage the script. When I am building it, I am getting lots ...
4
votes
1answer
288 views

How Does SQL Server Choose Indexes to Use

I've recently begun working on indexing the various views in our DB. Often, some of these tables are only used for joins, and rarely are they used in where or order by statements. What this means, is ...