Good day, I have about 4GB of data, separated in about 10 different tables. Each table has a lot of columns, and each column can be a search criteria in a query. I'm not a DBA at all, and I don't know much about indexes, but I want to speed up the search as much as possible. The important point is, there won't be any update, insert or d...
So we've been told that one source of TM Enq contention can be unindexed FK's. My question is which one. I have an INSERT INTO Table_B that is recording TM Enq Wait. It contains a PK that is the parent to other tables and it has columns that are FK constrained to other PKs. So which FKs need indexed. That table's columns or its childr...
I have a table (SQL 2000) with over 10,000,000 records. Records get added at a rate of approximately 80,000-100,000 per week. Once a week a few reports get generated from the data. The reports are typically fairly slow to run because there are few indexes (presumably to speed up the INSERTs). One new report could really benefit from ...
I was wondering if InnoDB would be the best way to format the table? The table contains one field, primary key, and the table will get 816k rows a day (est.). This will get very large very quick! I'm working on a file storage way (would this be faster)? The table is going to store ID numbers of Twitter Ids that have already been processe...
Hi folks, I've got a column in a table (eg. UserName) which I want to make sure is unique. So I create a unique key for that column and call it IX_Users_UserName. Now, if I do lots of searching for users based on their username I want to make sure there is an index for that field. Do I need to create a separate index, or is the unique...
I have a table that has 8 million records, with many fields, including lat/long values, and it has an index over the Lat/Long fields. I'm making a query to find the records that fall within a square around a point (to later refine into a circle), which is kind of: SELECT Lat,Long FROM Data WHERE (Lat BETWEEN 1 AND 2) AND (Long BETWEEN ...
Requirements: Must be able to use C strings as well as C++ strings Fast No maps No templates No direct lookup, i.e. index might be out of bounds. Index is not consecutive Enums and strings contained in one header file Only instantiate what you use. This is what I have come up with so far: - test.hh - // Generic mapper // // The id...
I'm looking to build a query that will use the non-clustered indexing plan on a street address field that is built with a non-clustered index. The problem I'm having is that if I'm searching for a street address I will most likely be using the 'like' eval function. I'm thinking that using this function will cause a table scan instead o...
I'm new to database indexing, if I have 2 columns in a table that are good choices for indexing like for example, [Posts]( [PostID] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](64) NOT NULL, [ApplicationType] [smallint] NOT NULL, ... ) in this case PostID would be the PRIMARY KEY CLUSTERED index, then I want to ...
I've a table with a lot of registers (more than 2 million). It's a transaction table but I need a report with a lot of joins. Whats the best practice to index that table because it's consuming too much time. I'm paging the table using the storedprocedure paging method but I need an index because when I want to export the report I need t...
What is the difference between mysql unique and non-unique index in terms of performance? Let us say I want to make an index on a combo of 2 columns, and the combination is unique, but I create a non-unique index. Will that have any significant effect on the performance or the memory mysql uses? Same question, is there is difference be...
Is there a way to stop Google from indexing a site? ...
select * from myTable where myInt will not show any possible_keys when explaining the query even though there is an index on myInt field. Edit: The index in question is not unique. ...
Hi people, Does anyone know a situation where a PostgreSQL HASH should be used instead of a B-TREE for it seems to me that these things are a trap. They are take way more time to CREATE or maintain than a B-TREE (at least 10 times more), they also take more space (for one of my table.columns, a B-TREE takes up 240 MB, while a HASH would...
There have been several questions recently about database indexing and clustered indexing and it has been kind of new to me until the last couple weeks. I was wondering how important it is and what kind of performance gains can be expected from creating them. Edit: What is usually the best type of fields to look at when putting in a clu...
I am trying to understand the file format of a Visual FoxPro compact index (*.IDX). I am currently referring to Microsoft's documentation for guidance. The index is a B-tree of 512-byte nodes. Each leaf ("exterior") node contains multiple entries. Each entry consists of four pieces of data: Row number [FIXED LENGTH] Duplicate byte cou...
The MySQL documentation isn't very clear on this. I want to add an index to an existing table. The table is a user table with the login id and password and I want to create an index for this to optimize logging in. This is how I thought I would try it: mysql> ALTER TABLE `users` ADD INDEX(`name`,`password`); This created: mysql> sh...
Is it possible to enforce uniqueness across two tables in MySQL? I have two tables, both describing users. The users in these tables were for two different systems previously, however now we're merging our authentication systems and I need to make sure that there are unique usernames across these two tables. (it's too much work to put t...
Hi again, I currently working with a larger wikipedia-dump derived PostgreSQL database; it contains about 40 GB of data. The database is running on an HP Proliant ML370 G5 server with Suse Linux Enterprise Server 10; I am querying it from my laptop over a private network managed by a simple D-Link router. I assigned static DHCP (private...
I'm looking for a full text indexing package that is being maintained (i.e. not an end of life dead package) that can would ideally have support for: substring matches incremental updates line level results Also ideal would be support for boolean matches adjacency searches "stringX found near stringY" A little more detail about t...