Which query will accomplish this?

You have enabled the Slow Query Log for a short period.
When you process the Slow Query Log, you receive the following snip of output:
Count: 100 Time=0 .22a (22s) Lock=0.00s (0s) Rows=0.0 (0), root[root] @localhost
CREATE TABLE ‘t1’ (id serial,id0 varchar(N) unique key,intcaoll INT (N)
,intco12 INT(N) ,intco13 INT(N) ,intco14 INT(N) ,intco15 INT(N)
,charcol1 VARVHAR(N) ,charcol2 VARCHAR(N) charcol3 VARCHAR (N)
,charcol4 VARVHAR(N) ,charcol5 VARCHAR(N) charcol6 VARCHAR (N)
,charcol7 VARVHAR(N) ,charcol8 VARCHAR(N) charcol9 VARCHAR (N) .charcol 10 VACHAR
(N) )
Count: 64000 Time-0.02s (1213s) Lock=0.00s (6s) Rows=1.0 (64000), root [root]@ localhost

SELECT intocl1, intco12, intco13, intco14, intco15, intco16,intco17, intco18
,intcol9, intcol10, charcol1, charcol2, charcol3, charcol4, charcol5, charcol6
,charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = ‘s’
Count: 1 Time=0.02s (0s) Lock=0.00s (0s) Rows=1.0 (1) agent [agent] @localhost
SELECT Select_priv, Repl_client_priv, Show_db_priv, Super_priv,
Process_priv FROM mysql.user WHERE CONCAT (user, ‘s’, host) = CURRENT_USER ()
Count: 48000 Time=0.02s (778s) Lock=0.00 (3s) Rows=1.0 (48000), root[root]@localhost
SELECT intocl1,intcol2,intcol3, intcol4, intcol5, charcol1, charcol2, charcol3
,charcol4, charcol5, charcol6, charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = ‘s’
You want to tune the query such that it provides the greatest overall time savings.
Which query will accomplish this?

You have enabled the Slow Query Log for a short period.
When you process the Slow Query Log, you receive the following snip of output:
Count: 100 Time=0 .22a (22s) Lock=0.00s (0s) Rows=0.0 (0), root[root] @localhost
CREATE TABLE ‘t1’ (id serial,id0 varchar(N) unique key,intcaoll INT (N)
,intco12 INT(N) ,intco13 INT(N) ,intco14 INT(N) ,intco15 INT(N)
,charcol1 VARVHAR(N) ,charcol2 VARCHAR(N) charcol3 VARCHAR (N)
,charcol4 VARVHAR(N) ,charcol5 VARCHAR(N) charcol6 VARCHAR (N)
,charcol7 VARVHAR(N) ,charcol8 VARCHAR(N) charcol9 VARCHAR (N) .charcol 10 VACHAR
(N) )
Count: 64000 Time-0.02s (1213s) Lock=0.00s (6s) Rows=1.0 (64000), root [root]@ localhost

SELECT intocl1, intco12, intco13, intco14, intco15, intco16,intco17, intco18
,intcol9, intcol10, charcol1, charcol2, charcol3, charcol4, charcol5, charcol6
,charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = ‘s’
Count: 1 Time=0.02s (0s) Lock=0.00s (0s) Rows=1.0 (1) agent [agent] @localhost
SELECT Select_priv, Repl_client_priv, Show_db_priv, Super_priv,
Process_priv FROM mysql.user WHERE CONCAT (user, ‘s’, host) = CURRENT_USER ()
Count: 48000 Time=0.02s (778s) Lock=0.00 (3s) Rows=1.0 (48000), root[root]@localhost
SELECT intocl1,intcol2,intcol3, intcol4, intcol5, charcol1, charcol2, charcol3
,charcol4, charcol5, charcol6, charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = ‘s’
You want to tune the query such that it provides the greatest overall time savings.
Which query will accomplish this?

A.
CHEATE TABLE ‘t1’ (id serial, id0 varchar (N) unique key, intcol1 INT (N)
,intcol2 INT (N), intcol3 INT(N) ,intcol4 INT(N), intcol5 INT(N), charool1 VARCHAR
(N)
,charcol2 VARCHAR (N), charcol3 VARCHAR(N), charcol4 VARCHAR(N), charcol5 VARCHAR
(N)
,charcol6 VARCHAR (N), charcol7 VARCHAR(N), charcol8 VARCHAR(N), charcol9 VARCHAR
(N)
,charcol10 VARCHAR (N);

B.
SELECT intcol1, intcol2, intcol3, intcol4, intcol5, intcol6, intcol7, intcol8, intcol9,
Intcol10, intcol11, intcol12, intcol13, intcol14, intcol15, intcol16, intcol17, intcol18, intcol19,
charcol10
FROM t1
WHERE id = ‘s’;

C.
SELECT Select_priv, Repl_client_priv, Show_db_priv, Super_priv, Process_priv
FROM mysql.user
WHERE CONCAT (user,’s’, host) = CURRENT_USER();

D.
SELECT intcol1, intcol2, intcol3, intcol4, intcol5, charcol1, charcol2, charcol3, charcol4,
charcol5, charcol6, charcol7, charcol8, charcol9, charcol10
FROM t1
WHERE id = ‘s’;

Explanation:



Leave a Reply 6

Your email address will not be published. Required fields are marked *


Steve

Steve

I am not sure this question has been translated well, given all of the typos.
The query is trying to lookup an auto-incremented integer value with a character string value.

None of the queries given solve this issue.

The select should be looking up against column `id0`, not `id`.

Thiago

Thiago

D – incomplete

Gabriel

Gabriel

A, because every query afterward depends on it. So if you improve table definition rest of them will be improved too

howard

howard

The answer could be A it is correct.

igbinigun

igbinigun

I Agree on A

seb

seb

Well, the question doesn’t say if you change the table definition but to tune the query, I believe tuning the query in the answer D would accomplish this task.