On Wed, Sep 14, 2011 at 4:04 AM, Marco Lanzotti <ma...@lanzotti.com> 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


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

- Alex
The trouble with programmers is that you can never tell what a programmer is
doing until it’s too late.  ~Seymour Cray

Reply via email to