A few days ago, I ran into an unexpected performance problem with a pretty standard Django setup. For an upcoming feature, we have to regenerate a table hourly, containing about 100k rows of data, 9M on the disk, 10M indexes according to pgAdmin. The problem is that inserting them by whatever method literally takes ages, up to 3 minutes...
I have the following two models: School and User, and a HABTM relationship between them, with a join table. In this join table, the foreign key refering to the User table is not called user_id but student_id. class School < ActiveRecord::Base has_and_belongs_to_many :students, :class_name => "User", :join_table => "schools_students",...
I have three tables as follows (please forgive the loose syntax), with NO cascading relationships (I do not want this, as the database is primarily managed by NHibernate). Invoice ( entity_id int not null, ... ) Ticket ( entity_id int not null, ... ) InvoiceTicket ( InvoiceId --> Not-null foreign key to Invoice.ent...
If I have three tables: music_genres ----------- music_type_id genres [other unique fields] minerals -------- mineral_id mineral [other unique fields] verbs ----- verb_id verbs [other unique fields] and these are populated with: rock jazz funk punk rock boulder stone shale rock wobble shake vibrate Now let's say I was displayin...
Hi All, I use "ON DELETE CASCADE" regular but never use "ON UPDATE CASCADE" as I am not so sure what situation it will be useful. For the sake of discussion let see some code. CREATE TABLE parent ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ); CREATE TABLE child ( id INT NOT NULL AUTO_INCREMENT, parent_id INT, I...
Hi guys, I have defined a many-to-many relationship between my two entity classes User and Permission. User has a primary key composite of username and countyId, and my Permission table has a regular integer Id. The table UserPermission has the three foreign keys as its primary key: username, countyId and permissionId. Since this is a ...
I have this schema: CREATE TABLE `lotto`.`combinaciones` ( `indice` mediumint(8) unsigned NOT NULL, `binario` int(10) unsigned NOT NULL, PRIMARY KEY USING BTREE (`indice`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `lotto`.`sorteo` ( `numeroSorteo` int(11) NOT NULL, `fechaSorteo` date NOT NULL, `precioCarton` doub...
Hi, I'm experiencing some difficulties with foreign key constraints. I have three tables: Features ID PK AUTO_INC Title VARCHAR Subscriptions ID PK AUTO_INC Title VARCHAR Subscriptionfeatures ID PK AUTO_INC feature_id INT (index) subscription_id INT (index) When I have the following records Features 1...
I have a table containing objects, something like this: PK ObjectId FK ObjectTypeId Description etc The objects need to be grouped. I have been given a number of suggestions, all of which 'work' and some I like more than others. None of them are perfect so I'm struggling to settle on any particular model. 1/ Add a self-referential ...
I have two tables. Linked with a foreign key. Now I fetch a row from the field table: IField Field = from f in DataContext.fields where f.mapId == mapId && f.x1 == x && f.y1 == y select f; Working with this row I need some data from the linked table fieldViews. So I just do somthing like this: [..] Fi...
I have a simple where clause on a keyed field, a join between two tables on a different field, and then a sort on another field of the second table. I have keys on everything, but it seems that the describe is giving me a filesort and a 30 second query execution : mysql> describe SELECT * FROM `alias` LEFT OUTER JOIN `aliaspoint` ON (`...
As you can see, create() works, but get_or_create() doesn't. Am I missing something obvious here? In [7]: f = FeedItem.objects.create(source=u, dest=q, type="greata") In [8]: f, created = FeedItem.objects.get_or_create(source=u, dest=q, type="greata") --------------------------------------------------------------------------- FieldErro...
When do you use each MySQL index type? PRIMARY - Primary key columns? UNIQUE - Foreign keys? INDEX - ?? For really large tables, do indexed columns improve performance? ...
How can I display objects that link to an object via a ForeignKey in Django (specifically in the admin interface). For example, if I click on an object, I'll not only see the object but also any other object that link to it. e.g. I have a model for "Manufacturer" and another one for "Model"...the "Model" model links to "Manufacturer" v...
I'm trying to create a table in Navicat and immediately add a foreign key relation after that. The syntax however seems to be incorrect... Is this even possible? CREATE TABLE `Bld` ( `id` int(10) NOT NULL PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `Bld` ( CONSTRAINT `fk_Bld_Bld...
For instance, suppose I have table A. Then I have tables B-Z that have a foreign key to table A's primary key. Then perhaps there are also some tables that have a foreign key constraint to a table in B-Z's primary key constraint. Is there any easy way to clear out table A and all of the tables that refer to A (or that refer to a table...
I want to have a database table that keeps data with revision history (like pages on Wikipedia). I thought that a good idea would be to have two columns that identify the row: (name, version). So a sample table would look like this: TABLE PERSONS: id: int, name: varchar(30), version: int, ... // some data assigne...
Hi There I am building a very simple asp.net MVC file upload form. Currently I have a problem creating the new database object that stores info on said file. The code for the action method looks as follows: [Authorize(Roles = "Admin")] public ActionResult AddFile(Guid? id) { var org = organisationRepository.GetOrganisa...
I'm trying to introspect a postgres 8.3 database to retrieve details of its foreign keys. Imagine I have the following schema: CREATE TABLE "a" ( "id" SERIAL PRIMARY KEY ); CREATE TABLE "b" ( "one" integer, "two" integer, "a_id" integer REFERENCES "a", PRIMARY KEY ("one", "two") ); CREATE TABLE "c" ( "id" SERIAL PRIMARY KEY, "...
Hi All, I am looking for "best-practices" to display the value of foreign-key in a BindingSource. Sample data: PetID---PetName---PetTypeID 1---Tom---1 2---Jerry---2 PetTypeID---PetType 1---Cat 2---Mouse I have a Pet class and Pet form. The following code is on the Pet form to return data from the database as a Pet collection and ...