MYSQL :: Multiple Table Calls And Slow Loading Times... Joins?

Slow Loading Times
It takes more than 30 seconds to load the phpmyadmin page. On a localhost server.
It says
Fatal error: Maximum execution time of 30 seconds exceeded in c:appservwwwphpmyadminlibrariesmysql_wrappers.lib.php on line 101

This sometimes also happens when loading my local website that uses MySQL.
I've tried reinstalling, but it doesnt help.

View Replies !

Joining A Table Multiple Times
I was wondering if it was possible to join 2 tables on 3 different feilds, but I don't want the 3 feilds to limit my results... I'll try and explain:

Table 1 = "offices"
Table 2 = "employees"

The offices table has the addresses of all the offices and 3 extra feilds "manager", "supervisor", and "employee". (these 3 feilds have a numeric value equal to the primary key in the employees table)

The employees table has the employee information like address and phone number and such.

I would like to write a query that would pull the office name as one feild, followed by "manager", "supervisor" and "employee".

Currently I have this, but it only pulls the employee and I'm not sure how to get the manager and supervisor to be a part of the query:

SELECT CONCAT(office.id_store, ' - ', office.storename) as offices, CONCAT(user.lastname, ', ', user.firstname) as fullname
FROM office LEFT JOIN user ON office.empoyee = user.id_user

View Replies !

Linking Table Entries Multiple Times
I have no idea how to handle certain kinds of relations between tables:

I have a table called 'labels' and a table called 'artists'. Every artist belongs to at least one label, but could also belong to any higher number of them. How would I solve this in my database architecture?

I discarded the idea of simply adding 10 columns called 'label1', 'label2', etc; Also I have found the ENUM and SET datatypes, but the possible values entered there should be set at table creation time which is also not quite what I'm looking for.

View Replies !

Multiple Joins To The Same Table?
In summary, to put it as simply as posiible

users table:
id, username

ref table:
id, user1_id, user2_id

I would like to return:
ref.id, users.username(ref.user1_id), users_username(ref.user2_id)

View Replies !

Multiple Joins To Same Table
I am tring to join 2 tables but one table has 4 references to the other table.

Can this be done?

example below ....

View Replies !

Multiple Joins To One Table
I have a table called "tasks" which has, amongst others, 3 columns called

raised_by,
assigned_to,
parked_with

these columns contain the user ids of individuals who have specific responsibilities for the task. I have another table called "users" that contains all the user information, such as login names and passwords as well as two columns called

user_id,
display_name

I am trying to write a query that will return the display_name for the raised_by, assigned_to and parked_with fields.

View Replies !

Multiple JOINS On The Same Table
two tables:

table one "matches" has the following fields:
-match_id
-team_one (has values:Liverpool, Everton etc)
-team_two (has values:Liverpool, Everton etc)

table two "teams":
-team_name (has values:Liverpool, Everton etc)
-team_stadium
-team_manager....

View Replies !

Multiple Table Joins In A Select
How do I write a select query to join more than two tables? =20. Table A relates to table B and table B relates to Table C. I need to return fields from Table A and C that are related.

View Replies !

Count On Multiple Table Joins
I have 3 tables: COURSE, USER_COURSE, COURSE_TOPICS

What I am trying to do is select all the rows of COURSE and have a count of all users on each course and a count of the topics assigned.

Id normally write that as an inline select in oracle but it seems Mysql doesnt work in quite the same way!

Using an outer join on just COURSE to USER_COURSE i got it to work:

select c.*, count(uc.course_id) as no_of_members from course c
LEFT OUTER JOIN user_course uc ON c.course_id = uc.course_id
group by course_id;

So following that example I then added an extra join to COURSE_TOPIC to get the count of topics:

select c.*, count(uc.course_id) as no_of_members, count(ct.topic_id) from course c
LEFT OUTER JOIN user_course uc ON c.course_id = uc.course_id
LEFT OUTER JOIN course_topic ct ON c.course_id = ct.course_id
group by course_id

but it returns the wrong figures for both counts!

View Replies !

Multiple Left Joins In One Table
When doing multiple left joins in one table: for each join, which table is considered to be the "left" table (ie: all results must be returned).

View Replies !

