0
votes
1answer
53 views

Why isn't this index helping my InnoDB MySQL query?

I'm having trouble understanding why an index I have on one of my tables is doing nothing to help the performance of a JOIN, even when forcing the index. Here is the query: EXPLAIN SELECT * FROM ...
2
votes
1answer
41 views

Adding Indexes To a Partitioned table in SQL Server 2012

I have recently converted our one large table to use table partitioning in SQL Server 2012. It's a table that stores logs based on a time. So I created a partition function to split the table on my ...
1
vote
2answers
96 views

How to add an “INCLUDE” part in an Index Created as a part of CONSTRAINT

I am going through the execution plan of a tsql query. There is a key LookUp (Clustered) in the plan. To avoid this Key LookUp, I tried to Include a column (Status_Ind) to The Index (The Index ...
0
votes
0answers
42 views

LIKE or CHARINDEX to check String Contains a Substring [duplicate]

To check a string contains a sub string or not we can use [LIKE] or [CHARINDEX]. I know that like uses indexing in case we have name%.(while %name% may not use index) .Also charindex does not use ...
2
votes
0answers
60 views

Can there be any case where it would require additional indexes while all columns compose primary key

Ok i don't know whether this question is asked before or not Here my table SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblProductPagesFeatures]( ...
1
vote
1answer
108 views

Why isn't SQL Server using my non-clustered index and doing a clustered index scan?

Here are my full tables: CREATE TABLE [dbo].[tblCrawlUrls]( [cl_IdUrl] [int] IDENTITY(1,1) NOT NULL, [cl_CrawlNormalizedUrl] [nvarchar](200) NOT NULL, [cl_RooSiteId] [smallint] NOT ...
2
votes
1answer
48 views

Running a query on an improperly indexed, 600gb table

This is my first post on here and I'm really baffled by this. I have joined a team and somewhere in the past a horrific table was created which has been bloated to a point of 600+gb of data. ...
3
votes
1answer
202 views

Optimizing large database query (25+ million rows, using max() and GROUP BY)

I'm using Postgres 9.3.5 and I have a large table in the database, currently it has over 25 million rows and it tends to get even larger rapidly. I'm trying to select specific rows (all unit_ids with ...
1
vote
1answer
18 views

Index on integer column not helping. Help? (EXPLAIN ANALYZE output inside)

The table market_trades has an integer column market_id which takes values from 1 to 5. This seems like a pretty straightforward query to get the number of trades on each market: WITH markets AS ( ...
2
votes
2answers
70 views

Database events that will cause deletion of index

In SQL server if we try to change a data type of a column having index ,then it deletes the indexes associated with that column and changes the data type . I was not aware of it and was faced problem ...
0
votes
1answer
106 views

MySQL 5.1 -> 5.6 problem, select distinct order by limit query stops using index

I'm testing a MySQL 5.1 -> 5.6 upgrade, and I've run into an issue. I have a query that hits a ~30 million row table, selecting distinct IDs with an order by/limit restriction. Sample query is ...
3
votes
2answers
88 views

Pagination with PostgreSQL 9.3: counting number of pages

I'm implementing pagination and sorting of rows in, let's say, products table, using multicolumn index on category, score and id. -- index create index category_score_id on products(category, score, ...
0
votes
1answer
45 views

Batch converting indexes to keys [closed]

I have a legacy MS SQL Server database where all the primary and foreign keys were created as indexes. I am developing the new version of our software with Entity Frameworks, which I have learned ...
2
votes
2answers
71 views

I need help optimizing a not that complicated query that returns unexpedcted performance problems

I have a table that stores details about incoming and outgoing phone calls such as when a call was made, or when a call was received, the phone number of the caller, and the phone number of the ...
1
vote
1answer
110 views

Why is this second query more expensive than the first?

While reading Graph Databases book, I came across this excerpt: I can't figure out why the second query is more expensive than the first. Indeed, both filter on the Bob person. May anyone ...
2
votes
2answers
65 views

best possible index on this situation [closed]

create table #testing(id int, name varchar(50)) go Insert Into #testing Values(231, 'fasd') Insert Into #testing Values(232, 'dsffd') Insert Into #testing Values(233, 'xas') Insert Into #testing ...
1
vote
1answer
177 views

Very slow simple PostgreSQL query on RDS

I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram). This is across a table of 4,152,928 rows.. select sum(some_field) c from pages where pages.some_id=123 and ...
10
votes
1answer
189 views

Rejoining range seek on nullable composite index?

For the following schema and example data CREATE TABLE T ( A INT NULL, B INT NOT NULL IDENTITY, C CHAR(8000) NULL, UNIQUE CLUSTERED (A, B) ) INSERT INTO T (A) ...
1
vote
1answer
41 views

Can't figure out this “trick” to optimize MySQL query

Here's an excerpt of this article: SELECT id, name, address, phone FROM customers ORDER BY name LIMIT 10 OFFSET 990; MySQL is first scanning an index then retrieving rows in the table by ...
1
vote
1answer
59 views

MySQL from table to table data transfer

I've got some problem with my MySQL. I have the database with many many data. Well, there is only one table actually with huge ammount of data, over 1 billion rows. For some reasons I need to create ...
1
vote
2answers
61 views

how to use an index on a mysql query with a where clause and a order by

I have a table CREATE TABLE activity ( id INT(10) UNSIGNED AUTO_INCREMENT, subscriberid INT(10) UNSIGNED NOT NULL DEFAULT '0', action VARCHAR(250) NOT NULL DEFAULT '', ip INT(10) UNSIGNED NOT ...
0
votes
2answers
42 views

Why search by date field requires engine to touch all records?

Why search by date field requires engine to touch all records? Is there any way to avoid it? Following query EXPLAIN select * FROM playdays where realdate>=Date('2010,01,01') ; shows that ...
2
votes
1answer
77 views

Does Index Intersection work with Included Columns?

I've been asked by a report designer whether it is acceptable to add a new index to a table to speed up a particular report. The index would be on a single column, CreatedDate, but would have ...
2
votes
1answer
83 views

Will adding more indexes speed up using RedGate SQL DATA compare?

We want to use RedGate's SQL Data Compare to migrate user data from one DB to a DB on a completely different provider. This will be a time-sensitive operation as we do not want our site offline for ...
3
votes
4answers
1k views

How to check for existence of an INDEX on a column independent of used SQL database

Is there are unified way of how to check for the existance of an INDEX for a given column irregardless of the actual SQL database system used? For MySQL one could for instance check for the existance ...
2
votes
1answer
75 views

Optimizing indexes

I just began optimizing my schemas with indexes (pretty late I guess). I never dealt with huge dataset but I'll be soon coping with about 1 to 5 millions rows. Here is the schema I'm using right now ...
0
votes
1answer
54 views

How can I rewrite the following removing the date_format function?

I have a table with a column when that is of type datetime. I am doing the following filter which can not use and index: AND DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) ...
8
votes
1answer
460 views

Deadlock when update different rows with non-clustered index

I'm solving a deadlocking issue while I noticed the lock behavior is different when I use clustered and non-clustered index on the id field. The deadlock issue seems to be solved if clusted index or ...
0
votes
2answers
75 views

Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

I just need advice on how I am going to optimize my database. Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN I have that format of tables on my database. Each ...
17
votes
2answers
649 views

SQL INDEX - how it works?

My knowledge of databases and SQL is based in most on university classes. Anyhow, I spent few monts (almost a year) in a company, where I was working with databases. I have read few books and I have ...
1
vote
1answer
73 views

SQL INDEX - how it works? [duplicate]

See this question here: Database Administrators meta My knowledge of databases and SQL is based in most on university classes. Anyhow, I spent few monts (almost a year) in a company, where I was ...
0
votes
1answer
77 views

Why is this query slow, it seems simple

I have a very typical table of user information for my application, with an average set of columns (ints, varchars, etc). In this table are two varchar(100) fields: emailAddress and emailAddress2. ...
1
vote
1answer
108 views

Optimizing Postgres query

I've got a one to one relation from users to addresses table. Where one user can have one search address and one verified address. I've got two indexes on addresses table : Index on state field ...
0
votes
0answers
134 views

Based on these DBCC SHOWCONTIG stats, should I index this table?

I have a SQL 2008 Standard server on full mirror (safety). I reorganize all indexes weekly. I am looking into rebuilding a few indexes to improve performance. I ran DBCC SHOWCONTIG on my largest, most ...
1
vote
1answer
43 views

What indexes would I create for a query like this?

Here's a query I've got: SELECT "balance_transactions"."fee" FROM "balance_transactions" JOIN charges ON balance_transactions.source = charges.balance_id WHERE "balance_transactions"."account_id" = ...
5
votes
1answer
97 views

2008R2 Cannot Disable Non-Clustered Index

We have identified a handful of superfluous non-clustered indexes and I would like to disable them for a few days before straight dropping them. However when using the GUI in SSMS (right click > ...
4
votes
2answers
93 views

StackExchange clone: where should I add my indexes?

I'm creating an open source stack exchange clone and the following is my schema. What should I add indexes on for it to be optimal? Here is the schema in Rails format (SQL format below as well): ...
0
votes
2answers
151 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...
0
votes
1answer
250 views

What is blocking 'SELECT… WITH(NOLOCK)'?

While following BrentOzar's "How Much is Offline During an Index Rebuild?" I was curious to run: SELECT Quantity FROM [Production].[TransactionHistory] WITH(NOLOCK) while executing a rebuild ...
0
votes
1answer
67 views

Constrain exclusion to fetch latest N rows

I'm managing a table with several million records which are being inserted in real time. A part of my app needs to display the last N inserted records, so at the beginning I just queried for: ...
1
vote
1answer
62 views

When is the index actually created?

I have noticed that even on huge tables with millions of records when I do a CREATE INDEX the index is created immediately (I get the console back in nsecs). Why is this? Doesn't it create the B-Tree ...
2
votes
2answers
168 views

What can be done to further enhance performance of Multiple Join and Aggregate Queries?

I have a typical star schema simulated here, and I am mentioning two queries: first query simply joins the fact table with 2 dimension tables and 1 calendar table, and the second query joins and ...
17
votes
2answers
991 views

Index not making execution faster, and in some cases is slowing down the query. Why is it so?

I was experimenting with indexes to speed up things, but in case of a join, the index is not improving the query execution time and in some cases it is slowing things down. The query to create test ...
1
vote
1answer
201 views

Using coalesce on an index column

Does doing a COALESCE on an indexed columns in a WHERE clause e.g. WHERE COALESCE(table1.indexed_column, '9999-01-01') > table2.other_date_column affect indexing in a bad way? I think that it ...
18
votes
3answers
887 views

Index on Persisted Computed column needs key lookup to get columns in the computed expression

I have a persisted computed column on a table which is simply made up concatenated columns, e.g. CREATE TABLE dbo.T ( ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY, A ...
6
votes
2answers
2k views

Efficient query to get greatest value per group from big table

Given the table: Column | Type id | integer latitude | numeric(9,6) longitude | numeric(9,6) ...
0
votes
0answers
230 views

Using an enum as part of an index definition

When I use an ENUM column in a table the ORDER BY uses the ordinal of the ENUM not its value. I wasn't aware of that. So my question is what are the subtle issues when an ENUM is part of an index ...
2
votes
3answers
110 views

Are these indexes redundant?

I have two indexes on a table that will contain roughly 10-20 million records at a given moment. Index 1 CREATE NONCLUSTERED INDEX [IX_NC_AccountNumber_UTC] ON [dbo].[LogWindow] ( ...
1
vote
0answers
2k views

How to speed up query on table with millions of rows

The Issue: I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are ...
1
vote
1answer
53 views

Creating Multiple Indexes on SQL Server [closed]

I am creating multiple indexes on a table in SQL Server. The script is still executing but all the required indexes are showing as objects in Object Explorer. Do the objects get created before the ...