Hi, I have an sqlite database used to store information about backup jobs. Each run, it increases approximately 25mb as a result of adding around 32,000 entries to a particular table. This table is a "map table" used to link certain info to records in another table... and it has a primary key (autoincrement int) that I don't use. sqli...
I've got these two queries: SELECT SELECT NamesRecord.NameID, NamesRecord.FulfillmentAddressID NameFulfillmentAddressID, ContractRecord.FulfillmentAddressID, ContractRecord.BillingAddressId FROM Magnet.dbo.ContractRecord ContractRecord INNER JOIN Magnet.dbo.NamesRecord NamesRecord ON NamesRecord.NameId = ContractRecord.DonorId WHERE ...
Hi all, I'm running the following query on a Macbook Pro 2.53ghz with 4GB of Ram: SELECT c.id AS id, c.name AS name, c.parent_id AS parent_id, s.domain AS domain_name, s.domain_id AS domain_id, NULL AS stats FROM stats s LEFT JOIN stats_id_category sic ON s.id = sic.stats_id LEFT JOIN cate...
I'm quite new to setting indexes myself. I'm currently just experimenting with it to discover how it works and in what cases a database will make use of the index. I've got a simple table with 3 columns; an id, a name and a status. I've set an index on the name which is a CHAR(30) column. Against my expectations, MySQL ignores this inde...
Can someone explain how to define multi column indexes in Grails? The documentation is at best sparse. This for example does not seem to work at all: http://grails.org/GORM+Index+definitions I've had some luck with this, but the results seems random at best. Definitions that works in one domain class does not when applied to another (w...
In the following example, MySQL fails to use to find a ref for the JOIN clause (or so it appears). Can anyone explain why? mysql> explain SELECT 1 FROM `businesses` INNER JOIN `categories` ON (`businesses`.`id` = `categories`.`business_id`) WHERE (`categories`.`category_id` IN (1321, 7304, 9189, 4736, 4737, 1322, 8554, 1323, 1324, 9...
hi What is the difference between primary, unique and foreign key constraints, and indexes? I work on Oracle 10g and Sql Server 2008 thanks in advance ...
I have a query that gets run often. its a dynmaic sql query because the sort by changes. SELECT userID, ROW_NUMBER(OVER created) as rownumber from users where divisionID = @divisionID and isenrolled=1 the OVER part of the query can be: userid created Should I create an index for: divisionID + isenrolled divisionID + isenrolle...
I have a table that stores "records" and already has primary key. Table A ======= id (INT) PK auto_increments project_id (INT) record_text (TEXT) This table stores "records" for all of my projects. It is desirable to have a secondary key that auto increments for the projects. Example: If project 1 had id's of (1,5,8,9). It would...
Our product takes tests of some 350 candidates at the same time. At the end of the test, results for each candidate are moved to a datawarehouse full of indexes on it. For each test there's some 400 records to be entered in datawarehouse. So 400 x 350 is a lot of records. If there are not much records in the datawarehouse, all goes well....
I don't get what is that? It's id which auto increase number? ...
I'm creating a MySQL table in Rails which has about 170 boolean fields, each of which needs to be searchable and hence indexed, but when I create the indexes I get the error message: To many keys specified; max 64 keys allowed Is this limit hard coded or is there a config switch I can flip which wold get around it? Or do I need to re...
Hi there, does it make sense to create indexes for a table called user_movies with the following columns: user_id movie_id There will be much more reading than inserting or updating on this table but I'm not sure what to do. Also: Is it adequate to omit a primary key in this situation? ...
Hi All, We've got a table we use as a queue. Entries are constantly being added, and constantly being updated, and then deleted. Though we might be adding 3 entries/sec the table never grows to be more than a few hundred rows. To get entries out of table we are doing a simple select. SELECT * FROM queue_table WHERE some_id = ? ...
I'm mainly an Actionscript developer and by no means an expert in SQL, but from time to time I have to develop simple server side stuff. So, I thought I'd ask more experienced people about the question in the title. My understanding is that you don't gain much by setting an index in a column that will hold few distinct values. I have a ...
I use a similar query like this select.....from.. with... (INDEX=IX_TABLE_1, INDEX=IX_TABLE_2)... I get the following error Only one list of index hints per table is allowed This seems to be working well with SQL Server 2005. Is it an issue with SQL server? ...
We have a series of tables that have grown organically to several million rows, in production doing an insert or update can take up to two seconds. However if I dump the table and recreate it from the dump queries are lightning fast. We have rebuilt one of the tables by creating a copy rebuilding the indexes and then doing a rename swit...
Hi, Im getting very confused with indexes in MySQL. I have two tables: TableA1 and TableA2. I created indexes on these for the joins between them and queries run really fast. I have another 2 tables with an identical setup e.g. TableB1 and TableB2. The only difference is that these tables have a few null values. For some reason the...
I have a table with several non PK-related indexes. Unfortunately there's some duplicity in that several indexes reference the same column with the same sort order. I typically create covering indexes which represent aggregations of non PK-related indexes for my tables because as long as the column is indexed it will use it if appropri...
Hi, I am having a problem with indexes building in my App Engine application. There are only about 200 entities in the indexes that are being built, and the process has now been running or over 24 hours. My application name is romanceapp. Is there any way that I can re-start or clear the indexes that are being built? Thank you and ki...