LinuxQuestions.org
Support LQ: Use code LQ3 and save $3 on Domain Registration
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 07-08-2004, 03:49 PM   #1
lackluster
Member
 
Registered: Apr 2002
Location: D.C - USA
Distribution: slackware-current
Posts: 488

Rep: Reputation: 30
MySQL :: Table in query adds significant delay


Oddity, bug or idiocy? I have the following query:

Code:
SELECT A.prID, A.name, A.min_qty, A.max_qty, A.total, B.name AS brand, C.name AS package, D.name AS 'type', F.name AS catname, G.name AS subcatname, H.name AS subcat2name, J.fk_cID, J.fk_scID, J.fk_scID2 FROM products A, brand B, package C, a2z.type D, categories F, keywords I, many_keywords J LEFT OUTER JOIN sub_categories G ON G.scID = J.fk_scID LEFT OUTER JOIN sub_categories2 H ON H.scID2 = J.fk_scID2 WHERE D.tID = A.fk_tID AND C.pID = A.fk_pID AND A.fk_cID = J.fk_cID AND A.fk_scID = J.fk_scID AND A.fk_scID2 = J.fk_scID2 AND B.bID = A.fk_bID AND J.fk_kID = I.kID AND I.name LIKE 'l%' GROUP BY A.prID ORDER BY catname ASC, subcatname ASC, subcat2name ASC, brand ASC , name ASC
Which does what I expect and returns after a short delay. When I try to add the prices table to the query, as in the code below, it hangs for a *long* time and comes back with the ran out of space on the device error (SQL Error : 1030 Got error 28 from table handler).

Code:
SELECT A.prID, A.name, A.min_qty, A.max_qty, A.total, B.name AS brand, C.name AS package, D.name AS 'type', F.name AS catname, G.name AS subcatname, H.name AS subcat2name, J.fk_cID, J.fk_scID, J.fk_scID2 FROM products A, brand B, package C, a2z.type D, categories F, keywords I, many_keywords J, prices K LEFT OUTER JOIN sub_categories G ON G.scID = J.fk_scID LEFT OUTER JOIN sub_categories2 H ON H.scID2 = J.fk_scID2 WHERE D.tID = A.fk_tID AND C.pID = A.fk_pID AND A.fk_cID = J.fk_cID AND A.fk_scID = J.fk_scID AND A.fk_scID2 = J.fk_scID2 AND B.bID = A.fk_bID AND J.fk_kID = I.kID AND I.name LIKE 'l%' GROUP BY A.prID ORDER BY catname ASC, subcatname ASC, subcat2name ASC, brand ASC , name ASC
Any thoughts? Does MySQL suck? Has this happend to anyone else?

Thanks,
lackluster
 
Old 07-08-2004, 05:11 PM   #2
deiussum
Member
 
Registered: Aug 2003
Location: Santa Clara, CA
Distribution: Slackware
Posts: 895

Rep: Reputation: 32
The SQL would be a bit more readable if you could use some line-breaks in there...

What I'm noticing is that you are adding the new table to your list of initial tables right after FROM, but you don't appear to be adding a restriction on it.

Generally, using tables like this is a bad idea anyway, unless you really need a cross-product. INNER JOINs should be used where possible. For instance, if you take the following 2 queries:

Code:
SELECT *
FROM Table1 A, Table2 B
WHERE A.id = B.id

SELECT * 
FROM Table1 A
INNER JOIN Table2 B
ON A.id = B.id
The first one first creates a cross-product of every possibility of columns in Table1 and Table2, and THEN restrict them to only where Table1.id = Table2.id. The second query joins them only where they match, without having to do the entire cross-product.

(Note: a good database engine probably optimizes these to actually treats these the same way underneath, but I still tend to try and use INNER JOINs as opposed to the cross-product of tables...)

Now in your example, you are doing the equivalent of this:

Code:
SELECT *
FROM Table1 A, Table2 B, Table3 C
WHERE A.id = B.id
Note that there is nothing there to restrict the rows of C to the other 2 tables, so you are going to have a cross-product of all rows where A.id = B.id, crossed with all rows of C.

In case I'm not explaining the cross-product thing very well, take a simple example:

Code:
Table1
id
1
2
3
4
5

Table2
id
1
2
4

SELECT *
FROM Table1, Table2

Results:
Table1.id         Table2.id
1                      1
1                      2
1                      3
1                      4
2                      1
2                      2
2                      3
2                      4
3                      1
3                      2
3                      3
3                      4
4                      1
4                      2
4                      3
4                      4
5                      1
5                      2
5                      3
5                      4

Last edited by deiussum; 07-08-2004 at 05:14 PM.
 
Old 07-08-2004, 05:24 PM   #3
deiussum
Member
 
Registered: Aug 2003
Location: Santa Clara, CA
Distribution: Slackware
Posts: 895

Rep: Reputation: 32
Here's a feeble attempt on my part to try and optimize your query.

Without knowing much about your schema, I couldn't determine what
the restriction for your new table was, or the restriction on the categories
table, since I didn't see any restrictions on that in the query.

That is almost definitely why you are seeing a slowdown. Doing an
unrestricted cross-product on basically 3 distinct sets of data is going to
generate quite a number of results, depending on the size of those tables.

Code:
SELECT A.prID, A.name, A.min_qty, A.max_qty, A.total, 
	B.name AS brand, C.name AS package, 
	D.name AS 'type', F.name AS catname, 
	G.name AS subcatname, H.name AS subcat2name, J.fk_cID, 
	J.fk_scID, J.fk_scID2 
