***I'm waiting for the repair man to show up to fix my waterheater... so... I'm bored. This is going to be to the point at the beginning, but get wordy and technical near the end. ;) Super over kill..... ahem****
To better answer your question "...is it better to...", it entirely depends on the size of your data and what you're doing with it. You will ALWAYS incur slower speeds when using foreign keys in either a join or "ON [DELETE/UPDATE]". Additional look ups have to happen, which means more time spent, which typically is the millisecond range per lookup. Now the AMOUNT of time it takes to DO those look ups DIRECTLY reflects on your indexes, and what approach the database virtual machine decides to take to get your data. With an index, you will ALWAYS incur slower speeds on an update or delete. However, the other side of the coin is that if you're doing more SELECTs than UPDATE/DELETEs, having an index is a serious benefit. However, if you're doing lots of UPDATES/DELETEs, outside of a transaction, indexes can hurt performance, and final commit could take a while, data size and contents depending. Foreign keys (FKs) and indexes are two very different things in database engines. You can legally have a FK with no indexes created between the pair of fields. Honestly, I don't know if SQLite actually internally makes an index when creating the FK relationships, but I doubt it since they're two different "things". An index is an "internal table" that keeps a list of pointers to where your data is. It is used internally by the [database engine/virtual machine] and isn't accessible by any SQL statements. See note 1 for technical info about indexes, and see note 2 about virtual machines. The times you should use indexes is when you know you're going to be looking up a bit of data against that particular field. This will significantly increase look up times. In the example below, you'd probably want an index against the book number, chapter number, page number, paragraph number, sentence number, word number, and letter. If you are not doing a text search, your code would never look up the word "FUR" and relate it to what book/chapter/page you're looking at. You're code would be looking at the identification information. If you ARE looking up the word FUR, if you were to put an index against the tWords table for both the WordID and the Word field itself (Probably as seperate indexes), searching for "FUR" would be fast, and you could get the results of your look up pretty fast. However, without an index against the Word field, the database engine would have to look at every single row in the tWords table and find "FUR" to procure a result. The index in any database works similar to how an index works in any book that contains pages with word definitions. Go to the back of the book, look up the word "Rabbit". Note the word list is sorted, so, you can quickly jump around that index and find the word easily. You'll will see the page numbers where the word or subject of Rabbits occur. This saves you from looking at every page in the book to find out about Rabbits. The table of contents also is a type of index as well, as it gives you a (typically) unsorted list of titles of things you want to look up and gives you the page number where the data starts, so then you can hop right to the page and start digesting the information. ARGUABLY the ToC and word index also is a FK concept as well, since you have the key (Rabbit or Chapter) and provides information on where to look that data up, however, that isn't what I wanted to portray. ;) FKs are two (or more) pointers that say one field in one table is related to that of another table in some regard. The use of FKs are typically used to delete data at the database level. For instance, say you're writing a book that has chapters in it. If you create database level FK that "ON DELETE"s the pages of each chapter, you execute one query (delete from tChatpers where ChapterID=10) and it'll go through and delete the chapter with an ID of 10 (Not necessarily "CHAPTER 10" since two or more books could have a CHAPTER 10) from the book that belong to that table. On top of that, if you were to have paragraphs that are linked "ON DELETE" to the pages, the paragraphs are deleted as well. Sentences linked to paragraphs, words linked to sentences, letters linked to words, etc. If all properly setup, the single delete will take care of getting rid of everything related to that chapter in one simple command. No thought process of actual database level indexes HAVE to be present at this point. When building SQL statements ground up, you CAN use non-equal comparisons for FKs, such as [ select BookName, ChapterName from tBooks join tChapters on tBooks.BookID <> tChapters.BookID where tChapters.ChapterID=10] but you'd get whacked out results which really make no sense to a person, but the point is, this is a LEGAL statement. Whether or not using joins is an actual FK I guess could be argued "FOR" or "AGAINST". Personally, I'm FOR terming a JOIN as a true FK, since by def'n, a FK is a link between two bits of data. The JOIN links them, so, its a key relationship. However, I've heard the other side and say it isn't an enforced FK when considering data integrity. *shrugs* Semantics, I guess. *1) A basic index would be something similar to a key/value pair. The list of keys would be sorted, however the list of values that key holds doesn't necessarily have to be. From memory, back when I was doing my MCDBA cert for SQL2k, the basic index look up engine would count how many unique indexes exist, read the key in the middle, decide if further look ups had to be done. If more had to be done, it'd either look at the key at the 1/4 mark, or the 3/4 mark, and decide again. It'd keep drilling the index page until it found what it needed. It'd then look at all the data pages required and process the data. So if you were looking for the number 30 in a list of 100 unique numbers (1-100), it'd look at 50, decide what it found was too high, look at 25, decide it was too low, then look at 37, decide too high, then look at 31, again find it too high, then look at 30 and read in the data which may live on pages 99, 45, 58, 109, and 55. *2) Virtual Machine as far as SQLite is concerned: http://www.sqlite.org/opcode.html On Fri, Mar 1, 2013 at 4:18 AM, Igor Korot <ikoro...@gmail.com> wrote: > Dan, > > On Fri, Mar 1, 2013 at 1:12 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > > On 03/01/2013 03:24 PM, Igor Korot wrote: > >> > >> Hi, > >> Will foreign key creation give me the speed increase? > >> Or I need more an index? > > > > > > Foreign keys only ever slow things down (more stuff to check each > > time a row is inserted/updated/deleted). Indexes sometimes speed > > up queries. > > So, it is better to make an index? > > Thank you. > > > > > Dan. > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
