Questions tagged [index]

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 sorted but structures the data differently to allow faster access.

Filter by
Sorted by
Tagged with
0
votes
1answer
14 views

MariaDB: Why is this still using temporary and filesort - is it because I'm using a self-join? How to fix?

SELECT e2.personId, COUNT(*) FROM entries e1 JOIN entries e2 ON e2.categoryId = e1.categoryId AND e2.personId != e1.personId WHERE e1....
2
votes
1answer
25 views

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

How to practically run sys.dm_db_index_physical_stats against 460 THOUSAND tables?

I've been tasked with maintaining an old (SQL 2005; no, I can't upgrade it!) server which has 461,000 tables and 1.15M indexes. How do I go about analyzing all those indexes in bite-sized chunks? My ...
0
votes
1answer
21 views

Unable to create partial indexes using bloom

I'm new to bloom indexes and currently my table has 3 partial indexes for the same column in the WHERE clause and with different column combinations for the same table. I was trying to replace them ...
0
votes
1answer
13 views

Composite multicolumn index for geopoint range and numeric range query

I am building an app where the server needs to select rows based on some criteria/filters. One of them is the location of the user and the radius at which the user want's to see posts and other ...
0
votes
1answer
21 views

Correct index not being used by Mariadb

Fiddle: https://www.db-fiddle.com/f/m4vsq4ERyBhiBNdZqALmVP/0 I have two indexes, one for some_other_id and one forcreated_at , some_other_id. The select query uses the first index on production server ...
0
votes
0answers
12 views

MySQL using different index when direct index exist on columns

