Tagged Questions
1
vote
1answer
60 views
SQL Index order and performance based on cardinality and data
I've been working quite a bit on a query that's not operating very efficiently on a DB2 database. The thing will return up to 30k rows or so and take up to 10 seconds. I've been working on getting ...
2
votes
1answer
121 views
Bitmask Flags with Lookup Tables Clarification
I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries ...
1
vote
1answer
14 views
Big jump in search time for Postgres Index query for results with high selectivity
I am doing some performance comparison of databases and lucene for full-text searching.
So I use Postgres to create an Index for the data to search:
CREATE INDEX bodies_index
ON bodies
USING gin
...
5
votes
2answers
249 views
Recreate Indexes on 1 billion record table
I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space ...
2
votes
1answer
34 views
What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?
Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this?
SELECT id FROM table
WHERE table.int_col > 1;
I'm specifically interested in ...
6
votes
1answer
180 views
Landed as BI, but databases are a big WTF, what to do?
Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
0
votes
3answers
18 views
Mysql - How to optimize retrival time in a table
I have query like this! which has 200 million Records in a single table.. I am using BTree Indexes in my table...
mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3;
...
3
votes
0answers
72 views
Key Lookup and Full-text index
I have the following query:
SELECT T2.Title
FROM TitleTable T1
INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1
WHERE T1.FKID2 = @ID_PARAM1
AND T2.FKID2 = @ID_PARAM2
AND ...
0
votes
1answer
68 views
What is the maximum number of rows in a clustered index on datetime column?
I would like know what the max no of rows is in a clustered index (non-unique) on a datetime column table in SQL Server 2008R2.
7
votes
2answers
175 views
Different results rebuilding an index online and offline
I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages.
When I run ...
1
vote
1answer
102 views
Query is not using indexes on third table in left join
My query is not using indexes on third table(pci promotion_coupon_images) .
i was tried with using index and force index in query but there is no change in result.
any body please suggest me how ...
1
vote
1answer
67 views
index doesn't work properly
I have this query
SELECT user_id, MAX( highscore ) AS highscore, stage, COUNT( * ) AS count
FROM single
GROUP BY user_id, stage
table engine is innodb, plus I haven't use any indexes except one key ...
4
votes
1answer
191 views
Indexing strategy when using the between operator SQL Server 2008
I have a large table ~25 million rows with the structure
CREATE TABLE [dbo].[rx](
[pat_id] [int] NOT NULL,
[fill_Date] [date] NOT NULL,
[script_End_Date] AS ...
5
votes
1answer
77 views
Efficiently Filter Large Set With Disjunctions
Let's say I have a single table
CREATE TABLE Ticket (
TicketId int NOT NULL,
InsertDateTime datetime NOT NULL,
SiteId int NOT NULL,
StatusId tinyint NOT NULL,
AssignedId int NULL,
...
4
votes
1answer
84 views
Does the order of fields in SELECT query matter when using composite indexing?
I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?
For example, if I ...
3
votes
1answer
124 views
Index Key Column Order
I have created a joining table for many-to-many relationship.
The table only has 2 cols in it, ticketid and groupid
typical data would be
groupid ticketid
20 56
20 87
20 ...
1
vote
1answer
61 views
Possible to query all SQL Server Indexes which have page locks enabled?
Is it possible to run a SQL Server Query in order to view all SQL Server Indexes which have page locks enabled?
0
votes
0answers
30 views
Algorithmic order of table indexes for table operations [duplicate]
Possible Duplicate:
Algorithmic order of table indexes for table operations
Duplicate of this question on stackoverflow
I have not been able to find a clear indication of the impact table ...
0
votes
2answers
86 views
Indexing a longtext field
I would like to run an index on a longtext field using:
CREATE INDEX post_meta ON wp_postmeta (meta_value(8));
There are currently ~1 million records.
Questions:
Will creating this index affect ...
3
votes
2answers
71 views
Algorithmic order of table indexes for table operations
I have not been able to find a clear indication of the impact table indexes have on the algorithmic order of table operations.
I am not interested in the nuts & bolts of specific implementations; ...
0
votes
1answer
91 views
clustered index - data warehouse
My source data comes from multiple OLTP databases where the primary key is an identitiy column. We anticipate adding data centers and/or splitting the databases by product. Currently i have one system ...
1
vote
2answers
133 views
How do I force a JOIN to use a specific index in MySQL?
I have a query that JOINs 2 tables; lineitem and part,
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem force index for join (l_pk),
part
where
(
...
1
vote
3answers
156 views
Usefulness of a multi-attribute index
If an index has more than one attribute in it, is there any speed gained in a select statement whose where clause uses one of the attributes in the index?
For example, take a table T with an index on ...
4
votes
2answers
87 views
We are running database maintenace with indexing the system, indexing fails due to locking?
We have been trying to do re-indexing in our system, there are users still using the database during the indexing. While doing index for certain tables there is failure of indexing this seems to be ...
5
votes
3answers
283 views
Query performance degrades with time and use
I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my ...
4
votes
1answer
173 views
Does UPDATE INDEXES work for local indexes?
I have two tables in Oracle 11.2. First is partitioned and has local index (non-prefixed):
CREATE TABLE table1 (col1 INT, col2 INT)
PARTITION BY LIST(col1)
(
PARTITION p1 VALUES(1),
PARTITION ...
3
votes
1answer
69 views
query discrepancies on very large postgres table
I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem.
these two queries although similar are taking massively ...
4
votes
5answers
250 views
How much does an index need to narrow the results of a search in order to be useful?
How much does an index need to narrow the results of a search in order to be useful in speeding up queries?
Some examples all across the spectrum:
A column for storing true/false values obviously ...
0
votes
1answer
161 views
how to pass url value to index [closed]
How to read the value from URL and compare with index that i have created in MYSQL table. I have a form where provide list of selection using drop down list and i pass the selected value to the next ...
1
vote
1answer
112 views
Index strategy for a Queue Table, getting rid of filesort
I am trying to get rid of a filesort operation (and preferably also optimize a bottleneck query). We are using an innodb mysql table as a queue to process various incoming business activities. It is ...
8
votes
2answers
148 views
Non-Clustered Indexes - keys and nonkeys
I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated.
Here's my theory from the query I've just optimised, through a process of trial and ...
2
votes
0answers
75 views
SQL fulltext indexing ranking combined with ranking used by window's file indexing service
We have a list of records contained within a MS SQL database table, we have enabled "Full Text Indexing" on the table. Also, we have set up a window's file indexing service and added a catalog ...
7
votes
2answers
336 views
Filtering data ordered by rowversion
I have a SQL table of data with the following structure :
CREATE TABLE Data(
Id uniqueidentifier NOT NULL,
Date datetime NOT NULL,
Value decimal(20, 10) NULL,
RV timestamp NOT NULL,
...
1
vote
0answers
31 views
How does MySQL 5.1 use indexes?
I've read the docs for MySQL v5.1.x on how about the indexes are used, but I'm not sure I understood it properly.
Suppose having the following MySQL table:
CREATE TABLE IF NOT EXISTS ...
2
votes
2answers
205 views
difference between having many non clustered index with single columns and with combination of many columns
Folks
I am a newbie to DB design stuffs. I am wondering what would be the difference and performance difference between having designed
many non clustered index with single columns and non ...
4
votes
2answers
263 views
Does a cluster index provide more benefits than pre-sorting the load file and creating non-cluster index?
Informix 11.70.TC4DE:
CREATE TABLE cluster_tbl
(
fk_id INT,
data CHAR(2048)
);
LOAD FROM "presorted.ld" INSERT INTO cluster_tbl;
CREATE UNIQUE CLUSTER INDEX cl_idx ON cluster_tbl(fk_id);
...
0
votes
2answers
77 views
SQL and Multiple Indexes
Consider the following table:
i | a | b | c
--------------
0 | 1 | 2 | 3
1 | 4 | 5 | 6
2 | 7 | 8 | 9
3 | x | y | z
The table, in the end, will have several thousand rows, and even more columns then ...
3
votes
2answers
299 views
Slow ORDER BY with LIMIT
I have this query:
SELECT *
FROM location
WHERE to_tsvector('simple',unaccent2("city"))
@@ to_tsquery('simple',unaccent2('wroclaw'))
order by displaycount
I'm happy with it:
"Sort ...
6
votes
1answer
279 views
computed columns, index, clustered index and covering index?
We all know the we can only have one clustered view per table, cool. But apparently you don't have values of computed columns on leaf even when using a clustered index and they are computed each time.
...
1
vote
1answer
106 views
SQl code performance
What measurement do you know if this sql code is efficient based on execution plans?
How do you know if this sql code is efficient based on execution plans?
SELECT [CustomerID], ...
2
votes
1answer
214 views
Disabling Indexes and Auditing for large data import
Hopefully the title describes this sufficiently, but I have a process that runs and performs thousands of individual inserts and updates (nothing I can do to change that). My questions are:
1.
Is it ...
0
votes
1answer
95 views
Creating Indexes to optimize query
I have this query and I want to increase its performance by adding appropriate Indexes.
DELETE
FROM MYTAB1
WHERE MYID1 IN
( SELECT MYID2 FROM MYTAB2 );
I am not familiar with the ...
3
votes
1answer
3k views
Unique index corrupted SQL. Select query returns single row but create unique index fails
I am not able to repair my database due to below errors, when I run dbcc checkdb(DBNAME, REPAIR_ALLOW_DATA_LOSS) I am getting the errors listed here.
When I run a select query for these key values, ...
2
votes
1answer
192 views
Error while creating index in SQL Azure
I'm trying to create an index on table [Children] in SQL Azure but get the following error:
The operation failed because an index or statistics with name
'IX_Children_ParentID' already exists on ...
2
votes
1answer
893 views
How is INDEX on Composite Primary Key in mysql?
When making a composite primary key for two or more columns, e.g. PRIMARY KEY(col1, col2, col3); will the system INDEX each column individually?
The reason that I am asking this question is that when ...
1
vote
2answers
83 views
Is it possible to have indexes for like '%*%' type queries
Now for my thesis project i need to make a lot of queries like the below
select * from tblMyTable where ItemName like '%item%'
Now my question : is that possible to create indexes for such queries ...
4
votes
1answer
110 views
Filtered indices: Why include the filtered-on field?
This just drives me mad.
Consider a simple table with irrelevant columns removed:
create table boxes (
row_id int not null identity(1,1) primary key,
location varchar(15) null,
dismantled bit ...
9
votes
1answer
2k views
Order by column should have index or not?
I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
0
votes
1answer
178 views
FTS - Index rebuild causing CPU issues (SQL Server 2008 R2)
I am encountering some critical issues on SQL Server 2008 in a production environment; We have a database that contains a FTS index; we also have a maintenance job that rebuilds the FTS index and ...
3
votes
2answers
2k views
MySql - How can I speed up this query
I have the following tables:
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`last_name` varchar(255) COLLATE ...
