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.
1
vote
3answers
5 views
Nonclustered index is faster than clustered index?
Both tables have same structure and 19972 rows in each table.
for practicing indexing, i created both tables having same structure and created
clustered index on persontb(BusinessEntityID)
and
...
2
votes
1answer
93 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
121 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 ...
6
votes
1answer
44 views
Index usage stats DMV indicates no index activity
I'm trying to obtain information on indexes. According to several websites, the below TSQL query should join information on indexes to the indexes table, returning indexes and related information. ...
2
votes
1answer
80 views
Does the size of the primary key contribute to table size?
I have a table (InnoDB) with data length around 36G and index length 23G.
It had a composite primary key across three columns.
As an initial attempt to reduce the size of this table, I dropped the ...
0
votes
1answer
43 views
Update performance: clustered versus covering index
I have a simple, 3-column table with about 50 million rows in it. This table gets about 5,000 inserts/updates per second, and perhaps 20 queries per second are executed against the table. The table ...
1
vote
5answers
68 views
What happens when we create index
What happens exactly when I create an index on the empid column of this employee table
CREATE TABLE employee
(
empid number(5),
ename varchar2(30),
sal number(10,2)
);
Let's assume the ...
3
votes
1answer
88 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) ...
6
votes
1answer
90 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,
...
16
votes
6answers
1k views
Why don't databases create their own indexes automatically?
I would have thought that databases would know enough about what they encounter often and be able to respond to the demands they're placed under that they could decide to add indexes to highly ...
0
votes
0answers
38 views
Why does selecting top 1 from composite index DESC also used to partition by month not select the top value?
We have very large database table that I need to select the value of the max id. Given the table size SELECT MAX(id) FROM tableA will not perform well enough.
Table definition (with columns omitted ...
1
vote
1answer
117 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 ...
0
votes
0answers
21 views
How to/advice for hosting a multi-user database on the cloud with specific objectives
I have developed an application that users will be able to download and create an account with through our company. This application will always be connected to the internet. I have been testing the ...
1
vote
1answer
29 views
Rebuild an index while it is being referenced in Oracle
I have come across many articles on this site and on Tom Kyte's blog on the how, whys and whens of Re-building indexes. Note that this question is about whether I can rebuild when there are concurrent ...
1
vote
1answer
73 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 ...
1
vote
1answer
28 views
Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”
Environment:
Ubuntu 12.04 (and 13.04)
MySQL 5.6.11
I have a table which has a full text index on it (real table has much more columns and rows):
DROP TABLE IF EXISTS articles;
CREATE TABLE ...
3
votes
1answer
71 views
Index on foreign key makes query extremely slow
We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem.
The queried table (table3) has an indexed PK, three FK with indexes ...
3
votes
2answers
67 views
If I update a column record in a table, will indexes that do NOT have this column in it be affected?
In terms of performance if I have a table like so:
CREATE TABLE [TESTDATA].[TableA](
[Col1] [nchar](5) NOT NULL,
[Col2] [nchar](2) NULL,
[Col3] [float] NULL
CONSTRAINT [TableA_PK] PRIMARY ...
4
votes
0answers
54 views
Indexing strategy for dynamic predicate
Assume SQL Server 2012 Standard edition.
My database has a table with 500 million rows. The table has about a dozen columns, none of which are very wide (some varchar(100)'s and some ints).
The ...
0
votes
1answer
15 views
MYSQL - Using simple inventory number convention as Primary Key
I have an inventory with three numbered groups like so:
Group 1: 0-999
Group 2: e0-e999
Group 3: v0-v999
Can I simply make a one-column table to store this information and make that column the ...
14
votes
3answers
888 views
Is it OK to blindly add missing indexes?
I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster ...
2
votes
1answer
202 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
4answers
76 views
MySql Order by isnull() Performance Problem
my sql below is use for listing stock added from 10 days ago.
Order by isnull(Price) is use so that stock without any price yet will still being listed.
AddDate and Price has an index.
SELECT Id, ...
0
votes
1answer
54 views
Is index on foreign key used? (No difference in EXPLAIN.)
Essentially, I am joining two MyISAM tables and have found that the EXPLAIN output for the query does not change regardless of whether I have an index on the foreign key in the parent table. I'm not ...
5
votes
1answer
353 views
How to set SQL Server index pages per fragment?
I have SQL Server 2008, and a number of databases. I have discovered that one of my table's indexes is extremely fragmented (How I know: http://msdn.microsoft.com/en-us/library/ms189858.aspx)
The ...
0
votes
2answers
52 views
Run query faster, what index to use
I have a table with 85M rows :
CREATE TABLE emissions_totales (
"schema_source" varchar(150),
"snap" varchar(20),
"gid" varchar(255),
"polluant" varchar(50),
"emi" float8,
"numreg" varchar(3),
...
0
votes
1answer
26 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
0answers
16 views
How implement MySQL unique index with two foreign key that one can be null?
I would know if can be declared in MySQl to create an index constraints in a table with two foreign key that one can be null.
For example cannot be repeated a null foreign key with the other foreign ...
1
vote
0answers
23 views
Oracle - Mysterious indexes
I have a table in Oracle 10.2g that I'm trying to import roughly 11 million rows into. The table contains an SDO_GEOMETRY column. It previously had NOT NULL constraints on all 3 columns, a primary key ...
0
votes
3answers
22 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
1answer
286 views
How can I set the index fill factor back to server default?
I have some indexes that I was playing with on my dev server. I set the fill factor of these indexes using the following command:
alter index all on dbo.Table
rebuild with (fillfactor=80)
That ...
2
votes
2answers
117 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
78 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 ...
7
votes
2answers
233 views
Must an index cover all selected columns for it to be used for ORDER BY?
Over at SO, someone recently asked Why isn't ORDER BY using the index?
The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
6
votes
1answer
79 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 ...
0
votes
1answer
37 views
Will Partitions and Indexes on the same table help in performace of Inserts and Selects?
I have a table containing the list of visitors and this table has the following information.
Visitor Browser Information
Visitor Location Information
Visitor Time Information
No of Visits
I have a ...
4
votes
2answers
49 views
How to design indexes for columns with NULL values in MySQL?
I have a database with 40 million entries and want to run queries with the following WHERE clause
...
WHERE
`POP1` IS NOT NULL
&& `VT`='ABC'
&& (`SOURCE`='HOME')
&& ...
1
vote
2answers
264 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
34 views
Longest prefix search in Oracle
I have a list of phone number prefixes defined for large number of zones (in query defined by gvcode and cgi).
I need to efficiently find a longest prefix that matches given number PHONE_NR.
I use ...
7
votes
4answers
620 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 ...
1
vote
2answers
102 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 ...
0
votes
2answers
53 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 ...
3
votes
0answers
82 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 ...
2
votes
1answer
145 views
Will a primary key be added as a clustered index?
I've inherited a database where no primary keys were defined on the tables. There are also no clustered indexes assigned to the tables.
If I perform an alter table to assign a primary key will this ...
6
votes
2answers
132 views
Does an index with multiple columns make a similar index redundant?
Let's say I have a table that looks like this:
CREATE TABLE Activity (
ActivityID int primary key identity(1,1) ,
ActivityName nvarchar(10),
InactiveFlag bit
)
with an index that looks ...
6
votes
1answer
220 views
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 ...
-2
votes
0answers
48 views
Unexpected index scan against partitioned table [closed]
I have a set of partitioned tables with CIX on (ID, DATE) and partitioned on DATE.
There is another identical set of non-partitioned tables with CIX on ID.
Case A: T1 and T2 are non-partitioned ...
6
votes
1answer
86 views
Dropping Hypothetical Indexes
In the past I thought I'd deleted hypothetical indexes using either a DROP INDEX statement for clustered indexes and DROP STATISTICS statement for non-clustered indexes.
I have a database that is ...
3
votes
0answers
31 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 ...
3
votes
1answer
198 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 ...
