Foreign Key Speeds Up INNER JOIN Query?
I've read the foreign key topic on the mysql manual but it says nothing if foreign keys optimize the queries with INNER JOIN on the foreign key. I've read that indexes in general speed it up, but is it valid too for foreign index?
I am often linking a medium sized table to a huge sized table via INNER JOIN and I wonder if setting up foreign keys would speed up the INNER JOIN. And is this effect valid to all engines? If not, to which ones is it valid?
View Complete Forum Thread with Replies
Related Forum Messages:
Slow Write Speeds
Here is what i have IBM x226 - 8GB ram - 2x 3.4 Ghz Xeon (single core) processors - RAID 5E (6x300GB disks) Suse Linux 9.0; MySQL 5.0.13 So, I am migrating from SQL server 2000 on a much smaller Dell PowerEdge (2gb ram, 2 Xeon 3.0 Ghz, Raid 5 over 5x74GB). I am exeriencing very slow write times. For instance, there is one table on both servers that has approx 9mil rows...here is a basic test: Update loan set test_col = 1; Platform Time IBM/Linux/MySQL 5 9:45 Dell/Win/SQL server 1:37 The tests were run during very light server activity and I tried them multiple times. Our IT department is fairly insistant that the IBM server is running per spec...obviously something is not right. I have changed some of the server variables with no improvement.
View Replies !
MYSQL Access Speeds
When developing complex systems using PHP and MySQL what technqieus do you suggest to quicken up the access speeds to MySQL? Here are a few that I already know and just wondered if there are any missing? INDEXES Only search relevant columns not * Have good joins and where clauses Use distinct whenever applicable
View Replies !
JOIN Or Foreign Key
I have a table containing property details "tblproperty" and I would like to have a field or another table to store (or link to) the images. After reading through the forum, it seems that linking to the images is better than actually storing the image in a database, as this will make the db bloated. tblProperty contain fields (amongst others): PropertyID propertydescription propertyprice tblImages contains fields ImageID Image (either actual image or path to image) PropertyID (to be linked to tblProperty) So if I create a new table "images" my questions are: 1) Do I "link" the fields using Foreign key, or use a JOIN on a query - What are the advantages or disadvantages of each method? 2) Could someone give me a run-through or an example for setting up the field to use the "path" to the image.
View Replies !
Automatic Natural Join On All Foreign Keys
Is there any automated way (in MySQL 4.1.3) to get the results of a natural join on all foreign keys in a table? In other words, if I have a table T with foreign keys K1, K2, ..., Kn can I get a derived table that has T joined with all the these foreign tables? I am trying to do it now by parsing the CREATE TABLE statement to extract the foreign keys for a table, then using this to join T with these foreign tables. This works decently for 1 level deep (T and all its foreign keys), but I realized that it must work recursively (ie. I must join all the foreign tables on _their_ foreign keys). Even though I can theoretically do it with subqueries, it quickly gets to be a nightmare. Note: I cannot hard-code any table-specific stuff since this is a generic function that is given a table name as input and generates a SELECT statement as output (or returns the records, if I cannot create a SELECT statement).
View Replies !
Foreign Key Value In SQL Query...?
Currently toying with this cart script... http://www.zend.com/zend/spotlight/p...ping-cart4.php At one stage when I get the contents of the cart this query: PHP SELECT inventory.product, inventory.price, shopping.quantity, inventory.description, SUM(inventory.price*shopping.quantity) as subtotal SUM(subtotal) as total FROM shopping, inventory WHERE session='SESSID' AND shopping.product = inventory.product GROUP BY shopping.product is generated by this: PHP function get_contents() { $count = 0; $query = "SELECT ".$this->inv_table.".product," . $this->inv_table.".price," . $this->cart_table.".quantity,". $this->inv_table.".description, SUM(".$this->inv_table.".price*" . $this->cart_table.quantity .") as subtotal, SUM(subtotal) as total FROM ".$this->cart_table.",".$this->inv_table." WHERE session='".$this->cart_id."' AND ".$this->cart_table.".product = ". $this->inv_table.".product GROUP BY ". $this->cart_table.".product"; $result = mysql_query($query, $this->dblink); for($count = 0; array_push($contents, mysql_fetch_array($result)); $count++); $contents['items'] = $count; return $contents; } My setup is a little different in that "inventory.price" is infact a foreign key to a "pricebands" table with 11 different price bands. How can i get the correct price from my "pricebands" table within the MYSQL query?? In essence something like this... PHP SELECT inventory.product, (SELECT pricebands.bndPrice WHERE inventory.price = pricebands.bndID) AS inventory.price, shopping.quantity, inventory.description, SUM(inventory.price*shopping.quantity) as subtotal SUM(subtotal) as total FROM shopping, inventory WHERE session='SESSID' AND shopping.product = inventory.product GROUP BY shopping.product
View Replies !
Query Problems - Foreign Key
I want to be able to use an SQL statement to: 1)First of all find the last entered record in a table called cms_stories THEN 2)Once it has found this record it then grabs the story_id from the cms_stories table 3)Then it looks in the cms_images table to match (equal to) the story_id against the cms_stories table and output the path entered in the main_dir column. Now I tried the below, which I thought would work, but it says that the statement is ambiguous. PHP <? $SQL = "SELECT * FROM cms_stories, cms_pictures WHERE story_id = '$story_id' AND section = 'news' ORDER BY story_id DESC LIMIT 1"; $result = mysql_query($SQL) OR die(mysql_error()); $row = mysql_fetch_array($result, MYSQL_ASSOC); echo '<img src="new_site/pictures/big/'. $row['story_id'] .'">' ?>
View Replies !
Any Way To Optimize Join To Find Rows Without Conditional Foreign Rows?
I've got a table of keywords that I regularly refresh against a remote search API, and I have another table that gets a row each each time I refresh one of the keywords. I use this table to block multiple processes from stepping on each other and refreshing the same keyword, as well as stat collection. So when I spin up my program, it queries for all the keywords that don't have a request currently in process, and don't have a successful one within the last 15 mins, or whatever the interval is. All was working fine for awhile, but now the keywords_requests table has almost 2 million rows in it and things are bogging down badly. I've got indexes on almost every column in the keywords_requests table, but to no avail. I'm logging slow queries and this one is taking forever, as you can see. What can I do? # Query_time: 20 Lock_time: 0 Rows_sent: 568 Rows_examined: 1826718 .....
View Replies !
Self Join Query
I am currently stuck on how the concept of a self-join works. At least in the format I want it. I need to create a query that will show only Active parts. Parts are being replaced by new revisions and new models constantly ....
View Replies !
Join-query
I cannot figure out how to query the db. I got 3 tables: players (player_id, name, number) stat_start (opponent_id, name_id) name_id = players.player_id stat_avb (opponent_id, name_id name_id = players.player_id Now I want to list all my players, and count how many times they have occured in stat_start and stat_avb. I guess I need to do an Inner join as i want all my players listed...
View Replies !
Using Join In A SQL Query
I have a problem which cant sort out. I have 3 tables where the primary keys in two tables also serve as keys in the third table. Table1:one two three four Table2:five six seven eight Table3:nine ten eleven one holds the same information as nine five does the same for ten Now I need to get information from the first and third tables but also need to use the second table to get the information from the third table. I can get the info from the first table grand , but its joining the 3 tables together is where I get stuck. I know its very abstact but this is the way I got the Tables.
View Replies !
Wish To Join A Query
I have this query: PHP Code: SELECT * FROM `news` ORDER BY `id` DESC LIMIT 6 I wish to also select the users id and username from the members table where the users id equals the "owner" field from the query above. As using another query apparently would be stupid?
View Replies !
Using Both JOIN And AVG In One Query
I'm looking to use both JOIN and AVG in the same query but not sure how I'm going to it. I'm VERY new to using MySQL. The ultimate goal is to take one table which counts votes for various criteria in a poll. I want to average those results together. Then I want to join that averaged table with another table and GROUP By URL (which is the column common to both tables). I've successfully used AVG with the first table and used JOIN (with a totally different) table, I'm not sure how to approach using both together.
View Replies !
Query: Inner Join Bug
What is wrong with this query? I cant get it to match the "company" field and it is throwing an error... this query works fine if i do "MATCH(date_year, market1, market2, market3, market4, market5, market6)" , but if I put "MATCH(company)" it breaks.... what am I missing here? PHP $query = "SELECT u.id , u.username , r.id , r.company , r.description , r.market1 , r.market2 , r.market3 , r.market4 , r.market5 , r.market6 , r.location , r.date_year , r.date_month , r.source , r.video , r.audio , r.pp , r.execsum , r.report_url , r.exec_url FROM user as u INNER JOIN user_reports as p ON p.user_id = u.username INNER JOIN emt_report as r ON r.id = p.report_id WHERE username = '$username' AND MATCH(company) AGAINST ('$P_search' IN BOOLEAN MODE) ORDER BY date_year DESC, date_month DESC, company ASC"; Here is my database schema: ================= user_reports ================= user_id report_id ================= user ================= id username ================= emt_report ================= id company description market1 market2 market3 market4 market5 market6 location date_year date_month source video audio pp execsum report_url exec_url
View Replies !
Help With A Query Join?
I have a query whereby I look in two tables a teams table and results table to output some data for some football scores. SELECT th.team_name AS home_team, ta.team_name AS away_team FROM results r INNER JOIN teams th ON r.team_one_id = th.team_id INNER JOIN teams ta ON r.team_two_id = ta.team_id Now I want to add a third join there on another table named reports to see if the match_date in the results table matches a match_date in the reports table SELECT th.team_name AS home_team, ta.team_name AS away_team, re.match_date FROM results r , reports re INNER JOIN teams th ON r.team_one_id = th.team_id INNER JOIN teams ta ON r.team_two_id = ta.team_id INNER JOIN reports WHERE r.match_date = re.match_date Now this works great, but I want to output everything in the results table, and not just where results.match_date = reports.match_date as there may not be a report for every result and I want to still output every result nomatter if there is a report or not?
View Replies !
Using 'if' In A 'join Query' ?
I have two tables items and food_names in mysql db items ->structure **************************************** id food_items 1 veg 2 non-veg 3 veg & non-veg **************************************** food-names ->structure **************************************** id items_id foods 1 1 vbvcb 2 2 cvbvbv 3 3 gfdgdfgd 4 3 bbvcbvcb ******************************************* if i choose veg if(id=1) I want to write a db query for both veg and veg & non-veg else if i choose non- veg I want to write a db query for both non-veg and veg & non-veg else if i choose 'veg & non-veg' then i have to dispaly all.
View Replies !
JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of student numbers for each school from two tables School,Student. select sc.name, Sum(if(student.SNO is not null, 1, 0)) nmbr, from school sc left outer join student on sc.school_id = student.school_id group by sc.school_id this runs just 0.30 sec with Mysql3.23 but 30 Sec with Mysql 4.0.13.
View Replies !
Query Involving JOIN
I have two tables populated during the use of an application to log user events and application states. They are named "EventTable" and "StateTable" and the structures follow: EventTable: ID EventTimeStep EventID -- ------------- --------- 1 5 E1 2 22 E2 3 56 E3 StateTable ID StateTimeStep StateID -- ------------- ------- 1 1 S1 2 39 S2 I want to perform a query that reports the StateID of the application at the time that each event was logged to the EventTable. The desired output is: ID TimeStep EventID StateID -- -------- ------- ------- 1 5 E1 S1 2 22 E2 S1 3 56 E3 S2 I have tried to create a query with an INNER JOIN where the value for the StateID output field comes from the last row in the StateTable WHERE StateTable.TimeStep <= EventTable.TimeStep and where I use a GROUP_BY EventTable.ID to merge the following rows from the join: 3 56 E3 S1 3 56 E3 S2 However, the closest I can get is a query that gives me the wrong state when applying the GROUP BY clause 3 56 E3 S1 I also think that the queries I have written is slow and inefficient. Is there a better way to perform this query or is my database design fatally flawed?
View Replies !
Join Query By Date
I've these tables: - PERSON (id,name,age,....) - EXAM (id,date,note,exam_type,id_user) One person has 0 or more exams. I have to do this report: name, age, date, note, exam_type This looks easy, but I have to list for each person, his/her LAST EXAM GIVEN. I mean, only the last exam must be shown for each person, and well, if person has no exam yet... to show blank (if possible)
View Replies !
MySQL JOIN Query
I am a recreational MySQL user, and I tend to make some non-optimal queries. In my latest project I made some LEFT JOIN queries that were extremely slow. I made two changes that made the queries almost instant. 1) indexed the columns that the joins were working on 2) optimized the tables being joined (using phpMyAdmin > Operations) All I can say is WOW!!! I had no idea that the resulting difference in speed would be so great. After I did step (1) it added a tremendous amount of speed, and after I did step (2) the queries responed almost immediately. So if you are running slow with JOINS, give indexing and optimization a try.
View Replies !
Large Join Query
I am at a loss. Right now I have a PHP script that needs to run a huge join query. They query joins two tables of approximately 200,000 records. I'm running mysql 3.x so I can't use a view... at this point indexes don't seem to help. I am thinking the only way to do this is with script processing
View Replies !
Left Join Query
I have this query SELECT user.user_id, email, password, full_name, company_name, first_login_date, last_login_date, first_draft_date, last_draft_date, first_submit_date, last_submit_date, rowc.* FROM user LEFT JOIN rowc ON rowc.user_id = user.user_id WHERE user.user_type = 'USER' ORDER BY user.email Which returns all records in user table plus rowc records if there are matches in the user id. but user.user_id is null when there are no matching recordings in the rowc. Isn't left join meant to pull all the records in the user table? What am I doing that is wrong here?
View Replies !
Table Join Query
I'm trying to join multiple database tables and having a little trouble with it, hope someone can help: There are 3 tables with columns: id, user, date, time, action I'm trying to get user, date, time and action from all 3 tables where the user equals "anyuser" and sort the result by date DESC, time DESC.
View Replies !
Run Outer Join Query
I am trying to run following query in mysql. select a.name, a.record, b.data from A a, B b where a.name*=b.name and b.date='2006-07-28' How can I do this in mysql? I looked up LEFT JOIN but still can't figure it out.
View Replies !
Multiple Join Query
I have a database with quite a lot of tables all with foreign keys of each other. One of the tables has a one to many relationship (it being the one) with two tables, but it only uses one of them for any one record. Which one is used depends on a field in the main table. This essentially defines three different types of the same item, which has mostly the same fields, but two of them also require additional, multiple record (hence the one to many) information. So the table has a field 'type' which takes the value 1 (in which case no other table is referenced), 2, (in which case the first other table is used) and 3 (in which the other table is used). What I would ideally like to do is have one SQL query which returns information (from the main table) about any record which applies to the day (as it is part of a calendar system) specified in the query. The problem is that to decide if the record is relivent, the query must look through at most two of the three tables. I doubt that there is a way to specify in an SQL query to join to a table depending on the field of the first, and to join all three and perform the query could produce unexpected results if there were for any reason records in the third which referred to the first table. I could do this with several queries, but it would probably be more efficient to do it by one query. Would I be able to do an outer join on this?
View Replies !
Delayed JOIN Query
I have tried to join 3 tables via 2 different column entries. the query is very slow. I suppose it has to do with the design of the tables. I read that for JOIN it is important to define primary keys. My problem is, that in one of the tables some entries of the column (which is used for joining) are empty. When I try to make a primary key on the table, phpmyadmin says that there are double entries in the column... I have tryed to declare the entries as NULL, but it still says that there are double entries (by the way, the other entries are not double, I have checked). For a better understanding, here my command: ....
View Replies !
Help With Join Query Mysql 4.0
As I understand subqueries do not work in version 4.0 Here is what i am trying to do; select a, b from table1 where table1.b != (select distinct c from table2); Other words: table1.b and table2.c have values I want to match on I run select a, b from table1; subtract all out using select distinct c from table2; and leave me with a and b from table 1 that have no corresponding value of c in table 2
View Replies !
SQL Query JOIN And LIKE Clause
I need to run a query which uses a like query like the one below, no problem: SELECT * FROM footballers WHERE sname LIKE '%$criteria%' however, if I want to join two tables and still use the LIKE clause how would the syntax look?? My guess below was incorrect. any help would be appreciated. thank you WHERE sname LIKE '%$criteria%' AND (footballers.footballerID = PremTeams.footballerID)
View Replies !
Join Query Chaos
I cannot get this join to give me what I need. Here is my setup. I have 3 tables: courses, modules, sessions. courses has this structure:
View Replies !
Join Query Problem
student ------- id name 1 name1 2 name2 3 name3 Class ----- cls_id stu_id marks 1 1 100 1 2 300 5 1 400 5 3 500 I want to show only those student that are not class Ƈ' So I wrote select s.id,s.name,c.marks from student s,class c where s.id=c.stu_id and c.cls_id<>Ƈ' but it gives 1 name1 400 3 name3 500 but name1 is already in class Ƈ' . I desire the output only 5 3 500
View Replies !
Need Query To Join 2 Tables Through A 3rd
I have a table called client (primary key = clientID), another called clientFamily (pk = clientFamilyID) and a table that joins those two called client2clientFamily. The latter table only has two rows: clientID and clientFamilyID (no pk). On the client table, say the client has 3 children and 2 siblings. Those would go on the clientFamily table. The part I don't know how to do is how to assign those 3 kids and 2 sibs to that particular client. I know I do it via the client2clientFamily table but I don't know the query. I'm also not sure I'm thinking of this right. The fields on clientFamily start off like: clientFamilyID spouseFirstName spouseMiddleName spouseLastName spouseOccupation motherFirstName motherLastName motherOccupation fatherFirstName fatherLastName fatherOccupation I think that part's ok, but if I add: childFirstName childLastName childBirthday childSex and sibFirstName sibLastName sibOccupation how are, for example, a child's birthday and sex are going to be linked to the correct child? And a sib's occupation linked to the correct sib? Now I'm wondering if I need a separate table for clientChildren (and a joining table client2clientChildren) and another one for clientSibs (& client2clientSibs) instead of putting everything in clientFamily. Ugh.
View Replies !
Join Query Problem... Help!
I have two tables: STORES ------- id name ORDERS ------- id total cost store_id date_ordered I want to select ALL stores and their total sales for a date range. Here's my query: SELECT stores.id, stores.name, SUM(orders.total), SUM(orders.cost), COUNT(orders.id) FROM stores LEFT JOIN orders ON stores.id = orders.store_id WHERE orders.date_ordered > 'xxxx-xx-xx xx:xx:xx' GROUP BY stores.id This works, except that it only selects stores that have orders. If I leave out the WHERE clause, it then selects ALL stores, even without orders, and populates the sums with "null" That is what I want - if the stores don't have any orders, the results should simply return null or 0. However it seems that stores without orders are simply not being returned in the results at all.
View Replies !
Left Outer Join Query
I have noticed when I do a Left Outer Join in short form that many rows become missing as result of null values. i.e Left Outer Join (t1,t2,t3) ON (....) T3 being table with null values in It seems to be all to hard when I log a bug.
View Replies !
Join Query Result Difference Between 3.23.49 And 4.0.13
I have what seems to me a very common operation i'm performing. I need to find the balance on an invoice. i was not having any problems until the production server was upgraded to mysql v4.0.13-standard for pc-linux. There must be a better way to query for this information than the method i'm using, since the result with v4.0 is not what I expected, nor what I received with v3.23. I'm including sample data and queries with my results.....
View Replies !
Select With Join Query Question
I'm trying to write a select query that involves 2 tables. One table (Submissions) has a one to many relationship with the other table (Jobs). I'm trying to find all the records in Jobs that do NOT have a corresponding record in Submissions with a status of 1. The problem I'm having is that when there is more than one record in Submissions that match a record in Jobs and one Submissions record has a status of 1 and one record doesn't, my query matches the one with status !=3D 1 and returns the record for Jobs (even though it has a record in Submissions with a status of 1 also). I've tried a variety of queries including left outer joins and more simple join relationships. I'm using MySQL 3.23.47 on Windows. Here's an example query: select j.job_id from jobs j left outer join submissions s on (j.job_id = =3D s.job_id) where s.status_id !=3D 1 group by j.job_id Any help is greatly appreciated.
View Replies !
DELETE Query With Left Join?
I want to delete items from a table that are determined with a left join. using DELETE ( SELECT * FROM item_colors LEFT JOIN items ON item_colors.item_id = items.item_id WHERE items.group_id =3 ) FROM item_colors does not work. how would be the right syntax? The SELECT part returns the items I want to delete from item_colors correctly.
View Replies !
Union Query To Join 4 Tables
I am trying to make a union query to join 4 tables. I have reduced the tables to just 5 fields and made sure that the field types and names are the same. I keep getting an error message ODBC call failed. I have tried various combinations of the tables and find that I can use any 2 of them but as soon as a third is included in the statement the query fails. Is there a limit on the number of tables in a union query. The union query looks like this in its simplest form when I have made sure that the fields names, types and position match but I have also tried by specifically naming the fields in the same order for each table select * from T1 union select * from T2 union select * from T3 UNION select * from T4;
View Replies !
Table Join Query Error
The query’s work fine when I try them separate. $query = "SELECT id,loginName,car,yearmod,city FROM Member UNION SELECT imgName1 FROM Gallery"; $result = mysql_query($query) or die ("DOH!"); I have tried with and without the "(" ")" phpinfo says my MySQL Client API version is 4.0.21 so that shouldn’t be a problem, right?
View Replies !
Simple JOIN Query Issues
What I want to do is have multiple tables interact with each other. payments TABLE id | paidfrom | paidto | paidmethod 1 | 2 | 1 | 2 2 | 3 | 1 | 3 users TABLE userid | username 1 | Jake 2 | Todd 3 | Spencer methods TABLE methodid | methodstring 1 | Cash 2 | Check 3 | Credit Card
View Replies !
Outer Join Count Query
I want to create an outer join along with usage of group by & count... So, I want the rows with count=0 to be part of the result... Select a.area, count(l.id) as no_listing from area a left join listing l on a.area=l.area group by a.area order by a.area; Note that an area can have multiple listings... Now, this query returns me only the areas with some positive count of listings, in spite of using a left join...
View Replies !
Distinct And Join Query In MySQL
I have a query question, it may be really easy but somehow I cannot get it What I have is a table with columns, two of which I will name Item1 and Item2, I would like to get a distict set of pairs Item1-Item2 from this table, more specifically if table looks like this ....
View Replies !
Table Join Invalid Query
I have this table join below and I am getting an invalid query. How can I put the 15th line ($sql .= " group.id = master.id "; and the 17th line ($sql .= " where ".$currentrow_sql ; together. Is it even possible to do that?
View Replies !
Problem In Left Join Query?
I have a pretty complex query and it's practically crashing my system due to generating more data than I am looking for. I basically have 2 tables. They should contain very similar data as they contain information about the same types of things. I was asked to join the 2 tables, getting any information from table 1 that does not exist in table 2, but keep everything of table 2. Anyway I tried this query and it just hangs forever. One problem could be because I have no ID field to match up between the tables. I'm trying to join them based on an address, city, and zip. Select * INTO OUTFILE 'output.tab' FIELDS TERMINATED BY ' ' LINES TERMINATED BY ' ' FROM table2 LEFT JOIN table1 ON table2.address = table1.address and table2.city = table1.city and LEFT(table2.zip,5) = table1.zip; My resulting file should be 13000 records in length, with 3000 records that have additional information based on table1 data.
View Replies !
Join Query - I'm Losing My Mind
I'm not sure if I've totally lost my mind on this, but I can't seem to find the solution. I have two tables: projects --------- project_id project_name project_type project_status -------------- profile_id project_id status comment So lets say I have the following data projects -------- 1, Project 1, Internal 2, Project 2, Internal 3, Project 3, External project_status -------------- 1, 1, submitted, comment 1, 2, final, comment2 2, 2, initial, comment3 I want to select all rows from projects joined with the data from project_status using project_id as the key, like this: project_query -------------- project_id project_name project_type status comment BUT I only want to join on rows in project_status where profile_id = 1, i.e. I get this data from the query: 1, Project 1, Internal, submitted, comment 2, Project 2, Internal, final, comment2 3, Project 3, External, , , Basically I don't know how I can join the project table with the project_status table which it only displays results with profile_id = 1.
View Replies !
Having Troubles With A LEFT JOIN Query
I'm currently building a private members area for a website where designs can be submitted and rated before they are released to the public under one unified "pack". What I am trying to do is to create a query that lists the latest submissions along with their ratings (if they have been rated) and if the user has rated that submission or not. Here is my query: SELECT s.id as id, s.title as title, s.users as creators, ROUND(AVG(r.rating), 1) as rating, r2.id as userhasrated FROM submissions AS s LEFT JOIN sub_ratings AS r ON r.subid = s.id LEFT JOIN sub_ratings AS r2 ON r2.userid = ".$user->getUserId()." AND r2.subid = s.id LEFT JOIN packs AS p ON p.id = s.pack AND p.released = 0 WHERE s.state > -1 GROUP BY r.subid ORDER BY s.id DESC LIMIT 10 The problem is that it only returns two rows: one with some submission that hasn't been rated and one with some submission that has been rated (but only rated by the user indicated by $user->getUserId()). How can I fix my code so that it returns as many rows as I desire?
View Replies !
Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below: It involves two tables, products and vendors. Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description: CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `vendor_id` smallint(6) NOT NULL default Ɔ', `product_code` varchar(255) NOT NULL default '', `internal_name` varchar(255) NOT NULL default '', `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `product_code` (`product_code`), KEY `vendor_id` (`vendor_id`) ) ENGINE=MyISAM; Vendors are much more straightforward: CREATE TABLE `vendors` ( `id` smallint(6) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; The following query executes in no MORE than 0.01 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name FROM products as p ORDER BY p.product_code ASC LIMIT 0, 30; And has the following attributes: +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name , v.name as vendor_name FROM products as p LEFT JOIN vendors as v ON v.id=p.vendor_id ORDER BY p.product_code ASC LIMIT 0, 30; It has the following characteristics: +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me. Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so: mysql> explain SELECT DISTINCT p.id -> , p.product_code -> , unix_timestamp(p.lastmodified) as lastmodified -> , p.internal_name -> , v.name as vendor_name -> FROM products as p -> LEFT JOIN vendors as v ON v.id=p.vendor_id -> LIMIT 0,30; +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,
View Replies !
|
TRACKER
