I'll start by explaining my scenario: I am creating a MMORPG and I want to get a better understanding of the most efficient way to store items in an inventory. From my research, I found that most games have an INVENTORY table where the rows have the ITEM_ID, the ITEM_QUANTITY, the USER_ID, and the SLOT_ID.  I'm thinking that this table could potentially have millions of rows.  So If I use a simple statement such as:  SELECT ITEM_ID FROM INVENTORY WHERE USER_ID = 25;   then how does the database really "find" the result to return? Does it have to "loop" through every single record until it finds the match? My concern is that, if that is in fact the way it is done, then wouldn't some SELECT FROM WHERE statements take forever?  Thanks, Ian
1563
Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level?  For information on queries to index a field, check out .
37749
I am working on a project involving cards from the Magic: The Gathering game. To be of any use, I must load the basic information about each card into my database, but some of the fields may contain arrays of text, that is, they may contain multiple values .  For example, a card can be classified to be both "green" and "blue". Or it can have several different creature types specified, and so on.  My first thought was to simply store the values as a string, and then search using the LIKE keyword. In combination with the NOT keyword, it should be possible to search for any desired combination of words, and also exclude certain words.   However, LIKE is going to be (relatively) slow as it can't use an index and has to rely on text pattern matching, and probably has other drawbacks as well. With normalization I could improve speed by assigning an integer key to each keyword, and then create a table that connects each card with the relevant keys (see  for a clearer description).  But with normalized tables I have to deals with joins (severely increased code complexity, not only in the SQL part), transactions (to update all relevant tables or rollback everything), and possibly decreased performance due to joins (see ).  AFAIK there are roughly 20000-25000 different magic cards in existence (and more being released every year). With one row for each card, is it worth it overall to normalize the data, or is the benefit not worth the costs? Why/Why not? And how do you decide this question in general?  EDIT: I am currently using a MySQL database, but I would also appreciate answers that apply to any of the other non-commercial/open source databases out there. Who knows - I might switch to a different database at some point.  Answers should not provide a technical solution only, but explain why it would/might be the right solution in terms of best/good enough performance for least amount of effort.
93413
I’m in a permanent discussion with the developers of the company where I work because they say it is better to get rid of relationship enforcement (via FOREIGN KEY constraint definitions) in a relational database in order to speed up large queries and to gain better performance. The platform under consideration is MySQL 5.x, and no FOREIGN KEY has been set up, even some PRIMARY KEY constraints of the relevant tables are missing which, at least for me, is not reasonable. Maybe they’re right and I’m wrong, but I don’t have enough arguments to discuss about this situation. This has been the preferred approach for three years now. I’m new in this company (only one month) but, as the product “works”, there is hesitation to enhance the database; nevertheles, the first thing I noticed is one page taking 1 minute to load (yes, 60 seconds!). One of the claims behind the current state of affairs is that a “denormalized” database is faster than a normalized one, but I don’t believe that’s true. Most of the relevant queries include JOIN operations, which makes them run very, very, very slow with large amounts of data (the database contains millions of rows). Commonly, the handling of “CRUD” operations is implemented at the application program code level; for example, in order to DELETE some data FROM, let’s say, TableA:  it is necessary to first check on the fly if there is some relationship between the rows of TableA and TableB, in case that said relationship is “detected”, then the app program code won’t allow to DELETE the pertinent row(s), but if for some reason the app program code fails, then the DELETE operation will “succeed”, no matter if there is any relationship regarding the involved rows and tables.  Question Could you help me to elaborate a good, accurate and solid answer to enrich the debate?  Note: Maybe something like this has been asked (and answered) before, but I couldn’t find anything by means of Google.
79420
If I have a table  CREATE TABLE users (   id int(10) unsigned NOT NULL auto_increment,   name varchar(255) NOT NULL,   profession varchar(255) NOT NULL,   employer varchar(255) NOT NULL,   PRIMARY KEY  (id) )   and I want to get all unique values of profession field, what would be faster (or recommended):  SELECT DISTINCT u.profession FROM users u   or  SELECT u.profession FROM users u GROUP BY u.profession   ?
22977
Is it possible to search every field of every table for a particular value in Oracle?  There are hundreds of tables with thousands of rows in some tables so I know this could take a very long time to query. But the only thing I know is that a value for the field I would like to query against is 1/22/2008P09RR8. &lt;  I've tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.  SELECT * from dba_objects  WHERE object_name like '%DTN%'   There is absolutely no documentation on this database and I have no idea where this field is being pulled from.  Any thoughts?
13604
I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!  Environments are supposed to be the same, so what should I do and/or where should I look in order to see why the query doesn't perform the same?
28828
I am pretty well versed with SQL Server, MySQL, Oracle etc but putting these Database products aside, is there a resource that will help me design relational databases well? Is there something like patterns or best practices for database design?  I have seen a few times that database is often not scalable; people have personal preferences with keeping columns like isChecked column which is boolean in nature but stored as Char(1) with values like 'Y' and 'N' instead of 0 and 1 which to me sounds better. Ways not to commit common mistakes while doing database design?  Links to books or articles will be highly appreciated.  Thanks in advance.
35596
Currently, I am trying to copy data from TABLE1 to TABLE2. In terms of insertions performance, would it be the same or faster if I would to do  BULK INSERT manually (i.e BULK insert every 10K records into TABLE2 via INSERT INTO TABLE2 VALUES (1,2), (5,5), ...), versus INSERT INTO TABLE2 SELECT * FROM TABLE1
44452
I am using MSSQL Server 2005. In my db, I have a table "customerNames" which has two columns "Id" and "Name" and approx. 1,000 results.   I am creating a functionality where I have to pick 5 customers randomly every time. Can anyone tell me how to create a query which will get random 5 rows (Id, and Name) every time  when query is executed?
84603
Say I have an ID row (int) in a database set as the primary key. If I query off the ID often do I also need to index it? Or does it being a primary key mean it's already indexed?  Reason I ask is because in MS SQL Server I can create an index on this ID, which as I stated is my primary key.  Edit: an additional question - will it do any harm to additionally index the primary key?
76289
How is it that websites like SCG or CFB created their card database inventory. How would i be able to do this myself now? I want to create a similar website where a user can search and find any card with images. Obviously entering manually into a sql server is not feasible. Any help on easiest way to get this done?
120183
How many records should there be before I consider indexing my sql tables?
68617
I have a table with approximately one million rows.  It is being used in production and I ran an UPDATE which covers ~95% of those rows.  After five hours I cancelled the request because it was taking so long.    The table has an auto-incrementing ID column, so I tried extending the WHERE condition of my query to include id BETWEEN 1 AND 500.  This update completed in approximately two seconds. I then manually iterated up through the id in batches of 500, e.g. BETWEEN 500 AND 1000, then BETWEEN 1000 AND 1500.  At this rate it will require 2000 batches of 500 to update the whole table.  Updating 2000 batches at 2 seconds each is just over an hour.    My questions are:    What reasons are there for this difference? I don't care about transaction isolation, so is there a way to simulate this 'update in batches', without having to script the 2000 updates to run separately.
77591
I've created main SELECT statement like below. For every row in Base table (base on IDKey I've added columns with values from result of INNER JOIN of few tables.  SELECT   IDKey,    Name,    (select Amount from Table1 INNER JOIN Table2 ... where ID=IDKey) AS AmountTypeX,    (select Amount from Table1 INNER JOIN Table2 ... where ID=IDKey) AS AmountTypeY FROM BaseTable   BaseTable:  IDKey Name  ---------- 1     Ian 2     John 3     Ann ...   INNER JOIN of few tables Table1, Table2... :   ID Amount Type -------------- 1  100    X 1  200    Y 2  300    X 2  400    Y 3  500    X 3  600    Y ...   Result looks like:  IDKey  Name  AmountTypeX  AmountTypeY ------------------------------------- 1      Ian   100          200 2      John  300          400 3      Ann   500          600 ...   Do you have any suggestion how to avoid this nested SELECT statements in the main query at the begining and made this query simpler and more effective ?  I wast thinking about PIVOTING of the INNER JOIN result to look like below (all data for particular ID in one row) and then JOIN with main query but Have no idea how to do this and is it possible.  ID  X   Y ----------- 1   100 200 2   300 400 3   500 600 ...
119219
Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?  TL;DR: If my joined query takes longer than running individual queries, is this my fault or is this to be expected?  First of, I am not very database savvy, so it may be just me, but I have noticed that when I have to get information from multiple tables, it is "often" faster to get this information via multiple queries on individual tables (maybe containing a simple inner join) and patch the data together on the client side that to try to write a (complex) joined query where I can get all the data in one query.  I have tried to put one extremely simple example together:    Schema Setup:  CREATE TABLE MASTER  ( ID INT NOT NULL , NAME VARCHAR2(42 CHAR) NOT NULL , CONSTRAINT PK_MASTER PRIMARY KEY (ID) );  CREATE TABLE DATA ( ID INT NOT NULL , MASTER_ID INT NOT NULL , VALUE NUMBER , CONSTRAINT PK_DATA PRIMARY KEY (ID) , CONSTRAINT FK_DATA_MASTER FOREIGN KEY (MASTER_ID) REFERENCES MASTER (ID) );  INSERT INTO MASTER values (1, 'One'); INSERT INTO MASTER values (2, 'Two'); INSERT INTO MASTER values (3, 'Three');  CREATE SEQUENCE SEQ_DATA_ID;  INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.3); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.5); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.7); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 2, 2.3); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.14); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.7);   Query A:  select NAME from MASTER where ID = 1   :  | NAME | -------- |  One |   Query B:  select ID, VALUE from DATA where MASTER_ID = 1   :  | ID | VALUE | -------------- |  1 |   1.3 | |  2 |   1.5 | |  3 |   1.7 |   Query C:  select M.NAME, D.ID, D.VALUE  from MASTER M INNER JOIN DATA D ON M.ID=D.MASTER_ID where M.ID = 1   :  | NAME | ID | VALUE | --------------------- |  One |  1 |   1.3 | |  One |  2 |   1.5 | |  One |  3 |   1.7 |   Of course, I didn't measure any performance with these, but one may observe:   Query A+B returns the same amount of usable information as Query C. A+B has to return 1+2x3==7 "Data Cells" to the client C has to return 3x3==9 "Data Cells" to the client, because with the join I naturally include some redundancy in the result set.   Generalizing from this (as far fetched as it is):   A joined query always has to return more data than the individual queries that receive the same amount of information. Since the database has to cobble together the data, for large datasets one can assume that the database has to do more work on a single joined query than on the individual ones, since (at least) it has to return more data to the client.  Would it follow from this, that when I observe that splitting a client side query into multiple queries yield better performance, this is just the way to go, or would it rather mean that I messed up the joined query?
68568
I'm working on a personal project where I want to make a database that holds the information for every owner of every public company. Say for example "Sears INC" and the program would get the information for every owner in "Sears INC". That is the idea. But I am having a really hard time trying to structure this database. I only have limited experience and knowledge about this subject so any guidance would be greatly appreciated.   Now I was first thinking about making a table called companies, and give them all unique ID's and make a table for every company. Then inside those tables would be all the owners with their unique ID. That would link to their information. I have tried to visualize this here:    Now I know that creating a table for every company would be tedious but I cant think of any other way of doing this. As you all know each company might have the same owner and therefore it would make sense of using Owner_ID to identify each owner.   I have all the other data in a CVS file that I could easily import in PostgreSQL after I have structured the database.  Any help would be greatly appreciated.   TL:DR; Creating a database of stock owners that will be searchable by company name, and need help to structure the database for maximum efficacy.   A person can only have one phone and one address. Same goes for the company, because I have made a script that finds the phone based on this information. The way I differentiate between knowing if a entity is a company or person is by using a column called F_Org which will be larger than 4 digits for companies. This is all handled by the script.
106834
\textbf{47.Consider the following relations:\\ \includegraphics[width=120mm]{Image6.png}\\ Consider the following SQL query.\\  SELECT S.Student$_$Name, sum (P.Marks)\\  FROM Student S,Performance P\\ WHERE S.Roll$_$No=P.Roll$_$No \\ GROUP BY S.Student$_$Name\\  The number of rows that will be returned by the SQL query is .........}\\ \vspace{2mm}\\   Error Missing { inserted.... Please help me out!