Merge lp:~akopytov/percona-server/priority-scheduling-for-threadpool-5.5 into lp:percona-server/5.5

Proposed by Alexey Kopytov
Status: Merged
Approved by: Alexey Kopytov
Approved revision: 461
Merged at revision: 494
Proposed branch: lp:~akopytov/percona-server/priority-scheduling-for-threadpool-5.5
Merge into: lp:percona-server/5.5
Diff against target: 2559 lines (+2383/-6)
12 files modified
Percona-Server/mysql-test/r/percona_server_variables_debug.result (+1/-0)
Percona-Server/mysql-test/r/percona_server_variables_release.result (+1/-0)
Percona-Server/mysql-test/r/pool_of_threads_high_prio_tickets.result (+2169/-0)
Percona-Server/mysql-test/suite/sys_vars/r/thread_pool_high_prio_tickets_basic.result (+47/-0)
Percona-Server/mysql-test/suite/sys_vars/t/thread_pool_high_prio_tickets_basic.test (+43/-0)
Percona-Server/mysql-test/t/pool_of_threads_high_prio_tickets.cnf (+16/-0)
Percona-Server/mysql-test/t/pool_of_threads_high_prio_tickets.test (+19/-0)
Percona-Server/sql/sys_vars.cc (+7/-0)
Percona-Server/sql/threadpool.h (+3/-0)
Percona-Server/sql/threadpool_common.cc (+3/-0)
Percona-Server/sql/threadpool_unix.cc (+28/-6)
doc/source/performance/threadpool.rst (+46/-0)
To merge this branch: bzr merge lp:~akopytov/percona-server/priority-scheduling-for-threadpool-5.5
Reviewer Review Type Date Requested Status
Alexey Kopytov (community) Approve
Laurynas Biveinis (community) Approve
Review via email: mp+156522@code.launchpad.net

Description of the change

Implementation of:

https://blueprints.launchpad.net/percona-server/+spec/priority-scheduling-for-threadpool

Introduce the new ' thread_pool_high_prio_tickets' configuration option
to thread pool implementation with the following semantics:

This variable controls the high priority queue policy. Each new
connection is assigned this many tickets to enter the high priority
queue. Whenever a query has to be queued to be executed later because no
threads are available, the thread pool puts the connection into the high
priority queue if the following conditions apply:

1. The connection has an open transaction in the server.
2. The number of high priority tickets of this connection is non-zero.

If both the above conditions hold, the connection is put into the high
priority queue and its tickets value is decremented. Otherwise the
connection is put into the common queue with the initial tickets value
specified with this option.

Each time the thread pool looks for a new connection to process, it
first checks the high priority queue, and picks connections from the
common queue only when the high priority one is empty.

The idea is to minimize the number of open transactions in the
server. In many cases it is beneficial to give short-running
transactions a chance to commit faster and thus deallocate server
resources and locks without waiting in the same queue with other
connections that are about to start a new transaction, or those that
have run out of their high priority tickets.

With the default value of 0, all connections are always put into the
common queue, i.e. no priority scheduling is used as in the original
implementation in |MariaDB|. The higher is the value, the more chances
each transaction gets to enter the high priority queue and commit before
it is put in the common queue.

http://jenkins.percona.com/view/PS%205.5/job/percona-server-5.5-param/711/

To post a comment you must log in.
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

- Double comma at the diff line 330.

- If I understand correctly, whenever the connection exhausts its tickets, and is put back into the regular queue, and is woken up, and has to be enqueued again, it goes to the high priority queue with the original number of tickets again. Do you think that this cyclic behavior needs to be elaborated upon a bit more than it is? The sentence "Otherwise the connection is put into the common queue with the initial tickets value specified with this option." sort of does it, but IMHO it's easy to miss, so I'd add a bit more to it, for example "Otherwise the connection is put into the common queue. If, after being taken for processing, it needs to be queued again, it goes to the high priority queue with the initial tickets value specified with this option."

- Would it be a good idea to a very minimal test (i.e. the server doesn't crash with the value set, not for the desired scheduling behavior) to pool_of_threads.test for the tickets > 0 case?

review: Needs Information
Revision history for this message
Alexey Kopytov (akopytov) wrote :

Hi Laurynas,

On Wed, 03 Apr 2013 05:23:21 -0000, Laurynas Biveinis wrote:
> Review: Needs Information
>
> - Double comma at the diff line 330.
>

Fixed.

> - If I understand correctly, whenever the connection exhausts its tickets, and is put back into the regular queue, and is woken up, and has to be enqueued again, it goes to the high priority queue with the original number of tickets again. Do you think that this cyclic behavior needs to be elaborated upon a bit more than it is? The sentence "Otherwise the connection is put into the common queue with the initial tickets value specified with this option." sort of does it, but IMHO it's easy to miss, so I'd add a bit more to it, for example "Otherwise the connection is put into the common queue. If, after being taken for processing, it needs to be queued again, it goes to the high priority queue with the initial tickets value specified with this option."
>

That's not technically correct. The correct version would be:

"
Otherwise the connection is put into the common queue. If, after being
taken for processing, it needs to be queued again, it goes to the high
priority queue *if it has an open transaction* with the initial tickets
value specified with this option *decremented by one*.
"

I tried to think of some text that would be both technically correct and
easy to read and came back to my original text :)

> - Would it be a good idea to a very minimal test (i.e. the server doesn't crash with the value set, not for the desired scheduling behavior) to pool_of_threads.test for the tickets > 0 case?
>

Added.

http://jenkins.percona.com/view/PS%205.5/job/percona-server-5.5-param/716/

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

> That's not technically correct. The correct version would be:
>
> "
> Otherwise the connection is put into the common queue. If, after being
> taken for processing, it needs to be queued again, it goes to the high
> priority queue *if it has an open transaction* with the initial tickets
> value specified with this option *decremented by one*.
> "
>
> I tried to think of some text that would be both technically correct and
> easy to read and came back to my original text :)

Heh, you are right :)

> http://jenkins.percona.com/view/PS%205.5/job/percona-server-5.5-param/716/

OK if no Jenkins surprises (the job has just started). The merge captain, please check at the merge time.

review: Approve
Revision history for this message
Alexey Kopytov (akopytov) wrote :

There Jenkins failures on main.pool_of_threads_high_prio_tickets.

review: Needs Fixing
461. By Alexey Kopytov on 2013-04-03

Implementation of:

https://blueprints.launchpad.net/percona-server/+spec/priority-scheduling-for-threadpool

Introduce the new ' thread_pool_high_prio_tickets' configuration option
to thread pool implementation with the following semantics:

This variable controls the high priority queue policy. Each new
connection is assigned this many tickets to enter the high priority
queue. Whenever a query has to be queued to be executed later because no
threads are available, the thread pool puts the connection into the high
priority queue if the following conditions apply:

1. The connection has an open transaction in the server.
2. The number of high priority tickets of this connection is non-zero.

If both the above conditions hold, the connection is put into the high
priority queue and its tickets value is decremented. Otherwise the
connection is put into the common queue with the initial tickets value
specified with this option.

Each time the thread pool looks for a new connection to process, it
first checks the high priority queue, and picks connections from the
common queue only when the high priority one is empty.

The idea is to minimize the number of open transactions in the
server. In many cases it is beneficial to give short-running
transactions a chance to commit faster and thus deallocate server
resources and locks without waiting in the same queue with other
connections that are about to start a new transaction, or those that
have run out of their high priority tickets.

With the default value of 0, all connections are always put into the
common queue, i.e. no priority scheduling is used as in the original
implementation in |MariaDB|. The higher is the value, the more chances
each transaction gets to enter the high priority queue and commit before
it is put in the common queue.

Revision history for this message
Alexey Kopytov (akopytov) wrote :

The reason was have_innodb.inc resulting in InnoDB as the default storage engine and hence inexact row numbers in EXPLAIN.

Since the test case doesn't actually test anything, it doesn't really require InnoDB :)

Revision history for this message
Alexey Kopytov (akopytov) wrote :
Revision history for this message
Alexey Kopytov (akopytov) wrote :

The Jenkins build looks good.

