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.

343 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
0answers
216 views

UUID V1 and V4 bloats substantially after sequntial inserts and deletes. Why is it so, compared to serial and temporal BTree (which does not bloat)?

I am trying to understand the impact of deletions to bloat of tables and indexes after large number of inserts and deletes. The inserts and deletes are following a rather strict pattern: first the ...
5
votes
1answer
298 views

PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd?

I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability ) CREATE TABLE scale_data ( section NUMERIC NOT NULL, ...
5
votes
3answers
110 views

Optimizing table/indices for getting the latest row (filtering by one additional column)

I have a SQL Server table (SQL Server 2012 SP3 Standard edition) that stores a bunch of configuration information (basically text blobs) for different organizations. The schema is something like this: ...
5
votes
1answer
273 views

Guidelines on best indexing strategy for varying searches on 20+ columns

I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation. Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search. The problem: ...
4
votes
1answer
1k views

Does PostgreSQL/PostGIS use R-Tree or R*-Tree?

PostgreSQL and PostGIS offer to create indexes over GiST with pretty much the same code. On 2D items, "This gives R-tree behavior" is what it says in the PostgreSQL source comments. However, it is a ...
4
votes
0answers
986 views

How can I determine why a concurrent Postgres index was marked as INVALID?

I'm trying to create an index concurrently. CREATE INDEX CONCURRENTLY blocks_expr_idx6 ON blocks USING btree ('blah') WHERE "type" = 'user root'; 12 hours after I ran the query, if I run \d blocks ...
4
votes
0answers
477 views

SQL Server 2012 duplicate key error with Spatial index that shouldn't be unique

We have a table that has a geometry column that is filled by a stored procedure from two x and y values whenever they're changed ( SW_GEOMETRY = GEOMETRY::STGeomFromText('POINT('+ CONVERT(VARCHAR(50)...
4
votes
0answers
577 views

SQL Server 2012 Error when creating Selective XML Index on XML Query Plan

I'm trying to create a database solution that will enable me to optimise high CPU/IO/Duration queries to improve overall performance of the database. Part of the optimisation requires persisting ...
4
votes
1answer
325 views

MySQL won't use an index on an int, if I provide a string

Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log: # Time: 130904 12:19:59 # User@Host: cron[cron] @ [10.71.41.38] # Query_time: 58.488760 ...
3
votes
1answer
3k views

How to estimate reindexing time?

How do you estimate the total time reindexing an entire table will take in PostgreSQL? I have a large PostgreSQL database, the primary table of which has 15 million rows with 100 columns and 200 ...
3
votes
1answer
40 views

SQL query with Like operator with Leading Wildcards

I have a query as follows: select top (30000) * from table1 where licenseplate like '%as43bv%' I have an index on licenseplate but the query will not hit the index due to leading wildcard. Since ...
3
votes
0answers
30 views

MySQL uses INDEX for optimizing ORDER BY with GROUP BY except when you add a JOIN

Question Why can't Query #2 use the same (car_trims.horsepower_peak) index to optimize the sorting of the rows as Query #1? The only difference between the two queries is the addition of the JOIN in ...
3
votes
0answers
31 views

Will my database index be more performant with a smaller varchar size?

We're using Mysql 5.7 We have an index over a varchar(255) column in a table of ~ 500M rows. select distinct varchar_column from table yields seven results, with the max length 21. A DBA consultant ...
3
votes
1answer
91 views

How to find dropped or ghosted columns in Azure SQL DB?

In SQL Server, dropping a column is a metadata only change; the data remains in the index until the index is rebuilt. On prem, I use system_internals_partition_columns to determine what indexes have ...
3
votes
1answer
29 views

Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?

I have a big table (30 millions rows) defined like this: CREATE TABLE `drawing` ( `id` int unsigned NOT NULL, `artist` int unsigned NOT NULL, `imageUrl` text NOT NULL, `uploadDate` timestamp NOT ...
3
votes
0answers
145 views

Best Way to Index Multiple Effective Date Columns

I've got a table that is primarily to represent relationships between entities (i.e. mostly composed of foreign keys). These relationships change over time, and so the table has a StartDate and an ...
3
votes
0answers
1k 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 ...
3
votes
0answers
153 views

TokuDB/Percona - Insert Performance worse as table grows

We have a bunch of existing servers which run just fine, inserting our thousand rows/second, but the new batch just will not do it. As the day goes on and the table grows, it gets slower and slower, ...
3
votes
0answers
146 views

Fast index creation issue

One thing that I can't explain is creating secondary index that doesn't trigger fast index creation as it should. I noticed that while creating secondary index on larger table, it gets actually ...
3
votes
2answers
152 views

Create index for multiple WHERE EXISTS subqueries

I have to find an efficient way to write a very specific query for an application that I'm developing. The idea is the following: Find all the genetic markers for which there exists at least one ...
3
votes
0answers
116 views

What's the point of prefixing each element in the column by its column number in multicolumn GiN indexes?

According to these slides which, I believe, are incorporated into the current implementation, each column element in multicolumn GiN indexes is prefixed by its column number and is stored separately ...
3
votes
1answer
92 views

Finding optimal indices, different query competing indices

My schema: create TABLE "logs" ( "id" serial not null default nextval('logs_id_seq'::regclass), "request" varchar(1024), "token" varchar(512) default NULL, "was_batch_request" bool ...
3
votes
1answer
539 views

Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

Background Information: I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server. I have to migrate a lot of Rows (not all), spread over a lot of tables (not all)....
3
votes
0answers
194 views

Disabled index still incurs overhead when inserting

I have a 38 million row table that I periodically truncate and refill. I disable the indexes (non-clustered) when I do this. But when I included the Execution Plan I noticed SQL Server 2005 is still ...
3
votes
0answers
261 views

custom population of full text index

Is there any way to populate a full text search index without storing the backing information in the database? I don't mean using remote-blob-storage or FILESTREAM types. The backing information is a ...
2
votes
0answers
39 views

Oracle DISTINCT with NULLable columns

There is a longstanding(?) problem(?) with Oracle that if you want to use a DISTINCT select on a column which has a B*-Tree Index but is nullable, that the index is not used. (As other answers suggest,...
2
votes
0answers
28 views

Querying a High Cardinality Field

I am designing a data model for our orders for our upcoming Cassandra migration. An order has an orderId (arcane UUID field) and an orderNumber (user-friendly number). A getOrder query can be done by ...
2
votes
1answer
40 views

Is searching faster if indexed values are the same?

Currently I have a collection events with an attribute createdAt of type date (for example 2019/09/09 10:32:12.123). Because my users search a lot by date, I added an index on this attribute. There ...
2
votes
0answers
40 views

the cost decreased but the time was practically the same, why?

Table definition: CREATE TABLE schema.mylogoperation ( id_mylogoperation serial, data DATE, myschema VARCHAR(255), column_var_2 VARCHAR(255), user VARCHAR(255), action TEXT, ...
2
votes
0answers
33 views

Do we need index on “order by” if the “where” clause alreday have index ( small data set )

Assume I have a very large table X and I want to run below SQL. select * from table X where type='X1234' order by time; type is not unique and indexed ( with high cardinality), and there are very ...
2
votes
0answers
118 views

Graphical representation of SQL Server Index

Are there any methods or tools to enable someone to visualise/graphically represents the b-tree that underpins an index? The purpose would be to aid in the teaching of how indexes work to enable ...
2
votes
0answers
155 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)....
2
votes
0answers
234 views

Postgresql - Primary key index getting corrupted after upgrade from 9.2 to 9.6

Im using a Postgresql-9.2 on Centos 6.7. I tried to upgrade the PostgreSQL using --link. After that, I took a table backup and tried to restore this on Dev server. But Im getting an error like, ...
2
votes
1answer
145 views

PostgreSQL: simple equality query returning no results for indexed columns

I have a very large table (around 140M rows) with five columns: CREATE TABLE IF NOT EXISTS citations ( citing_id text, citing_issn text, cited_id text, cited_issn text, year int ); ...
2
votes
0answers
195 views

Searching between two BIGINT

I have a table defined as: CREATE TABLE [dbo].[IPS_INTEGERS]( [id] [int] IDENTITY(1,1) NOT NULL, [network_start_integer] [bigint] NULL, [network_last_integer] [bigint] NULL, [...
2
votes
0answers
40 views

How to implement a “Command T” (VIM) -like search

I am thinking of implementing a string search similar to the command-t search in VIM. The context is almost the same: Finding Files in a Webapp. It's not easy to explain how the command-t search ...
2
votes
0answers
39 views

Spatial index on raster tiles not used

I have a table where each row contains a raster covering a different geographic area. I need to query the value of the raster at a given point, but only need to query the raster that covers my lon/lat,...
2
votes
0answers
68 views

TSQL: how to define efficient index on partitioned panel-data table?

The underlying table piddt is a panel-data structure containing three fields: (pid, dt, attribs). The defined primary key is (pid, dt). The number of rows is around 6 Billion, and the table is ...
2
votes
1answer
463 views

Why does postgres choose to sort instead of scanning the index?

I have the following table: # \d service Table "public.service" Column | Type | Modifiers -------------+----------+----------- customer_id | integer | not null date | date ...
2
votes
2answers
160 views

Can indices save a giant `WHERE foo IN (…)` query with thousands of entries in the query list?

I have some client code hitting my postgres instance with a giant SELECT statement that is matching against a list of IDs. The query ends up looking like this... SELECT "id","name","status","...
2
votes
0answers
300 views

Avoiding array of foreign keys but maintaining its performance?

Consider a many-to-many relationship between sources and tags, modeled in the conventional relational manner: CREATE TABLE sources (ix INTEGER PRIMARY KEY); CREATE TABLE tags (ix INTEGER ...
2
votes
0answers
158 views

SQL Query produced by Umbraco app & NHibernate ORM - help!

https://www.brentozar.com/pastetheplan/?id=SJ1BhSun This is a sample of a query that this CMS called "Umbraco 5" runs against its backend DB, which is currently hosted on SQL 2008R2. This query runs ...
2
votes
0answers
408 views

Filter by list of values pairs

I have Postgresql 9.4 database and products table. For each product there must be list of pairs: price; quantity for which this price is acutal Products table may contain millions and billions of ...
2
votes
0answers
155 views

Is the average row size listed in Management Studio's index properties accurate?

I just inherited some SQL Server 2008 R2 databases and have reason to be reviewing the indexes on some tables. For a table with two columns such as: CREATE TABLE My_Table ( Obj_Id int NOT NULL, ...
2
votes
0answers
31 views

How to create ROW VALUE index

I have a table, lets call it mytable: CREATE TABLE mytable { id INTEGER, ts TIMESTAMP WITHOUT TIME ZONE, .. ) and I have following query in multiple client applications: SELECT ... FROM ...
2
votes
1answer
64 views

best way for creating Index in mysql (multi-column/table)?

i have a big problem with my database.. I need a very fast query over 2 tables. 1. table: (node) ~42.500.000 entrys 2. table: (waynode ~51.200.000 entrys The following query is much to slow: ...
2
votes
0answers
84 views

what if all the non clustered indexes on my table were filtered indexes?

I have a table that is used heavily. This table has 370,370 records. When I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [ImageSize] ...
2
votes
0answers
137 views

Add index to mysql table with ndbcluster engine

I'm having trouble while adding index on mysql table with ndbcluster storage engine below is the error . [mysqlcluster ver. 7.4 GA] mysql> alter table `sks_staff_office` add index `pid_index` (pid)...
2
votes
0answers
71 views

Order by index unexpectedly slowing query down

I have two tables: houses has ~415,000 records, and account_houses has ~525,000 records. I've changed the table names for security reasons, but basically I am using this query: SELECT "houses".* FROM ...
2
votes
1answer
31 views

How do I find what keys mysql will use for a query?

I just had a query encounter a deadlock, where the lock it was trying to acquire was for an index. But when I used explain to visualize the query, I saw that a different index was listed under key and ...