On Wed, Sep 14, 2011 at 4:04 AM, Marco Lanzotti <marco@xxxxxxxxxxxx> wrote: > Il 13/09/2011 20:58, Alex Nikitin ha scritto: > > Correction on Marco's post. You can absolutely stop a mysql query > > I know I can stop a query, but I don't know how to realize HTTP client > has closed connection during query execution. > > My query count how many records match selected fields in a 50M records > table. > Any query field is indexed and innodb uses 20GB of RAM to store data and > indexes, but some queries take about 30 seconds to run. > When user changes filters and asks for a new count, the old queries > continue to run using DB resurces unnecessarily. > > Bye, > Marco > Marco, I ran queries on a table that had 12M rows added to it each month with a year+ worth of data going back, pulling 80-90 thousand records with over a dozen columns on an older dual dual core box with 8gb ram (so 6 for MySQL) joining multiple tables for various criteria, matching on various values with query execution in a second range (depending on load, from under a second, to under 2 seconds). I think, and i am not trying to sound like pompous buffoon or to put anyone down or say that you or anyone here don't know what they are talking about or anything like that, but i think that you should first look into how you can optimize your database and your query, as well as maybe the access to this information (volume of information that you are presenting vs getting, also how you filter it, etc). Sometimes it's a very simple thing that can make or brake query execution time, and it's not immediately apparent. I was once tasked to fix a process in which about 2-300 queries were ran against the database in periodic ajax calls, they took about a 1/4 second to execute for each query. This ofcourse means that the refresh took almost a minute to run, which was getting very annoying, so i glimpsed over the queries and the tables at hand and 5 minutes later issued 2 queries, one to delete a useless index that was created for the main table, and another to create a new index on the database that reduced the execution time of those queries from 1/4 sec for each to 1.4 or 1.6 sec for all 2-300. And most of that time was actually caused by the network lag for the 2-300 queries, since they were individually executed from php, i wanted to reduce that whole thing to one query, but wasn't allowed to. Other times its a lot more complex, and sometimes blowing a query up from something simple or straight forward to something more complex can wield similar increases in performance, this ofcourse has to be with thorough understanding of how the database works. Perhaps if I, or we can understand your application a little better, we could suggest better solutions, just remember that you are not the first person to have to solve these similar issues. I can help you if you want, glimpse over your database design and queries for a fresh look, i have fairly extensive php (and many other languages) programming experience, as well as database design and administration, system development and administration, optimization, security, caching (many other things, that don't directly pertain to this) though we should probably keep it off the list. - Alex -- The trouble with programmers is that you can never tell what a programmer is doing until it’s too late. ~Seymour Cray
