In a legacy database (SQL Server 2000), we have a clustered index that looks like this: CREATE CLUSTERED INDEX [IX_usr] ON [dbo].[usr] ( [uid] ASC, [ssn] ASC, [lname] ASC ) The thing is, as far as I know none of these fields are used together in a WHERE clause. Nor is there really any reason to use any of them together. ...
Sorry for a pretty specific question. I have a table (see bottom), and when I try to delete a lot of records from it, my PostgreSQL 8.2.5 spends 98% of the time doing the parent-child constraint. I'm trying to figure out what index should I add to make it go fast. I have to say that all columns on this table have either 0 or null as the...
I have been poking around for a recipe / example to index a list of tuples without taking a modification of the decorate, sort, undecorate approach. For example: l=[(a,b,c),(x,c,b),(z,c,b),(z,c,d),(a,d,d),(x,d,c) . . .] The approach I have been using is to build a dictionary using defaultdict of the second element from collections...
We have a table called table1 ... (c1 int indentity,c2 datetime not null,c3 varchar(50) not null, c4 varchar(50) not null,c5 int not null,c6 int ,c7 int) on column c1 is primary key(clusterd Index) on column c2 is index_2(Nonclusterd) on column c3 is index_2(Nonclusterd) on column c4 is index_2(Nonclusterd) on column c5 is index_2(...
For example, when I post to Stackoverflow, the post appears in the Google index a minute later. How is this accomplished? What do I have to do to my web-site to get the same frequency of indexing? ...
I have a table with two indexes, one of which is the faster covering index for a certain query. However, mySQL (5.1) is not choosing the correct index. I have looked at the explain for this query and done some speed tests and it makes a big difference if you force the key. Is there any way of examining how it picks the index and what cr...
I just start integrate Hibernate Search with my hibernate application. The data is indexed by using Hibernate Session everytime i start the server. FullTextSession fullTextSession = Search.getFullTextSession(session); Transaction tx = fullTextSession.beginTransaction(); List books = session.createQuery("from Book as book").list(); for...
I've been reading a lot lately about signature trees, or S-Trees. For example, this paper. The literature speaks very highly of them, and evidence is provided for considerable performance gains over, for example, inverted files or B-Trees, for some applications. Now, why is it that I don't see S-Trees used very much? Do you know of any ...
I've been using X1 and Microsoft Search to index my source code, but find that asp and other web files are indexed as HTML and searchable by the HTML content rather than the source code. Is there a desktop search that lets me index my code samples as TEXT and not apply the formatting and restrict my search? ...
I have read that one should not analyze a temp table, as it screws up the table statistics for others. What about an index? If I put an index on the table for the duration of my program, can other programs using the table be effected by that index? Does an index effect my process, and all other processes using the table? or Does it e...
Hey guys, i'm running into a strange problem in Microsoft SQL Server 2008. I have a large database (20 GB) with about 10 tables and i'm attempting to make a point regarding how to correctly create indexes. Here's my problem: on some nested queries i'm getting faster results without using indexes! It's close (one or two seconds), but i...
EXPLAIN SELECT * FROM content_link link STRAIGHT_JOIN content ON link.content_id = content.id WHERE link.content_id = 1 LIMIT 10; +----+-------------+---------+-------+---------------+------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+...
Python's list type has an index() method that takes one parameter and returns the index of the first item in the list matching the parameter. For instance: >>> some_list = ["apple", "pear", "banana", "grape"] >>> some_list.index("pear") 1 >>> some_list.index("grape") 3 Is there a graceful (idiomatic) way to extend this to lists of co...
How do I list all tables / columns in my database that have a full-text index? ...
mysql> desc users; +-------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | email | varc...
I know that SQLite does not enforce foreign keys natively, but that's not my primary concern. The question is: If I declare CREATE TABLE invoice ( invoiceID INTEGER PRIMARY KEY, clientID INTEGER REFERENCES client(clientID), ... ) will sqlite at least use the information that clientID is a foreign key to optimize queries and au...
I've got an odd thing going on with a query. I have a table with two bigints, StartNum and EndNum that defines number ranges. I have an index on these two columns. The query takes a given number and returns the range it falls into. The WHERE clause is where @Num >= StartNum and @Num <= EndNum. There's a big difference in the Profiler b...
Scenario: I am using a T-SQL stored proc (Sql Server Management Studio) to return search matches for text documents using the MS Indexing Service and this (simplified) query: SELECT * FROM openquery( filesystem2, 'SELECT Path, Rank, Filename FROM SCOPE('' "e:\test\documents" '') WHERE CONTAINS('' FORMSOF ...
I'm currently trying to implement the Index Fabric for a dna sequence data search system: Index fabric algorithm I could implement the normal patricia trie, but I still couldn't understand how to add layers. I also tried google but couldn't find enough information about adding layers to the patricia trie there either. In the paper me...
So I’m trying to create a FULLTEXT index on an attribute of a table. Mysql returns ERROR 1214: The used table type doesn’t support FULLTEXT indexes. Any idea what I’m doing wrong? ...