Hi there, I have two database with same structures, but one of them is missing INDEXES (i think i've missed out), i mean the table didn't have any INDEXES yet. I was plan to generate CREATE INDEX for the database which have indexes but can't found any method available in Management Studio, yes we can generate script for tables, view, e...
Keep in mind that I am a rookie in the world of sql/databases. I am inserting/updating thousands of objects every second. Those objects are actively being queried for at multiple second intervals. What are some basic things I should do to performance tune my (postgres) database? ...
I've been freelancing for 5 weeks at a Rails shop where I just learned today that the "Technical Lead" of a team of about a half dozen engineers, never applies indexes to the databases backing the websites being developed. I have a week left on my contract and have no idea if they intend on offering an extension. I feel like I owe it t...
I have an Sqlite3 database with a table and a primary key consisting of two integers, and I'm trying to insert lots of data into it (ie. around 1GB or so) The issue I'm having is that creating primary key also implicitly creates an index, which in my case bogs down inserts to a crawl after a few commits (and that would be because the da...
I've got an n-by-k sized matrix, containing k numbers per row. I want to use these k numbers as indexes into a k-dimensional matrix. Is there any compact way of doing so in MATLAB or must I use a for loop? This is what I want to do (in MATLAB pseudo code), but in a more MATLAB-ish way: for row=1:1:n finalTable(row) = kDimensionalMa...
I've inherited some code at work that has a really bad smell. I'm hoping to find the most painless solution possible. Is there a way to check if some arbitrary number is a valid element in an array? Example - I need to check if array[25] exists. Preferably I would prefer to do this without doing a foreach() through the array to found...
I've created composite indexes (indices for you mathematical folk) on tables before with an assumption of how they worked. I was just curious if my assumption is correct or not. I assume that when you list the order of columns for the index, you are also specifying how the indexes will be grouped. For instance, if you have columns a, b,...
Hello all, I am looking to implement a simple forward indexer in PHP. Yes I do understand that PHP is hardly the best tool for the task, but I want to do it anyway. The rationale behind it is simple: I want one, and in PHP. Let us make a few basic assumptions: The entire Interweb consists of about five thousand HTML and/or plain-text...
I've got (for example) an index: CREATE INDEX someIndex ON orders (customer, date); Does this index only accelerate queries where customer and date are used or does it accelerate queries for a single-column like this too? SELECT * FROM orders WHERE customer > 33; I'm using SQLite. If the answer is yes, why is it possible to crea...
As this article's figure 4 says, SQL Server 2005 + can return you a list of missing indexes. It stores 2 important info about missing indexes: [EqualityUsage],[InequalityUsage] If I have a missing index where: [EqualityUsage]='col1',[InequalityUsage]='col2' Should I create an index with Indexed Key Columns: 'col1,col2' or 'col2,co...
According to the sqlite3 documentation, The COLLATE clause following each column name defines a collating sequence used for text entries in that column. The default collating sequence is the collating sequence defined for that column in the CREATE TABLE statement. Or if no collating sequence is otherwise defined, the bu...
I am working in existing C code which has a couple of lines with statements similar to this one: struct collect_conn *tc = (struct collect_conn *) ((char *)c - offsetof(struct collect_conn, runicast_conn)); The struct collect_conn goes along the following lines: struct collect_conn { struct runicast_conn runicast_conn; stru...
Hello. I have what I'd thought would be a simple query, but it takes 'forever'. I'm not great with SQL optimizations, so I thought I could ask you guys. Here's the query, with EXPLAIN: EXPLAIN SELECT * FROM `firms_firmphonenumber` INNER JOIN `firms_location` ON ( `firms_firmphonenumber`.`location_id` = `firms_location`....
I have a number of indexes on some tables, they are all similar and I want to know if the Clustered Index is on the correct column. Here are the stats from the two most active indexes: Nonclustered I3_Identity (bigint) rows: 193,781 pages: 3821 MB: 29.85 user seeks: 463,355 user_scans: 784 user_lookups: 0 updates: 256,516 Clustered Pr...
I've accidentally added a new filter to my GAE application. The status of the index is 'serving' now - however I don't need that index at all and I'd like to remove. How can I do that? ...
I am developing a application that needs to store data with many writes and reads as requiring fast searching of data (the need for indexes of some sort), and also be able to serialize and save the data. Currently I am thinking about using SQLite, which gets the job done, but I am open for alternatives. The SQLite's syntax really doesn...
I'm in the process of trying to optimize a query that looks up historical data. I'm using the query analyzer to lookup the Execution Plan and have found that the majority of my query cost is on something called a "Bookmark Lookup". I've never seen this node in an execution plan before and don't know what it means. Is this a good thi...
I have read the various questions/answers here that basically indicate that having a clustered index on a uniqueidentifier column is a poor choice for performance reasons. Regardless, I need to use a uniqueidentifier as my primary key, and I do NOT want to use newsequentialid() because the generated values are too similar to one another ...
I am using SQL Server 2000 and I am using full text indexing on a table. Can I use full text indexing on a view of this table? If yes, then how can I use? If no, then what should I do? Actually, I am doing refinement of the results obtained from a table and for this refinement I am using a view of this table... ...
I have a table with sets of settings for users, it has the following columns: UserID INT Set VARCHAR(50) Key VARCHAR(50) Value NVARCHAR(MAX) TimeStamp DATETIME UserID together with Set and Key are unique. So a specific user cannot have two of the same keys in a particular set of settings. The settings are retrieved by set, so if a use...