UNIONs and slow log

Hello,

I use slow query log to intercept queries that are not using indexes .
It looks like every UNION query appears there, so it becomes
huge mess :(

(mysql 4.0.12)

--
Dmitry

How-To-Repeate:
1) CREATE TABLE table1 (
id int(11) NOT NULL auto_increment,
comment varchar(100) NOT NULL default '',
PRIMARY KEY (id) ) TYPE=MyISAM;
2) INSERT INTO table1 VALUES (1,'aaaaaaaaa.'),(2,'bbbbbbbbbbb'),(3,'cccccccccccc'),
(4,'dddddddddd'),(5,'eeeeeeeee'),(6,'fffffffffffffff'),(7,'g ggggggggggg');
3) execute query and get result in slow-log:
# Query_time: 0 Lock_time: 0 Rows_sent: 4 Rows_examined: 4
use test;
(SELECT comment FROM `table1` where id<2)
UNION
(SELECT comment FROM `table1` where id=4)
UNION
(SELECT comment FROM `table1` where id>5);


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14013@lists.mysql.com
To unsubscribe, e-mail

Re: UNIONs and slow log

Dmitry writes:
> Hello,
>
> I use slow query log to intercept queries that are not using indexes .
> It looks like every UNION query appears there, so it becomes
> huge mess :(
>
> (mysql 4.0.12)
>
> --
> Dmitry
>
> How-To-Repeate:
> 1) CREATE TABLE table1 (
> id int(11) NOT NULL auto_increment,
> comment varchar(100) NOT NULL default '',
> PRIMARY KEY (id) ) TYPE=MyISAM;
> 2) INSERT INTO table1 VALUES (1,'aaaaaaaaa.'),(2,'bbbbbbbbbbb'),(3,'cccccccccccc'),
> (4,'dddddddddd'),(5,'eeeeeeeee'),(6,'fffffffffffffff'),(7,'g ggggggggggg');
> 3) execute query and get result in slow-log:
> # Query_time: 0 Lock_time: 0 Rows_sent: 4 Rows_examined: 4
> use test;
> (SELECT comment FROM `table1` where id<2)
> UNION
> (SELECT comment FROM `table1` where id=4)
> UNION
> (SELECT comment FROM `table1` where id>5);
>

Hi!

Are you using long format of logging ???

If yes, and it seems to me that you are, then every query not using
indices will pop up in that log.

MySQL is not using indices on very small tables, so that explains it.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14014@lists.mysql.com
To unsubscribe, e-mail

Re: UNIONs and slow log

At 12:44 -0500 3/21/03, Dmitry wrote:
>Hello,
>
>I use slow query log to intercept queries that are not using indexes .
>It looks like every UNION query appears there, so it becomes
>huge mess :(

What is your suggestion for what to do about it?


>
>(mysql 4.0.12)
>
>--
>Dmitry
>
>How-To-Repeate:
>1) CREATE TABLE table1 (
> id int(11) NOT NULL auto_increment,
> comment varchar(100) NOT NULL default '',
> PRIMARY KEY (id) ) TYPE=MyISAM;
>2) INSERT INTO table1 VALUES
>(1,'aaaaaaaaa.'),(2,'bbbbbbbbbbb'),(3,'cccccccccccc'),
> (4,'dddddddddd'),(5,'eeeeeeeee'),(6,'fffffffffffffff'),(7,'g ggggggggggg');
>3) execute query and get result in slow-log:
># Query_time: 0 Lock_time: 0 Rows_sent: 4 Rows_examined: 4
>use test;
>(SELECT comment FROM `table1` where id<2)
>UNION
>(SELECT comment FROM `table1` where id=4)
>UNION
>(SELECT comment FROM `table1` where id>5);


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14016@lists.mysql.com
To unsubscribe, e-mail

Re: UNIONs and slow log

At March 21, 2003 1:26 PM "Paul DuBois" wrote:
> >I use slow query log to intercept queries that are not using indexes .
> >It looks like every UNION query appears there, so it becomes
> >huge mess :(
>
> What is your suggestion for what to do about it?
>

I'd like to have in slow log only queries which do not use indexes
(it is exactly I use it for and it is exactly how ref.manual describes it)
According to EXPLAIN all those queries do use indexes, but they make
my slow log growing 10MB/hour.

--
Dmitry

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14017@lists.mysql.com
To unsubscribe, e-mail

Re: UNIONs and slow log

Dmitry writes:
> I always do that and teach others to do :)
>
> EXPLAIN:
> 'table2','range','rd_ip','rd_ip','4',[NULL],'155','Using where; Using index'
> 'table2','ref','rd_ip','rd_ip','4','const','1','Using where; Using index'
> 'table2','ref','rd_ip','rd_ip','4','const','1','Using where; Using index'
>
> --
> Dmitry

Well this "bug" is hard to fix.

This is because UNIOn's have to use temporary tables to be
resolved. Although single queries can use indices, UNION may result in
a small result table, which is why they popup in the slow query log,
but only if logged in the long format.

This patch fixes this truly small problem:

===== sql/sql_union.cc 1.40 vs edited =====
*** /tmp/sql_union.cc-1.40-9197 Wed Feb 19 15:58:43 2003
--- edited/sql/sql_union.cc Fri Mar 21 22:28:51 2003
***************
*** 209,216 ****
item_list, NULL, (describe) ? 0 : order,
(ORDER*) NULL, NULL, (ORDER*) NULL,
thd->options, result);
! if (found_rows_for_union && !res)
! thd->limit_found_rows = (ulonglong)table->file->records;
}
}

--- 209,221 ----
item_list, NULL, (describe) ? 0 : order,
(ORDER*) NULL, NULL, (ORDER*) NULL,
thd->options, result);
! if (!res)
! {
! if (found_rows_for_union)
! thd->limit_found_rows = (ulonglong)table->file->records;
! // To prevent UNION query of popping up in slow query log in the long format
! lex->select->options &= ~QUERY_NO_INDEX_USED;
! }
}
}




--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14020@lists.mysql.com
To unsubscribe, e-mail