Search Results

Search found 52589 results on 2104 pages for 'read table'.

Page 257/2104 | < Previous Page | 253 254 255 256 257 258 259 260 261 262 263 264  | Next Page >

  • What would be the best way to dynamically add a set of rows to a table using JQuery?

    - by user312157
    I have a following table using MVC that shows number of items the user has. <table border = "1" id="tblItems"> <% var itemnum = 1; foreach (var item in Model.Items) {%> <tr> <td colspan="2" bgcolor="Silver"><strong>Item#<%=itemnum%></strong></td> <td><%=Html.ActionLink("Delete", "DeleteItem", "Item", new { id = item.ID })%></td> </tr> <tr> <td><strong>Name:</strong></td> <td><%=Html.TextBox("ItemName_" + itemnum, item.Name)%></td> </tr> <tr> <td><strong>Description:</strong></td> <td><%=Html.TextBox("ItemDescription_" + itemnum, item.Description)%></td> </tr> <%itemnum++; } %> </table> I will have a button that will Add New Item that will dynamically add identical structure. Any ideas on what is the best way to do this using JQuery? Thanks for all the suggestions!

    Read the article

  • How can I determine if a file I want to read from actually exists?

    - by Imray
    I'm learning Java, and I'm trying to write a program that can read from a .ser file, which I've already created with a writeTo method. I want to know a given file exists in the system before I tell the program to read from it. My code looks like this: public boolean readFromSerializedFile(String fileName){ FileInputStream fileInStream = null; ObjectInputStream objectInStream = null; try{ fileInStream = new FileInputStream(fileName); objectInStream = new ObjectInputStream(fileInStream); Is there a simple way I can determine if the file with the name of the parameter exists in the root directory (or wherever else specified)?

    Read the article

  • .NET and XML: How can I read nested namespaces (<abc:xyz:name attr="value"/>)?

    - by Entrase
    Suppose there is an element in XML data: <abc:xyz:name attr="value"/> I'm trying to read it with XmlReader. The problem is that I get XmlException that says The ‘:’ character, hexadecimal value 0x3A, cannot be included in a name I have already declared "abc" namespace. I have also tried adding "abc:xyz" and "xyz" namespaces. But this doesn't help at all. I could replace some text before parsing but there may be some more elegant solution. So what should I do? Here is my code: XmlReaderSettings settings = new XmlReaderSettings() NameTable nt = new NameTable(); XmlNamespaceManager nsmgr = new XmlNamespaceManager(nt); nsmgr.AddNamespace("abc", ""); nsmgr.AddNamespace("xyz", ""); XmlParserContext context = new XmlParserContext(null, nsmgr, null, XmlSpace.None); // So this reader can't read <abc:xyz:name attr="value"/> XmlReader reader = XmlReader.Create(path, settings, context);“

    Read the article

  • Adding defaults and indexes to a script/generate command in a Rails Template?

    - by charliepark
    I'm trying to set up a Rails Template that would allow for comprehensive set-up of a specific Rails app. Using Pratik Naik's overview (http://m.onkey.org/2008/12/4/rails-templates), I was able to set up a couple of scaffolds and models, with a line that looks something like this ... generate("scaffold", "post", "title:string", "body:string") I'm now trying to add in Delayed Jobs, which normally has a migration file that looks like this: create_table :delayed_jobs, :force => true do |table| table.integer :priority, :default => 0 # Allows some jobs to jump to the front of the queue table.integer :attempts, :default => 0 # Provides for retries, but still fail eventually. table.text :handler # YAML-encoded string of the object that will do work table.text :last_error # reason for last failure (See Note below) table.datetime :run_at # When to run. Could be Time.now for immediately, or sometime in the future. table.datetime :locked_at # Set when a client is working on this object table.datetime :failed_at # Set when all retries have failed (actually, by default, the record is deleted instead) table.string :locked_by # Who is working on this object (if locked) table.timestamps end So, what I'm trying to do with the Rails template, is to add in that :default = 0 into the master template file. I know that the rest of the template's command should look like this: generate("migration", "createDelayedJobs", "priority:integer", "attempts:integer", "handler:text", "last_error:text", "run_at:datetime", "locked_at:datetime", "failed_at:datetime", "locked_by:string") Where would I put (or, rather, what is the syntax to add) the :default values in that? And if I wanted to add an index, what's the best way to do that?

    Read the article

  • How to read comma separated values from text file in JAVA?

    - by user1425223
    I have got this text file with latitude and longitude values of different points on a map. I want to store these coordinates into a mySQL database using hibernate. I want to know how can I split my string into latitudes and longitudes? What is the general way to do these type of things that is with other delimiters like space, tab etc.? File: 28.515046280572285,77.38258838653564 28.51430151808072,77.38336086273193 28.513566177802456,77.38413333892822 28.512830832397192,77.38490581512451 28.51208605426073,77.3856782913208 28.511341270865113,77.38645076751709 28.510530488025346,77.38720178604126 28.509615992924807,77.38790988922119 28.50875805732363,77.38862872123718 28.507994394490268,77.38943338394165 28.50728729434496,77.39038825035095 28.506674470385246,77.39145040512085 28.506174780521828,77.39260911941528 28.505665660113582,77.39376783370972 28.505156537248446,77.39492654800415 28.50466626846366,77.39608526229858 28.504175997400655,77.39724397659302 28.503685724059455,77.39840269088745 28.503195448440064,77.39956140518188 28.50276174118543,77.4007523059845 28.502309175192945,77.40194320678711 28.50185660725938,77.40313410758972 28.50140403738471,77.40432500839233 28.500951465568985,77.40551590919495 28.500498891812207,77.40670680999756 28.5000463161144,77.40789771080017 28.49959373847559,77.40908861160278 Code I am using to read from file: try { BufferedReader in = new BufferedReader(new FileReader("G:\\RoutePPAdvant2.txt")); String str; str = in.readLine(); while ((str = in.readLine()) != null) { System.out.println(str); } in.close(); } catch (IOException e) { System.out.println("File Read Error"); }

    Read the article

  • one 16K random read I/O issues 2 scsi I/O (16K and 4K) requests in linux

    - by hiroyuki
    I noticed weird issue when benchmarking random read I/O for files in linux (2.6.18). The Benchmarking program is my own program and it simply keeps reading 16KB of a file from a random offset. I traced I/O behavior at system call level and scsi level by systemtap and I noticed that one 16KB sysread issues 2 scsi I/Os as following. SYSPREAD random(8472) 3, 0x16fc5200, 16384, 128137183232 SCSI random(8472) 0 1 0 0 start-sector: 226321183 size: 4096 bufflen 4096 FROM_DEVICE 1354354008068009 SCSI random(8472) 0 1 0 0 start-sector: 226323431 size: 16384 bufflen 16384 FROM_DEVICE 1354354008075927 SYSPREAD random(8472) 3, 0x16fc5200, 16384, 21807710208 SCSI random(8472) 0 1 0 0 start-sector: 1889888935 size: 4096 bufflen 4096 FROM_DEVICE 1354354008085128 SCSI random(8472) 0 1 0 0 start-sector: 1889891823 size: 16384 bufflen 16384 FROM_DEVICE 1354354008097161 SYSPREAD random(8472) 3, 0x16fc5200, 16384, 139365318656 SCSI random(8472) 0 1 0 0 start-sector: 254092663 size: 4096 bufflen 4096 FROM_DEVICE 1354354008100633 SCSI random(8472) 0 1 0 0 start-sector: 254094879 size: 16384 bufflen 16384 FROM_DEVICE 1354354008111723 SYSPREAD random(8472) 3, 0x16fc5200, 16384, 60304424960 SCSI random(8472) 0 1 0 0 start-sector: 58119807 size: 4096 bufflen 4096 FROM_DEVICE 1354354008120469 SCSI random(8472) 0 1 0 0 start-sector: 58125415 size: 16384 bufflen 16384 FROM_DEVICE 1354354008126343 As shown above, one 16KB pread issues 2 scsi I/Os. (I traced scsi io dispatching with probe scsi.iodispatching. Please ignore values except for start-sector and size.) One scsi I/O is 16KB I/O as requested from the application and it's OK. The thing is the other 4KB I/O which I don't know why linux issues that I/O. of course, I/O performance is degraded by the weired 4KB I/O and I am having trouble. I also use fio (famous I/O benchmark tool) and noticed the same issue, so it's not from the application. Does anybody know what is going on ? Any comments or advices are appreciated. Thanks

    Read the article

  • How to automatically generate html table from image in Linux?

    - by alfish
    In Photoshop, you can easily devide the image into zones using point and click and it automatically generates the corresponding html with image slices addressed in tables. Gimp also has a Slice (Filter Web Slice) but it is so rudimentary and, as far as I can see, does not allow point and click selection of slices. I am wondering if the functionality can be added into Gimp, or there are other Linux software to do this. I hate to return to Windows jut to do this simple task which I happen to use frequently. Thanks in advance for your suggestions.

    Read the article

  • Database localization

    - by Don
    Hi, I have a number of database tables that contain name and description columns which need to be localized. My initial attempt at designing a DB schema that would support this was something like: product ------- id name description local_product ------- id product_id local_name local_description locale_id locale ------ id locale However, this solution requires a new local_ table for every table that contains name and description columns that require localization. In an attempt to avoid this overhead I redesigned the schema so that only a single localization table is needed product ------- id localization_id localization ------- id local_name local_description locale_id locale ------ id locale Here's an example of the data which would be stored in this schema when there are 2 tables (product and country) requiring localization: country id, localization_id ----------------------- 1, 5 product id, localization_id ----------------------- 1, 2 localization id, local_name, local_description, locale_id ------------------------------------------------------ 2, apple, a delicious fruit, 2 2, pomme, un fruit délicieux, 3 2, apfel, ein köstliches Obst, 4 5, ireland, a small country, 2 5, irlande, un petite pay, 3 locale id, locale -------------- 2, en 3, fr 4, de Notice that the compound primary key of the localization table is (id, locale_id), but the foreign key in the product table only refers to the first element of this compound PK. This seems like 'a bad thing' from the POV of normalization. Is there any way I can fix this problem, or alternatively, is there a completely different schema that supports localization without creating a separate table for each localizable table? Update: A number of respondents have proposed a solution that requires creating a separate table for each localizable table. However, this is precisely what I'm trying to avoid. The schema I've proposed above almost solves the problem to my satisfaction, but I'm unhappy about the fact that the localization_id foreign keys only refer to part of the corresponding primary key in the localization table. Thanks, Don

    Read the article

  • Association Mapping Details confusion?

    - by AaronLS
    I have never understood why the associations in EntityFramework look the way they do in the Mapping Details window. When I select the line between 2 tables for an association, for example FK_ApplicationSectionsNodes_FormItems, it shows this: Association Maps to ApplicationSectionNodes FormItems (key symbol) FormItemId:Int32 <--> FormItemId:int ApplicationSectionNodes (key symbol) NodeId:Int32 <--> (key symbol) NodeId : int Fortunately this one was create automatically for me based on the foreign key constraints in my database, but whenever no constraints exist, I have a hard to creating associations manually(when the database doesn't have a diagram setup) because I don't understand the mapping details for associations. FormItems table has a primary key identity column FormItemId, and ApplicationSectionNodes contains a FormItemId column that is the foreign key and has NodeId as a primary key identity column. What really makes no sense to me is why the association has anything listed about the NodeId, when NodeId doesn't have anything to do with the foreign key relationship? (It's even more confusing with self referencing relationships, but maybe if I could understand the above case I'd have a better handle). CREATE TABLE [dbo].[ApplicationSectionNodes]( [NodeID] [int] IDENTITY(1,1) NOT NULL, [OutlineText] [varchar](5000) NULL, [ParentNodeID] [int] NULL, [FormItemId] [int] NULL, CONSTRAINT [PK_ApplicationSectionNodes] PRIMARY KEY CLUSTERED ( [NodeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [UQ_ApplicationSectionNodesFormItemId] UNIQUE NONCLUSTERED ( [FormItemId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[ApplicationSectionNodes] WITH NOCHECK ADD CONSTRAINT [FK_ApplicationSectionNodes_ApplicationSectionNodes] FOREIGN KEY([ParentNodeID]) REFERENCES [dbo].[ApplicationSectionNodes] ([NodeID]) GO ALTER TABLE [dbo].[ApplicationSectionNodes] NOCHECK CONSTRAINT [FK_ApplicationSectionNodes_ApplicationSectionNodes] GO ALTER TABLE [dbo].[ApplicationSectionNodes] WITH NOCHECK ADD CONSTRAINT [FK_ApplicationSectionNodes_FormItems] FOREIGN KEY([FormItemId]) REFERENCES [dbo].[FormItems] ([FormItemId]) GO ALTER TABLE [dbo].[ApplicationSectionNodes] NOCHECK CONSTRAINT [FK_ApplicationSectionNodes_FormItems] GO FormItems Table: CREATE TABLE [dbo].[FormItems]( [FormItemId] [int] IDENTITY(1,1) NOT NULL, [FormItemType] [int] NULL, CONSTRAINT [PK_FormItems] PRIMARY KEY CLUSTERED ( [FormItemId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[FormItems] WITH NOCHECK ADD CONSTRAINT [FK_FormItems_FormItemTypes] FOREIGN KEY([FormItemType]) REFERENCES [dbo].[FormItemTypes] ([FormItemTypeId]) GO ALTER TABLE [dbo].[FormItems] NOCHECK CONSTRAINT [FK_FormItems_FormItemTypes] GO

    Read the article

  • How do I understand the partition table? (I want to start over.)

    - by Sammy Black
    I have Ubuntu 10.04 Lucid installed through wubi on my laptop (it came with Windows 7 preinstalled). This was my first foray into Linux, and I'm here to stay. I have no use for Windows, and yet I must manually choose not to boot into it! Should I shrink the Windows partition to something negligible and grow the Linux one using something like gparted or fdisk, and just be content that everything runs? In that case, I need to understand the filesystems. Which is which? Here's the output of $ df -h: Filesystem Size Used Avail Use% Mounted on /dev/loop0 17G 11G 4.5G 71% / none 1.8G 300K 1.8G 1% /dev none 1.8G 376K 1.8G 1% /dev/shm none 1.8G 316K 1.8G 1% /var/run none 1.8G 0 1.8G 0% /var/lock none 1.8G 0 1.8G 0% /lib/init/rw /dev/sda3 290G 50G 240G 18% /host I would prefer to start over with a clean install of 10.10 Maverick, but I fear what I may lose. Certainly, I will backup my home directory tree (gzip?), but what about various pieces of software that I've acquired from the repositories? Can I keep a record of them? By the way, I asked a similar question over on Ubuntu forums.

    Read the article

  • How can I read PDFs with e-reader on a Lenovo tablet?

    - by Jack M
    This is my first time using a tablet and I'm totally lost - this must be what computers look like to old people. This is a Lenovo Thinkpad tablet. I don't see a specific model number on it (it was passed down to me second hand). What I'd like to do is read a PDF. I'm fairly certain I successfully transferred the PDF onto the tablet via an SD card, and I noticed this thing has an app called "eReader" on it, so I started that up. All I see are empty bookshelves, and no obvious buttons or settings anywhere. I saw in multiple places that you have to, as one site puts it: ...copy and paste the relevant files into the eBooks folder and they will appear on the eReader shelf for your consumption... But I have no idea how to: Access the file system. Locate the eBooks folder. Copy and paste files. Will I need a file explorer app? If so, how can I install that given that my only method of getting data onto this tablet is an SD card (no wireless internet).

    Read the article

  • alternate way to read excel files without providing "network service" security.

    - by Jebli
    Hi, I am using a web application to read the excel file and uploading it in the database. I have deployed the application in the server.I am using windows service 2003 as the server.I am able to read the excel file only if i set the set the security settings to "network service" for the microsoft excel application in the control panel -- administrative tools -- componetn service -- computer -- My computer -- DCOM config -- microsoft excel application . Why is it so . There are also other applications running in the server. 1) Doing like this will create any issue ? 2) Why do we need to do it ? 3) Is there any alternative way to do it ? 4) What are we doing actually by providing network service access ? Please help

    Read the article

  • Is it Possible to Use Constraints on Hierarchical Data in a Self-Referential Table?

    - by pbarney
    Suppose you have the following table, intended to represent hierarchical data: +--------+-------------+ | Field | Type | +--------+-------------+ | id | int(10) | | parent | int(10) | | name | varchar(45) | +--------+-------------+ The table is self-referential in that the parent_id refers to id. So you might have the following data: +----+--------+---------------+ | id | parent | name | +----+--------+---------------+ | 1 | 0 | fruit | | 2 | 0 | vegetable | | 3 | 1 | apple | | 4 | 1 | orange | | 5 | 3 | red delicious | | 6 | 3 | granny smith | | 7 | 3 | gala | +----+--------+---------------+ Using MySQL, I am trying to impose a (self-referential) foreign key constraint upon the data to cascade on update and prevent deletion of a record if it has any "children." So I used the following: CREATE TABLE `test`.`fruit` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `parent` INT(10) UNSIGNED, `name` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `fk_parent` FOREIGN KEY (`parent`) REFERENCES `fruit` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE = InnoDB; From what I understand, this should fit my requirements. (And parent must default to null to allow insertions, correct?) The problem is, if I change the id of a record, it will not cascade: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`fruit`, CONSTRAINT `fk_parent` FOREIGN KEY (`parent`) REFERENCES `fruit` (`id`) ON UPDATE CASCADE) What am I missing? Feel free to correct me if my terminology is screwed up... I'm new to constraints.

    Read the article

  • SQL DB design to support user feeds (in application like facebook)

    - by Yoav
    I have a social network server with a MySql DB. I want to show the users feeds like done in Facebook. Example - UserX now Friend with userY, userX did like on postX etc. Currently I have table: C1 : UserId C2 : LogType (now friend, did like etc) C3 : ObjectId (Can be userId or postId) - set depending on the LogType. Currently to get all related logs to show to the user I do the following queries: 1. Get All user Friends userIds 2. Query all rows which C1 is in userIds (I query completed) 3. Scan the DB and see - if LogType equals DidLike, check if post's OwnerId is the userId - if yes add it to logs. And so on. Obvious this is not efficient at all. I am looking for a better way. I thought I had in mind: Create a new table (in addition to the Log table) C1 : UserId C2 : LogId (from Log table) C3 : UserID of the one who did the action When querying logs - look in the table and get related Logs (by LogId) from LogTable. Updating the table: Whenever user doing action that should be in the log: 1. Add the Log entry to LogTable. 2. Scan the DB and see which users are interested with the Log (Who my friends are, Who is the owner of the post) and add related entries to the new table. (must be done in BG). 3. If user UNFRIEND another user - then look in the logs for all rows where C3 == UNFRIENDED user id and delete them. Any opinions? Other suggestions?

    Read the article

  • Alternative to NOT EXISTS

    - by Dave Colwell
    Hi all, I have two tables linked by an ID column, lets call them Table A and table B. My goal is to find all the records in table A that have no record in table B. For instance: Table A: ID----Value 1-----value1 2-----value2 3-----value3 4-----value4 Table B ID----Value 1-----x 2-----y 4-----z 4-----l As you can see, record with ID = 3 does not exist in table B, so i want a query that will give me record 3 from table A. the way i am currently doing this is by saying AND NOT EXISTS (SELECT ID FROM TableB) but since the tables are huge, the performance on this is terrible. Also, when i tried using a Left Join where TableB.ID is null, it didnt work. Can anyone suggest an alternative?

    Read the article

  • C++ Beginner - Best way to read 3 consecutive values from the command line?

    - by Francisco P.
    Hello everyone, I am writing a text-based Scrabble implementation for a college project. The specification states that the user's position input must be read from single line, like this: Coordinates of the word's first letter and orientation (<A – P> <1 – 15> <H ou V>): G 5 H G 5 H is the user's input for that particular example. The order, as shown, must be char int char. What is the best way to read the user's input? cin >> row >> column >> orientation will cause crashes if the user screws up. A getline and a subsequent string parser are a valid solution, but represent a bit of work. Is there another, better, way to do this, that I am missing? Thanks for your time!

    Read the article

  • How to make django test framework read from live database?

    - by lfborjas
    I realize there's a similar question here, but this one has a different approach: I have a django app that does queries over data indexed with djapian ; I'd like to write unit tests for this app's search component, and, obviously, I'd need the django settings module and all connections with the database active, so the test runner that django provides seems ideal. however, the django testing framework creates a dummy database and I'd hate to dump all my data to a fixture and then index it (the tests would take forever!); My data isn't at risk because the tests would only read from the database, so, how could this be achieved? -I'm new at this whole unit testing thing, so the solution of writing a new test runner I read in that similar question doesn't enlighten me a bit, at least not without some details

    Read the article

  • How do you make Python wait so that you can read the output?

    - by anonnoir
    I've always been a heavy user of Notepad2, as it is fast, feature-rich, and supports syntax highlighting. Recently I've been using it for Python. My problem: when I finish editing a certain Python source code, and try to launch it, the screen disappears before I can see the output pop up. Is there any way for me to make the results wait so that I can read it, short of using an input() or time-delay function? Otherwise I'd have to use IDLE, because of the output that stops for you to read. (My apologies if this question is a silly one, but I'm very new at Python and programming in general.)

    Read the article

  • Entity Framework 5 Enum Naming

    - by Tyrel Van Niekerk
    I am using EF 5 with migrations and code first. It all works rather nicely, but there are some issues/questions I would like to resolve. Let's start with a simple example. Lets say I have a User table and a user type table. The user type table is an enum/lookup table in my app. So the user table has a UserTypeId column and a foreign key ref etc to UserType. In my poco, I have a property called UserType which has the enum type. To add the initial values to the UserType table (or add/change values later) and to create the table in the initial migrator etc. I need a UserType table poco to represent the actual table in the database and to use in the map files. I mapped the UserType property in the User poco to UserTypeId in the UserType poco. So now I have a poco for code first/migrations/context mapping etc and I have an enum. Can't have the same name for both, so do I have a poco called UserType and something else for the enum or have the poco for UserType be UserTypeTable or something? More importantly however, am I missing some key element in how code first works? I tried the example above, ran Add-Migration and it does not add the lookup table for the enum.

    Read the article

  • Why is SQLite3 using covering indices instead of the indices I created?

    - by Geoff
    I have an extremely large database (contacts has ~3 billion entries, people has ~280 million entries, and the other tables have a negligible number of entries). Most other queries I've run are really fast. However, I've encountered a more complicated query that's really slow. I'm wondering if there's any way to speed this up. First of all, here is my schema: CREATE TABLE activities (id INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE contacts ( id INTEGER PRIMARY KEY, person1_id INTEGER NOT NULL, person2_id INTEGER NOT NULL, duration REAL NOT NULL, -- hours activity_id INTEGER NOT NULL -- FOREIGN_KEY(person1_id) REFERENCES people(id), -- FOREIGN_KEY(person2_id) REFERENCES people(id) ); CREATE TABLE people ( id INTEGER PRIMARY KEY, state_id INTEGER NOT NULL, county_id INTEGER NOT NULL, age INTEGER NOT NULL, gender TEXT NOT NULL, -- M or F income INTEGER NOT NULL -- FOREIGN_KEY(state_id) REFERENCES states(id) ); CREATE TABLE states ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, abbreviation TEXT NOT NULL ); CREATE INDEX activities_name_index on activities(name); CREATE INDEX contacts_activity_id_index on contacts(activity_id); CREATE INDEX contacts_duration_index on contacts(duration); CREATE INDEX contacts_person1_id_index on contacts(person1_id); CREATE INDEX contacts_person2_id_index on contacts(person2_id); CREATE INDEX people_age_index on people(age); CREATE INDEX people_county_id_index on people(county_id); CREATE INDEX people_gender_index on people(gender); CREATE INDEX people_income_index on people(income); CREATE INDEX people_state_id_index on people(state_id); CREATE INDEX states_abbreviation_index on states(abbreviation); CREATE INDEX states_name_index on states(name); Note that I've created an index on every column in the database. I don't care about the size of the database; speed is all I care about. Here's an example of a query that, as expected, runs almost instantly: SELECT count(*) FROM people, states WHERE people.state_id=states.id and states.abbreviation='IA'; Here's the troublesome query: SELECT * FROM contacts WHERE rowid IN (SELECT contacts.rowid FROM contacts, people, states WHERE contacts.person1_id=people.id AND people.state_id=states.id AND states.name='Kansas' INTERSECT SELECT contacts.rowid FROM contacts, people, states WHERE contacts.person2_id=people.id AND people.state_id=states.id AND states.name='Missouri'); Now, what I think would happen is that each subquery would use each relevant index I've created to speed this up. However, when I show the query plan, I see this: sqlite> EXPLAIN QUERY PLAN SELECT * FROM contacts WHERE rowid IN (SELECT contacts.rowid FROM contacts, people, states WHERE contacts.person1_id=people.id AND people.state_id=states.id AND states.name='Kansas' INTERSECT SELECT contacts.rowid FROM contacts, people, states WHERE contacts.person2_id=people.id AND people.state_id=states.id AND states.name='Missouri'); 0|0|0|SEARCH TABLE contacts USING INTEGER PRIMARY KEY (rowid=?) (~25 rows) 0|0|0|EXECUTE LIST SUBQUERY 1 2|0|2|SEARCH TABLE states USING COVERING INDEX states_name_index (name=?) (~1 rows) 2|1|1|SEARCH TABLE people USING COVERING INDEX people_state_id_index (state_id=?) (~5569556 rows) 2|2|0|SEARCH TABLE contacts USING COVERING INDEX contacts_person1_id_index (person1_id=?) (~12 rows) 3|0|2|SEARCH TABLE states USING COVERING INDEX states_name_index (name=?) (~1 rows) 3|1|1|SEARCH TABLE people USING COVERING INDEX people_state_id_index (state_id=?) (~5569556 rows) 3|2|0|SEARCH TABLE contacts USING COVERING INDEX contacts_person2_id_index (person2_id=?) (~12 rows) 1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT) In fact, if I show the query plan for the first query I posted, I get this: sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM people, states WHERE people.state_id=states.id and states.abbreviation='IA'; 0|0|1|SEARCH TABLE states USING COVERING INDEX states_abbreviation_index (abbreviation=?) (~1 rows) 0|1|0|SEARCH TABLE people USING COVERING INDEX people_state_id_index (state_id=?) (~5569556 rows) Why is SQLite using covering indices instead of the indices I created? Shouldn't the search in the people table be able to happen in log(n) time given state_id which in turn is found in log(n) time?

    Read the article

  • Using a join with three tables when a field might be null

    - by John
    Hello, The code below works great. It combines data from two MySQL tables. I would like to modify it by pulling in some data from a third MySQL table called "comment." In the HTML table below, "title" is a field in the MySQL table "submission." Every "title" has a corresponding "submissionid" field. The field "submissionid" is also found in the "comment" MySQL table. In the HTML table below, I would like "countComments" to equal the number of times a field called "commentid" appears in the MySQL table "comment" for any given "submissionid," where the "submissionid" is the same in both the "submission" and "comment" tables, and where the "submissionid" corresponds to the "title" being used. Here's the catch: if there is no row in the MySQL table "comment" that corresponds with the "submissionid" being used for "table", I would like "countComments" to equal to zero. How can I do this? Thanks in advance, John $sqlStr = "SELECT s.loginid, s.title, s.url, s.displayurl, l.username FROM submission AS s, login AS l WHERE s.loginid = l.loginid ORDER BY s.datesubmitted DESC LIMIT 10"; $result = mysql_query($sqlStr); $arr = array(); echo "<table class=\"samplesrec\">"; while ($row = mysql_fetch_array($result)) { echo '<tr>'; echo '<td class="sitename1"><a href="http://www.'.$row["url"].'">'.$row["title"].'</a></td>'; echo '</tr>'; echo '<tr>'; echo '<td class="sitename2"><a href="http://www...com/sandbox/members/index.php?profile='.$row["username"].'">'.$row["username"].'</a><a href="http://www...com/sandbox/comments/index.php?submission='.$row["title"].'">'.$row["countComments"].'</a></td>'; echo '</tr>'; } echo "</table>";

    Read the article

< Previous Page | 253 254 255 256 257 258 259 260 261 262 263 264  | Next Page >