"Lindh Kenneth" <[EMAIL PROTECTED]> wrote on 08/23/2005 07:52:23 AM:
> When I run a query I get this error-message. It doesn't say which > table, but I checked all my tables and none is near max_table_size. > We have MyISAM-tables and we're using version: 5.0.4 > Any ideas? > > BR /Kenneth > > This query works: > > SELECT > pr.sectionid, t2.text heading, se.fungroupno, pd.pdstring > FROM > wi_description t1 > LEFT OUTER JOIN lisa_part pa ON pa.textid = t1.textid > LEFT OUTER JOIN lisa_partrow pr ON pa.partid = pr.partno > LEFT OUTER JOIN lisa_pisection se ON pr.sectionid = se.sectionid > LEFT JOIN wi_description t2 ON t2.textid=se.heading1 AND t2. > language = 'eng' AND t2.country = 'GB' > LEFT JOIN lisa_pd pd ON pd.sectionid=se.sectionid , lisa_bomtomodel > bm > WHERE > t1.language = 'eng' AND t1.country = 'GB' > and > t1.text LIKE '%flywheel%' > and > se.fungroupno LIKE '21%' AND ((pd.pdstring is null) OR pd. > pdstring LIKE '%FH12%') > AND > bm.bom=se.sectionid AND bm.truckmodel='FH12' > GROUP BY pr.sectionid, heading, se.fungroupno, pd.pdstring > ORDER BY heading > > This one doesn't... > > SELECT > distinct(pr.sectionid), t1.text,t2.text,t3.text,se.subheadtext, > t5.text, fi.vssno, fi.vsscompany, > fi.vssgroupno, infotext.text infotypetext > FROM > wi_sifilter fi > LEFT OUTER JOIN wi_siinfotype info ON fi.infotypeid=info.elementname > LEFT OUTER JOIN wi_text infotext ON infotext.textid=info.textid AND > infotext.language='eng' AND infotext.country='GB', wi_siinfoelement > ie, wi_pisection se > LEFT OUTER JOIN wi_text t1 ON t1.textid = se.heading1 AND t1. > language = 'eng' AND t1.country = 'GB' > LEFT OUTER JOIN wi_text t2 ON t2.textid = se.heading2 AND t2. > language = 'eng' AND t2.country = 'GB' > LEFT OUTER JOIN wi_text t3 ON t3.textid = se.heading3 AND t3. > language = 'eng' AND t3.country = 'GB' , wi_partrow pr > LEFT OUTER JOIN wi_pifootnote f ON pr.sectionid = f.sectionid AND > f.colrowno = 'S' > LEFT OUTER JOIN wi_text t5 ON f.textid=t5.textid AND t5.language = > 'eng' AND t5.country = 'GB' , wi_text t4, wi_part pa > WHERE > pr.sectionid = se.sectionid AND t4.textid = pa.textid AND t4. > language = 'eng' AND t4.country = 'GB' > AND > pr.partno = pa.partid > AND > t4.text LIKE '%flywheel%' AND se.fungroupno LIKE '21%' AND ie. > linktype = 'partinforef' > AND > CONCAT(SUBSTRING(se.sectionid,1,14),'00001') = ie.toid > AND > ie.fromid = fi.infoelementid AND SUBSTRING(pr.sectionid,15,19) > NOT LIKE '00001' > ORDER BY fi.vssgroupno,fi.vsscompany,fi.vssno, 2,3,4,5,6 LIMIT 0, 1201 > To understand this error, you need to understand a bit about how MySQL processes JOINs. Without getting too technical, what happens when you run a query that JOINS two or more tables is that MySQL builds a hidden table (in memory) that contains the data or references to the data in the tables you are joining together. In order to do that it generates the equivalent of a Cartesian product between any single pair of tables then processes the ON clause in order to "throw out" all of the non-matching combinations. Then it takes that result and pairs it with another table in the JOIN list until you have paired all of the tables in the statement and applied all of the ON clause conditions to the results. What you have left is the results of all of those JOINs sitting around in memory (most probably spilling over into cached memory on disk as well) along with several, if not all, of the indexes from the original tables. At this point, MySQL is ready to begin processing the other clauses of your full statement such as the WHERE, GROUP BY, and HAVING clauses. That is oversimplified but basically true. What you do in your second query by JOINing seven tables together is construct an "internal" table that is in some way too large for MySQL to handle internally. The solution may be as simple as raising the value of your "max_join_size" server variable. My recommended solution is to break your larger second query into two or more smaller queries. Use temporary tables to store your intermediate results. I recommend this for two reasons: A) computing a Cartesian product, the combination of each row of one table with every row of the other, become exponentially more expensive as you join more tables. If there are M rows in table1 and N rows in table2, the Cartesian product of the JOIN produces an intermediate stage where the engine has to process M*N rows. For a third table with P rows in it that Cartesian product has a theoretical maximum of M*N*P rows to examine. It just gets worse the more tables you JOIN in any one statement. To make this point more obvious, assume that all three tables have 1000 rows but you are only going to use 200 rows from table1, 100 rows from table2, and all 1000 rows from table3. Your Cartesian product of the three table join will only be 200*100*1000=20000000 (2.0e06) and not 1000*1000*1000=1000000000(1.0e09). I would rather work with two million rows over a billion any day. B) the query optimizer will try to analyze (only as limited by the optimizer_prune_level and optimizer_search_depth) every possible sequence of joining the tables in the attempt to find the most efficient method. This search also becomes exponentially more expensive the more tables you attempt to join in any single statement.( http://dev.mysql.com/doc/mysql/en/controlling-optimizer.html) Here is your original query (reformatted to reduce wrapping): SELECT distinct(pr.sectionid) , t1.text ,t2.text ,t3.text ,se.subheadtext ,t5.text ,fi.vssno ,fi.vsscompany ,fi.vssgroupno , infotext.text infotypetext FROM wi_sifilter fi LEFT OUTER JOIN wi_siinfotype info ON fi.infotypeid=info.elementname LEFT OUTER JOIN wi_text infotext ON infotext.textid=info.textid AND infotext.language='eng' AND infotext.country='GB' , wi_siinfoelement ie , wi_pisection se LEFT OUTER JOIN wi_text t1 ON t1.textid = se.heading1 AND t1.language = 'eng' AND t1.country = 'GB' LEFT OUTER JOIN wi_text t2 ON t2.textid = se.heading2 AND t2.language = 'eng' AND t2.country = 'GB' LEFT OUTER JOIN wi_text t3 ON t3.textid = se.heading3 AND t3.language = 'eng' AND t3.country = 'GB' , wi_partrow pr LEFT OUTER JOIN wi_pifootnote f ON pr.sectionid = f.sectionid AND f.colrowno = 'S' LEFT OUTER JOIN wi_text t5 ON f.textid=t5.textid AND t5.language = 'eng' AND t5.country = 'GB' , wi_text t4 , wi_part pa WHERE pr.sectionid = se.sectionid AND t4.textid = pa.textid AND t4.language = 'eng' AND t4.country = 'GB' AND pr.partno = pa.partid AND t4.text LIKE '%flywheel%' AND se.fungroupno LIKE '21%' AND ie.linktype = 'partinforef' AND CONCAT(SUBSTRING(se.sectionid,1,14),'00001') = ie.toid AND ie.fromid = fi.infoelementid AND SUBSTRING(pr.sectionid,15,19) NOT LIKE '00001' ORDER BY fi.vssgroupno,fi.vsscompany,fi.vssno, 2,3,4,5,6 LIMIT 0, 1201 First, I would have organized my query so that the ON conditions for your INNER JOINS are explicitly declared. I would have also made all of my INNER JOINs first. The DISTINCT operator works on rows, not columns ( http://dev.mysql.com/doc/mysql/en/select.html) so you do not need the parentheses around the first term in your SELECT clause. With all of that in mind I might have written this first version of the query Like this: SELECT DISTINCT pr.sectionid , t1.text ,t2.text ,t3.text ,se.subheadtext ,t5.text ,fi.vssno ,fi.vsscompany ,fi.vssgroupno , infotext.text infotypetext FROM wi_sifilter fi INNER JOIN wi_siinfoelement ie ON ie.fromid = fi.infoelementid AND ie.linktype = 'partinforef' INNER JOIN wi_pisection se ON CONCAT(SUBSTRING(se.sectionid,1,14),'00001') = ie.toid AND se.fungroupno LIKE '21%' INNER JOIN wi_partrow pr ON pr.sectionid = se.sectionid AND SUBSTRING(pr.sectionid,15,19) NOT LIKE '00001' INNER JOIN wi_part pa ON pr.partno = pa.partid INNER JOIN wi_text t4 ON t4.textid = pa.textid AND t4.language = 'eng' AND t4.country = 'GB' AND t4.text LIKE '%flywheel%' LEFT JOIN wi_siinfotype info ON fi.infotypeid=info.elementname LEFT JOIN wi_text infotext ON infotext.textid=info.textid AND infotext.language='eng' AND infotext.country='GB' LEFT JOIN wi_text t1 ON t1.textid = se.heading1 AND t1.language = 'eng' AND t1.country = 'GB' LEFT JOIN wi_text t2 ON t2.textid = se.heading2 AND t2.language = 'eng' AND t2.country = 'GB' LEFT JOIN wi_text t3 ON t3.textid = se.heading3 AND t3.language = 'eng' AND t3.country = 'GB' LEFT JOIN wi_pifootnote f ON pr.sectionid = f.sectionid AND f.colrowno = 'S' LEFT JOIN wi_text t5 ON f.textid=t5.textid AND t5.language = 'eng' AND t5.country = 'GB' ORDER BY fi.vssgroupno,fi.vsscompany,fi.vssno, 2,3,4,5,6 LIMIT 0, 1201 Some will argue that putting the extra AND conditions into the ON clauses is "bad form". I tend to agree and don't normally do it. However in this case, I think it makes the query more readable and may actually help performance by not delaying the evaluation of those terms until the WHERE clause. Test this version and see if it's efficient enough to work, yet. In this form the query still has **WAY** too many JOINS in one statement for my comfort level. I would break it down into two statements. Part A is everything collected from the tables that are INNER JOINed that play a role in the final result. The second part is all of the "optional" information that is added to the query by the LEFT JOINS. /* part A - collect the 'required' information */ CREATE TEMPORARY TABLE tmpPartsInfo SELECT DISTINCT pr.sectionid , se.heading1 (t1.text) , se.heading2 (t2.text) , se.heading3 (t3.text) , se.subheadtext ,fi.vssno ,fi.vsscompany ,fi.vssgroupno ,fi.infotypeid (infotext.text infotypetext) FROM wi_sifilter fi INNER JOIN wi_siinfoelement ie ON ie.fromid = fi.infoelementid AND ie.linktype = 'partinforef' INNER JOIN wi_pisection se ON CONCAT(SUBSTRING(se.sectionid,1,14),'00001') = ie.toid AND se.fungroupno LIKE '21%' INNER JOIN wi_partrow pr ON pr.sectionid = se.sectionid AND SUBSTRING(pr.sectionid,15,19) NOT LIKE '00001' INNER JOIN wi_part pa ON pr.partno = pa.partid INNER JOIN wi_text t4 ON t4.textid = pa.textid AND t4.language = 'eng' AND t4.country = 'GB' AND t4.text LIKE '%flywheel%'; /* part B - combine all optional data to the data already collected in the temp table to produce the desired query output */ SELECT DISTINCT tpi.sectionid , t1.text , t2.text , t3.text , tpi.subheadtext , t5.text , tpi.vssno , tpi.vsscompany , tpi.vssgroupno , infotext.text infotypetext FROM tmpPartsInfo tpi LEFT JOIN wi_siinfotype info ON tip.infotypeid=info.elementname LEFT JOIN wi_text infotext ON infotext.textid=info.textid AND infotext.language='eng' AND infotext.country='GB' LEFT JOIN wi_text t1 ON t1.textid = tpi.heading1 AND t1.language = 'eng' AND t1.country = 'GB' LEFT JOIN wi_text t2 ON t2.textid = tpi.heading2 AND t2.language = 'eng' AND t2.country = 'GB' LEFT JOIN wi_text t3 ON t3.textid = tpi.heading3 AND t3.language = 'eng' AND t3.country = 'GB' LEFT JOIN wi_pifootnote f ON tpi.sectionid = f.sectionid AND f.colrowno = 'S' LEFT JOIN wi_text t5 ON f.textid=t5.textid AND t5.language = 'eng' AND t5.country = 'GB' ORDER BY 7,8,9,2,3,4,5,6 LIMIT 0, 1201; /* end */ Assuming I haven't made any serious cut-and-paste errors. That should work (and work much faster). Let me know if you run into any problems. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
