Always CC the list. That way everyone gets a chance to help. Problems like I had yesterday with my mail router won't slow down a response as everyone will have seen it. Understand?
This latest post is rather disjointed... You show us a sample SELECT statement but tell us that an EXPLAIN SELECT STRAIGHT_JOIN said something. If you want us to evaluate a statement, include the EXPLAIN for *that* statement not some other. When you say "the main sql goes fast" does that mean that you get your results quickly but are still waiting for a count of how many records you processed? If you are processing these queries through some kind of GUI tool, try turning off the option that inserts the SQL_CALC_FOUND_ROWS into your statements. That is a tool-specific setting and I don't use ANY GUIs so I can't help you with that. Have you had a chance to evaluate my previous suggestion about breaking your query into smaller passes (using a temporary table to store your intermediate results) ? Shawn Green Database Administrator Unimin Corporation - Spruce Pine ----- Forwarded by Shawn Green/Unimin on 09/24/2004 12:09 PM ----- "DeRyl" <[EMAIL PROTECTED]> 09/24/2004 06:28 AM To <[EMAIL PROTECTED]> cc Subject Re: great problem with questions it works much worse like before I've changed bigint(14) to bigint(12) next I reindexed all I've changed key_bueer_size to 128M then I restarted mySQL when I try to execute that: select logo, klient.klientid, klientnazwa, struktura, kodpocztowy, miejscowosc, aparatnumer, ulicaskrot, ulicanazwa, posesja, wojewodztwo, powiat, klientbranza, email, www, wizytowka from klientmiejscowosc, klientmiejscowosci, klient where klientmiejscowosc.klientmiejscowosc like'war%' AND klientmiejscowosc.klientmiejscowoscid = klientmiejscowosci.klientmiejscowoscid AND klientmiejscowosci.klientid = klient.klientid explain select straight_join ... show that the conditions are written in proper order... the main sql goes fast but showprocesslist shows: ID Uzytkownik Host Baza danych Polecenie Czas Status zapytanie SQL Zabicie 5 root localhost ikt Sleep 769 --- --- Zabicie 10 root localhost ikt Sleep 678 --- --- Zabicie 11 root localhost ikt Query 898 Sending data SELECT SQL_CALC_FOUND_ROWS logo, klient.klientid, klientnazwa, struktura, kodpocztowy, miejscowosc, Zabicie 34 root localhost ikt Sleep 101 --- --- Zabicie 36 root localhost ikt Sleep 40 --- --- Zabicie 40 root localhost ikt Sleep 161 --- --- Zabicie 41 root localhost ikt Sleep 192 --- --- Zabicie 42 root localhost ikt Sleep 70 --- --- Zabicie 49 root localhost ikt Query 592 Sending data SELECT SQL_CALC_FOUND_ROWS logo, klient.klientid, klientnazwa, struktura, kodpocztowy, miejscowosc, Zabicie 50 root localhost ikt Sleep 10 --- --- Zabicie 51 root localhost ikt Sleep 131 --- --- Zabicie 52 root localhost ikt Sleep 222 --- --- Zabicie 60 root localhost mysql Query 0 --- SHOW PROCESSLIST and the sql is not ended... how to accelerate the question? why SQL_CALC_FOUND_ROWS work terribly long and delay showing result or prevent them from displaying? with regards Darek ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "DeRyl" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, September 23, 2004 3:34 PM Subject: Re: great problem with questions The fact that you are joining 10 tables together in one query may be a major portion of your performance problem. Even if the *average* size of each table is only 10 rows, the MySQL engine will have to pour over 10x10x10x10x10x10x10x10x10x10 = 10 to the 10th power = 1e10 = 10,000,000,000 (10 billion) combinations of data rows before it gets to even think about some of your WHERE conditions. I have a few suggestions: FIRST - Rewrite your query to use the explicit INNER JOIN form of table joins and NOT the comma-join form you are currently using. The explicit form is much MUCH easier to double check that you have properly JOINed each table to each other table. Without properly defined join conditions you will create a Cartesian product between two or more of your tables. Cartesian products are rarely good things for query performance. SECOND - Check your indexes. Make sure that you are joining tables on columns that appear first in at least one index. Carefully consider creating a multiple column index or two (especially if this is a common query and/or you use those conditions often). THIRD - Consider evaluating this query in stages. Based on your re-write to eliminate the comma-joins, I can help you with that, too. FOURTH - Consider using table aliases. It may make your SELECT and WHERE clauses easier to read. I look forward to seeing the rewrite. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