I have following table (stripped off other columns, indexes for simplicity) CREATE TABLE `issues` ( `id` int(11) NOT NULL AUTO_INCREMENT, `book_id` int(11) NOT NULL, `number` int(11) DEFAULT '0',...
0
votes
1answer
15 views

Create partial index with single value

I have PostgreSQL 12 DB with a table with a several columns, two of them are a bigint ("numCol") and a boolean ("boolCol"). numCol can have any integer number, and 0 is about 0.1%-...
0
votes
0answers
24 views

PostgreSQL efficient lookup for 2 column combinations

I have a PostgreSQL (v. 12) database storing match results. I have integer columns team_home and team_away which have foreign key constraints for team_id in table teams . My question has a few ...
0
votes
0answers
36 views

MySql Group By Query Taking More 5 seconds On An Indexed Table

I have a simple analytics table which has a little over 2 Million rows and currently growing at a pace of 20k rows per day. Please check the table CREATE TABLE IF NOT EXISTS `affiliate_analytics` ( `...
1
vote
3answers
54 views

Best way to choose which columns to index to improve performance

I use a MSSQL Database for my web application and now the user audit(USER_AUDIT) records in the application have crossed beyond 5 million records/rows. Every day the performance of the application is ...
2
votes
1answer
106 views

Can adding a columnstore index to a table affect read performance of a query that uses a rowstore index on the same table?

I'm doing some testing of columnstore indexing on a single table that has about 500 million rows. The performance gains on aggregate queries have been awesome (a query that previously took about 2 ...
0
votes
1answer
52 views

Known pitfalls with MySQL partitioning

Are there any known pitfalls with MySQL partitioning when server behavior differs a lot from the expected one? I'll try to explain what I mean. Let's create partitioned table and fill it with random ...
0
votes
1answer
29 views

“Cannot continue the execution because the session is in the kill state” when querying system tables

This is a snippet from some code I run on a 2012 (SP3-CU9) (KB4016762) - 11.0.6598.0 (X64) system that I just inherited and have not yet had a chance to fully patch. AlwaysOn AG are not enabled, and ...
0
votes
1answer
33 views

How can I design a table with greater than in where statement and ordering to avoid filesort?

I have a list of documents that I store along with creation date and rank: CREATE TABLE `document` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `category_id` INT UNSIGNED NOT NULL, `rank` INT ...
0
votes
2answers
20 views

Why is the candidate composite index not used?

Consider the following table : CREATE TABLE `entityusage_trackbyyear` ( `track_id` binary(16) NOT NULL, `album_id` binary(16) DEFAULT NULL, `library_id` binary(16) DEFAULT NULL, `...
1
vote
1answer
27 views

PostgreSQL incorrectly using primary key index for min/max query

I have a table with the structure similar to this: CREATE TABLE employees ( id bigserial NOT NULL, name_id uuid NOT NULL, department uuid NOT NULL, details text NULL, deleted bool ...
1
vote
2answers
22 views

MySQL index size doesn't include all values

I have a table with ~1.4M rows and an id field as primary key and not all primary keys are included in index: mysql> SELECT DISTINCT(COUNT(id)) FROM my_table; +-------------+ | (COUNT(id)) | +------...
0
votes
1answer
25 views

InnoDB performance implications of one 3 column index VS three 2 column indexes

I'm using MariaDB with InnoDB but this would apply with MySql as well. I have a list of records that belong to categories and would like to order them by 3 different criteria. I have come up with two ...
0
votes
2answers
17 views

Is there any performance difference between UNIQUE INDEX and INDEX with the same cardinality on MySQL?

I have a table that has id and created_at. Column | Non Unique | Cardinality ------------------------------------- id | 0 | 1000 created_at | 1 | 1000 id is unique, and ...
3
votes
1answer
62 views

Can you create a primary xml index in the CREATE TABLE statement (SQL Server)?

I'm creating a temporary table and would like to create a primary and secondary XML index on a contained xml column within the CREATE TABLE statement since that is the only way I can create an index ...
2
votes
1answer
27 views

What is a Compressed Prefix B+-Tree (CPBTree) in SAP HANA?

I'm studying the SAP HANA main memory database. There is an index called CPBTree in it. In its documentation, it is described as follows: CPB+-tree stands for Compressed Prefix B+-Tree; this index ...
1
vote
1answer
56 views

If a rowstore index is stored in a b-tree data structure, what kind of data structure is used for a columnstore index? [duplicate]

My understanding of how regular rowstore indexes are stored is in a type of b-tree data structure, but I was wondering because of the different nature of columnstore indexes, what type of underlying ...
0
votes
1answer
38 views

Trying to find a way fit an extremely large index in to memory

Description: I am having trouble with the on-growing data size in my MySQL database. I am using Ejabberd and MAM function which will make use of an archive table to store messages sent between ...
1
vote
2answers
30 views

MYSQL Query Index Check

I have a table with the following structure; CREATE TABLE `operations_copy` ( `id` bigint(22) NOT NULL AUTO_INCREMENT, `date` datetime NOT NULL, `zoneId` int(11) unsigned DEFAULT NULL, `...
0
votes
1answer
17 views

pt-online-schema-change error creating triggers

I am trying to add an index with pt-online-schema-change. The dry-run succeeds, but I am stuck at creating triggers. Any help ? pt-online-schema-change --alter "ADD INDEX domain_index (domain) USING ...
0
votes
1answer
43 views

Can filtering be done more efficiently with/without sql joins?

I have a simple problem that has a simple solution with SQL, but would like to explore alternative ways to solve it if they turn out to be more efficient on large scale. Let's assume that we have a ...
0
votes
1answer
20 views

query an indexed jsonb column

I want to query a jsonb column with the @> operator. This query gives results: select * from json_table1 where p_attribute @> '{"age":"37"}' This query doesn't fail but also doesn't give ...
0
votes
1answer
11 views

MariaDB reset table index value?

I was wondering what to do when for example your table is reaching it's maximum index values, I was using this Query to determine the index usage per table: SELECT c.TABLE_NAME, c.COLUMN_TYPE, ...
3
votes
1answer
54 views

Pre Caching Index on a large table in PostgrSQL

I have a table with about 10mln rows in it with a primary key and an index defined on it: create table test.test_table( date_info date not null, string_data varchar(64) not null, data ...
3
votes
2answers
494 views

Index: Does 0% fragmentation imply a B-tree?

I am currently dealing with Microsoft SQL Server and its index structures. The data structures for this are trees. When I reorganize a table index (e.g. the primary key), the result afterwards is 0% ...
0
votes
1answer
20 views

Inheritence and Primary Key Constraints

I am currently trying to see how the object oriented features come in play in Postgres. During a few tests I noticed that when you insert in or delete from inheriting tables that those changes also ...
0
votes
3answers
33 views

Index of which columns are NOT empty

I am trying to enhance the search performance of a table based on whether certain columns contain values. I have the following columns : id | link | text | date | lat | long | type If a record ...
0
votes
1answer
20 views

Azure SQL: Should Manual Indexes be Applied with automatic tuning?

Should Manual Indexes on tables be applied in AzureSQL Cloud Database? We read that AzureSQL does automatic tuning, so are manual indexes even required? We read, there can be cases where AzureSQL ...
0
votes
0answers
33 views

SQL Azure Indexes need Rebuilding for Foreign Keys to work

We have a product that runs off SQL databases. Until recently, each of our clients had their own fileserver running SQL Server, however we have now started hosting on Azure for some existing and some ...
4
votes
2answers
118 views

How can I set a column to NOT NULL without locking the table during a table scan?

(former question was: Will Postgres use a multi-column index when setting multiple colums not null?) Typically, when I set a column not null, if it doesn't have an index then I add it first, so that ...
0
votes
0answers
16 views

MongoDB indexing strategy for any subsequence of multiple fields

Let's say I have huge mongodb collection and each element has fields A, B, C, D, E. I want to filter this collection by different groups of this fields like ABC, BDE, AE and so on. Documentation says ...
1
vote
0answers
54 views

My question is in a select statement how do you return the RID along with the table data?

I need your help with a concept I want to show developers, that concept is to help them understand how SQL Server retrieves data from a table. In the event that a developer does not use a clustered ...
0
votes
1answer
27 views

SQL Server AlwaysOn Availability Group - Creating an Index Online in SSMS

I am running a 2-node SQL Server 2016 AlwaysOn Availability Group. I would like to create a non-clustered index online in SSMS for one of the availability databases in the AlwaysOn Availability Group....
0
votes
0answers
38 views

Database index creation multiple criterias

Context: Designing a B-tree database. I have a table with say 12 columns (A,B,C..). Those columns can be varchar or int in any order (in case of multiple tables). When running a query any of those ...
0
votes
1answer
19 views

Index dive/sampling row count very inaccurate in MySQL

The table emp_address_phone is filled with many random values. CREATE TABLE `emp_address_phone` ( `address_no` int(11) NOT NULL, `emp_no` int(11) DEFAULT NULL, `street1` varchar(255), `...
0
votes
1answer
37 views

Range Optimizer in Mysql: What is an Index dive during Query Optimization?

On following Jorgen Loland post on range optimization, I have come to the concept of "index dive" which is made during MySQL query planning (aka in Mysql Optimizer). Quoting his point, For as long ...
0
votes
1answer
31 views

Better jsonb schema for querying in postgres

I have a JSON with the following structure: uid | item 1 |[{"id":1, "m":"123", "s":1},{"id":2, "msg":"on","s":2},{"id":3, "msg":"of","s":1}] 2 |[{"id":1, "m":"yes", "s":2},{"id":3, "msg":"gh","s":...
0
votes
0answers
41 views

Using a full text index with PostgreSQL and Doctrine

I received the following error: SQLSTATE[54000]: Program limit exceeded: 7 ERROR: index row size 2728 exceeds btree version 4 maximum 2704 for index "fulltext" DETAIL: Index row references tuple (1,...
0
votes
1answer
25 views

Updating column having index causing Deadlock

My update statement: Update XyzTable set state ='second' where pgmNumber = 'prog123' and state = 'first' Both state and pgmNumber columns has individual index. Deadlock is happening even if above ...
0
votes
1answer
48 views

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one. Do you think that indexes going to reduce query run time? Any ideas how ...
0
votes
0answers
16 views

Query planner not using Index scan for WHERE IN condition [duplicate]

I have a table INVENTORY in PGv9.6.11 and when a run query with WHERE _column_ IN(), query planner using seq scan even have index on that _column_ CREATE TABLE INVENTORY ( process_id bigint, ...
2
votes
3answers
93 views

Could the creation of an index break table dependencies?

I ran a query using the sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats to determine what are my options for missing indexes. After analyzing ...
3
votes
1answer
28 views

Postgres 12: Are there performance differences between PRIMARY KEY(A, B) and PRIMARY KEY (B, A) on a table PARTITION BY (A)?

Given a table this table: CREATE TABLE tbl ( a int not null, b int not null, created timestamp not null default now(), primary key(a, b) ) PARTITION BY LIST(a) The query plan for a lookup on ...
1
vote
1answer
19 views

MariaDB Full Text Index - How to query old data as well

I have added new full text index to mariadb ALTER TABLE <table name> ADD FULLTEXT INDEX records I can query against this index only newly added and updated records but for older records (which ...

1
2 3 4 5
53