review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'Percona-Server/mysql-test/r/percona_server_variables_debug.result'
2--- Percona-Server/mysql-test/r/percona_server_variables_debug.result 2013-03-05 12:16:18 +0000
3+++ Percona-Server/mysql-test/r/percona_server_variables_debug.result 2013-04-03 10:29:25 +0000
4@@ -380,6 +380,7 @@
5 THREAD_CACHE_SIZE
6 THREAD_CONCURRENCY
7 THREAD_HANDLING
8+THREAD_POOL_HIGH_PRIO_TICKETS
9 THREAD_POOL_IDLE_TIMEOUT
10 THREAD_POOL_MAX_THREADS
11 THREAD_POOL_OVERSUBSCRIBE
12
13=== modified file 'Percona-Server/mysql-test/r/percona_server_variables_release.result'
14--- Percona-Server/mysql-test/r/percona_server_variables_release.result 2013-03-05 12:16:18 +0000
15+++ Percona-Server/mysql-test/r/percona_server_variables_release.result 2013-04-03 10:29:25 +0000
16@@ -371,6 +371,7 @@
17 THREAD_CACHE_SIZE
18 THREAD_CONCURRENCY
19 THREAD_HANDLING
20+THREAD_POOL_HIGH_PRIO_TICKETS
21 THREAD_POOL_IDLE_TIMEOUT
22 THREAD_POOL_MAX_THREADS
23 THREAD_POOL_OVERSUBSCRIBE
24
25=== added file 'Percona-Server/mysql-test/r/pool_of_threads_high_prio_tickets.result'
26--- Percona-Server/mysql-test/r/pool_of_threads_high_prio_tickets.result 1970-01-01 00:00:00 +0000
27+++ Percona-Server/mysql-test/r/pool_of_threads_high_prio_tickets.result 2013-04-03 10:29:25 +0000
28@@ -0,0 +1,2169 @@
29+SELECT @@thread_pool_high_prio_tickets;
30+@@thread_pool_high_prio_tickets
31+2
32+drop table if exists t1,t2,t3,t4;
33+CREATE TABLE t1 (
34+Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
35+Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
36+);
37+INSERT INTO t1 VALUES (9410,9412);
38+select period from t1;
39+period
40+9410
41+select * from t1;
42+Period Varor_period
43+9410 9412
44+select t1.* from t1;
45+Period Varor_period
46+9410 9412
47+CREATE TABLE t2 (
48+auto int not null auto_increment,
49+fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL,
50+companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL,
51+fld3 char(30) DEFAULT '' NOT NULL,
52+fld4 char(35) DEFAULT '' NOT NULL,
53+fld5 char(35) DEFAULT '' NOT NULL,
54+fld6 char(4) DEFAULT '' NOT NULL,
55+UNIQUE fld1 (fld1),
56+KEY fld3 (fld3),
57+PRIMARY KEY (auto)
58+);
59+select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";
60+fld3
61+imaginable
62+select fld3 from t2 where fld3 like "%cultivation" ;
63+fld3
64+cultivation
65+select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3;
66+fld3 companynr
67+concoct 58
68+druggists 58
69+engrossing 58
70+Eurydice 58
71+exclaimers 58
72+ferociousness 58
73+hopelessness 58
74+Huey 58
75+imaginable 58
76+judges 58
77+merging 58
78+ostrich 58
79+peering 58
80+Phelps 58
81+presumes 58
82+Ruth 58
83+sentences 58
84+Shylock 58
85+straggled 58
86+synergy 58
87+thanking 58
88+tying 58
89+unlocks 58
90+select fld3,companynr from t2 where companynr = 58 order by fld3;
91+fld3 companynr
92+concoct 58
93+druggists 58
94+engrossing 58
95+Eurydice 58
96+exclaimers 58
97+ferociousness 58
98+hopelessness 58
99+Huey 58
100+imaginable 58
101+judges 58
102+merging 58
103+ostrich 58
104+peering 58
105+Phelps 58
106+presumes 58
107+Ruth 58
108+sentences 58
109+Shylock 58
110+straggled 58
111+synergy 58
112+thanking 58
113+tying 58
114+unlocks 58
115+select fld3 from t2 order by fld3 desc limit 10;
116+fld3
117+youthfulness
118+yelped
119+Wotan
120+workers
121+Witt
122+witchcraft
123+Winsett
124+Willy
125+willed
126+wildcats
127+select fld3 from t2 order by fld3 desc limit 5;
128+fld3
129+youthfulness
130+yelped
131+Wotan
132+workers
133+Witt
134+select fld3 from t2 order by fld3 desc limit 5,5;
135+fld3
136+witchcraft
137+Winsett
138+Willy
139+willed
140+wildcats
141+select t2.fld3 from t2 where fld3 = 'honeysuckle';
142+fld3
143+honeysuckle
144+select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_';
145+fld3
146+honeysuckle
147+select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_';
148+fld3
149+honeysuckle
150+select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%';
151+fld3
152+honeysuckle
153+select t2.fld3 from t2 where fld3 LIKE 'h%le';
154+fld3
155+honeysuckle
156+select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_';
157+fld3
158+select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%';
159+fld3
160+explain select t2.fld3 from t2 where fld3 = 'honeysuckle';
161+id select_type table type possible_keys key key_len ref rows Extra
162+1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index
163+explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle';
164+id select_type table type possible_keys key key_len ref rows Extra
165+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
166+explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle';
167+id select_type table type possible_keys key key_len ref rows Extra
168+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
169+explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle';
170+id select_type table type possible_keys key key_len ref rows Extra
171+1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index
172+explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle';
173+id select_type table type possible_keys key key_len ref rows Extra
174+1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index
175+explain select fld3 from t2 ignore index (fld3,not_used);
176+ERROR 42000: Key 'not_used' doesn't exist in table 't2'
177+explain select fld3 from t2 use index (not_used);
178+ERROR 42000: Key 'not_used' doesn't exist in table 't2'
179+select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
180+fld3
181+honeysuckle
182+honoring
183+explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
184+id select_type table type possible_keys key key_len ref rows Extra
185+1 SIMPLE t2 range fld3 fld3 30 NULL 2 Using where; Using index
186+select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3;
187+fld1 fld3
188+148504 Colombo
189+068305 Colombo
190+000000 nondecreasing
191+select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes';
192+fld1 fld3
193+232605 appendixes
194+1232605 appendixes
195+1232606 appendixes
196+1232607 appendixes
197+1232608 appendixes
198+1232609 appendixes
199+select fld1 from t2 where fld1=250501 or fld1="250502";
200+fld1
201+250501
202+250502
203+explain select fld1 from t2 where fld1=250501 or fld1="250502";
204+id select_type table type possible_keys key key_len ref rows Extra
205+1 SIMPLE t2 range fld1 fld1 4 NULL 2 Using where; Using index
206+select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
207+fld1
208+250501
209+250502
210+250505
211+250601
212+explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
213+id select_type table type possible_keys key key_len ref rows Extra
214+1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index
215+select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';
216+fld1 fld3
217+218401 faithful
218+018007 fanatic
219+228311 fated
220+018017 featherweight
221+218022 feed
222+088303 feminine
223+058004 Fenton
224+038017 fetched
225+018054 fetters
226+208101 fiftieth
227+238007 filial
228+013606 fingerings
229+218008 finishers
230+038205 firearm
231+188505 fitting
232+202301 Fitzpatrick
233+238008 fixedly
234+012001 flanking
235+018103 flint
236+018104 flopping
237+188007 flurried
238+013602 foldout
239+226205 foothill
240+232102 forgivably
241+228306 forthcoming
242+186002 freakish
243+208113 freest
244+231315 freezes
245+036002 funereal
246+226209 furnishings
247+198006 furthermore
248+select fld3 from t2 where fld3 like "L%" and fld3 = "ok";
249+fld3
250+select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");
251+fld3
252+Chantilly
253+select fld1,fld3 from t2 where fld1 like "25050%";
254+fld1 fld3
255+250501 poisoning
256+250502 Iraqis
257+250503 heaving
258+250504 population
259+250505 bomb
260+select fld1,fld3 from t2 where fld1 like "25050_";
261+fld1 fld3
262+250501 poisoning
263+250502 Iraqis
264+250503 heaving
265+250504 population
266+250505 bomb
267+select distinct companynr from t2;
268+companynr
269+00
270+37
271+36
272+50
273+58
274+29
275+40
276+53
277+65
278+41
279+34
280+68
281+select distinct companynr from t2 order by companynr;
282+companynr
283+00
284+29
285+34
286+36
287+37
288+40
289+41
290+50
291+53
292+58
293+65
294+68
295+select distinct companynr from t2 order by companynr desc;
296+companynr
297+68
298+65
299+58
300+53
301+50
302+41
303+40
304+37
305+36
306+34
307+29
308+00
309+select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%";
310+fld3 period
311+obliterates 9410
312+offload 9410
313+opaquely 9410
314+organizer 9410
315+overestimating 9410
316+overlay 9410
317+select distinct fld3 from t2 where companynr = 34 order by fld3;
318+fld3
319+absentee
320+accessed
321+ahead
322+alphabetic
323+Asiaticizations
324+attitude
325+aye
326+bankruptcies
327+belays
328+Blythe
329+bomb
330+boulevard
331+bulldozes
332+cannot
333+caressing
334+charcoal
335+checksumming
336+chess
337+clubroom
338+colorful
339+cosy
340+creator
341+crying
342+Darius
343+diffusing
344+duality
345+Eiffel
346+Epiphany
347+Ernestine
348+explorers
349+exterminated
350+famine
351+forked
352+Gershwins
353+heaving
354+Hodges
355+Iraqis
356+Italianization
357+Lagos
358+landslide
359+libretto
360+Majorca
361+mastering
362+narrowed
363+occurred
364+offerers
365+Palestine
366+Peruvianizes
367+pharmaceutic
368+poisoning
369+population
370+Pygmalion
371+rats
372+realest
373+recording
374+regimented
375+retransmitting
376+reviver
377+rouses
378+scars
379+sicker
380+sleepwalk
381+stopped
382+sugars
383+translatable
384+uncles
385+unexpected
386+uprisings
387+versatility
388+vest
389+select distinct fld3 from t2 limit 10;
390+fld3
391+abates
392+abiding
393+Abraham
394+abrogating
395+absentee
396+abut
397+accessed
398+accruing
399+accumulating
400+accuracies
401+select distinct fld3 from t2 having fld3 like "A%" limit 10;
402+fld3
403+abates
404+abiding
405+Abraham
406+abrogating
407+absentee
408+abut
409+accessed
410+accruing
411+accumulating
412+accuracies
413+select distinct substring(fld3,1,3) from t2 where fld3 like "A%";
414+substring(fld3,1,3)
415+aba
416+abi
417+Abr
418+abs
419+abu
420+acc
421+acq
422+acu
423+Ade
424+adj
425+Adl
426+adm
427+Ado
428+ads
429+adv
430+aer
431+aff
432+afi
433+afl
434+afo
435+agi
436+ahe
437+aim
438+air
439+Ald
440+alg
441+ali
442+all
443+alp
444+alr
445+ama
446+ame
447+amm
448+ana
449+and
450+ane
451+Ang
452+ani
453+Ann
454+Ant
455+api
456+app
457+aqu
458+Ara
459+arc
460+Arm
461+arr
462+Art
463+Asi
464+ask
465+asp
466+ass
467+ast
468+att
469+aud
470+Aug
471+aut
472+ave
473+avo
474+awe
475+aye
476+Azt
477+select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10;
478+a
479+aba
480+abi
481+Abr
482+abs
483+abu
484+acc
485+acq
486+acu
487+Ade
488+adj
489+select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10;
490+substring(fld3,1,3)
491+aba
492+abi
493+Abr
494+abs
495+abu
496+acc
497+acq
498+acu
499+Ade
500+adj
501+select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10;
502+a
503+aba
504+abi
505+Abr
506+abs
507+abu
508+acc
509+acq
510+acu
511+Ade
512+adj
513+create table t3 (
514+period int not null,
515+name char(32) not null,
516+companynr int not null,
517+price double(11,0),
518+price2 double(11,0),
519+key (period),
520+key (name)
521+);
522+create temporary table tmp engine = myisam select * from t3;
523+insert into t3 select * from tmp;
524+insert into tmp select * from t3;
525+insert into t3 select * from tmp;
526+insert into tmp select * from t3;
527+insert into t3 select * from tmp;
528+insert into tmp select * from t3;
529+insert into t3 select * from tmp;
530+insert into tmp select * from t3;
531+insert into t3 select * from tmp;
532+insert into tmp select * from t3;
533+insert into t3 select * from tmp;
534+insert into tmp select * from t3;
535+insert into t3 select * from tmp;
536+insert into tmp select * from t3;
537+insert into t3 select * from tmp;
538+insert into tmp select * from t3;
539+insert into t3 select * from tmp;
540+alter table t3 add t2nr int not null auto_increment primary key first;
541+drop table tmp;
542+SET SQL_BIG_TABLES=1;
543+select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;
544+namn
545+Abraham Abraham
546+abrogating abrogating
547+admonishing admonishing
548+Adolph Adolph
549+afield afield
550+aging aging
551+ammonium ammonium
552+analyzable analyzable
553+animals animals
554+animized animized
555+SET SQL_BIG_TABLES=0;
556+select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;
557+concat(fld3," ",fld3)
558+Abraham Abraham
559+abrogating abrogating
560+admonishing admonishing
561+Adolph Adolph
562+afield afield
563+aging aging
564+ammonium ammonium
565+analyzable analyzable
566+animals animals
567+animized animized
568+select distinct fld5 from t2 limit 10;
569+fld5
570+neat
571+Steinberg
572+jarring
573+tinily
574+balled
575+persist
576+attainments
577+fanatic
578+measures
579+rightfulness
580+select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
581+fld3 count(*)
582+affixed 1
583+and 1
584+annoyers 1
585+Anthony 1
586+assayed 1
587+assurers 1
588+attendants 1
589+bedlam 1
590+bedpost 1
591+boasted 1
592+SET SQL_BIG_TABLES=1;
593+select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
594+fld3 count(*)
595+affixed 1
596+and 1
597+annoyers 1
598+Anthony 1
599+assayed 1
600+assurers 1
601+attendants 1
602+bedlam 1
603+bedpost 1
604+boasted 1
605+SET SQL_BIG_TABLES=0;
606+select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;
607+fld3 repeat("a",length(fld3)) count(*)
608+circus aaaaaa 1
609+cited aaaaa 1
610+Colombo aaaaaaa 1
611+congresswoman aaaaaaaaaaaaa 1
612+contrition aaaaaaaaaa 1
613+corny aaaaa 1
614+cultivation aaaaaaaaaaa 1
615+definiteness aaaaaaaaaaaa 1
616+demultiplex aaaaaaaaaaa 1
617+disappointing aaaaaaaaaaaaa 1
618+select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2;
619+companynr rtrim(space(512+companynr))
620+37
621+78
622+101
623+154
624+311
625+447
626+512
627+select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3;
628+fld3
629+explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
630+id select_type table type possible_keys key key_len ref rows Extra
631+1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
632+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index
633+explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
634+id select_type table type possible_keys key key_len ref rows Extra
635+1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
636+1 SIMPLE t3 ref period period 4 test.t1.period 4181
637+explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
638+id select_type table type possible_keys key key_len ref rows Extra
639+1 SIMPLE t3 index period period 4 NULL 1
640+1 SIMPLE t1 ref period period 4 test.t3.period 4181
641+explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
642+id select_type table type possible_keys key key_len ref rows Extra
643+1 SIMPLE t1 index period period 4 NULL 1
644+1 SIMPLE t3 ref period period 4 test.t1.period 4181
645+select period from t1;
646+period
647+9410
648+select period from t1 where period=1900;
649+period
650+select fld3,period from t1,t2 where fld1 = 011401 order by period;
651+fld3 period
652+breaking 9410
653+select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;
654+fld3 period
655+breaking 1001
656+explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period;
657+id select_type table type possible_keys key key_len ref rows Extra
658+1 SIMPLE t2 const fld1 fld1 4 const 1
659+1 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1
660+select fld3,period from t2,t1 where companynr*10 = 37*10;
661+fld3 period
662+breaking 9410
663+Romans 9410
664+intercepted 9410
665+bewilderingly 9410
666+astound 9410
667+admonishing 9410
668+sumac 9410
669+flanking 9410
670+combed 9410
671+subjective 9410
672+scatterbrain 9410
673+Eulerian 9410
674+Kane 9410
675+overlay 9410
676+perturb 9410
677+goblins 9410
678+annihilates 9410
679+Wotan 9410
680+snatching 9410
681+concludes 9410
682+laterally 9410
683+yelped 9410
684+grazing 9410
685+Baird 9410
686+celery 9410
687+misunderstander 9410
688+handgun 9410
689+foldout 9410
690+mystic 9410
691+succumbed 9410
692+Nabisco 9410
693+fingerings 9410
694+aging 9410
695+afield 9410
696+ammonium 9410
697+boat 9410
698+intelligibility 9410
699+Augustine 9410
700+teethe 9410
701+dreaded 9410
702+scholastics 9410
703+audiology 9410
704+wallet 9410
705+parters 9410
706+eschew 9410
707+quitter 9410
708+neat 9410
709+Steinberg 9410
710+jarring 9410
711+tinily 9410
712+balled 9410
713+persist 9410
714+attainments 9410
715+fanatic 9410
716+measures 9410
717+rightfulness 9410
718+capably 9410
719+impulsive 9410
720+starlet 9410
721+terminators 9410
722+untying 9410
723+announces 9410
724+featherweight 9410
725+pessimist 9410
726+daughter 9410
727+decliner 9410
728+lawgiver 9410
729+stated 9410
730+readable 9410
731+attrition 9410
732+cascade 9410
733+motors 9410
734+interrogate 9410
735+pests 9410
736+stairway 9410
737+dopers 9410
738+testicle 9410
739+Parsifal 9410
740+leavings 9410
741+postulation 9410
742+squeaking 9410
743+contrasted 9410
744+leftover 9410
745+whiteners 9410
746+erases 9410
747+Punjab 9410
748+Merritt 9410
749+Quixotism 9410
750+sweetish 9410
751+dogging 9410
752+scornfully 9410
753+bellow 9410
754+bills 9410
755+cupboard 9410
756+sureties 9410
757+puddings 9410
758+fetters 9410
759+bivalves 9410
760+incurring 9410
761+Adolph 9410
762+pithed 9410
763+Miles 9410
764+trimmings 9410
765+tragedies 9410
766+skulking 9410
767+flint 9410
768+flopping 9410
769+relaxing 9410
770+offload 9410
771+suites 9410
772+lists 9410
773+animized 9410
774+multilayer 9410
775+standardizes 9410
776+Judas 9410
777+vacuuming 9410
778+dentally 9410
779+humanness 9410
780+inch 9410
781+Weissmuller 9410
782+irresponsibly 9410
783+luckily 9410
784+culled 9410
785+medical 9410
786+bloodbath 9410
787+subschema 9410
788+animals 9410
789+Micronesia 9410
790+repetitions 9410
791+Antares 9410
792+ventilate 9410
793+pityingly 9410
794+interdependent 9410
795+Graves 9410
796+neonatal 9410
797+chafe 9410
798+honoring 9410
799+realtor 9410
800+elite 9410
801+funereal 9410
802+abrogating 9410
803+sorters 9410
804+Conley 9410
805+lectured 9410
806+Abraham 9410
807+Hawaii 9410
808+cage 9410
809+hushes 9410
810+Simla 9410
811+reporters 9410
812+Dutchman 9410
813+descendants 9410
814+groupings 9410
815+dissociate 9410
816+coexist 9410
817+Beebe 9410
818+Taoism 9410
819+Connally 9410
820+fetched 9410
821+checkpoints 9410
822+rusting 9410
823+galling 9410
824+obliterates 9410
825+traitor 9410
826+resumes 9410
827+analyzable 9410
828+terminator 9410
829+gritty 9410
830+firearm 9410
831+minima 9410
832+Selfridge 9410
833+disable 9410
834+witchcraft 9410
835+betroth 9410
836+Manhattanize 9410
837+imprint 9410
838+peeked 9410
839+swelling 9410
840+interrelationships 9410
841+riser 9410
842+Gandhian 9410
843+peacock 9410
844+bee 9410
845+kanji 9410
846+dental 9410
847+scarf 9410
848+chasm 9410
849+insolence 9410
850+syndicate 9410
851+alike 9410
852+imperial 9410
853+convulsion 9410
854+railway 9410
855+validate 9410
856+normalizes 9410
857+comprehensive 9410
858+chewing 9410
859+denizen 9410
860+schemer 9410
861+chronicle 9410
862+Kline 9410
863+Anatole 9410
864+partridges 9410
865+brunch 9410
866+recruited 9410
867+dimensions 9410
868+Chicana 9410
869+announced 9410
870+praised 9410
871+employing 9410
872+linear 9410
873+quagmire 9410
874+western 9410
875+relishing 9410
876+serving 9410
877+scheduling 9410
878+lore 9410
879+eventful 9410
880+arteriole 9410
881+disentangle 9410
882+cured 9410
883+Fenton 9410
884+avoidable 9410
885+drains 9410
886+detectably 9410
887+husky 9410
888+impelling 9410
889+undoes 9410
890+evened 9410
891+squeezes 9410
892+destroyer 9410
893+rudeness 9410
894+beaner 9410
895+boorish 9410
896+Everhart 9410
897+encompass 9410
898+mushrooms 9410
899+Alison 9410
900+externally 9410
901+pellagra 9410
902+cult 9410
903+creek 9410
904+Huffman 9410
905+Majorca 9410
906+governing 9410
907+gadfly 9410
908+reassigned 9410
909+intentness 9410
910+craziness 9410
911+psychic 9410
912+squabbled 9410
913+burlesque 9410
914+capped 9410
915+extracted 9410
916+DiMaggio 9410
917+exclamation 9410
918+subdirectory 9410
919+Gothicism 9410
920+feminine 9410
921+metaphysically 9410
922+sanding 9410
923+Miltonism 9410
924+freakish 9410
925+index 9410
926+straight 9410
927+flurried 9410
928+denotative 9410
929+coming 9410
930+commencements 9410
931+gentleman 9410
932+gifted 9410
933+Shanghais 9410
934+sportswriting 9410
935+sloping 9410
936+navies 9410
937+leaflet 9410
938+shooter 9410
939+Joplin 9410
940+babies 9410
941+assails 9410
942+admiring 9410
943+swaying 9410
944+Goldstine 9410
945+fitting 9410
946+Norwalk 9410
947+analogy 9410
948+deludes 9410
949+cokes 9410
950+Clayton 9410
951+exhausts 9410
952+causality 9410
953+sating 9410
954+icon 9410
955+throttles 9410
956+communicants 9410
957+dehydrate 9410
958+priceless 9410
959+publicly 9410
960+incidentals 9410
961+commonplace 9410
962+mumbles 9410
963+furthermore 9410
964+cautioned 9410
965+parametrized 9410
966+registration 9410
967+sadly 9410
968+positioning 9410
969+babysitting 9410
970+eternal 9410
971+hoarder 9410
972+congregates 9410
973+rains 9410
974+workers 9410
975+sags 9410
976+unplug 9410
977+garage 9410
978+boulder 9410
979+specifics 9410
980+Teresa 9410
981+Winsett 9410
982+convenient 9410
983+buckboards 9410
984+amenities 9410
985+resplendent 9410
986+sews 9410
987+participated 9410
988+Simon 9410
989+certificates 9410
990+Fitzpatrick 9410
991+Evanston 9410
992+misted 9410
993+textures 9410
994+save 9410
995+count 9410
996+rightful 9410
997+chaperone 9410
998+Lizzy 9410
999+clenched 9410
1000+effortlessly 9410
1001+accessed 9410
1002+beaters 9410
1003+Hornblower 9410
1004+vests 9410
1005+indulgences 9410
1006+infallibly 9410
1007+unwilling 9410
1008+excrete 9410
1009+spools 9410
1010+crunches 9410
1011+overestimating 9410
1012+ineffective 9410
1013+humiliation 9410
1014+sophomore 9410
1015+star 9410
1016+rifles 9410
1017+dialysis 9410
1018+arriving 9410
1019+indulge 9410
1020+clockers 9410
1021+languages 9410
1022+Antarctica 9410
1023+percentage 9410
1024+ceiling 9410
1025+specification 9410
1026+regimented 9410
1027+ciphers 9410
1028+pictures 9410
1029+serpents 9410
1030+allot 9410
1031+realized 9410
1032+mayoral 9410
1033+opaquely 9410
1034+hostess 9410
1035+fiftieth 9410
1036+incorrectly 9410
1037+decomposition 9410
1038+stranglings 9410
1039+mixture 9410
1040+electroencephalography 9410
1041+similarities 9410
1042+charges 9410
1043+freest 9410
1044+Greenberg 9410
1045+tinting 9410
1046+expelled 9410
1047+warm 9410
1048+smoothed 9410
1049+deductions 9410
1050+Romano 9410
1051+bitterroot 9410
1052+corset 9410
1053+securing 9410
1054+environing 9410
1055+cute 9410
1056+Crays 9410
1057+heiress 9410
1058+inform 9410
1059+avenge 9410
1060+universals 9410
1061+Kinsey 9410
1062+ravines 9410
1063+bestseller 9410
1064+equilibrium 9410
1065+extents 9410
1066+relatively 9410
1067+pressure 9410
1068+critiques 9410
1069+befouled 9410
1070+rightfully 9410
1071+mechanizing 9410
1072+Latinizes 9410
1073+timesharing 9410
1074+Aden 9410
1075+embassies 9410
1076+males 9410
1077+shapelessly 9410
1078+mastering 9410
1079+Newtonian 9410
1080+finishers 9410
1081+abates 9410
1082+teem 9410
1083+kiting 9410
1084+stodgy 9410
1085+feed 9410
1086+guitars 9410
1087+airships 9410
1088+store 9410
1089+denounces 9410
1090+Pyle 9410
1091+Saxony 9410
1092+serializations 9410
1093+Peruvian 9410
1094+taxonomically 9410
1095+kingdom 9410
1096+stint 9410
1097+Sault 9410
1098+faithful 9410
1099+Ganymede 9410
1100+tidiness 9410
1101+gainful 9410
1102+contrary 9410
1103+Tipperary 9410
1104+tropics 9410
1105+theorizers 9410
1106+renew 9410
1107+already 9410
1108+terminal 9410
1109+Hegelian 9410
1110+hypothesizer 9410
1111+warningly 9410
1112+journalizing 9410
1113+nested 9410
1114+Lars 9410
1115+saplings 9410
1116+foothill 9410
1117+labeled 9410
1118+imperiously 9410
1119+reporters 9410
1120+furnishings 9410
1121+precipitable 9410
1122+discounts 9410
1123+excises 9410
1124+Stalin 9410
1125+despot 9410
1126+ripeness 9410
1127+Arabia 9410
1128+unruly 9410
1129+mournfulness 9410
1130+boom 9410
1131+slaughter 9410
1132+Sabine 9410
1133+handy 9410
1134+rural 9410
1135+organizer 9410
1136+shipyard 9410
1137+civics 9410
1138+inaccuracy 9410
1139+rules 9410
1140+juveniles 9410
1141+comprised 9410
1142+investigations 9410
1143+stabilizes 9410
1144+seminaries 9410
1145+Hunter 9410
1146+sporty 9410
1147+test 9410
1148+weasels 9410
1149+CERN 9410
1150+tempering 9410
1151+afore 9410
1152+Galatean 9410
1153+techniques 9410
1154+error 9410
1155+veranda 9410
1156+severely 9410
1157+Cassites 9410
1158+forthcoming 9410
1159+guides 9410
1160+vanish 9410
1161+lied 9410
1162+sawtooth 9410
1163+fated 9410
1164+gradually 9410
1165+widens 9410
1166+preclude 9410
1167+evenhandedly 9410
1168+percentage 9410
1169+disobedience 9410
1170+humility 9410
1171+gleaning 9410
1172+petted 9410
1173+bloater 9410
1174+minion 9410
1175+marginal 9410
1176+apiary 9410
1177+measures 9410
1178+precaution 9410
1179+repelled 9410
1180+primary 9410
1181+coverings 9410
1182+Artemia 9410
1183+navigate 9410
1184+spatial 9410
1185+Gurkha 9410
1186+meanwhile 9410
1187+Melinda 9410
1188+Butterfield 9410
1189+Aldrich 9410
1190+previewing 9410
1191+glut 9410
1192+unaffected 9410
1193+inmate 9410
1194+mineral 9410
1195+impending 9410
1196+meditation 9410
1197+ideas 9410
1198+miniaturizes 9410
1199+lewdly 9410
1200+title 9410
1201+youthfulness 9410
1202+creak 9410
1203+Chippewa 9410
1204+clamored 9410
1205+freezes 9410
1206+forgivably 9410
1207+reduce 9410
1208+McGovern 9410
1209+Nazis 9410
1210+epistle 9410
1211+socializes 9410
1212+conceptions 9410
1213+Kevin 9410
1214+uncovering 9410
1215+chews 9410
1216+appendixes 9410
1217+appendixes 9410
1218+appendixes 9410
1219+appendixes 9410
1220+appendixes 9410
1221+appendixes 9410
1222+raining 9410
1223+infest 9410
1224+compartment 9410
1225+minting 9410
1226+ducks 9410
1227+roped 9410
1228+waltz 9410
1229+Lillian 9410
1230+repressions 9410
1231+chillingly 9410
1232+noncritical 9410
1233+lithograph 9410
1234+spongers 9410
1235+parenthood 9410
1236+posed 9410
1237+instruments 9410
1238+filial 9410
1239+fixedly 9410
1240+relives 9410
1241+Pandora 9410
1242+watering 9410
1243+ungrateful 9410
1244+secures 9410
1245+poison 9410
1246+dusted 9410
1247+encompasses 9410
1248+presentation 9410
1249+Kantian 9410
1250+select fld3,period,price,price2 from t2,t3 where t2.fld1=t3.t2nr and period >= 1001 and period <= 1002 and t2.companynr = 37 order by fld3,period, price;
1251+fld3 period price price2
1252+admonishing 1002 28357832 8723648
1253+analyzable 1002 28357832 8723648
1254+annihilates 1001 5987435 234724
1255+Antares 1002 28357832 8723648
1256+astound 1001 5987435 234724
1257+audiology 1001 5987435 234724
1258+Augustine 1002 28357832 8723648
1259+Baird 1002 28357832 8723648
1260+bewilderingly 1001 5987435 234724
1261+breaking 1001 5987435 234724
1262+Conley 1001 5987435 234724
1263+dentally 1002 28357832 8723648
1264+dissociate 1002 28357832 8723648
1265+elite 1001 5987435 234724
1266+eschew 1001 5987435 234724
1267+Eulerian 1001 5987435 234724
1268+flanking 1001 5987435 234724
1269+foldout 1002 28357832 8723648
1270+funereal 1002 28357832 8723648
1271+galling 1002 28357832 8723648
1272+Graves 1001 5987435 234724
1273+grazing 1001 5987435 234724
1274+groupings 1001 5987435 234724
1275+handgun 1001 5987435 234724
1276+humility 1002 28357832 8723648
1277+impulsive 1002 28357832 8723648
1278+inch 1001 5987435 234724
1279+intelligibility 1001 5987435 234724
1280+jarring 1001 5987435 234724
1281+lawgiver 1001 5987435 234724
1282+lectured 1002 28357832 8723648
1283+Merritt 1002 28357832 8723648
1284+neonatal 1001 5987435 234724
1285+offload 1002 28357832 8723648
1286+parters 1002 28357832 8723648
1287+pityingly 1002 28357832 8723648
1288+puddings 1002 28357832 8723648
1289+Punjab 1001 5987435 234724
1290+quitter 1002 28357832 8723648
1291+realtor 1001 5987435 234724
1292+relaxing 1001 5987435 234724
1293+repetitions 1001 5987435 234724
1294+resumes 1001 5987435 234724
1295+Romans 1002 28357832 8723648
1296+rusting 1001 5987435 234724
1297+scholastics 1001 5987435 234724
1298+skulking 1002 28357832 8723648
1299+stated 1002 28357832 8723648
1300+suites 1002 28357832 8723648
1301+sureties 1001 5987435 234724
1302+testicle 1002 28357832 8723648
1303+tinily 1002 28357832 8723648
1304+tragedies 1001 5987435 234724
1305+trimmings 1001 5987435 234724
1306+vacuuming 1001 5987435 234724
1307+ventilate 1001 5987435 234724
1308+wallet 1001 5987435 234724
1309+Weissmuller 1002 28357832 8723648
1310+Wotan 1002 28357832 8723648
1311+select t2.fld1,fld3,period,price,price2 from t2,t3 where t2.fld1>= 18201 and t2.fld1 <= 18811 and t2.fld1=t3.t2nr and period = 1001 and t2.companynr = 37;
1312+fld1 fld3 period price price2
1313+018201 relaxing 1001 5987435 234724
1314+018601 vacuuming 1001 5987435 234724
1315+018801 inch 1001 5987435 234724
1316+018811 repetitions 1001 5987435 234724
1317+create table t4 (
1318+companynr tinyint(2) unsigned zerofill NOT NULL default '00',
1319+companyname char(30) NOT NULL default '',
1320+PRIMARY KEY (companynr),
1321+UNIQUE KEY companyname(companyname)
1322+) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';
1323+select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1324+companynr companyname
1325+00 Unknown
1326+29 company 1
1327+34 company 2
1328+36 company 3
1329+37 company 4
1330+40 company 5
1331+41 company 6
1332+50 company 11
1333+53 company 7
1334+58 company 8
1335+65 company 9
1336+68 company 10
1337+select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1338+companynr companyname
1339+00 Unknown
1340+29 company 1
1341+34 company 2
1342+36 company 3
1343+37 company 4
1344+40 company 5
1345+41 company 6
1346+50 company 11
1347+53 company 7
1348+58 company 8
1349+65 company 9
1350+68 company 10
1351+select * from t1,t1 t12;
1352+Period Varor_period Period Varor_period
1353+9410 9412 9410 9412
1354+select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505;
1355+fld1 fld1
1356+250501 250501
1357+250502 250501
1358+250503 250501
1359+250504 250501
1360+250505 250501
1361+250501 250502
1362+250502 250502
1363+250503 250502
1364+250504 250502
1365+250505 250502
1366+250501 250503
1367+250502 250503
1368+250503 250503
1369+250504 250503
1370+250505 250503
1371+250501 250504
1372+250502 250504
1373+250503 250504
1374+250504 250504
1375+250505 250504
1376+250501 250505
1377+250502 250505
1378+250503 250505
1379+250504 250505
1380+250505 250505
1381+insert into t2 (fld1, companynr) values (999999,99);
1382+select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1383+companynr companyname
1384+99 NULL
1385+select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
1386+count(*)
1387+1199
1388+explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1389+id select_type table type possible_keys key key_len ref rows Extra
1390+1 SIMPLE t2 ALL NULL NULL NULL NULL 1200
1391+1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists
1392+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
1393+id select_type table type possible_keys key key_len ref rows Extra
1394+1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1395+1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists
1396+select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1397+companynr companyname
1398+select count(*) from t2 left join t4 using (companynr) where companynr is not null;
1399+count(*)
1400+1200
1401+explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1402+id select_type table type possible_keys key key_len ref rows Extra
1403+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1404+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
1405+id select_type table type possible_keys key key_len ref rows Extra
1406+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1407+delete from t2 where fld1=999999;
1408+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
1409+id select_type table type possible_keys key key_len ref rows Extra
1410+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1411+1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1412+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
1413+id select_type table type possible_keys key key_len ref rows Extra
1414+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1415+1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1416+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
1417+id select_type table type possible_keys key key_len ref rows Extra
1418+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1419+1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1420+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
1421+id select_type table type possible_keys key key_len ref rows Extra
1422+1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1423+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1424+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
1425+id select_type table type possible_keys key key_len ref rows Extra
1426+1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1427+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1428+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
1429+id select_type table type possible_keys key key_len ref rows Extra
1430+1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1431+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1432+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
1433+id select_type table type possible_keys key key_len ref rows Extra
1434+1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1435+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1436+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
1437+id select_type table type possible_keys key key_len ref rows Extra
1438+1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12
1439+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1440+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
1441+id select_type table type possible_keys key key_len ref rows Extra
1442+1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1443+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1444+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
1445+id select_type table type possible_keys key key_len ref rows Extra
1446+1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1447+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1448+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
1449+id select_type table type possible_keys key key_len ref rows Extra
1450+1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1451+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1452+explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
1453+id select_type table type possible_keys key key_len ref rows Extra
1454+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where
1455+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1456+select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1457+companynr companynr
1458+37 36
1459+41 40
1460+explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1461+id select_type table type possible_keys key key_len ref rows Extra
1462+1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary
1463+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer
1464+select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
1465+fld1 companynr fld3 period
1466+038008 37 reporters 1008
1467+038208 37 Selfridge 1008
1468+select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t2.fld1 = 38208 or t2.fld1 = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;
1469+fld1 companynr fld3 period
1470+038008 37 reporters 1008
1471+038208 37 Selfridge 1008
1472+select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t3.t2nr = 38208 or t3.t2nr = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;
1473+fld1 companynr fld3 period
1474+038008 37 reporters 1008
1475+038208 37 Selfridge 1008
1476+select period from t1 where (((period > 0) or period < 10000 or (period = 1900)) and (period=1900 and period <= 1901) or (period=1903 and (period=1903)) and period>=1902) or ((period=1904 or period=1905) or (period=1906 or period>1907)) or (period=1908 and period = 1909);
1477+period
1478+9410
1479+select period from t1 where ((period > 0 and period < 1) or (((period > 0 and period < 100) and (period > 10)) or (period > 10)) or (period > 0 and (period > 5 or period > 6)));
1480+period
1481+9410
1482+select a.fld1 from t2 as a,t2 b where ((a.fld1 = 250501 and a.fld1=b.fld1) or a.fld1=250502 or a.fld1=250503 or (a.fld1=250505 and a.fld1<=b.fld1 and b.fld1>=a.fld1)) and a.fld1=b.fld1;
1483+fld1
1484+250501
1485+250502
1486+250503
1487+250505
1488+select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606);
1489+fld1
1490+250502
1491+250503
1492+select fld1 from t2 where fld1 between 250502 and 250504;
1493+fld1
1494+250502
1495+250503
1496+250504
1497+select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ;
1498+fld3
1499+label
1500+labeled
1501+labeled
1502+landslide
1503+laterally
1504+leaflet
1505+lewdly
1506+Lillian
1507+luckily
1508+select count(*) from t1;
1509+count(*)
1510+1
1511+select companynr,count(*),sum(fld1) from t2 group by companynr;
1512+companynr count(*) sum(fld1)
1513+00 82 10355753
1514+29 95 14473298
1515+34 70 17788966
1516+36 215 22786296
1517+37 588 83602098
1518+40 37 6618386
1519+41 52 12816335
1520+50 11 1595438
1521+53 4 793210
1522+58 23 2254293
1523+65 10 2284055
1524+68 12 3097288
1525+select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;
1526+companynr count(*)
1527+68 12
1528+65 10
1529+58 23
1530+53 4
1531+50 11
1532+select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1533+count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1534+70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1535+explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1536+id select_type table type possible_keys key key_len ref rows filtered Extra
1537+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where
1538+Warnings:
1539+Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))
1540+select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
1541+companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1542+00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087
1543+29 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026
1544+34 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1545+select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1546+companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1547+37 1 1 5987435 5987435 5987435 5987435.0000
1548+37 2 1 28357832 28357832 28357832 28357832.0000
1549+37 3 1 39654943 39654943 39654943 39654943.0000
1550+37 11 1 5987435 5987435 5987435 5987435.0000
1551+37 12 1 28357832 28357832 28357832 28357832.0000
1552+37 13 1 39654943 39654943 39654943 39654943.0000
1553+37 21 1 5987435 5987435 5987435 5987435.0000
1554+37 22 1 28357832 28357832 28357832 28357832.0000
1555+37 23 1 39654943 39654943 39654943 39654943.0000
1556+37 31 1 5987435 5987435 5987435 5987435.0000
1557+select /*! SQL_SMALL_RESULT */ companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1558+companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1559+37 1 1 5987435 5987435 5987435 5987435.0000
1560+37 2 1 28357832 28357832 28357832 28357832.0000
1561+37 3 1 39654943 39654943 39654943 39654943.0000
1562+37 11 1 5987435 5987435 5987435 5987435.0000
1563+37 12 1 28357832 28357832 28357832 28357832.0000
1564+37 13 1 39654943 39654943 39654943 39654943.0000
1565+37 21 1 5987435 5987435 5987435 5987435.0000
1566+37 22 1 28357832 28357832 28357832 28357832.0000
1567+37 23 1 39654943 39654943 39654943 39654943.0000
1568+37 31 1 5987435 5987435 5987435 5987435.0000
1569+select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ;
1570+companynr count(price) sum(price) min(price) max(price) avg(price)
1571+37 12543 309394878010 5987435 39654943 24666736.6667
1572+78 8362 414611089292 726498 98439034 49582766.0000
1573+101 4181 3489454238 834598 834598 834598.0000
1574+154 4181 4112197254950 983543950 983543950 983543950.0000
1575+311 4181 979599938 234298 234298 234298.0000
1576+447 4181 9929180954 2374834 2374834 2374834.0000
1577+512 4181 3288532102 786542 786542 786542.0000
1578+select distinct mod(companynr,10) from t4 group by companynr;
1579+mod(companynr,10)
1580+0
1581+9
1582+4
1583+6
1584+7
1585+1
1586+3
1587+8
1588+5
1589+select distinct 1 from t4 group by companynr;
1590+1
1591+1
1592+select count(distinct fld1) from t2;
1593+count(distinct fld1)
1594+1199
1595+select companynr,count(distinct fld1) from t2 group by companynr;
1596+companynr count(distinct fld1)
1597+00 82
1598+29 95
1599+34 70
1600+36 215
1601+37 588
1602+40 37
1603+41 52
1604+50 11
1605+53 4
1606+58 23
1607+65 10
1608+68 12
1609+select companynr,count(*) from t2 group by companynr;
1610+companynr count(*)
1611+00 82
1612+29 95
1613+34 70
1614+36 215
1615+37 588
1616+40 37
1617+41 52
1618+50 11
1619+53 4
1620+58 23
1621+65 10
1622+68 12
1623+select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr;
1624+companynr count(distinct concat(fld1,repeat(65,1000)))
1625+00 82
1626+29 95
1627+34 70
1628+36 215
1629+37 588
1630+40 37
1631+41 52
1632+50 11
1633+53 4
1634+58 23
1635+65 10
1636+68 12
1637+select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr;
1638+companynr count(distinct concat(fld1,repeat(65,200)))
1639+00 82
1640+29 95
1641+34 70
1642+36 215
1643+37 588
1644+40 37
1645+41 52
1646+50 11
1647+53 4
1648+58 23
1649+65 10
1650+68 12
1651+select companynr,count(distinct floor(fld1/100)) from t2 group by companynr;
1652+companynr count(distinct floor(fld1/100))
1653+00 47
1654+29 35
1655+34 14
1656+36 69
1657+37 108
1658+40 16
1659+41 11
1660+50 9
1661+53 1
1662+58 1
1663+65 1
1664+68 1
1665+select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr;
1666+companynr count(distinct concat(repeat(65,1000),floor(fld1/100)))
1667+00 47
1668+29 35
1669+34 14
1670+36 69
1671+37 108
1672+40 16
1673+41 11
1674+50 9
1675+53 1
1676+58 1
1677+65 1
1678+68 1
1679+select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10;
1680+sum(fld1) fld3
1681+11402 Romans
1682+select name,count(*) from t3 where name='cloakroom' group by name;
1683+name count(*)
1684+cloakroom 4181
1685+select name,count(*) from t3 where name='cloakroom' and price>10 group by name;
1686+name count(*)
1687+cloakroom 4181
1688+select count(*) from t3 where name='cloakroom' and price2=823742;
1689+count(*)
1690+4181
1691+select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name;
1692+name count(*)
1693+cloakroom 4181
1694+select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name;
1695+name count(*)
1696+extramarital 4181
1697+gazer 4181
1698+gems 4181
1699+Iranizes 4181
1700+spates 4181
1701+tucked 4181
1702+violinist 4181
1703+select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1704+fld3 count(*)
1705+spates 4181
1706+select companynr|0,companyname from t4 group by 1;
1707+companynr|0 companyname
1708+0 Unknown
1709+29 company 1
1710+34 company 2
1711+36 company 3
1712+37 company 4
1713+40 company 5
1714+41 company 6
1715+50 company 11
1716+53 company 7
1717+58 company 8
1718+65 company 9
1719+68 company 10
1720+select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname;
1721+companynr companyname count(*)
1722+29 company 1 95
1723+68 company 10 12
1724+50 company 11 11
1725+34 company 2 70
1726+36 company 3 215
1727+37 company 4 588
1728+40 company 5 37
1729+41 company 6 52
1730+53 company 7 4
1731+58 company 8 23
1732+65 company 9 10
1733+00 Unknown 82
1734+select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1735+fld1 count(*)
1736+158402 4181
1737+select sum(Period)/count(*) from t1;
1738+sum(Period)/count(*)
1739+9410.0000
1740+select companynr,count(price) as "count",sum(price) as "sum" ,abs(sum(price)/count(price)-avg(price)) as "diff",(0+count(price))*companynr as func from t3 group by companynr;
1741+companynr count sum diff func
1742+37 12543 309394878010 0.0000 464091
1743+78 8362 414611089292 0.0000 652236
1744+101 4181 3489454238 0.0000 422281
1745+154 4181 4112197254950 0.0000 643874
1746+311 4181 979599938 0.0000 1300291
1747+447 4181 9929180954 0.0000 1868907
1748+512 4181 3288532102 0.0000 2140672
1749+select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg;
1750+companynr avg
1751+154 983543950.0000
1752+select companynr,count(*) from t2 group by companynr order by 2 desc;
1753+companynr count(*)
1754+37 588
1755+36 215
1756+29 95
1757+00 82
1758+34 70
1759+41 52
1760+40 37
1761+58 23
1762+68 12
1763+50 11
1764+65 10
1765+53 4
1766+select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc;
1767+companynr count(*)
1768+41 52
1769+58 23
1770+68 12
1771+50 11
1772+65 10
1773+53 4
1774+select t2.fld4,t2.fld1,count(price),sum(price),min(price),max(price),avg(price) from t3,t2 where t3.companynr = 37 and t2.fld1 = t3.t2nr group by fld1,t2.fld4;
1775+fld4 fld1 count(price) sum(price) min(price) max(price) avg(price)
1776+teethe 000001 1 5987435 5987435 5987435 5987435.0000
1777+dreaded 011401 1 5987435 5987435 5987435 5987435.0000
1778+scholastics 011402 1 28357832 28357832 28357832 28357832.0000
1779+audiology 011403 1 39654943 39654943 39654943 39654943.0000
1780+wallet 011501 1 5987435 5987435 5987435 5987435.0000
1781+parters 011701 1 5987435 5987435 5987435 5987435.0000
1782+eschew 011702 1 28357832 28357832 28357832 28357832.0000
1783+quitter 011703 1 39654943 39654943 39654943 39654943.0000
1784+neat 012001 1 5987435 5987435 5987435 5987435.0000
1785+Steinberg 012003 1 39654943 39654943 39654943 39654943.0000
1786+balled 012301 1 5987435 5987435 5987435 5987435.0000
1787+persist 012302 1 28357832 28357832 28357832 28357832.0000
1788+attainments 012303 1 39654943 39654943 39654943 39654943.0000
1789+capably 012501 1 5987435 5987435 5987435 5987435.0000
1790+impulsive 012602 1 28357832 28357832 28357832 28357832.0000
1791+starlet 012603 1 39654943 39654943 39654943 39654943.0000
1792+featherweight 012701 1 5987435 5987435 5987435 5987435.0000
1793+pessimist 012702 1 28357832 28357832 28357832 28357832.0000
1794+daughter 012703 1 39654943 39654943 39654943 39654943.0000
1795+lawgiver 013601 1 5987435 5987435 5987435 5987435.0000
1796+stated 013602 1 28357832 28357832 28357832 28357832.0000
1797+readable 013603 1 39654943 39654943 39654943 39654943.0000
1798+testicle 013801 1 5987435 5987435 5987435 5987435.0000
1799+Parsifal 013802 1 28357832 28357832 28357832 28357832.0000
1800+leavings 013803 1 39654943 39654943 39654943 39654943.0000
1801+squeaking 013901 1 5987435 5987435 5987435 5987435.0000
1802+contrasted 016001 1 5987435 5987435 5987435 5987435.0000
1803+leftover 016201 1 5987435 5987435 5987435 5987435.0000
1804+whiteners 016202 1 28357832 28357832 28357832 28357832.0000
1805+erases 016301 1 5987435 5987435 5987435 5987435.0000
1806+Punjab 016302 1 28357832 28357832 28357832 28357832.0000
1807+Merritt 016303 1 39654943 39654943 39654943 39654943.0000
1808+sweetish 018001 1 5987435 5987435 5987435 5987435.0000
1809+dogging 018002 1 28357832 28357832 28357832 28357832.0000
1810+scornfully 018003 1 39654943 39654943 39654943 39654943.0000
1811+fetters 018012 1 28357832 28357832 28357832 28357832.0000
1812+bivalves 018013 1 39654943 39654943 39654943 39654943.0000
1813+skulking 018021 1 5987435 5987435 5987435 5987435.0000
1814+flint 018022 1 28357832 28357832 28357832 28357832.0000
1815+flopping 018023 1 39654943 39654943 39654943 39654943.0000
1816+Judas 018032 1 28357832 28357832 28357832 28357832.0000
1817+vacuuming 018033 1 39654943 39654943 39654943 39654943.0000
1818+medical 018041 1 5987435 5987435 5987435 5987435.0000
1819+bloodbath 018042 1 28357832 28357832 28357832 28357832.0000
1820+subschema 018043 1 39654943 39654943 39654943 39654943.0000
1821+interdependent 018051 1 5987435 5987435 5987435 5987435.0000
1822+Graves 018052 1 28357832 28357832 28357832 28357832.0000
1823+neonatal 018053 1 39654943 39654943 39654943 39654943.0000
1824+sorters 018061 1 5987435 5987435 5987435 5987435.0000
1825+epistle 018062 1 28357832 28357832 28357832 28357832.0000
1826+Conley 018101 1 5987435 5987435 5987435 5987435.0000
1827+lectured 018102 1 28357832 28357832 28357832 28357832.0000
1828+Abraham 018103 1 39654943 39654943 39654943 39654943.0000
1829+cage 018201 1 5987435 5987435 5987435 5987435.0000
1830+hushes 018202 1 28357832 28357832 28357832 28357832.0000
1831+Simla 018402 1 28357832 28357832 28357832 28357832.0000
1832+reporters 018403 1 39654943 39654943 39654943 39654943.0000
1833+coexist 018601 1 5987435 5987435 5987435 5987435.0000
1834+Beebe 018602 1 28357832 28357832 28357832 28357832.0000
1835+Taoism 018603 1 39654943 39654943 39654943 39654943.0000
1836+Connally 018801 1 5987435 5987435 5987435 5987435.0000
1837+fetched 018802 1 28357832 28357832 28357832 28357832.0000
1838+checkpoints 018803 1 39654943 39654943 39654943 39654943.0000
1839+gritty 018811 1 5987435 5987435 5987435 5987435.0000
1840+firearm 018812 1 28357832 28357832 28357832 28357832.0000
1841+minima 019101 1 5987435 5987435 5987435 5987435.0000
1842+Selfridge 019102 1 28357832 28357832 28357832 28357832.0000
1843+disable 019103 1 39654943 39654943 39654943 39654943.0000
1844+witchcraft 019201 1 5987435 5987435 5987435 5987435.0000
1845+betroth 030501 1 5987435 5987435 5987435 5987435.0000
1846+Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000
1847+imprint 030503 1 39654943 39654943 39654943 39654943.0000
1848+swelling 031901 1 5987435 5987435 5987435 5987435.0000
1849+interrelationships 036001 1 5987435 5987435 5987435 5987435.0000
1850+riser 036002 1 28357832 28357832 28357832 28357832.0000
1851+bee 038001 1 5987435 5987435 5987435 5987435.0000
1852+kanji 038002 1 28357832 28357832 28357832 28357832.0000
1853+dental 038003 1 39654943 39654943 39654943 39654943.0000
1854+railway 038011 1 5987435 5987435 5987435 5987435.0000
1855+validate 038012 1 28357832 28357832 28357832 28357832.0000
1856+normalizes 038013 1 39654943 39654943 39654943 39654943.0000
1857+Kline 038101 1 5987435 5987435 5987435 5987435.0000
1858+Anatole 038102 1 28357832 28357832 28357832 28357832.0000
1859+partridges 038103 1 39654943 39654943 39654943 39654943.0000
1860+recruited 038201 1 5987435 5987435 5987435 5987435.0000
1861+dimensions 038202 1 28357832 28357832 28357832 28357832.0000
1862+Chicana 038203 1 39654943 39654943 39654943 39654943.0000
1863+select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3;
1864+companynr fld3 sum(price)
1865+512 boat 786542
1866+512 capably 786542
1867+512 cupboard 786542
1868+512 decliner 786542
1869+512 descendants 786542
1870+512 dopers 786542
1871+512 erases 786542
1872+512 Micronesia 786542
1873+512 Miles 786542
1874+512 skies 786542
1875+select t2.companynr,count(*),min(fld3),max(fld3),sum(price),avg(price) from t2,t3 where t3.companynr >= 30 and t3.companynr <= 58 and t3.t2nr = t2.fld1 and 1+1=2 group by t2.companynr;
1876+companynr count(*) min(fld3) max(fld3) sum(price) avg(price)
1877+00 1 Omaha Omaha 5987435 5987435.0000
1878+36 1 dubbed dubbed 28357832 28357832.0000
1879+37 83 Abraham Wotan 1908978016 22999735.1325
1880+50 2 scribbled tapestry 68012775 34006387.5000
1881+select t3.companynr+0,t3.t2nr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 37 group by 1,t3.t2nr,fld3,fld3,fld3,fld3,fld3 order by fld1;
1882+t3.companynr+0 t2nr fld3 sum(price)
1883+37 1 Omaha 5987435
1884+37 11401 breaking 5987435
1885+37 11402 Romans 28357832
1886+37 11403 intercepted 39654943
1887+37 11501 bewilderingly 5987435
1888+37 11701 astound 5987435
1889+37 11702 admonishing 28357832
1890+37 11703 sumac 39654943
1891+37 12001 flanking 5987435
1892+37 12003 combed 39654943
1893+37 12301 Eulerian 5987435
1894+37 12302 dubbed 28357832
1895+37 12303 Kane 39654943
1896+37 12501 annihilates 5987435
1897+37 12602 Wotan 28357832
1898+37 12603 snatching 39654943
1899+37 12701 grazing 5987435
1900+37 12702 Baird 28357832
1901+37 12703 celery 39654943
1902+37 13601 handgun 5987435
1903+37 13602 foldout 28357832
1904+37 13603 mystic 39654943
1905+37 13801 intelligibility 5987435
1906+37 13802 Augustine 28357832
1907+37 13803 teethe 39654943
1908+37 13901 scholastics 5987435
1909+37 16001 audiology 5987435
1910+37 16201 wallet 5987435
1911+37 16202 parters 28357832
1912+37 16301 eschew 5987435
1913+37 16302 quitter 28357832
1914+37 16303 neat 39654943
1915+37 18001 jarring 5987435
1916+37 18002 tinily 28357832
1917+37 18003 balled 39654943
1918+37 18012 impulsive 28357832
1919+37 18013 starlet 39654943
1920+37 18021 lawgiver 5987435
1921+37 18022 stated 28357832
1922+37 18023 readable 39654943
1923+37 18032 testicle 28357832
1924+37 18033 Parsifal 39654943
1925+37 18041 Punjab 5987435
1926+37 18042 Merritt 28357832
1927+37 18043 Quixotism 39654943
1928+37 18051 sureties 5987435
1929+37 18052 puddings 28357832
1930+37 18053 tapestry 39654943
1931+37 18061 trimmings 5987435
1932+37 18062 humility 28357832
1933+37 18101 tragedies 5987435
1934+37 18102 skulking 28357832
1935+37 18103 flint 39654943
1936+37 18201 relaxing 5987435
1937+37 18202 offload 28357832
1938+37 18402 suites 28357832
1939+37 18403 lists 39654943
1940+37 18601 vacuuming 5987435
1941+37 18602 dentally 28357832
1942+37 18603 humanness 39654943
1943+37 18801 inch 5987435
1944+37 18802 Weissmuller 28357832
1945+37 18803 irresponsibly 39654943
1946+37 18811 repetitions 5987435
1947+37 18812 Antares 28357832
1948+37 19101 ventilate 5987435
1949+37 19102 pityingly 28357832
1950+37 19103 interdependent 39654943
1951+37 19201 Graves 5987435
1952+37 30501 neonatal 5987435
1953+37 30502 scribbled 28357832
1954+37 30503 chafe 39654943
1955+37 31901 realtor 5987435
1956+37 36001 elite 5987435
1957+37 36002 funereal 28357832
1958+37 38001 Conley 5987435
1959+37 38002 lectured 28357832
1960+37 38003 Abraham 39654943
1961+37 38011 groupings 5987435
1962+37 38012 dissociate 28357832
1963+37 38013 coexist 39654943
1964+37 38101 rusting 5987435
1965+37 38102 galling 28357832
1966+37 38103 obliterates 39654943
1967+37 38201 resumes 5987435
1968+37 38202 analyzable 28357832
1969+37 38203 terminator 39654943
1970+select sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1= t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008;
1971+sum(price)
1972+234298
1973+select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1 = t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008 or t3.t2nr = t2.fld1 and t2.fld1 = 38008 group by t2.fld1;
1974+fld1 sum(price)
1975+038008 234298
1976+explain select fld3 from t2 where 1>2 or 2>3;
1977+id select_type table type possible_keys key key_len ref rows Extra
1978+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1979+explain select fld3 from t2 where fld1=fld1;
1980+id select_type table type possible_keys key key_len ref rows Extra
1981+1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1982+select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;
1983+companynr fld1
1984+34 250501
1985+34 250502
1986+select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502;
1987+companynr fld1
1988+34 250501
1989+34 250502
1990+select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000;
1991+companynr count sum
1992+00 82 10355753
1993+29 95 14473298
1994+34 70 17788966
1995+37 588 83602098
1996+41 52 12816335
1997+select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ;
1998+companynr
1999+00
2000+29
2001+34
2002+37
2003+41
2004+select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40;
2005+companynr companyname count(*)
2006+68 company 10 12
2007+50 company 11 11
2008+40 company 5 37
2009+41 company 6 52
2010+53 company 7 4
2011+58 company 8 23
2012+65 company 9 10
2013+select count(*) from t2;
2014+count(*)
2015+1199
2016+select count(*) from t2 where fld1 < 098024;
2017+count(*)
2018+387
2019+select min(fld1) from t2 where fld1>= 098024;
2020+min(fld1)
2021+98024
2022+select max(fld1) from t2 where fld1>= 098024;
2023+max(fld1)
2024+1232609
2025+select count(*) from t3 where price2=76234234;
2026+count(*)
2027+4181
2028+select count(*) from t3 where companynr=512 and price2=76234234;
2029+count(*)
2030+4181
2031+explain select min(fld1),max(fld1),count(*) from t2;
2032+id select_type table type possible_keys key key_len ref rows Extra
2033+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2034+select min(fld1),max(fld1),count(*) from t2;
2035+min(fld1) max(fld1) count(*)
2036+0 1232609 1199
2037+select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742;
2038+min(t2nr) max(t2nr)
2039+2115 2115
2040+select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78;
2041+count(*) min(t2nr) max(t2nr)
2042+4181 4 41804
2043+select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20;
2044+t2nr count(*)
2045+9 1
2046+19 1
2047+29 1
2048+39 1
2049+49 1
2050+59 1
2051+69 1
2052+79 1
2053+89 1
2054+99 1
2055+109 1
2056+119 1
2057+129 1
2058+139 1
2059+149 1
2060+159 1
2061+169 1
2062+179 1
2063+189 1
2064+199 1
2065+select max(t2nr) from t3 where price=983543950;
2066+max(t2nr)
2067+41807
2068+select t1.period from t3 = t1 limit 1;
2069+period
2070+1001
2071+select t1.period from t1 as t1 limit 1;
2072+period
2073+9410
2074+select t1.period as "Nuvarande period" from t1 as t1 limit 1;
2075+Nuvarande period
2076+9410
2077+select period as ok_period from t1 limit 1;
2078+ok_period
2079+9410
2080+select period as ok_period from t1 group by ok_period limit 1;
2081+ok_period
2082+9410
2083+select 1+1 as summa from t1 group by summa limit 1;
2084+summa
2085+2
2086+select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1;
2087+Nuvarande period
2088+9410
2089+show tables;
2090+Tables_in_test
2091+t1
2092+t2
2093+t3
2094+t4
2095+show tables from test like "s%";
2096+Tables_in_test (s%)
2097+show tables from test like "t?";
2098+Tables_in_test (t?)
2099+show full columns from t2;
2100+Field Type Collation Null Key Default Extra Privileges Comment
2101+auto int(11) NULL NO PRI NULL auto_increment #
2102+fld1 int(6) unsigned zerofill NULL NO UNI 000000 #
2103+companynr tinyint(2) unsigned zerofill NULL NO 00 #
2104+fld3 char(30) latin1_swedish_ci NO MUL #
2105+fld4 char(35) latin1_swedish_ci NO #
2106+fld5 char(35) latin1_swedish_ci NO #
2107+fld6 char(4) latin1_swedish_ci NO #
2108+show full columns from t2 from test like 'f%';
2109+Field Type Collation Null Key Default Extra Privileges Comment
2110+fld1 int(6) unsigned zerofill NULL NO UNI 000000 #
2111+fld3 char(30) latin1_swedish_ci NO MUL #
2112+fld4 char(35) latin1_swedish_ci NO #
2113+fld5 char(35) latin1_swedish_ci NO #
2114+fld6 char(4) latin1_swedish_ci NO #
2115+show full columns from t2 from test like 's%';
2116+Field Type Collation Null Key Default Extra Privileges Comment
2117+show keys from t2;
2118+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
2119+t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE
2120+t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE
2121+t2 1 fld3 1 fld3 A NULL NULL NULL BTREE
2122+drop table t4, t3, t2, t1;
2123+CREATE TABLE t1 (
2124+cont_nr int(11) NOT NULL auto_increment,
2125+ver_nr int(11) NOT NULL default '0',
2126+aufnr int(11) NOT NULL default '0',
2127+username varchar(50) NOT NULL default '',
2128+hdl_nr int(11) NOT NULL default '0',
2129+eintrag date NOT NULL default '0000-00-00',
2130+st_klasse varchar(40) NOT NULL default '',
2131+st_wert varchar(40) NOT NULL default '',
2132+st_zusatz varchar(40) NOT NULL default '',
2133+st_bemerkung varchar(255) NOT NULL default '',
2134+kunden_art varchar(40) NOT NULL default '',
2135+mcbs_knr int(11) default NULL,
2136+mcbs_aufnr int(11) NOT NULL default '0',
2137+schufa_status char(1) default '?',
2138+bemerkung text,
2139+wirknetz text,
2140+wf_igz int(11) NOT NULL default '0',
2141+tarifcode varchar(80) default NULL,
2142+recycle char(1) default NULL,
2143+sim varchar(30) default NULL,
2144+mcbs_tpl varchar(30) default NULL,
2145+emp_nr int(11) NOT NULL default '0',
2146+laufzeit int(11) default NULL,
2147+hdl_name varchar(30) default NULL,
2148+prov_hdl_nr int(11) NOT NULL default '0',
2149+auto_wirknetz varchar(50) default NULL,
2150+auto_billing varchar(50) default NULL,
2151+touch timestamp NOT NULL,
2152+kategorie varchar(50) default NULL,
2153+kundentyp varchar(20) NOT NULL default '',
2154+sammel_rech_msisdn varchar(30) NOT NULL default '',
2155+p_nr varchar(9) NOT NULL default '',
2156+suffix char(3) NOT NULL default '',
2157+PRIMARY KEY (cont_nr),
2158+KEY idx_aufnr(aufnr),
2159+KEY idx_hdl_nr(hdl_nr),
2160+KEY idx_st_klasse(st_klasse),
2161+KEY ver_nr(ver_nr),
2162+KEY eintrag_idx(eintrag),
2163+KEY emp_nr_idx(emp_nr),
2164+KEY wf_igz(wf_igz),
2165+KEY touch(touch),
2166+KEY hdl_tag(eintrag,hdl_nr),
2167+KEY prov_hdl_nr(prov_hdl_nr),
2168+KEY mcbs_aufnr(mcbs_aufnr),
2169+KEY kundentyp(kundentyp),
2170+KEY p_nr(p_nr,suffix)
2171+) ENGINE=MyISAM;
2172+INSERT INTO t1 VALUES (3359356,405,3359356,'Mustermann Musterfrau',52500,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1485525,2122316,'+','','N',1909160,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',3,24,'MobilCom Shop Koeln',52500,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2173+INSERT INTO t1 VALUES (3359357,468,3359357,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1503580,2139699,'+','','P',1909171,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2174+INSERT INTO t1 VALUES (3359358,407,3359358,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1501358,2137473,'N','','N',1909159,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2175+INSERT INTO t1 VALUES (3359359,468,3359359,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1507831,2143894,'+','','P',1909162,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2176+INSERT INTO t1 VALUES (3359360,0,0,'Mustermann Musterfrau',29674907,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1900169997,2414578,'+',NULL,'N',1909148,'',NULL,NULL,'RV99066_2',20,NULL,'POS',29674907,NULL,NULL,20010202105916,'Mobilfunk','','','97317481','007');
2177+INSERT INTO t1 VALUES (3359361,406,3359361,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag storniert','','(7001-84):Storno, Kd. möchte nicht mehr','privat',NULL,0,'+','','P',1909150,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2178+INSERT INTO t1 VALUES (3359362,406,3359362,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1509984,2145874,'+','','P',1909154,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2179+SELECT ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA','V','VA',''), 'Privat (Private Nutzung)','Privat (Private Nutzung) Sitz im Ausland','Privat (geschaeftliche Nutzung)','Privat (geschaeftliche Nutzung) Sitz im Ausland','Firma (Kapitalgesellschaft)','Firma (Kapitalgesellschaft) Sitz im Ausland','Firma (Personengesellschaft)','Firma (Personengesellschaft) Sitz im Ausland','oeff. rechtl. Koerperschaft','oeff. rechtl. Koerperschaft Sitz im Ausland','Eingetragener Verein','Eingetragener Verein Sitz im Ausland','Typ unbekannt') AS Kundentyp ,kategorie FROM t1 WHERE hdl_nr < 2000000 AND kategorie IN ('Prepaid','Mobilfunk') AND st_klasse = 'Workflow' GROUP BY kundentyp ORDER BY kategorie;
2180+Kundentyp kategorie
2181+Privat (Private Nutzung) Mobilfunk
2182+Warnings:
2183+Warning 1052 Column 'kundentyp' in group statement is ambiguous
2184+drop table t1;
2185+CREATE TABLE t1(a INT);
2186+START TRANSACTION;
2187+INSERT INTO t1 VALUES (1);
2188+SELECT * FROM t1;
2189+a
2190+1
2191+INSERT INTO t1 VALUES (2);
2192+SELECT * FROM t1;
2193+a
2194+1
2195+2
2196+COMMIT;
2197+DROP TABLE t1;
2198
2199=== added file 'Percona-Server/mysql-test/suite/sys_vars/r/thread_pool_high_prio_tickets_basic.result'
2200--- Percona-Server/mysql-test/suite/sys_vars/r/thread_pool_high_prio_tickets_basic.result 1970-01-01 00:00:00 +0000
2201+++ Percona-Server/mysql-test/suite/sys_vars/r/thread_pool_high_prio_tickets_basic.result 2013-04-03 10:29:25 +0000
2202@@ -0,0 +1,47 @@
2203+SET @start_global_value = @@global.thread_pool_high_prio_tickets;
2204+select @@global.thread_pool_high_prio_tickets;
2205+@@global.thread_pool_high_prio_tickets
2206+0
2207+select @@session.thread_pool_high_prio_tickets;
2208+ERROR HY000: Variable 'thread_pool_high_prio_tickets' is a GLOBAL variable
2209+show global variables like 'thread_pool_high_prio_tickets';
2210+Variable_name Value
2211+thread_pool_high_prio_tickets 0
2212+show session variables like 'thread_pool_high_prio_tickets';
2213+Variable_name Value
2214+thread_pool_high_prio_tickets 0
2215+select * from information_schema.global_variables where variable_name='thread_pool_high_prio_tickets';
2216+VARIABLE_NAME VARIABLE_VALUE
2217+THREAD_POOL_HIGH_PRIO_TICKETS 0
2218+select * from information_schema.session_variables where variable_name='thread_pool_high_prio_tickets';
2219+VARIABLE_NAME VARIABLE_VALUE
2220+THREAD_POOL_HIGH_PRIO_TICKETS 0
2221+set global thread_pool_high_prio_tickets=60;
2222+select @@global.thread_pool_high_prio_tickets;
2223+@@global.thread_pool_high_prio_tickets
2224+60
2225+set global thread_pool_high_prio_tickets=4294967295;
2226+select @@global.thread_pool_high_prio_tickets;
2227+@@global.thread_pool_high_prio_tickets
2228+4294967295
2229+set session thread_pool_high_prio_tickets=1;
2230+ERROR HY000: Variable 'thread_pool_high_prio_tickets' is a GLOBAL variable and should be set with SET GLOBAL
2231+set global thread_pool_high_prio_tickets=1.1;
2232+ERROR 42000: Incorrect argument type to variable 'thread_pool_high_prio_tickets'
2233+set global thread_pool_high_prio_tickets=1e1;
2234+ERROR 42000: Incorrect argument type to variable 'thread_pool_high_prio_tickets'
2235+set global thread_pool_high_prio_tickets="foo";
2236+ERROR 42000: Incorrect argument type to variable 'thread_pool_high_prio_tickets'
2237+set global thread_pool_high_prio_tickets=-1;
2238+Warnings:
2239+Warning 1292 Truncated incorrect thread_pool_high_prio_tickets value: '-1'
2240+select @@global.thread_pool_high_prio_tickets;
2241+@@global.thread_pool_high_prio_tickets
2242+0
2243+set global thread_pool_high_prio_tickets=10000000000;
2244+Warnings:
2245+Warning 1292 Truncated incorrect thread_pool_high_prio_tickets value: '10000000000'
2246+select @@global.thread_pool_high_prio_tickets;
2247+@@global.thread_pool_high_prio_tickets
2248+4294967295
2249+set @@global.thread_pool_high_prio_tickets = @start_global_value;
2250
2251=== added file 'Percona-Server/mysql-test/suite/sys_vars/t/thread_pool_high_prio_tickets_basic.test'
2252--- Percona-Server/mysql-test/suite/sys_vars/t/thread_pool_high_prio_tickets_basic.test 1970-01-01 00:00:00 +0000
2253+++ Percona-Server/mysql-test/suite/sys_vars/t/thread_pool_high_prio_tickets_basic.test 2013-04-03 10:29:25 +0000
2254@@ -0,0 +1,43 @@
2255+# uint global
2256+--source include/not_windows.inc
2257+--source include/not_embedded.inc
2258+SET @start_global_value = @@global.thread_pool_high_prio_tickets;
2259+
2260+#
2261+# exists as global only
2262+#
2263+select @@global.thread_pool_high_prio_tickets;
2264+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
2265+select @@session.thread_pool_high_prio_tickets;
2266+show global variables like 'thread_pool_high_prio_tickets';
2267+show session variables like 'thread_pool_high_prio_tickets';
2268+select * from information_schema.global_variables where variable_name='thread_pool_high_prio_tickets';
2269+select * from information_schema.session_variables where variable_name='thread_pool_high_prio_tickets';
2270+
2271+#
2272+# show that it's writable
2273+#
2274+set global thread_pool_high_prio_tickets=60;
2275+select @@global.thread_pool_high_prio_tickets;
2276+set global thread_pool_high_prio_tickets=4294967295;
2277+select @@global.thread_pool_high_prio_tickets;
2278+--error ER_GLOBAL_VARIABLE
2279+set session thread_pool_high_prio_tickets=1;
2280+
2281+#
2282+# incorrect types
2283+#
2284+--error ER_WRONG_TYPE_FOR_VAR
2285+set global thread_pool_high_prio_tickets=1.1;
2286+--error ER_WRONG_TYPE_FOR_VAR
2287+set global thread_pool_high_prio_tickets=1e1;
2288+--error ER_WRONG_TYPE_FOR_VAR
2289+set global thread_pool_high_prio_tickets="foo";
2290+
2291+
2292+set global thread_pool_high_prio_tickets=-1;
2293+select @@global.thread_pool_high_prio_tickets;
2294+set global thread_pool_high_prio_tickets=10000000000;
2295+select @@global.thread_pool_high_prio_tickets;
2296+
2297+set @@global.thread_pool_high_prio_tickets = @start_global_value;
2298
2299=== added file 'Percona-Server/mysql-test/t/pool_of_threads_high_prio_tickets.cnf'
2300--- Percona-Server/mysql-test/t/pool_of_threads_high_prio_tickets.cnf 1970-01-01 00:00:00 +0000
2301+++ Percona-Server/mysql-test/t/pool_of_threads_high_prio_tickets.cnf 2013-04-03 10:29:25 +0000
2302@@ -0,0 +1,16 @@
2303+!include include/default_my.cnf
2304+
2305+[mysqld.1]
2306+loose-thread-handling= pool-of-threads
2307+loose-thread_pool_size= 2
2308+loose-thread_pool_max_threads= 2
2309+#extra-port= @ENV.MASTER_EXTRA_PORT
2310+extra-port= @OPT.port
2311+loose-thread_pool_high_prio_tickets=2
2312+
2313+[client]
2314+connect-timeout= 2
2315+
2316+[ENV]
2317+;MASTER_EXTRA_PORT= @mysqld.2.port
2318+MASTER_EXTRA_PORT= @OPT.port
2319
2320=== added file 'Percona-Server/mysql-test/t/pool_of_threads_high_prio_tickets.test'
2321--- Percona-Server/mysql-test/t/pool_of_threads_high_prio_tickets.test 1970-01-01 00:00:00 +0000
2322+++ Percona-Server/mysql-test/t/pool_of_threads_high_prio_tickets.test 2013-04-03 10:29:25 +0000
2323@@ -0,0 +1,19 @@
2324+# Start with thread_handling=pool-of-threads
2325+# and run some basic tests with --thread_pool_high_prio_tickets=2
2326+
2327+-- source include/have_pool_of_threads.inc
2328+
2329+SELECT @@thread_pool_high_prio_tickets;
2330+
2331+-- source include/common-tests.inc
2332+
2333+CREATE TABLE t1(a INT);
2334+
2335+START TRANSACTION;
2336+INSERT INTO t1 VALUES (1);
2337+SELECT * FROM t1;
2338+INSERT INTO t1 VALUES (2);
2339+SELECT * FROM t1;
2340+COMMIT;
2341+
2342+DROP TABLE t1;
2343
2344=== modified file 'Percona-Server/sql/sys_vars.cc'
2345--- Percona-Server/sql/sys_vars.cc 2013-03-05 12:16:18 +0000
2346+++ Percona-Server/sql/sys_vars.cc 2013-04-03 10:29:25 +0000
2347@@ -2377,6 +2377,13 @@
2348 NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),
2349 ON_UPDATE(fix_threadpool_stall_limit)
2350 );
2351+static Sys_var_uint Sys_threadpool_high_prio_tickets(
2352+ "thread_pool_high_prio_tickets",
2353+ "Number of tickets to enter the high priority event queue for each "
2354+ "transaction.",
2355+ GLOBAL_VAR(threadpool_high_prio_tickets), CMD_LINE(REQUIRED_ARG),
2356+ VALID_RANGE(0, UINT_MAX), DEFAULT(0), BLOCK_SIZE(1)
2357+);
2358 #endif /* !WIN32 */
2359 static Sys_var_uint Sys_threadpool_max_threads(
2360 "thread_pool_max_threads",
2361
2362=== modified file 'Percona-Server/sql/threadpool.h'
2363--- Percona-Server/sql/threadpool.h 2013-01-30 09:55:26 +0000
2364+++ Percona-Server/sql/threadpool.h 2013-04-03 10:29:25 +0000
2365@@ -22,6 +22,9 @@
2366 extern uint threadpool_stall_limit; /* time interval in 10 ms units for stall checks*/
2367 extern uint threadpool_max_threads; /* Maximum threads in pool */
2368 extern uint threadpool_oversubscribe; /* Maximum active threads in group */
2369+#ifndef _WIN32
2370+extern uint threadpool_high_prio_tickets; /* High prio queue tickets */
2371+#endif
2372
2373
2374
2375
2376=== modified file 'Percona-Server/sql/threadpool_common.cc'
2377--- Percona-Server/sql/threadpool_common.cc 2013-01-28 11:27:04 +0000
2378+++ Percona-Server/sql/threadpool_common.cc 2013-04-03 10:29:25 +0000
2379@@ -33,6 +33,9 @@
2380 uint threadpool_stall_limit;
2381 uint threadpool_max_threads;
2382 uint threadpool_oversubscribe;
2383+#ifndef _WIN32
2384+uint threadpool_high_prio_tickets;
2385+#endif
2386
2387 /* Stats */
2388 TP_STATISTICS tp_stats;
2389
2390=== modified file 'Percona-Server/sql/threadpool_unix.cc'
2391--- Percona-Server/sql/threadpool_unix.cc 2013-01-28 11:33:54 +0000
2392+++ Percona-Server/sql/threadpool_unix.cc 2013-04-03 10:29:25 +0000
2393@@ -25,6 +25,7 @@
2394 #include <time.h>
2395 #include <sql_plist.h>
2396 #include <threadpool.h>
2397+#include <mysql/thread_pool_priv.h> // thd_is_transaction_active()
2398 #include <time.h>
2399 #ifdef __linux__
2400 #include <sys/epoll.h>
2401@@ -112,6 +113,7 @@
2402 bool logged_in;
2403 bool bound_to_poll_descriptor;
2404 bool waiting;
2405+ uint tickets;
2406 };
2407
2408 typedef I_P_List<connection_t,
2409@@ -126,6 +128,7 @@
2410 {
2411 mysql_mutex_t mutex;
2412 connection_queue_t queue;
2413+ connection_queue_t high_prio_queue;
2414 worker_list_t waiting_threads;
2415 worker_thread_t *listener;
2416 pthread_attr_t *pthread_attr;
2417@@ -398,8 +401,13 @@
2418 {
2419 DBUG_ENTER("queue_get");
2420 thread_group->queue_event_count++;
2421- connection_t *c= thread_group->queue.front();
2422- if (c)
2423+ connection_t *c;
2424+
2425+ if ((c= thread_group->high_prio_queue.front()))
2426+ {
2427+ thread_group->high_prio_queue.remove(c);
2428+ }
2429+ else if ((c= thread_group->queue.front()))
2430 {
2431 thread_group->queue.remove(c);
2432 }
2433@@ -572,7 +580,8 @@
2434 do wait and indicate that via thd_wait_begin/end callbacks, thread creation
2435 will be faster.
2436 */
2437- if (!thread_group->queue.is_empty() && !thread_group->queue_event_count)
2438+ if ((!thread_group->high_prio_queue.is_empty() ||
2439+ !thread_group->queue.is_empty()) && !thread_group->queue_event_count)
2440 {
2441 thread_group->stalled= true;
2442 wake_or_create_thread(thread_group);
2443@@ -690,7 +699,8 @@
2444 more workers.
2445 */
2446
2447- bool listener_picks_event= thread_group->queue.is_empty();
2448+ bool listener_picks_event= thread_group->high_prio_queue.is_empty() &&
2449+ thread_group->queue.is_empty();
2450
2451 /*
2452 If listener_picks_event is set, listener thread will handle first event,
2453@@ -700,7 +710,16 @@
2454 for(int i=(listener_picks_event)?1:0; i < cnt ; i++)
2455 {
2456 connection_t *c= (connection_t *)native_event_get_userdata(&ev[i]);
2457- thread_group->queue.push_back(c);
2458+ if (c->tickets > 0 && thd_is_transaction_active(c->thd))
2459+ {
2460+ c->tickets--;
2461+ thread_group->high_prio_queue.push_back(c);
2462+ }
2463+ else
2464+ {
2465+ c->tickets= threadpool_high_prio_tickets;
2466+ thread_group->queue.push_back(c);
2467+ }
2468 }
2469
2470 if (listener_picks_event)
2471@@ -995,6 +1014,7 @@
2472 DBUG_ENTER("queue_put");
2473
2474 mysql_mutex_lock(&thread_group->mutex);
2475+ connection->tickets= threadpool_high_prio_tickets;
2476 thread_group->queue.push_back(connection);
2477
2478 if (thread_group->active_thread_count == 0)
2479@@ -1150,7 +1170,8 @@
2480 DBUG_ASSERT(thread_group->connection_count > 0);
2481
2482 if ((thread_group->active_thread_count == 0) &&
2483- (thread_group->queue.is_empty() || !thread_group->listener))
2484+ (thread_group->high_prio_queue.is_empty() ||
2485+ thread_group->queue.is_empty() || !thread_group->listener))
2486 {
2487 /*
2488 Group might stall while this thread waits, thus wake
2489@@ -1193,6 +1214,7 @@
2490 connection->logged_in= false;
2491 connection->bound_to_poll_descriptor= false;
2492 connection->abs_wait_timeout= ULONGLONG_MAX;
2493+ connection->tickets = 0;
2494 }
2495 DBUG_RETURN(connection);
2496 }
2497
2498=== modified file 'doc/source/performance/threadpool.rst'
2499--- doc/source/performance/threadpool.rst 2013-04-02 12:44:40 +0000
2500+++ doc/source/performance/threadpool.rst 2013-04-03 10:29:25 +0000
2501@@ -24,6 +24,12 @@
2502
2503 Current implementation of the thread pool is built in the server, unlike the upstream version which is implemented as a plugin. Another significant implementation difference is that this implementation doesn't try to minimize the number of concurrent transactions like the ``MySQL Enterprise Threadpool``. Because of these things this implementation isn't compatible with the upstream one.
2504
2505+.. note::
2506+
2507+ Percona Server adds priority scheduling to the original
2508+implementation introduced in |MariaDB|. See description of the
2509+:variable:`thread_pool_high_prio_tickets` below.
2510+
2511 Version Specific Information
2512 ============================
2513
2514@@ -88,6 +94,46 @@
2515
2516 The number of milliseconds before a running thread is considered stalled. When this limit is reached thread pool will wake up or create another thread. This is being used to prevent a long-running query from monopolizing the pool.
2517
2518+.. variable:: thread_pool_high_prio_tickets
2519+
2520+ :cli: Yes
2521+ :conf: Yes
2522+ :scope: Global
2523+ :dyn: Yes
2524+ :vartype: Numeric
2525+ :default: 0
2526+
2527+This variable controls the high priority queue policy. Each new
2528+connection is assigned this many tickets to enter the high priority
2529+queue. Whenever a query has to be queued to be executed later because no
2530+threads are available, the thread pool puts the connection into the high
2531+priority queue if the following conditions apply:
2532+
2533+1. The connection has an open transaction in the server.
2534+2. The number of high priority tickets of this connection is non-zero.
2535+
2536+If both the above conditions hold, the connection is put into the high
2537+priority queue and its tickets value is decremented. Otherwise the
2538+connection is put into the common queue with the initial tickets value
2539+specified with this option.
2540+
2541+Each time the thread pool looks for a new connection to process, it
2542+first checks the high priority queue, and picks connections from the
2543+common queue only when the high priority one is empty.
2544+
2545+The idea is to minimize the number of open transactions in the
2546+server. In many cases it is beneficial to give short-running
2547+transactions a chance to commit faster and thus release server resources
2548+and locks without waiting in the same queue with other connections that
2549+are about to start a new transaction, or those that have run out of
2550+their high priority tickets.
2551+
2552+With the default value of 0, all connections are always put into the
2553+common queue, i.e. no priority scheduling is used as in the original
2554+implementation in |MariaDB|. The higher is the value, the more chances
2555+each transaction gets to enter the high priority queue and commit before
2556+it is put in the common queue.
2557+
2558 Status Variables
2559 =====================
2560

Subscribers

People subscribed via source and target branches