Run an explain on the query and post it here. Make sure all the join columns are appropriately indexed. Also, notice the large difference in rows examined vs sent (My guess is this is due to the left joins.) Left joins are horribly overused and I know you said that you cannot change the query but try to ask the developers if they really need more than an inner join.
Quoting iv <[EMAIL PROTECTED]>: > Hi > I have a big problem with my mysq server. ater installing it in a new > machine it works extremally slowly. > My mysql server serves a quite big web site with numerous queries. > Unfortunately, I can't alter the queries. > And one more thing. Everything run much better on a previous machine. > What I have now: > AMD Sempron 3100+, Asus K8N-E, Western Digital WDC-WD800JD SATA drive; > MemTotal: 1553596 kB > I'm running now Debian GNU/Linux 3.0, kernel 2.6.10, mysql Ver 14.7 > Distrib 4.1.9, for pc-linux-gnu; > My.cfg: > > [client] > port = 3306 > socket = /var/run/mysqld/mysqld.sock > [mysqld_safe] > socket = /var/run/mysqld/mysqld.sock > nice = 0 > [mysqld] > user = mysql > pid-file = /var/run/mysqld/mysqld.pid > socket = /var/run/mysqld/mysqld.sock > port = 3306 > log = /var/log/mysql/mysql.log > basedir = /usr > datadir = /var/lib/mysql > tmpdir = /tmp > language = /usr/share/mysql/polish > skip-external-locking > default-character-set = latin2 > old-passwords = 1 > bind-address = 127.0.0.1 > key_buffer = 16M > max_allowed_packet = 16M > thread_stack = 128K > query_cache_limit = 1258292 > query_cache_size = 26214400 > query_cache_type = 1 > query_cache_min_res_unit = 8000 > log-slow-queries = /var/log/mysql/mysql-slow.log > [mysqldump] > quick > quote-names > max_allowed_packet = 1M > [mysql] > [isamchk] > key_buffer = 16M > ** > the query from /var/log/mysql/mysql-slow.log: > # Time: 050309 17:02:37 > # [EMAIL PROTECTED]: wwwuser[wwwuser] @ localhost [] > # Query_time: 254 Lock_time: 0 Rows_sent: 185 Rows_examined: 1956616 > SET timestamp=1110384157; > SELECT pojazdy.*, marki.marka, IF(marki.marka is NULL,'???',marki.marka) > as sort , zdjecia.nazwa as zdjecie_na > zwa, zdjecia.katalog as zdjecie_katalog, zdjecia.typ as zdjecie_typ FROM > pojazdy LEFT JOIN marki ON ( pojazdy. > marka_id=marki.id ) LEFT JOIN zdjecia ON ( pojazdy.id = > zdjecia.pojazd_id ) ,firmy WHERE 1 and pojazdy.open_d > ate < NOW() and pojazdy.validate > NOW() and pojazdy.block_date=0 and > pojazdy.kontakt_id=firmy.id and firmy.n > azwa='carstein' and pojazdy.typ_id='1' GROUP BY pojazdy.id order by > sort, model, cena; > # Time: 050309 17:03:10 > > additionally, this query returns 2216 rows with pictures. the process > longs about 2 minutes :( > and additionally, I'm a newbie with mysql. > Any ideas will be highly appreciated. > Thanks in advance, > /iv > *** > * > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > James Nobis Web Developer Academic Superstore 223 W. Anderson Ln. Suite A110, Austin, TX 78752 Voice: (512) 450-1199 x453 Fax: (512) 450-0263 http://www.academicsuperstore.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
