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

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.

0
votes
1answer
23 views

How to Replace Hash Match (Partial Aggregate) with Stream Aggregate

My execution plan shows a hash match (partial aggregate) instead of a stream aggregate even though I have a non-clustered index (which basically means sorting is already in place). It gives me a ...
0
votes
0answers
18 views

How to best define a Spatial Index

I find spatial indexes a dark art, and difficult to grasp. I need some help in defining them in the following scenarios. I have a table, called Positions that holds hundreds of millions ot rows. In ...
1
vote
1answer
50 views

Why full text search on table with GIN index is still very slow

With what I've been able to gather thus far, if you need to run a full text search on a table in a PostgreSQL (psql 9.6.2, server 9.6.5) database with lots of entries (say on the order 1.2M+), the ...
0
votes
0answers
38 views

Newbie Clustered Index question - when to NUSE?

I have a bit of a newbie question, which I feel I should really know having dealt with databases for so long, but... I know the general advice for indexes is NUSE, but a lot of my tables have a non-...
2
votes
0answers
42 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 ...
0
votes
3answers
51 views

How can I speed up this query?

Here is the definition of the relevant table: CREATE TABLE IF NOT EXISTS pricehistory ( id INTEGER(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, price INTEGER NOT NULL, region ...
0
votes
0answers
49 views

Long running SQL Server Query, eats up a lot of resource

I have already posted on stack however as recommended by the guys on there I am now putting it on here. See my previous posts: https://stackoverflow.com/questions/52850961/sql-query-long-running-...
1
vote
2answers
35 views

How correct Postgres plan in case of a double JOIN, ORDER BY and a LIMIT?

I got a SQL request that takes more than 4s: SELECT "Content"."contentId" FROM "UserFeed" JOIN "FeedRole" ON "FeedRole"."feedId" = "UserFeed"."feedId" JOIN "Permission" ON "Permission"."...
0
votes
1answer
54 views

How can I get SQL to do the sensible thing without resorting to a query hint?

I have two tables, Items and Grouping. Items has about 7 million rows whereas Grouping only has 300,000. Of the two conditions on my query, the 'human obvious' thing is to evaluate G.StatusID IN (1,2)...
-1
votes
0answers
49 views

Hash Functions and SQL Server Unique Index Collision rates [on hold]

We want to create Hash Unique indexes for all our rows in SQL Server. Due to legacy reasons, we cannot use CDC, encryption, etc. We expect every row to be different. We're using every field to ...
1
vote
2answers
66 views

Find MySQL InnoDB index corruption

I have large MySQL InnoDB table on my localhost. In this table primary key is id, unique index is set on fields a, b, c. When I run query: SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3; the ...
1
vote
0answers
16 views

ERROR: invalid input syntax for type oid: “english”

While creating an index in Postgres I am getting an error: invalid input syntax for type oid: "english" LINE 1: ...s ON entity_attributes2 USING gist ( (to_tsvector('english',... The query ...
-1
votes
1answer
15 views

mysql 5.5 compound index order

I got this table(made up but similar ) Items (ot_id,numval1,numval2) And I do a query like this: Select from OtherTable ot JOIN Items on ot.id=Items.ot_id where Items.numval1 > 1 and Items....
0
votes
0answers
19 views

How should we select proper columns to create indexes? [duplicate]

For a given query, I need to know how we select the suitable columns to create indexes. In oracle documentation I found below considerations, The column is queried frequently. A referential integrity ...
0
votes
2answers
56 views

why optimizer in Oracle database does not use index?

when I execute following queries see completely different result where is problem? here I execute a simple select query which takes a few seconds to complete: SELECT * FROM PRODUCTION....
2
votes
1answer
41 views

Efficient pagination for big tables

Using PostgreSQL 10.5. I'm trying to create a pagination system where the user can go back and forth between various of results. In an attempt to not use OFFSET, I pass the id from the last row in ...
3
votes
1answer
48 views

leaf_allocation_count in dm_db_index_operational_stats not changing on insert bulk

I was doing some index maintenance on a SQL Server 2016 SP2 CU2 and I noticed something I couldn't explain. I've a clustered index on a table of about 66 million rows and rows are added throughout ...
0
votes
1answer
25 views

I need help creating an index for timestamp

I have a query taking too long. This is the table: mysql> describe parking_data; +----------------+--------------+------+-----+-------------------+----------------+ | Field | Type ...
1
vote
1answer
49 views

Changing stored procedure parameter results slow stored procedure performance

I have a stored procedure to fetch all users in the database. To do this it queries a view which is composed on two tables: Users (holds basic info like username) and UserPortals (maps users to ...
0
votes
2answers
37 views

DB2 huge IREF and lots of scans

I received a task from one of my colleagues to check why he always receives high CPU load alerts. I don't have too much experience with databases and I need some clarifications. I created a script to ...
-1
votes
1answer
18 views

MariaDB query optimizer not using correct index

I got the following query and it was supposed to use index to speed up but it is using the wrong index image_likes and takes around 60 seconds to execute. If I add use index(image_user_id) then it ...
0
votes
1answer
44 views

Having different Table space for different indexes of same table, will causes any performance impact?

Table Master_Table is created in App1_Schema1. Index_I01 is created in default TABLE SPACE. Index_I02 is created in TABLE SPACE "APP2_INDEX". Having different Table space for different indexes of ...
-2
votes
1answer
43 views

Is there any Performance Issue joining Row based Table to Clustered Columnstore Index Table [closed]

Is there any Performance Degradation joining Row based Table to Clustered Columnstore Index Table? Does SQL Server have any guidance for or against this? We have a table that is 2 million rows, and ...
0
votes
1answer
49 views

How Large Table before Using Clustered Columnstore Index

At what size should we consider applying Columnstore Index on a table? Is there a SQL Server storage or design guidance? Our Data Warehouse is 5TB total. Additionally, we have tables which are 100GB ...
0
votes
1answer
27 views

Internal error code, kksgaItLvl_Setup [closed]

A simple query fails with the error. Has anyone faced this issue or has any solutions? SELECT MAX(some_value) FROM some_table WHERE some_id = 62328188199 AND some_name = '...
0
votes
2answers
32 views

Can a table have Both a Clustered Columnstore Index and Nonclustered Columnstore Index?

Can I a table have both Clustered Columnstore Index and Nonclustered Columnstore Index? I am seeing example strategies to use Clustered Columnstore Indexes with only Nonclustered Binary row indexes. ...
1
vote
2answers
34 views

Strange deadlock with multi-column index with innodb on MySQL

I'm seeing some odd (to me) behavior in MySQL. Let's start with the table that I'll be talking about. CREATE TABLE `active_foo` ( `active_foo_id` bigint(20) NOT NULL AUTO_INCREMENT, `c_id` ...
1
vote
0answers
8 views

Postgresql GIN index not used on larger table

PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit I have a table named main_email with column 'sender', type character varying(150), not nullable....
12
votes
1answer
490 views

Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?

I have a SQL Server database where the queries are pretty slow, and there's a lot of locking and blocking. When I look at the missing index DMVs and query plans, there aren't any suggestions. Why ...
-2
votes
1answer
54 views

Can ETL access Data while Rebuild Index Operation is running

Can I get some help to understand below questions: We have a ETL job running (Datawarehouse Server) and weekly index operations (Production Server)(Ola Hallengren script) , below questions are ...
1
vote
1answer
38 views

Cassandra querying without full primary key

As far as I understand there are no secondary indices with multiple columns, only single columns. So how do I solve the index problem I'm having? This is my table: CREATE TABLE IF NOT EXISTS logstv....
1
vote
0answers
17 views

Query that fetchs average values over timestamp range of time, on a certain value of another pair of fields

This is the situation, over the time our table reached around 800 millions rows. And it is going to get bigger and bigger. It is a service that collects metrics of energy (voltage, power, current, etc)...
1
vote
1answer
49 views

Functional Indexes in Oracle, compared to MySQL

Hoping you can help me understand a particular problem I'm facing when converting an Oracle 12c database to MySQL 5.7. For context, let's say we have a table that looks a bit like this: id ...
0
votes
2answers
34 views

Optimize Postgresql query

I'm struggling to get the below query to perform well. It's doing a sequential scan on the children table even though there's indexes on the join columns. Any ideas? EXPLAIN ANALYZE SELECT "children"....
0
votes
0answers
528 views

Rebuild Index vs Drop and create indexes in Dynamics GP

So we're a Dynamics GP house, running on top of SQL 2014. When our vendor does a software update, they run a special Microsoft Dexterity procedure called Table_CreateIndexes(), which drops the keys ...
0
votes
1answer
23 views

Does dropping index on column A affect queries on column B?

I have a table like below. Column | Type ------------------------------- id | integer foo_1 | character varying(255) foo_2 | text ...
2
votes
1answer
53 views

Temporarily disable indexes before updating whole table

For testing purposes I periodically restore production backup into a new database (AWS RDS PostgreSQL instance) with pg_restore. In the new database I run SQL queries that anonymize sensitive data. ...
0
votes
1answer
40 views

Index for split string. PostgreSQL 10

I have the following string in db: A/B/C/ A/B/D/ E/F/C/ It's the uri column. The table is the following: create table meta_info ( id bigserial primary key, uri varchar, created ...
1
vote
2answers
42 views

MySQL refuses UPDATE with (1071, 'Specified key was too long; max key length is 1024 bytes') sometimes

I'm using MySQL 5.7.2.23. I need to copy some columns between two tables (u -> m) and I am using the following update command: UPDATE m JOIN u ON u.id=m.user_id AND u.package_name=m....
4
votes
2answers
842 views

SELECT is using a non-PK Index instead of the PK

In the [dbo].[Programs] table, the column [Id] is the PrimaryKey (not par of a composite key). There are also quite a few other indexes on that table. When I'm running this simple query, SELECT [Id] ...
0
votes
0answers
25 views

log_queries_not_using_indexes appears to log queries that ARE indexed

Firstly, I also have long-query-time = 999 to prevent any "slow" queries from showing up. So the only queries that should be in my "mysql_slow.log" file should be those without indexes. But here's ...
1
vote
1answer
23 views

How can I index a text[] field in postgresql?

I have two columns: alternate_names text[] first_name character varying I am not sure how I can write an index on this type of query? SELECT "people".* FROM "people" WHERE (lower(first_name) = '...
0
votes
1answer
28 views

What are the pros and cons of adding secondary columns to a table's primary key?

In MySQL, I understand that with InnoDB the contents of the primary key get attached to the leaf node of every other index on the table. I have a photos table. Queries on it come in many different ...
-1
votes
1answer
37 views

Why would someone choose to increment a unique key with a value higher than 1? [duplicate]

Why would someone choose to increment a unique key with a value higher than 1? For example instead of unique index going from (1, 2, 3, 4, 5) It goes(8, 15, 22, 29, 36), starting at 8 and then each ...
0
votes
0answers
28 views

Index for reverse regex match

I have a table with hundreds of thousands of regexes. I need to query if a given text matches any of those. A demo case would look something like: create table regexes as select i as id, ...
1
vote
0answers
52 views

Dynamic creating Indexes [closed]

We have more than 100 indexes exist in our DB. Sometimes due to some processes a part or all the indexes disappear. This is not in our hands to avoid this situation. I can write the SP which will ...
0
votes
3answers
49 views

Partial local index on partitioned Oracle table

We have a big transactional table, partitioned on transaction date (day). Each day, several million records are added. We need to do selects on this table, over a period of the last five days, and ...
1
vote
3answers
42 views

How to get rid of a Bitmap Heap Scan if the table already has the proper indices

I am running PostgreSQL 9.6. These are the relevant definitions: CREATE TABLE IF NOT EXISTS instagram.profiles_1000 ( id SERIAL PRIMARY KEY, username ...
0
votes
1answer
89 views

Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index “foo”?

We have repeatedly seen failing attempts to index columns with values exceeding a maximum size. Postgres 10 has this kind of error message for it: ERROR: index row size xxxx exceeds maximum 2712 for ...
1
vote
1answer
52 views

Are there any performance differences with a GUID vs INT clustered indexed column, especially with joins on those columns?

Basically what the title says: What are the differences in performance (if any) between using a clustered index on a GUID column vs a clustered index on an INT column, especially when those columns ...