MySQL :: Table in query adds significant delay

lackluster · 07-08-2004, 02:49 PM

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

deiussum · 07-08-2004, 04:11 PM

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

deiussum · 07-08-2004, 04:24 PM

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

lackluster · 07-08-2004, 09:15 PM

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?

deiussum · 07-09-2004, 08:29 AM

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.

vasudevadas · 07-09-2004, 02:21 PM

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?

lackluster · 07-09-2004, 07:44 PM

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.

vasudevadas · 07-10-2004, 03:48 AM

Quote:

Originally posted by lackluster
hope I can return the favor sometime.

Know anything about programming with ALSA?