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.
3
votes
2answers
27 views
Which non-clustered index should I have to use composite or single column in SQL Server?
I have following columns in my database table (Medicines).
ID bigint,
MedicineName nvarchar(50),
BrandName nvarchar(50),
MedicineCode nvarchar(20),
and price,quantity.
I am making a stored ...
5
votes
4answers
133 views
Index Seek vs Index Scan
Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan.
What is the difference between and index seek and an index ...
4
votes
1answer
128 views
Indexes file damaged, was on ramdisk
I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
1
vote
2answers
94 views
Auto-generate scripts to create tables, indexes, etc
In SSMS, we can right click a database/table/index/... and select the "Script As" option to generate a drop and create script.
Is there a way to automate this script generation and drop the scripts ...
2
votes
1answer
71 views
mysql: need help to optimize my query/table
I'm wondering if someone could help me optimize my tables/query to speed up a query. It is currently running ridiculously slow. I think a well-thought out index could help me. Any help would be really ...
0
votes
2answers
90 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 ...
0
votes
0answers
23 views
How to make continues cluster in postgres?
I have a report table with the following index:
providerid,date
The table is around 30M records and its grow on a daily basis based on provider actions (100K grow for a day).
I want to use in the ...
2
votes
1answer
119 views
+100
MySQL Hanging Completely when `ALTER TABLE… ENABLE KEYS`
I know very little about database administration but I have to deal with some very large tables on my site.
This server has 64GB of RAM and Intel Core i7-3820 (4 x 3600 MHz). Most of everything it ...
-3
votes
0answers
24 views
How do I add a index column to a database with non unique columns [closed]
I have a database with non unique data in columns, How do I add a index column or add a primairy key column?
3
votes
1answer
72 views
In MySQL, does the order of the columns in a WHERE clause affect query performance,why?
I have a query that doesn't use any indexes:
SELECT 32,
guid,
1,
1,
1,
0,
5
FROM test
WHERE level >= 20
AND ( ( fun_GetIndexValue(data, 354) ...
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,
...
0
votes
1answer
49 views
Can I use the database and query it while full-text indexing?
I need to index my database, but I need to query it while it is indexing, it may take a long time, can I do that?
1
vote
2answers
475 views
How can an identity primary key index become fragmented?
From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered.
Example:
ALTER TABLE [dbo].[ClaimLineInstitutional] ADD ...
1
vote
1answer
66 views
SQL Server clustered index high fragmentation
I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index.
Inserts are working perfectly fine. After inserting 2000 rows the fragmentation ...
2
votes
2answers
75 views
Suspended in index rebuild
In SQL Server 2012, an Index Rebuild job is taking a very long time (up to 8 hours). However, not even one Index rebuild completed, so I stopped the index job.
In monitoring SQL task:
state = ...
0
votes
1answer
51 views
Two identical MySQL tables, one not using indexes
I have one MySQL Server (5.0.37) on a Linux Server that have many databases. Some of them (12) have each an identical table to log sensors data.
Schema of one table
CREATE TABLE `measuresHistory` (
...
2
votes
2answers
520 views
Can I add “Included Columns” to an index without affecting performance?
I have a non-clustered index with 63 Million leaf level rows. Currently it does not have any included columns. I would like to add one included column while the site is online. Will this significantly ...
1
vote
1answer
63 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 ...
1
vote
1answer
69 views
I would like to query a range of criteria on multiple columns in MySQL
I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example :
We have 3 columns as ...
39
votes
6answers
8k views
How to determine if an Index is required or necessary
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
0
votes
0answers
7 views
Fine grained (milliseconds) temporal indexes in neo4j
What are the best practices for modeling fine grained timelines in neo4j? If I were to use milliseconds as the grain in the pattern described here and here would it prove somehow problematic and ...
3
votes
1answer
57 views
How can I get this query to use it's index?
Queries matching this query below shows up in the slow log (with different contentVersionId & modifiedDateTime).
As I don't really know what I'm doing I tried adding indexes to all columns in ...
5
votes
2answers
143 views
Why disabling a clustered index makes the table inaccessible?
When an index is disabled, the definition remains in the system catalog but is no longer
used.
SQL Server does not maintain the index (as data in the table changes), and the index
cannot be used to ...
1
vote
1answer
103 views
Why do MySQL MyISAM table indexes go out of date?
I have few MyISAM tables, where only the auto_increment primary key is up to date with the number of columns, but not other indexes.
Any idea, why this happens?
1
vote
1answer
74 views
Consolidating indexes
I have one big table that is used to generate business intelligence cube. Currently it has around 40M rows and 55 columns. A lot of the cube dimensions are generated by running 'select distinct' on a ...
0
votes
1answer
33 views
MySQL - Index on a query with order
I have the following table with >1M rows:
CREATE TABLE `wishlist_place` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`wishlist_id` int(11) DEFAULT NULL,
`place_id` int(11) DEFAULT NULL,
`city_id` ...
1
vote
1answer
46 views
mysql varchar index - will hashing value make it faster?
I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values 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 ...
2
votes
1answer
122 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 ...
0
votes
1answer
74 views
SQL Server: Sync Indexes between two tables within Same database
I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within same database.
Tried using SSIS SQL Server Objects Task, but looks like it works only when we sync between ...
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
...
0
votes
1answer
24 views
Which text-index I should create for xooops engine to achieve better search results?
In one of projects we use xoops engine to manage content. In mysql slow query log most of queries are following :
SELECT p.uid,f.forum_id, p.topic_id, p.poster_name, p.post_time, f.forum_name, ...
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;
...
2
votes
0answers
90 views
Why does CREATE INDEX … WITH ONLINE=ON block access to the table over a period of minutes?
I have an existing table:
CREATE TABLE dbo.ProofDetails
(
ProofDetailsID int NOT NULL
CONSTRAINT PK_ProofDetails
PRIMARY KEY CLUSTERED IDENTITY(1,1),
ProofID int NULL,
...
2
votes
2answers
105 views
Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?
I have a table that has a large number of rows in it.
The primary key (an auto-incrementing integer) is, by default, indexed.
While waiting for a row count to be returned I did an EXPLAIN in another ...
0
votes
1answer
69 views
Mysql. Block/disconnect slow queries
I am experiencing issue.
Someone 'attacked' my server: simply by searching the same phrase with multiple requests.
As it is text search request and database indices are not used, the engine searches ...
6
votes
1answer
72 views
Minimizing Indexed Reads with Complex Criteria
I'm optimizing a Firebird 2.5 database of work tickets. They're stored in a table declared as such:
CREATE TABLE TICKETS (
TICKET_ID id PRIMARY KEY,
JOB_ID id,
ACTION_ID id,
STATUS str256 ...
1
vote
2answers
26 views
Why PostreSQL 9.2 does not use index scan if it can?
My PosgreSQL planner (9.2) does not pick index and rather choose to do seq scan.
Here is my table. The index I am talking about is name_left_prefix
cwu=# \d web_city;
...
2
votes
1answer
34 views
Rebuilding Unique Index with uniqueidentifier in SQL Azure never succeeds
We have a number of tables (~1M records) that have a column on them defined as: [GlobalID] [uniqueidentifier] NOT NULL that gets auto-populated with newid(). We use this ID for synchronizing data ...
0
votes
0answers
18 views
MySQL (v5.6.11) InnoDB 5.6.11 restoring indexes during table copy
When i see the profile for my optimize table query here is what i see for the InnoDB storage engine 5.6.11:
+----------------------+------------+
| Status | Duration |
...
0
votes
2answers
103 views
How do you correct a corrupted index in Oracle?
I have this ORA error in our logs:
Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921) ORA-01110: data file 8: ...
4
votes
3answers
125 views
Index before or after bulk load using load infile?
I have a database with over 1B rows and two columns that are indexed (in addition to the PK).
Is it better to have the index pre-defined in the table before the load infile or better to index after ...
0
votes
1answer
54 views
How do you correct a corrupted index in Oracle? [duplicate]
I have this ORA error in our logs:
Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921)
ORA-01110: data file 8: ...
1
vote
2answers
243 views
Slow insert with MySQL full-text index
I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right?
...
0
votes
0answers
42 views
one vs two column index difference when doing JOIN query?
Let`s suppose that alfa,beta and gamma contains milions of rows so we need to create indexes obviously to get optimal performace for this query :
SELECT * FROM alfa
JOIN beta on beta.id = ...
0
votes
2answers
36 views
Is there slowdown inserting into an InnoDB table that has no index set?
I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such.
Those tables only contain a few thousand rows.
Would it be faster to INSERT data ...
-1
votes
0answers
61 views
What is meant by Clustering index and Multilevel indices? [closed]
What is meant by Clustering Index and Multilevel Indices ?
I could not find much when I googled it.
3
votes
1answer
174 views
Schema design: Use of association (aka: bridge/junction) table vs foreign key constraint with composite index containing a non-key field
This is an inventory database for IT assets. The models used are trimmed in order to focus on the problem at hand. Using SQL Server 2008. Thanks for taking the time to read and for any input you can ...
1
vote
0answers
29 views
parenthetic grouping of join clauses
What is the difference between these joins?
a left join b left join c
(a left join b) left join c
a left join (b left join c)
Does the grouping only affect the order of the joins? Will the query ...
1
vote
2answers
120 views
Clustered vs Nonclustered Index
My database currently has a primary Key/Clustered index on the ID column for each table. However, the application that connects to the database is always looking at views defined as:
SELECT * FROM ...
