Klaus, >>in this case, I suspect, is that the disconnect happens in the >> pre-data-sending stage, which is possibly quite lengthy.
> Exactly ! It takes hours till these results are erased and i even don't know > where the period when they are erased come from. > I still hope this is set in a server-variable. In addition to what Sasha suggested (max_join_size), you might want to have a look at query_prealloc_size and range_alloc_block_size. Prerequisites are that you're using MySQL 4.0.16 or later, and that you have enough memory for tuning those system variables. http://www.mysql.com/doc/en/SHOW_VARIABLES.html >>I would also check to see if there might be some simple fixes >>, such as adding appropriate keys to the tables, that would optimize the >> slow queries. > The problem is, that i can't prevent queries like > SELECT * FROM <TABLE> WHERE <COLUMN> LIKE 'a%' > where an index will not help on a Table > 100.000.000 records. I wouldn't say "it will not help", but I admit that "it might not help" ;-). I've even seen significant performance improvements by adding indexes for queries like "LIKE '%...'", so it might be worth a try. Be aware, though, that creating an index on some 100 million rows will take some time by itself. > It's not that we have to answer hese queries (therefore the timeout), but > it's impossible to prevent dumm users. I don't know if that might help in your situation, but you should consider limiting user resources: http://www.mysql.com/doc/en/User_resources.html >>The solution I would propose is to maintain a separate connection on which > the KILL >>commands will be sent for the long queries. > I did this (by reading processlist and kiliing any process that > lasts longer then 20 seconds) and it works fine, but it is not such a > 'proper' solution. > I still hope there is a way to get that by setting up the server. If I'm not missing something obvious, there's no single system variable that you could set for a "proper" solution. More probably, you will have to set a number of variables affecting performance, depending on your (hardware and software) environment. If you can connect to your MySQL server from a Windows or a Linux box (and if you're using MySQL 4.0 or later), you should try out MySQL Administrator. That program allows you to view system variables that affect performance in a pretty intuitive way (just click Health, System Variables, General, Performance). Most of those variables can be set at server runtime by double-clicking them in MySQL Administrator. http://www.mysql.com/products/administrator/index.html Regards, -- Stefan Hinz <[EMAIL PROTECTED]> iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
