Re: power-hungry SELECT query - how to reduce its priority?

[Szymon Grabowski]

Markus & all,

> Show the Query and table design. So someone might help.

Sorry about that. Here's the query:

$currentdate=int(time()/3600);
$sqlquery="select a.responder,a.from_field,a.pro, b.* from responders a,
prospects b where a.active='y' and a.seq>0 and a.responder=b.responder
and b.active='y' and (b.sign_up+(24*b.intval))<=$currentdate";

And here are two relevant tables (prospects & responders)

TABLE prospects (
   prospect_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
   responder varchar(60) binary NOT NULL,
   to_field varchar(240) NOT NULL,
   email varchar(120) NOT NULL,
   name varchar(120) NOT NULL,
   first_name varchar(120) NOT NULL,
   seq mediumint(9) DEFAULT '0' NOT NULL,
   ref varchar(10) NOT NULL,
   intval smallint(6) DEFAULT '0' NOT NULL,
   active char(1) DEFAULT 'y' NOT NULL,
   sign_up mediumint(9) DEFAULT '0' NOT NULL,
   PRIMARY KEY (prospect_id),
   KEY responder (responder),
   KEY responder_2 (responder, seq),
   KEY responder_3 (responder, intval),
   KEY responder_4 (responder, active),
   KEY to_field (to_field)
);

TABLE responders (              ;only responder,from_field,pro,active,seq
are relevant to this query
   key_old varchar(16) NOT NULL,
   responder varchar(60) binary NOT NULL,
   name varchar(120) NOT NULL,
   email varchar(120) NOT NULL,
   id varchar(5) NOT NULL,
   reg_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   exp_days mediumint(6) DEFAULT '0' NOT NULL,
   pro char(1) DEFAULT 'n' NOT NULL,
   usage_type char(1) DEFAULT 'b' NOT NULL,
   from_field varchar(120) NOT NULL,
   inqto varchar(120) NOT NULL,
   pwd varchar(80) NOT NULL,
   defname varchar(40) DEFAULT 'Friend' NOT NULL,
   seq mediumint(6) DEFAULT '0' NOT NULL,
   ip varchar(240) NOT NULL,
   referer varchar(240) NOT NULL,
   parent varchar(60) binary NOT NULL,
   active char(1) DEFAULT 'n' NOT NULL,
   reg_exp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   pwd_enc varchar(80) NOT NULL,
   mass_limit smallint(6) DEFAULT '0' NOT NULL,
   emailstats char(1) DEFAULT 'n' NOT NULL,
   PRIMARY KEY (responder),
   KEY responder (responder),
   KEY pro (pro),
   KEY usage_type (usage_type),
   KEY active (active),
   KEY active_2 (active, seq, responder),
   KEY emailstats (emailstats)
);

Thanks for your help!

-S


>
> >One of my Perl scripts (run every single hour via
> >crontab) starts mysql SELECT query that takes
> >approx. 5 minutes to finish. It operates on 1.3 million
> >records and it's fairly sophisticated (for me, at least).
> >The problem is that while that query is running other
> >Perl & PHP3 scripts tend to timeout when they try to
> >access MySQL. It looks that this hourly SELECT
> >eats up to much of mysql daemon and nothing's left
> >for other applications & scripts. Non-mysql stuff
> >works without a fail even when that SELECT is
> >running.
> >
> >Is it possible to reduce priority of that job, so that it
> >doesn't consume so much of mysql daemon resources?
> >It wouldn't be a problem if that SELECT took 50 minutes,
> >but let other scripts access mysql as well.
> >
> >Any ideas?
> >
> >I'm running MySQL 3.22.22.
> >
> >Here are MySQL system variables:
> >
> >  back_log   5
> >  connect_timeout   5
> >  delayed_insert_limit   100
> >  delayed_insert_timeout   300
> >  delayed_queue_size   1000
> >  join_buffer   131072
> >  flush_time   0
> >  key_buffer   8388600
> >  language
> >/usr/local/mysql-3.22.22-sun-solaris2.6-sparc/share/mysql/english/
> >  log   OFF
> >  log_update   OFF
> >  long_query_time   10
> >  low_priority_updates   OFF
> >  max_allowed_packet   1048576
> >  max_connections   500
> >  max_connect_errors   10
> >  max_delayed_insert_threads   20
> >  max_join_size   4294967295
> >  max_sort_length   1024
> >  net_buffer_length   16384
> >  port   3306
> >  protocol-version   10
> >  record_buffer   131072
> >  skip_locking   OFF
> >  socket   /tmp/mysql.sock
> >  sort_buffer   2097144
> >  table_cache   64
> >  thread_stack   65536
> >  tmp_table_size   1048576
> >  tmpdir   /var/tmp/
> >  version   3.22.22
> >  wait_timeout   28800
> >
> >
> >Thanks for your help!
> >
> >Cheers,
> >
> >Simon
> >
> >
> >--
> >---------------------------------------------------------------------
> >Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> >posting. To request this thread, e-mail mysql-thread28060@stripped
> >
> >To unsubscribe, send a message to:
> >     <mysql-unsubscribe-mw-mysql=welters.de@stripped>
>
> -
> Mit freundlichen Grüßen
>
> Markus Welters
>
> ----
> Welters Datentechnik   Grafenberger Allee 81  40237 Duesseldorf
> Telefon [phone]  GSM 0172-2007477   Fax [phone]
> http://www.welters.de    MW411-RIPE   mailto: markus@stripped
> ---------------------------------------------------------------
>
> --
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread28064@stripped
>
> To unsubscribe, send a message to:
>     <mysql-unsubscribe-szymon=profitstudio.it.pl@stripped>
>