index

What are the methods for identifying unnecessary columns within a covering index?

What methods are there for indentifying superfluous columns in covering indices: columns which are never searched against, and therefore may be extracted into Include's, or even removed completely without affecting the applicability of the index? ...

Difference between 2 indexes with columns defined in reverse order

Are there any differences between following two indexes? IDX_IndexTables_1 IDX_IndexTables_2 If there are any, what are the differences? create table IndexTables ( id int identity(1, 1) primary key, val1 nvarchar(100), val2 nvarchar(100), ) create index IDX_IndexTables_1 on IndexTables (val1, val2) GO create index IDX_IndexTab...

Index on UNION query?

i've got this union query: (SELECT INSTALLER, INSTALLTIME, RESULT, JOBNUMBER, HONAME, ADDRESS, CITY, STATE, ZIP, NOTES, SMNOTES, '' as priority, PAFS, upsell, TERM, MMRUPGRADE, WARRANTY, EFT FROM ACCOUNTS WHERE INSTALLDATE = '$date' && FUNDINGSTATUS !='DEAD') UNION (SELECT technician, servicetime, result, ID, Customername, address, c...

Configure Solr for SQL Server

I am using tomcat 6.0 and running Solr in it. Can some one help me how to configure solr to index SQL Server DB? ...

SQL Server 2005 Index Filter Feature

I was told that there is new a feature in SQL Server 2005 called index filters. What I want to do is add an Index to a column and have the index ignore null values. I can't find good information on this feature (maybe my source is wrong). Can anyone provide additional information on this feature? ...

Unneeded MySQL indices

We have one of our tables in our database that is starting to be pretty big : 10M rows 2.14G for data 3.55G for indices I was pretty surprised to see that the indices are almost twice as big as the data itself :/ So I showed the indices : show index from entries; +---------+------------+----------------------------------------+-------...

How to drop unique in MySQL?

Create Table: CREATE TABLE `fuinfo` ( `fid` int(10) unsigned NOT NULL, `name` varchar(40) NOT NULL, `email` varchar(128) NOT NULL, UNIQUE KEY `email` (`email`), UNIQUE KEY `fid` (`fid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 I want to drop the unique key on email,how? ...

mysql 7columns pk vs. 1 column md5 unique constraint

Hi, i have a very large table which is currently approx 70M rows and growing daily by the thousands , this schema is tipping over every day now so i'm moving to a partitioned table and redesigning the ddl . the table is basicly a collection of NOT NULL INTEGERS(some medium some INT some tiny) which need to have a unique constraint for a...

Oracle refuses to use index

I have a partitioned table like so: create table demo ( ID NUMBER(22) not null, TS TIMESTAMP not null, KEY VARCHAR2(5) not null, ...lots more columns... ) The partition is on the TS column (one partition per year). Since I search a lot via the timestamp, I created a combined index: create index demo.x1 on demo (ts, k...

mysql ( innodb )foreign key constraints problems

Hello I am running into a couple of issues while trying to generate foreign keys for my tables in MySql(Innodb). Can you please help me with them ? Referenced tables : *create table entity { PID INT(20) auto inc not null, ENTITYID INT(20) not null, details VARCHAR(100) not null, primary key(PID,ENTITYID) } create table user { ...

Django: create Index: non-unique, multiple column

Given the following model, I want to index the fields (sequence,stock) class QuoteModel(models.Model): quotedate = models.DateField() high = models.FloatField() #(9,2) DEFAULT NULL, low = models.FloatField() #(9,2) DEFAULT NULL, close = models.FloatField() #(9,2) DEFAULT NULL, closeadj = models.FloatFi...

Blackberry index search

hello, following is my code for application which takes data from user.displays it in a list. what i am trying to do is if user clicks delete button specified list entry should be deleted. but my index is not returning properly. please help me i am trying it for last two days. import net.rim.device.api.ui.; import net.rim.device.api.ui...

Create a mysql primary key without a clustered index?

I'm a SQL Server guy experimenting with MySQL for a large upcoming project (due to licensing) and I'm not finding much information in the way of creating a primary key without a clustered index. All the documentation I've read says on 5.1 says that a primary key is automatically given a clustered index. Since I'm using a binary(16) for t...

A covered index formed by a composite index or index with included columns

Are there any differences between these two covered indexes? A composite index with both FriendID and UserID A index with FriendID and UserID as an included column Separate MS SQL Server related point. If the answer to the above question is 'No difference', is it me or does the Database Engine Tuning Advisor (DTA) always go crazy on...

What's the easiest way to add an index on a live myISAM table?

I have a myISAM table running in production on mySQL, and by doing a few tests, we've found we can tremendously speed up a query by adding a certain compound index. So far so good. However, I am not really about the best way to add this index in a production environment without locking the table for a long time (it's got 27GBs of data, s...

Index, assignment and increment in one statement behaves differently in C++ and C#. Why?

Why is this example of code behaving differently in c++ and C#. [C++ Example] int arr[2]; int index = 0; arr[index] = ++index; The result of which will be arr[1] = 1; [C# Example] int[] arr = new int[2]; int index = 0; arr[index] = ++index; The result of which will be arr[0] = 1; I find this very strange. Surely there must be so...

MySQL Slow Query Analysis and Indexing

Recently, we've noticed a particular query popping up in our slow query logs taking quite some time. I've analyzed it to the best of my ability, but can't figure out for the life of me why it's taking so long, and why the indexes we've set up aren't being used. Here's a simplified (i.e., readable) version of the query for the purpose ...

Find Tabel Row Index using jQuery

Hello All, I'm an intermediate user in jQuery. I know to find the rowIndex of a table using jQUery, but my scenario is a different one. My table(GridView) consists of 20 columns and each column with different controls like textbox, dropdownlist, image, label. All are server side controls in each row. I bind the gridview with the records...

PHP Changing index of variable

Hello I have the variable $string[$k] = $function[$k] defined within a foreach loop with index $k. I want $string to be defined as $string[$k] = $function[$(k-5)] except that isn't correct. So for $k=8 I would have $string[8] = $function[3] How do I achieve this? Thank you ...

Keeping a file index in memory, compression?

I'm doing a file-search utility that indexes a drive and let you find a file based on different criteria. To make it as fast as possible I'd want to keep the index in memory (which also allows me to do some PLINQ). In order to keep the memory requirement down I'm thinking of putting all directory names into a lookup table (with a point...