PhpBB Slow Loading
my site runs quickly except when a user hits forum (allready signed in or logging in) to login to the forums it takes 10x as long to get to the forums as it does the rest of the site, once in the forums its ok and runs as normal,is there any settings in SQL that can speed this up? or is there another problem.

View Replies !

Slow Log File Has Times In Whole Seconds But Mysqldumpslow Has Fractions Of Seconds?
When I look at the mysql-slow.log file itself that I've set up, it lists times in whole seconds. However, when i run mysqldumpslow to parse that same log file, it shows times in fractions of seconds. The info from mysqldumpslow seems correct, but I can't figure out where it gets its info. Where is it coming from?

View Replies !

One To Many Multiple Times
I have a 'links directory' database that currently contains 4 tables: category, subcategory, region and links. My links table contains company names and, among others, three fields catID, subcatID and regID. Each of these are related to primary keys in the other three tables, category (catID), subcategory (subcatID) and region (regID).

My issue is that the records in the links table can be in multiple regions, multiple subcategories and multiple categories, but the way I have it designed, each record in links can only be associated with a single catID, subcatID and regID.

Currently the only workaround I can think of it to have duplicate entries in the links table for a single company, but with every variation of catID, subcatID and regID. This will result in a very large table, but I cannot currently think of any better idea.

View Replies !

Select Statement With Multiple Counts/multiple Joins Trouble
SQL
SELECT g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username, count(c.comment_id) as comment_count, count(v.video_id) as video_count, count(m.user_id) as user_count
FROM duo_groups as g
LEFT JOIN duo_group_members as m on m.group_id=g.group_id
LEFT JOIN duo_users as u on u.user_id=m.user_id
LEFT JOIN duo_videos as v ON g.group_id=v.group_id
LEFT JOIN duo_video_comments as c on v.video_id=c.video_id
GROUP BY g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username
Will return

My current problem with the above statement is with the counts. The count is accurate when only one of the counts is returned. However when more than 1 of the counts is returned the other counts (if they are not 0) will return the same number. For example look at the screenshot above. The first row, all three counts are the same but they are not accurate. They alll point to 8 because there are 8 comments, but there aren't 8 videos or 8 users. Same with the second row. There aren't 2 users only 2 videos.

View Replies !

Select Same Row Multiple Times Using Column
I've seen this asked before and although someone answered that it is possible, they did not post the solution, so if anyone can enlighten me that would be helpful.

Basiclly with table info

name | quantity
john | 3
anne | 1
will | 2
Return results

john
john
john
anne
will
will

View Replies !

Check Same Column Multiple Times
What would like help with is how to best structure a query that will select user_id and username from Table1 where user_id matches user_id in Table2 and BOTH (group_id = 6 where value = 10) AND (group_id = 8 and value = 15) in Table2 (or even more comparisons)

If both conditions are not met in table two, then no results are returned.....

View Replies !

Would Like To Avoid Iterating Through Data And UPDATING Multiple Times
I've got product data that belongs to a category table. The business requirement is for the user to be allowed to update the order in which products appear in that category. There is a field called product.sequence for that very purpose.

I'm being given the product id's in order for each category.

Do I have to iterate through the data in PHP and UPDATE the table w/ many SQL statements, or is there one SQL statement where I can provide the list of products to update in order?

I've used MySQL user variables before (e.g. SELECT @m:=0;) but I don't see how I can use it here... Is what I want possible in one query?

View Replies !

Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads
and posts. when i query my threads table with a join, i need to access
the users table twice to get the username of the first poster and last
poster. But how? I can only figure out how to get one or the other. Is
my design bad? eg

SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic,
username FROM DiscussionThreads, users WHERE
DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT
10 .

View Replies !

Loading Multiple Text Or CSV Files
I have a folder of approximately 6000 csv files. I would like to load them all into the same table. (i.e. what code do I use)? I really don't want to load them one at a time using the phpmyadmin import function, and I have been searching online for hours, but have not found the coded solution yet using mysqlimport or load data infile or similar.

View Replies !

Multiple Joins
My database has three tables with the following fields:

--tblIngredients--
IngredientID
Ingredient
IngredientInfo

--tblRecipeIngredients--
RecipeIngredientID
RecipeID
IngredientID
Quantity

