Hello Benjamin. Thanks a lot. Yes, you are right. It is a search engine. But I can?t get rid of the OR expressions, cause it is implemented in the search formular mask. So the User can choose any search criteria, which he wants. Neverthe- less I will follow your hint and check out these queries manually to view the load gage.
Greetz Daniel -----Ursprungliche Nachricht----- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Im Auftrag von Benjamin Pflugmann Gesendet: Dienstag, 11. Dezember 2001 22:04 An: Daniel Backhausen Cc: mySQL Mailingliste Betreff: Re: HELP... lot of Load ! Hi. On Mon, Dec 10, 2001 at 02:06:41PM +0100, [EMAIL PROTECTED] wrote: > ----------------------------------------------------------------------- [...] > # Time: 011210 13:40:23 > # User@Host: eff21[eff21] @ localhost [] > # Query_time: 25 Lock_time: 0 Rows_sent: 20 Rows_examined: 596309 > > SELECT SQL_BIG_RESULT Locations.Location_ID, Locations.Neu, > Locations.Sichtbar, Locations.Location_Name, Locations.PLZ, Locations.Ort, > Locations.Strasse, COUNT(Termine.Location_ID) AS Count_Termine FROM > Locations > LEFT JOIN Termine > ON Locations.Location_ID = Termine.Location_ID > WHERE Locations.Kino != '1' > AND (Locations.Location_Name LIKE '%%' > OR Locations.Ort LIKE '%%' > OR Locations.PLZ LIKE '%' > OR Locations.Kategorien_Locations1_Name LIKE '%%' > OR Locations.Kategorien_Locations2_Name LIKE '%%' ) > GROUP BY Locations.Location_ID > ORDER BY Locations.Location_Name > LIMIT 0,20 > ----------------------------------------------------------------------- > > Did this say anything to you advertently my high load ? [...] Not directly. But it is a *possible* reason. To see if these queries are the reason, run such a query by hand and watch the load (note that OS caching may influence the result for subsequent runs) and the result of 'mysqladmin processlist'. If the query is to blame, there should be a lot of LOCKED processes. The query needed 25 seconds and had to scan a major part of the table (596309 rows). MySQL is not good in optimizing OR expression. Note, that the OR expressions above are redundant, as they will always match. If you can get rid of them (I assume the query is build automatically), the query will run faster. Btw, one of the queries returned thousand of rows. Does that make really sense (looks like a search engine to me). If not, don't allow such queries, or use LIMIT shorten the search to a reasonable size. Unfortunately you did not quote the result of EXPLAIN as I asked for, so I cannot say more. But simply start with what I have told and see how far you get. Bye, Benjamin. -- [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
