Hello all, first of all, sorry for the non descriptive title, I'm just too rushed so I couldn't come up with a better one. Second: I have a portion of my database the looks like the following diagram: I have contributors on the system, each write to many sources, and a source can have many working contributors. Users can subscribe...
I have a search implemented on my site, it runs the following queries: SELECT COUNT(mov_id) AS total_things FROM content WHERE con_status = 1 AND con_incomplete = 0 AND con_type = 1 AND ((con_title) LIKE ('%search keyword%') OR soundex(con_title) LIKE soundex('search keyword') OR MATCH (con_tit...
Hello guys, I need advises and want to share my experience about Query Optimization. This week, I found myself stuck in an interesting dilemma. I'm a novice person in mySql (2 years theory, less than one practical) Environment : I have a table that contains articles with a column 'type', and another table article_version that contain a...
I have a mid-size collection of records --about 20 million -- that I need to load into mySQL for use in data analysis. These happen to be records of people visiting places. They are uniquely identified by three data items: place - a unique INT person - a character string, sometimes numeric and sometimes alphanumeric , eg AB123456...
The question from quite a long time boiling in my head, that out of the following two stored procedures which one would perform better. Proc 1 CREATE PROCEDURE GetEmployeeDetails @EmployeeId uniqueidentifier, @IncludeDepartmentInfo bit AS BEGIN SELECT * FROM Employees WHERE Employees.EmployeeId = @EmployeeId IF (@Includ...
I have a couple stored procedures that run for about 2-3 minutes a piece (lots of data). When I run the stored procedures inside SQL Server Management Studio, the queries run fine and return the appropriate data, however, when I run my SSRS Report, it errors out with "Object has been disconnected or does not exist at the server." Any su...
I working on a food database, every food has a list of properties (fats, energy, vitamins, etc.) These props are composed by 50 different columns of proteins, fat, carbohydrates, vitamins, elements, etc.. (they are a lot) the number of columns could increase in the future, but not too much, 80 for extreme case Each column needs an indiv...
Say I have a database table T with 4 fields, A, B, C, and D. A, B, and C are the primary key. For any combination of [A, B], there is always a row where C == spaces. There may or may not be other rows where C != spaces. I have a query that gets all rows where [A, B] == [in_a, in_b], and also where C == in_c if such a row exists, or C...
I am trying to use the following query on a table with ~200k records in it. There are all sorts of other fields that can be filtered by, but this is a base example. SELECT b.isbn FROM books b WHERE b.price IS NOT NULL AND b.deleted = '' AND b.publication_date <= '2009-12-04' AND ( b.subject1_id IN ('CAT1','CAT2','CAT3','CAT4','C...
I have this database: Tab1 ---1:n--->tab2 (parent->id) (max1:4) - the complicated part Tab1 ---1:n--->tab3 (parent->id) - simple join tab1 id name version ..etc. tab2 id parent type price ..etc. tab3 id parent type data I'd like to select complete set of information from these 3 joined tables, but I need to use many v...
Possible Duplicates: Select * vs Specifying Column Names Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc. Is there a performance difference between select * from tablename and select column1, column2 from tablename? When it is select * from, the database pulls out all fields/columns which are more than...
I am writing the following sub query for some project specific purpose: SELECT count(*) from table1 WHERE userB='$p' AND userA IN (SELECT userB FROM table1 WHERE userA='$row[username]') I was curious if this was the best practice when doing it in PHP or should I resort to the conventional way of first getting the subquery resu...
SELECT * FROM table WHERE col IN (1,2,3) or SELECT * FROM table WHERE col = 1 OR col = 2 OR col = 3 ...
I am joining multiple tables in a MySQL query, and the execution of this query is very slow. I badly need to improve the execution time! I did some optimization, but still it loads slowly. Any suggestions? ...
I've developed a web based point of sale system for one of my clients in Ruby on Rails with MySQL backend. These guys are growing so fast that they are ringing close to 10,000 transactions per day corporate-wide. For this question, I will use the transactions table as an example. Currently, I store the transactions.status as a string ...
Have I to break this query SELECT ISNULL(SUM(AF.[amount]), 0) AS [firm], ISNULL(SUM(ACA.[amount]), 0) AS [cash], ISNULL(SUM(AC.[amount]), 0) AS [client], ISNULL(SUM(AFR.[amount]), 0) AS [fr], ISNULL((SUM(AF.[amount]) + SUM(ACA.[amount]) - (SUM(AC.[amount]) + SUM(AFR.[amount])), 0) AS [total] FROM ... into two: DECLARE ...
i have a table called orders. one column on order is customer_id i have a table called customers with 10 fields Given the two options if i want to build up an array of order objects and embedded in an order object is a customer object i have two choices. Option 1: a. first query orders table. b. loop through records and query the pe...
i asked this question around a single join or multiple (select n + 1) queries i wanted to find out if this was the same if you had many to many relationship and a lot of bridge tables for example, here are my tables: Table: People (id, first, last, age, phone, etc . .) Table: Roles (id, name) Table: PeopleRoles (id, personID, roleID)...
i have the following tables: Table: People (id, first, last, age, phone, etc . .) Table: Roles (id, name) Table: Skills (id, name) Table: People_Roles (id, personID^, roleID^) Table: People_Skills (id, personID^, skillID^) ^ = foreign key I basically want a query that gives me the full result set of all people and their roles and the...
Two examples are: Columns that will show up in a queries where clause (where Name = "xtz") Columns that you are going to order (sort) on in queries Is this correct and are there other important use cases? Can SQL Server recommend fields to index based on usage patterns ? ...