I have situation, where running a query that filters by an indexed column in a partitioned table, performs a full table scan. Apparently , this is a known issue in postgresql, and it's explained in detail here. Is there a more elegant way around this other than performing a query on each partition, and then performing a UNION on all of...
Hello! I wonder, how are systems for fulltext search implemented, to be able to query millions of entries very fast? Please note: I'm not talking about systems which tokenize the content by separating it at whitespaces, but about system which are able to query even parts from the middle of tokens (which is a real challange). Background...
Hi, I have some SQL similar to the following, which joins four tables and then orders the results by the "status" column of the first: SELECT * FROM a, b, c, d WHERE b.aid=a.id AND c.id=a.cid AND a.did=d.id AND a.did='XXX' ORDER BY a.status It works. However, it's slow. I've worked out this is because of the ORDER BY clause and ...
I would like to index a list with another list like this L = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h'] Idx = [0, 3, 7] T = L[ Idx ] and T should end up being a list containing ['a', 'd', 'h']. Is there a better way than T = [] for i in Idx: T.append(L[i]) print T # Gives result ['a', 'd', 'h'] ...
I have a pretty large table: 20+ million rows and I need to update about 5% of that - or 1 million rows. Unfortunately, I am updating the (int) column that is being used as the clustered index. My question is: What is the fastest way to update these rows? I have tried updating the rows directly: update t1 set t1.groupId = t2.groupId...
I'm Working with a new Oracle DB, with one table having the following indexes: Index 1: ColA, ColB Index 2: ColA Is the second index redundant, and Will this have a negative impact on performance? ...
Hi, Every time I try to find out a variable or string or some text in my codes (in project folder), I am in trouble. It seems I don't know easy techniques to do that. I was wondering if there is any tool which indexes a specified folder (in my case project folder) and updates in real-time (with updating codes). Also any string can be se...
I implemented Hibernate search in my application i.e. based on Lucene. Whenever, i indexes the database, the size of the lucene indexes increase. But, the result of the query return same no of results every time. Why the size of lucene increases each time if i index the same data everytime? FullTextSession fullTextSession = Search.get...
Hi All, I want to know whether is it a good idea to catch exception based on unique index of sql in java. i want to catch an exception like 'duplicate entry for 1-0' if so then handle exception otherwise insert properly in database table? ...
Hello everyone, I'm busy with programming a class that creates an index out of a text-file ASCII/BINARY. My problem is that I don't really know how to start. I already had some tries but none really worked well for me. I do NOT need to find the address of the file via the MFT. Just loading the file and finding stuff much faster by searc...
Hypothetically, in a SQL Server database, if I have a table with two int fields (say a many-to-many relation) that participates in joins between two other tables, at what approximate size does the table become large enough where the performance benefit of indexes on the two int fields overcomes the overhead imposed by said indexes? Are ...
I have a stored procedure that is working with a large amount of data. I have that data being inserted in to a temp table. The overall flow of events is something like CREATE #TempTable ( Col1 NUMERIC(18,0) NOT NULL, --This will not be an identity column. ,Col2 INT NOT NULL, ,Col3 BIGINT, ,Col4 VARCHAR(25) NOT NULL, ...
How to speed up select count(*) with group by? It's too slow and is used very frequently. I have a big trouble using select count(*) and group by with a table having more than 3,000,000 rows. select object_title,count(*) as hot_num from relations where relation_title='XXXX' group by object_title relation_title, object_title i...
I need to index a large number of Java properties and manifest files. The data in the files is just key-value pairs. I am thinking to use Lucene for this. However, I do not need any real full-text search capabilities, as the data is quite structured. I only need to search for exact matches of property values, and the property key is a...
what is the best way to do it ? put it in iframe . set this section after load with javascript ? thanks ...
If I have a lookup table with very few records in it (say, less than ten), should I bother putting an index on the Foreign Key of another table to which it is attached? For that matter, does the lookup table even need an index on the Primary Key? Specifically, is there any performance benefit that outweighs the overhead of maintainin...
The consensus seems to be that all foreign keys need to have indexes. How much overhead am I going to incur on inserts if I follow the letter of the law? NOTES: Assume that the database is a good design, and that all of the joins are legitimate. All Primary and Foreign Keys are of type Int. Some tables are lookup tables, with fewer t...
I can't create index on varchar(500). Mysql:"Specified key was too long; max key length is 1000 bytes" ...
In my attempt to further future-proof a project I am trying to find the best way to retrieve the full path and filename of the index/default page in a web directory using C# and without knowing the web server's list of filename possibilities. 'Server.MapPath("/test/")' gives me 'C:\www\test\' ...so does: 'Server.MapPath(Page.ResolveUrl...
Given an ACL list with 10 billion IPv4 ranges in CIDR notiation or between two IPs: x.x.x.x/y x.x.x.x - y.y.y.y What is an effecient search/indexing algorithm for testing that a given IP address meets the critera of one or more ACL ranges? Lets assume most ACL range definitions span a great number of class C blocks. Indexing points ...