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
2answers
90 views

LEFT JOIN not working as intended

I'm using mysql 8.0.21. I'm trying to join two tables such that I can find the index name of foreign keys. This is my query: SELECT KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA, KEY_COLUMN_USAGE....
0
votes
0answers
27 views

Postgresql index size: penalty for composite type?

My question refers to Postgresql 12.2. I have two almost identical tables, structure- and contentwise, the main difference being that the primary key of the second table is a composite type, while the ...
0
votes
1answer
20 views

covering index mystery?

So has anyone else applied a covering index in MySQL and gotten improved performance but when you run explain, it shows using where, using temporary under the extra column? Any thoughts why this is ...
2
votes
1answer
104 views

Query customers attributes

I need to create a system where the user creates dynamic filters based on our customer's attributes. There are more or less 30 possible filters and 30 millions of customers, but the number of customer ...
1
vote
1answer
46 views

Adding an index without a lag (PostgreSQL)

PostgreSQL: Adding an index to a table with big amount of data causes a lag for both SELECT and INSERT operations on that table, doesn't it? While PostgreSQL does not support live (without lags) ...
0
votes
1answer
64 views

Postgres not using index in citext column

The following query is returning a SEQ Scan instead a Index. select file_name from myschemadb.my_files where file_name = 'djsaidjasdjoasdjoaidad' Engine Postgres 11.5 My table: CREATE TABLE ...
0
votes
1answer
132 views

Mongodb index keeps growing in size eventhough the collection has a TTL index

I work on a mongodb sharded cluster (3.0.2 i know it's very outdated, not my call), on which i have a sharded db and a sharded collection on which i have multiple indexes. One of the indexes is a TTL ...
2
votes
1answer
210 views

Trigram similarity (pg_trgm) with German umlauts

I try to figure out how to improve Postgres 10.6 pg_trgm queries with German umlauts (äöü). In german 'ö' can be written as 'oe'. But beware: not every 'oe' can be written as 'ö'. CREATE TABLE public....
0
votes
0answers
31 views

Relational DB which can do inserts at billions of rows with multiple indexed columns?

I am looking for advice on a database to potentially replace SQLite for my application. To briefly describe my DB requirements: needs to support billions of rows and the most queried table has ...
-1
votes
1answer
27 views

What's the point of primary dense indexing?

I'm refreshing my basic knowledge of indexing specifically to database indexing. Pratically, I don't put much attention to how it works internally I just do that CREATE INDEX index_name and assume ...
0
votes
1answer
40 views

100k bit column in PostgreSQL

What is the best way to be store, query & index a column containing around 100k bits? I am attempting to do it creating a table like so: CREATE TABLE public.search_bf ( id serial, bf ...
0
votes
1answer
56 views

PostgreSQL: how can I create an index for querying variable combinations of booleans

I have a table of people, each person has a color and they seek various other colors and require various skills. So I can query this I've created a table like so: person id, color_is_red, ...
0
votes
1answer
38 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
33 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
87 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
27 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
17 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
116 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
15 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
17 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
48 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
39 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` ( `...
2
votes
3answers
77 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 ...
5
votes
1answer
177 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
70 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
906 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
37 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
22 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
65 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
24 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
36 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 ...
1
vote
2answers
92 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
79 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
234 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
82 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
52 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
36 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
145 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
47 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
29 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
21 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
290 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
512 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
50 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
36 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
37 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
44 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 ...
5
votes
2answers
603 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
19 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
57 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 ...

1
3 4
5
6 7
57