--tblRecipe--
RecipeID
RecipeName
Directions

tblRecipeIngredients is a join table to link each recipe from tbRecipe with it's respective ingredient(s) from tblIngredients.

Now, I'm trying to write a query that will return all recipe names (RecipeName) that don't have each of the Ingredients specified. In other words, I'm going to have a form that allows the user to select their on-hand ingredients and I want the query to eliminate all the recipes that include ingredients that the user does not have on hand and return the rest. I hope I'm making sense here...

Anyhow, this is what I have so far, but it doesn't work the way I would like it to:

View Replies !

Multiple Self Joins
I am a newcomer to MySQL, from Access. My first project is porting a single-user, no-security Access DB to MySQL, largely due to Access size limitations, but I was also hoping for a sizeable improvement in speed. The data is a set of over 2,000 text files spanning around 130MB, with considerable growth expected over the next few years.

The app uses VBA to read in all the text and index the location of every word. A sample from one file is shown at the end. What is important is the dot-and-two-letter descriptor preceding each line, and the number of the block in which it lives. (A block is the range from one '._HS nnn' line to the next '._HS nnn' line.) ......

View Replies !

Multiple Joins To Same Field
I have two tables.
The first table (t1) has color codes that describe different parts of a book, such as page_edge_color, binding_color, type_color, and so forth.
The second table (t2) contains all of the color descriptions (it has two fields, one is the "code" and the other is "color_description"...e.g., "BK" = "Black".

I can get the description of one color from t1 by joining as follows:

Code:

SELECT
t2.color_description
FROM t1
LEFT OUTER JOIN t2 ON t2.code = t1.code

But how do I display the color description for more than one field in t1? In other words, how do I use the description table to describe multiple fields in the color code table.

View Replies !

Using Joins Or Multiple Queries
I have two very large tables, with relational id's. Would it be faster to
1. use a join on the two tables?
2. make two queries

If I were to make two queries would it be faster to use '...WHERE FIND_IN_SET(...' or to use '...WHERE IN(...'

View Replies !

Sum Two Tables With Multiple JOINS
i'm trying to get more efficient at SQL rather then creating tons of PHP work arounds to get the job done. I have two queries here for example. One query shows the Budget Cost/Hours of a Work Order, the other shows the RFI(aka Change Orders) for a Work Order. I want to sum the budget and the rfi in one query and get Total Hours/Total Cost. Here are the two queries ...

View Replies !

Multiple Complex Joins
How can one link two fields in one table to single field (Primary key) in another table in a single (left join) query, to return two values? e.g.

Table 1
ID (PK)
...other fields
Departure_ID
Arrival_ID
...other fields

"places" table
Place_ID
Placename (value to use in view)

I think this is illegal but, more probably, impossible; perhaps someone could suggest an alternative methodology.

View Replies !

Multiple Joins Expensive?
I need to extract some information which comes from two of several tables. I have a query with multiple joins - kind of like this:

SELECT
M.MessageID, C.ConversationID, M.SentDate, M.ReadDate, M.Read,
M.Sender, M.Receiver, M.Subject, M.Body, M.Answered,
M.LetterCategory, M.Type,
FROM Messages M
INNER JOIN MessagesInConversations C ON M.MessageID = C.MessageID
INNER JOIN Translators T1 ON M.Sender = T1.Username
INNER JOIN Translators T2 ON M.Receiver = T2.Username
INNER JOIN Admin A1 ON M.Sender = A1.Username
INNER JOIN Admin A2 ON M.Receiver = A2.Username

WHERE blah blah blah

My question is how inefficient is it to have so many joins? Is it ok, or should I restructure the data in some other way.

View Replies !

Inner Joins On Multiple Tables
I have multiple tables about 6 to be exact. I need to show columns from different tables in a swing application. How to I show colums from different tables in one table and then use the new table to create, update, delete and view. Do I need to create an inner join? If yes, how do I do inner join on multiple tables.

View Replies !

Multiple Counts And Joins
SELECT
tin.traffic_referer,
COUNT( tin.traffic_referer ) AS hits_in,
COUNT( tout.traffic_page ) AS hits_out
FROM traffic_in tin
LEFT JOIN traffic_out tout
ON tout.traffic_referer = tin.traffic_referer
GROUP BY tin.traffic_referer, tout.traffic_page
LIMIT 0 , 30
That's the code I'm trying to use but the result sets are coming up weird.

If theres no hits out then the results come up like they should:

http://babelfish.altavista.com | 6 | 0
But if there are any hits out then the results come up like this:

http://forum.phap.net | 6314 | 6314
The hits_in and hits_out always come up the same.

View Replies !

Problem With Multiple Joins
I have a database with three tables - eaGroups, eaGroupDelegates and eaDelegates.

Groups can hold multiple delegates and delegates can belong to more than one group, so I have used a link table (eaGroupDelegates).

What I need to do is return a list of groups that have a 'client' field set to a specific value, along with a count of the number of delegates in each group that don't have 1 in their 'deleted' field.

I thought the following statement should work, but it returns a high number of delegates, even though it should return none (i.e. there are actually no delegates in the groups for client 13). It does return the right groups, though.

SELECT eaGroups.id, eaGroups.name, eaGroups.parent, eaGroups.level, COUNT(link.delegate) AS delegateCount FROM eaGroups LEFT JOIN (eaGroupDelegates link INNER JOIN eaDelegates delegate ON link.delegate=delegate.id AND delegate.deleted=0) ON link.groupID=eaGroups.id WHERE eaGroups.client=13 GROUP BY eaGroups.id ORDER BY level, name

View Replies !

Loading Database From Multiple Text Files Each Containing A Partial List Of Columns
I'm kind of new to mysql. I would like to know if there is a way to use load infile or mysql import syntax to load one table with multiple files each containing a partial list of the table's columns:

Table contains : ID, first name, last name, occupation, salary

There are two files one containing the first three columns, the other the first field (ID) and last two fields. Is there a way to load them with just two load statements?

Note: I know that they can be loaded into two tables and later joined where ID is equal, but the actual example is about 30 text files rather than two, which is a bit too much.

View Replies !

Multiple Joins Concatenated Into In A Single Row
I have some trouble understanding how I can make multible joins work the way I expect it to. I have a database that looks something like this: ....

View Replies !

Indexing Effeciently With Multiple Left Joins
Can anyone point me in the right direction here.
Tell me what to do, or where to find information?

I am trying to access a PHP page and it takes too long.
The problem is the SQL code is in not efficient.

How do you index effieciently with Joins?

The code has four Left Joins.
Looks something like:

$sql = "SELECT
distinct(hi.item_id),
hi.*,
CONCAT(c.contact_first_name,' ',c.contact_last_name) assigned_fullname,
c.contact_email as assigned_email,
p.project_id,
p.project_person,
p.project_color_identifier,
his.status_date
FROM helpdesk_items hi
LEFT JOIN helpdesk_item_status his ON his.status_item_id = hi.item_id
LEFT JOIN users u ON u.user_id = hi.item_assigned_to
LEFT JOIN contacts c ON c.contact_id = u.user_contact
LEFT JOIN projects p ON p.project_id = hi.item_project_id
WHERE $where" .

View Replies !

Joins Across Multiple Tables, Influence Which Item To Take If Only One Is Needed Of Each Kind
I'm trying to get a join across multiple tables to work where I have to walk across several pivot tables to get one of the selection criteria.

What I want is the latest entries for each supplied wp_terms (the category) in the wp_posts table. While fetching each one with its own query is easy, I'm stumbling when trying to build a query that returns the latest entry for a list of supplied categories. Group by doesnt seem to cut it, since it takes place before the order by.

Here is the query and its output (without grouping, this will return all entries for the specified categories): ....

View Replies !

Loading Table From CSV
I have a table like this

create table test(emp_no integer primary key,
emp_name varchar(25),
doj date);

I have to load it with the following CSV file

1,"Sam,Daniel",2008-11-01
2,"Jennifer,Jane",2008-05-11
1,"Louie,Philip",2008-04-23
4,"David,Alfred",2008-13-11

I used the following load data query to load my table ....

View Replies !

Loading A File Into A TABLE
I am new to MySQL. I am trying to load from a text file to a table,
and am having problems
with the fields in the text file being translated to the correct fields
in the TABLE. Right now,
I have a tab between each field in the text file, something like:

Chicago USA 2,500,000
New York USA 5,000,000
Madrid Spain 2,000,000

My question: Can I use something else to differentiate between fields,
like a coma?

View Replies !

Loading Mysql Table
I'm trying to make a simple photog gallery, using phpmyadmin....but I need to load the table with all the image filenames. i.e.: pic001.jpg, pic002.jpg, pic003.jpg, ....... I don't want to sit there entering every single filename (if I dont have to). My question is, is there any way I can write an SQL script, to automatically load, let's say .... pic001.jpg - pic300.jpg into the mySQL database?

View Replies !

Loading Text File Into Table
i created a table with the key attribute having an autoincrement property... i loaded a text file without the key attribute column to populate my table...but there's an error... but when i loaded the file with the key attribute column it doesnt post an error... how can i insert or load a text file without the key attribute column since it is already autoincremented?

View Replies !

Data Structure Vs Table Loading
I have just started designing database driven websites with MySQL and I would like to know if there is likely to be any performance issues if lots of pages share records from a single table.

I want to design a simple website where the contents are derived from two tables "PageDetails" and "contentDetails". The page details will contain all page related info that occurs only once (titles, meta tags etc) and the contents page will contain all the repeating "block" on each page.

I am used to designing relational databses for use on a LAN. when I do this I always aim to normalise the design as much as possible for ease of data entry and design maintenance.

When I design on a LAN I have a good idea how many concurrent users there are likely to be and how loaded my database tables will be.

My concern is, if I use this approach online, the resources of one table will be shared by all pages and all users and I have no idea how many concurrent users there will be on this site but it could be potentially 200. Will there be a noticable performance degredation?

The alternative would be to create a pair of tables per page. This would mean any one table would have much less loading, but it would be a maintenance nightmare.

View Replies !

Loading A Database Table With Data From PHP
I tried making a php file that updates my database when it's executed from a browser... But the table doesn't get loaded properly with the data. I can't get it to work. Here's my code:

View Replies !

Update Calls
gjust found out i can do something like
update value set value=value+1 where id=2

can i do something similar with strings as to append some string onto another string

View Replies !

Nesting SET Calls
Seems like I am wasting processing power with this...

$sql="UPDATE traffic SET hits=hits+1 Where (record_no='0001')";
$sql="UPDATE traffic SET hits_mtd=hits_mtd+1 Where (record_no='0001')";

Am having trouble nesting this.... I've tried

$sql="UPDATE traffic SET hits=hits+1, SET hits_mtd=hits_mtd+1 Where (record_no='0001')";

and

$sql="UPDATE traffic SET hits=hits+1, hits_mtd=hits_mtd+1 Where (record_no='0001')";

But those have no effect ...

How can I nest a bunch of SET statements in a single SQL call?

View Replies !

Loading Lots Of Data Into Mysql Table
So I need to load lots of data into my database.

So I discover LOAD DATA INFILE.

Great! This little gem loads my CSV in blazing times (compared to
parsing the file and doing INSERT for each row). Its still slow on
large files, but just barely acceptable.

Only one problem. It truncates fields to 256 characters, even on a
text field.

View Replies !

System Resets When Loading A Large Table
I have an application which is essentially 'read-only' - once the database is loaded it is only read from. So, I am trying to implement it with various indices and a table containg some large content in blob columns. I observe the max_allowed_packet setting and
'split' blobs too large among multiple rows. All is fine during testing. However, when I try a production load (which should load about 1.5G to the various tables) the system resets at or near the 1G mark leaving no logs/msgs (that I can find) telling me
what went wrong. I have tried the following to no avail:

View Replies !

Loading Data Into A Table From Excel Spreadsheet
I have a excel spreadsheet from which I would like to load data into a table in mysql.

View Replies !

Loading Data Into A Table From A Text File
After creating an empty table I try to load data into it from a local .txt file on my machine. This is my command:

load data local infile 'doc.txt' into table data;

MySQL returns this error message: File 'doc.txt' not found.

What is the default folder MySQL looks into? I have installed MySQL on a Windows XP SP3 machine. The doc.txt file is written with MS Notepad.

View Replies !