FROM products A
INNER JOIN brand B
ON B.bID = A.fk_bID
INNER JOIN package C
ON C.pID = A.fk_pID
INNER JOIN a2z.type D
ON D.tID = A.fk_tID
INNER JOIN categories F
ON not sure, didn't see a restriction there...
INNER JOIN many_keywords J 
ON 	A.fk_cID = J.fk_cID AND A.fk_scID = J.fk_scID AND A.fk_scID2 = J.fk_scID2
INNER JOIN keywords I
ON 	J.fk_kID = I.kID
INNER JOIN prices K
ON dunno, you had no restriction that I saw...
LEFT OUTER JOIN sub_categories G ON G.scID = J.fk_scID 
LEFT OUTER JOIN sub_categories2 H ON H.scID2 = J.fk_scID2 
WHERE I.name LIKE 'l%' 
GROUP BY A.prID 
ORDER BY catname ASC, subcatname ASC, subcat2name ASC, brand ASC , name ASC

Last edited by deiussum; 07-08-2004 at 05:27 PM.
 
Old 07-08-2004, 10:15 PM   #4
lackluster
Member
 
Registered: Apr 2002
Location: D.C - USA
Distribution: slackware-current
Posts: 488

Original Poster
Rep: Reputation: 30
Quote:
Originally posted by deiussum
The SQL would be a bit more readable if you could use some line-breaks in there...
Sorry, I haven't been here in a while. The last time I was here, [ code ] tags boxed up neatly .

Thanks much for your in-depth explanation. I wasn't aware that a database could potentally import everything first before restricting on an implicit inner join. I will keep that in mind. The reason the prices table isn't linked into anything was just for examples sake. I was trying to find the bottleneck in my query and removed the restrictions. I see now that simply mentioning it imports it all, unrestricted. I'll try some other options to try to find the code clot. Know of any good SQL debugger for linux?
 
Old 07-09-2004, 09:29 AM   #5
deiussum
Member
 
Registered: Aug 2003
Location: Santa Clara, CA
Distribution: Slackware
Posts: 895

Rep: Reputation: 32
As I stated in my note, most DBMSes are smart enough to treat both types of queries the same, but in cases where there is not a clear restriction to join the tables on, it obviously can't make that optimization.

I tend to think of using INNER JOINs as telling the DBMS up front how it should join the tables.

Just as a test, I took a couple of queries in MS SQL Query Analyzer, and had it show an estimated execution plan. For SQL Server 2000, the plan looked exactly the same for a multi-table select with a restriction, and an inner join on those same two tables.

I don't know of any similar execution plan tools for Linux offhand, but I'm sure there must be some out there. Just gotta look for them.
 
Old 07-09-2004, 03:21 PM   #6
vasudevadas
Member
 
Registered: Jul 2003
Location: Bedford, UK
Distribution: Slackware 11.0, LFS 6.1
Posts: 519

Rep: Reputation: 30
Idiocy, I think!

The reason why adding the PRICES table into the table list adds such a performance delay is because you don't include any kind of join to the new table in your WHERE clauses.

This causes a cartesian join. What this means is that every single row from your original result set (before the extra table was added) gets joined, in turn, to every single row in PRICES. If PRICES has two rows, this means you get double the number of rows as before. If PRICES has two hundred thousand rows then....you get the picture.

I don't know your database, but it seems likely to me that PRICES will have a lot of rows in it. This is almost certainly why you're running out of memory and the query is taking so long. I'm also almost 100% certain that you don't mean to be doing this. Shouldn't you be explicitly joining PRICES to something? To PRODUCTS.prID for example? It's not to do with inner joins versus outer joins, it's just that you need some kind of join in there, be it inner- or outer!

By the way, I notice that you are also not including any columns from PRICES in your select list. That would make adding the table fairly pointless. Are you halfway through something here that you haven't finished yet?

Last edited by vasudevadas; 07-09-2004 at 03:32 PM.
 
Old 07-09-2004, 08:44 PM   #7
lackluster
Member
 
Registered: Apr 2002
Location: D.C - USA
Distribution: slackware-current
Posts: 488

Original Poster
Rep: Reputation: 30
vasudevadas,

Idiocy indeed! Yes, I realized earlier that simply by mentioning the table it brings in its rows. A bit counter-intutive, but now I know. I was just throwing it in without the links for examples sake. I do have some joins in there, but no columns from it; it is only for filtering.

deiussum,

Thanks again for your advice. Once I broke it down into explicit joins I saw the bigger problem I was having: I was INNER JOINing the products table with the many_keywords table, as in A.fk_cID = J.fk_cID AND A.fk_scID = J.fk_scID AND A.fk_scID2 = J.fk_scID2 but doing outer joins on category tables later as in LEFT OUTER JOIN sub_categories G ON G.scID = J.fk_scID LEFT OUTER JOIN sub_categories2 H ON H.scID2 = J.fk_scID2. Silly me ....

Thanks both for the help - hope I can return the favor sometime.
 
Old 07-10-2004, 04:48 AM   #8
vasudevadas
Member
 
Registered: Jul 2003
Location: Bedford, UK
Distribution: Slackware 11.0, LFS 6.1
Posts: 519

Rep: Reputation: 30
Quote:
Originally posted by lackluster
hope I can return the favor sometime.
Know anything about programming with ALSA?
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Routing table query mickboda Linux - Networking 3 11-05-2005 12:11 PM
[Perl] multi table query dsheller Programming 4 11-03-2005 06:32 AM
MySQL non-realtime table-by-table mirroring Passive Linux - Software 1 01-20-2004 01:11 PM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 12:30 PM
Mysql Error: Lost Connection to Mysql during query ramnath Programming 5 11-18-2003 01:27 PM


All times are GMT -5. The time now is 11:29 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration