Monty,
there is an SQL optimizer deficiency if a two-column index is used.
(Added later: actually not, see the end of this email.)
Ryan does the following query, for which there is a very good
index on (parentmsgid, clean). But from the printout below we
see that though MySQL uses the right key, it does not communicate
the value of the second column to InnoDB (i.e., key_len is only 4, the
length of an integer).
The query:
select * from messages where clean=0 and parentmsgid=0;
should return 5 rows.
Another strange thing is that when InnoDB returns rows to MySQL
which MySQL apparently sieves away because 'clean' is '1' in them,
the query seems to take hours. It should not take that long to sieve
away maybe 1 million rows. I looked at the 45 MB long printout
Ryan sent and and apparently InnoDB was not in a loop, it just sent
a very large number of rows to MySQL, since parentmsgid = 0 in
most rows. clean is '0' in only a few rows.
I remember that a user complained 2 months ago on the mailing list
about the same deficiency in the SQL optimizer when using 2-column
indexes.
Now I found the solution! For the following query the optimizer
gives a 2-column search key:
select * from messages where clean = '0' and parentmsgid = 0;
Ok, Ryan, you can use the above formulation. The reason why
the optimizer does not use the second column in the first query
is that it probably considers type conversion '0' -> 0 a function,
and generally optimizers ignore conditions like
sin(col1) = 0.5
Well, mystery solved :). Except that why MySQL uses hours to
sieve away the extraneous rows in the first query.
Regards,
Heikki
http://www.innodb.com
.........................................
Match mode 1
search tuple DATA TUPLE: 1 fields;
0: len 4; hex 00000000; asc ;;
N tables locked 0
Using index parentmsgid cnt 0 ; Page no 1834
PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
0: len 4; hex 00000000; asc ;; 1: len 1; hex 30; asc 0;; 2: len 8; hex
80000000
00000002; asc ;;
Comparing rec and search tuple
Using index parentmsgid cnt 0 ret value 10
Match mode 1
search tuple DATA TUPLE: 1 fields;
0: len 4; hex 00000000; asc ;;
N tables locked 0
..........................................
(gdb) print key_len
$1 = 4
(gdb) bt
#0 ha_innobase::index_read (this=0x8313b80, buf=0x8312280 "",
key_ptr=0x8307668 "", key_len=4, find_flag=HA_READ_KEY_EXACT)
at ha_innobase.cc:1675
#1 0x80f085b in join_read_always_key (tab=0x8307548) at sql_select.cc:4370
#2 0x80efb24 in sub_select (join=0xbe9ff2a4, join_tab=0x8307548,
end_of_records=false) at sql_select.cc:4100
#3 0x80ef7a9 in do_select (join=0xbe9ff2a4, fields=0x8311ccc, table=0x0,
procedure=0x0) at sql_select.cc:4013
#4 0x80e66ef in mysql_select (thd=0x8311a80, tables=0x8306b98,
fields=@0x8311ccc, conds=0x8306dc0, ftfuncs=@0x8311d00, order=0x0,
group=0x0, having=0x0, proc_param=0x0, select_options=8950784,
result=0x8306e40) at sql_select.cc:785
#5 0x80cd92e in mysql_execute_command () at sql_parse.cc:1137
#6 0x80d0bc9 in mysql_parse (thd=0x8311a80,
inBuf=0x8306ad8 "select * from messages where parentmsgid = 0 and clean
= 0
limit 2", length=66) at sql_parse.cc:2290
#7 0x80ccc50 in do_command (thd=0x8311a80) at sql_parse.cc:829
#8 0x80cc04e in handle_one_connection (arg=0x8311a80) at sql_parse.cc:551
#9 0x40041ca3 in pthread_start_thread () from /lib/libpthread.so.0
#10 0x40041cee in pthread_start_thread_event () from /lib/libpthread.so.0
(gdb)
-----Original Message-----
From: ryc <[EMAIL PROTECTED]>
To: Heikki Tuuri <[EMAIL PROTECTED]>
Date: Wednesday, June 27, 2001 12:38 AM
Subject: Re: Re:innobase bug w/ 3.23.29???
>Sorry about the delay in getting you the output. Had a few problems to take
>care of before I could take down the db server for the testing.
>
>I managed to get all of the input for both queries, however they were
rather
>large. So instead of attaching them to the messages and clogging your
>mailbox, I have them on my home dsl line. The file size is 750k or so.
>Inside the archive there are two files, log.limit2 and log.limit6. They
>correspond with the two queries listed below.
>
>http://p0key.dnsq.org/ryc/log.tar.gz
>
>Here is some information:
>Using innobase compiled with 64kb pages.
>Using Openbsd 2.7
>Mysql compiled with:
>export CXXFLAGS="-O6 -felide-constructors -fno-rtti -fno-exceptions"
>export CFLAGS="-O6 -felide-constructors -fno-rtti -fno-exceptions"
>./configure --enable-static --localstatedir="/var/mysql" --with-libwrap --w
i
>th-mysqld-user="mysql" --with-unix-socket-path="/var/mysql/mysql.sock" --wi
t
>hout-perl --without-debug --without-readline --without-bench --without-mit-
t
>hreads --with-innodb
>
>The create statement:
>CREATE TABLE messages (
> messageid bigint(20) NOT NULL auto_increment,
> msgid varchar(255) NOT NULL default '',
> groupid int(10) unsigned NOT NULL default '0',
> userid int(10) unsigned NOT NULL default '0',
> parentmsgid int(10) unsigned NOT NULL default '0',
> subject text NOT NULL,
> body text NOT NULL,
> closedflag char(1) NOT NULL default '',
> postdate int(10) unsigned NOT NULL default '0',
> adddate int(10) unsigned NOT NULL default '0',
> replies int(10) unsigned NOT NULL default '0',
> ipaddr int(10) unsigned NOT NULL default '0',
> clean char(1) NOT NULL default '0',
> msent char(1) NOT NULL default '',
> PRIMARY KEY (messageid),
> UNIQUE KEY usenetmsgid (msgid),
> KEY parentmsgid (parentmsgid,clean),
> KEY MSGSELECT (groupid,clean,parentmsgid,postdate),
> KEY adddatestats (adddate)
>) TYPE=MyISAM MAX_ROWS=100000000;
>
>The queries:
>select * from messages where clean=0 and parentmsgid=0 limit 2;
>select * from messages where clean=0 and parentmsgid=0 limit 6;
>
>The key that these queries use is 'parentmsgid', the number of rows in the
>table that matches the where criteria is 5. The number of rows in the table
>is around 10million.
>
>The innobase section in my.cnf:
>innodb_data_file_path =
>dr1/ibdata0:2G;var/mysql/ibdata1:2G;var/mysql/ibdata2:2G;var/mysql/ibdata5:
2
>G;var/mysql/ibdata6:2G;dr3/ibdata7:2G;dr3/ibdata8:2G;dr3/ibdata9:2G;dr3/ibd
a
>ta10:2G;dr3/ibdata11:2G;dr3/ibdata12:2G;dr3/ibdata13:2G;dr3/ibdata14:2G;dr3
/
>ibdata15:2G;dr3/ibdata16:2G;dr3/ibdata17:2G;dr3/ibdata18:2G;dr3/ibdata19:2G
;
>dr3/ibdata20:2G;dr3/ibdata21:2G;dr3/ibdata22:2G;dr3/ibdata23:2G;dr3/ibdata2
4
>:2G;dr3/ibdata25:2G
>innodb_data_home_dir = /
>innodb_log_group_home_dir = /var/mysql/
>innodb_log_arch_dir = /var/mysql/
>set-variable = innodb_mirrored_log_groups=1
>set-variable = innodb_log_files_in_group=3
>set-variable = innodb_log_file_size=5M
>set-variable = innodb_log_buffer_size=8M
>innodb_flush_log_at_trx_commit=0
>innodb_log_archive=0
>set-variable = innodb_buffer_pool_size=300M
>set-variable = innodb_additional_mem_pool_size=16M
>set-variable = innodb_file_io_threads=4
>set-variable = innodb_lock_wait_timeout=50
>
>Thanks for the help! If you need any more information let me know.
>
>ryan
>
>
>----- Original Message -----
>From: "Heikki Tuuri" <[EMAIL PROTECTED]>
>To: "ryc" <[EMAIL PROTECTED]>
>Sent: Monday, June 25, 2001 10:26 AM
>Subject: Re: Re:innobase bug w/ 3.23.29???
>
>
>> Ryan,
>>
>> really strange trace. For example, does it really say 'using index col2'
>> in the first printout? It seems to scan 1098 records before finding the
>> first matching one. Why does it only scan 8 in the next?
>>
>> Anyway, I have attached a new row0sel.c which will print a much more
>> detailed trace, like I have pasted below.
>>
>> What is your CREATE TABLE statement like for table1? Does
>>
>> CHECK TABLE table1;
>>
>> say that the table is ok? Check table will scan all the indexes
>> and may thus take 15 min for a 1 GB table.
>>
>> I ran some tests with my own Linux-2.4.0 and 64 kB pages,
>> but it worked ok. On what computer and OS are you running?
>>
>> Regards,
>>
>> Heikki
>>
>> ............................................................
>> Match mode 1
>> search tuple DATA TUPLE: 2 fields;
>> 0: len 4; hex 8000009b; asc ;; 1: len 4; hex 8000009b; asc ;;
>> Using index col1_2 cnt 0 ; Page no 1188
>> PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
>> 0: len 4; hex 8000009b; asc ;; 1: len 4; hex 8000009b; asc ;; 2: len 4;
>hex
>> 800
>> 0009b; asc ;;
>> Comparing rec and search tuple
>> Using index col1_2 cnt 0 ret value 10
>> Match mode 1
>> search tuple DATA TUPLE: 2 fields;
>> 0: len 4; hex 8000009b; asc ;; 1: len 4; hex 8000009b; asc ;;
>> Using index col1_2 cnt 1 ; Page no 1188
>> PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
>> 0: len 4; hex 8000009b; asc ;; 1: len 4; hex 8000009b; asc ;; 2: len 4;
>hex
>> 800
>> 00483; asc ;;
>> Comparing rec and search tuple
>> Using index col1_2 cnt 1 ret value 10
>> Match mode 1
>> search tuple DATA TUPLE: 2 fields;
>> 0: len 4; hex 8000009b; asc ;; 1: len 4; hex 8000009b; asc ;;
>> Using index col1_2 cnt 1 ; Page no 1188
>> PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
>> 0: len 4; hex 8000009b; asc ;; 1: len 4; hex 8000009b; asc ;; 2: len 4;
>hex
>> 800
>> 0086b; asc k;;
>> Comparing rec and search tuple
>> Using index col1_2 cnt 1 ret value 10
>> Match mode 1
>> search tuple DATA TUPLE: 2 fields;
>> 0: len 4; hex 8000009b; asc ;; 1: len 4; hex 8000009b; asc ;;
>> Using index col1_2 cnt 1 ; Page no 1188
>> PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
>> 0: len 4; hex 8000009b; asc ;; 1: len 4; hex 8000009b; asc ;; 2: len 4;
>hex
>> 800
>> 00c53; asc S;;
>> Comparing rec and search tuple
>> Using index col1_2 cnt 1 ret value 10
>> Match mode 1
>> search tuple DATA TUPLE: 2 fields;
>> 0: len 4; hex 8000009b; asc ;; 1: len 4; hex 8000009b; asc ;;
>> Using index col1_2 cnt 1 ; Page no 1188
>> PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
>> 0: len 4; hex 8000009b; asc ;; 1: len 4; hex 8000009b; asc ;; 2: len 4;
>hex
>> 800
>> 0103b; asc ;;;
>> Comparing rec and search tuple
>> Using index col1_2 cnt 2 ; Page no 1188
>> PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
>> 0: len 4; hex 8000009b; asc ;; 1: len 4; hex 8000009b; asc ;; 2: len 4;
>hex
>> 800
>> 01423; asc #;;
>> Comparing rec and search tuple
>> Using index col1_2 cnt 3 ; Page no 1188
>> PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
>> 0: len 4; hex 8000009b; asc ;; 1: len 4; hex 8000009b; asc ;; 2: len 4;
>hex
>> 800
>> 0180b; asc ;;
>> Comparing rec and search tuple
>>
>> -----Original Message-----
>> From: ryc <[EMAIL PROTECTED]>
>> To: Heikki Tuuri <[EMAIL PROTECTED]>
>> Date: Sunday, June 24, 2001 11:48 PM
>> Subject: Re: Re:innobase bug w/ 3.23.29???
>>
>>
>> >OK thanks for the help. I got it going this time, dont know what went
>when
>> I
>> >tried it before. Maybe the wrapper was eating the output. Anyhow...
There
>> >are a total of 5 rows that match the where criteria. Here is what I get
>> with
>> >the following queries:
>> >
>> >SELECT id FROM table1 WHERE col1 = 0 AND col2 = 0 limit 2;
>> >Using index col2 cnt 1000
>> >Page no 193666
>> >Using index col12 cnt 1098 ret value 10
>> >Using index col12 cnt 1 ret value 10
>> >
>> >SELECT id FROM table1 WHERE col1 = 0 AND col2 = 0 limit 6;
>> >Using index col12 cnt 8 ret value 10
>> >Using index col12 cnt 10 ret value 10
>> >Using index col12 cnt 8 ret value 10
>> >Using index col12 cnt 8 ret value 10
>> >(the line with 'cnt 8' repeats many times, with an occasional 'cnt 10'..
>> >then back to 8 and repeats forever)
>> >
>> >This is using innobase compiled with 64k pages.
>> >
>> >Another note is that the query "SELECT COUNT(*) FROM table1 WHERE col1=0
>> AND
>> >col2=0" also loops forever with the same output as above.
>> >
>> >Thanks for your help.
>> >
>> >ryan
>> >
>> >
>> >> Ryan,
>> >>
>> >> did you recompile also safe-mysqld? The file name is row0sel.c, not
>> >> row2sel.c.
>> >>
>> >> It is easiest that you go to mysql/innodb/row directory, remove .o and
>.a
>> >> files and
>> >> type make.
>> >>
>> >> Then go to mysql/sql, delete mysqld, and type make. You will get a new
>> >> mysqld
>> >> in the same directory mysql/sql
>> >>
>> >> You can start:
>> >>
>> >> mysqld
>> >>
>> >> Then it will print that InnoDB started etc. When you run the select
>> query,
>> >> the output
>> >> should appear there.
>> >>
>> >> Regards,
>> >>
>> >> Heikki
>> >>
>> >> -----Original Message-----
>> >> From: ryc <[EMAIL PROTECTED]>
>> >> To: Heikki Tuuri <[EMAIL PROTECTED]>
>> >> Date: Sunday, June 24, 2001 1:51 AM
>> >> Subject: Re: Re:innobase bug w/ 3.23.29???
>> >>
>> >>
>> >> >In this case, 480 is below the amount of rows that meet the where
>> >criteria,
>> >> >and the explain is the same for when its above the number... and also
>> >when
>> >> >the limit is ommited all together.
>> >> >
>> >> >mysql> explain SELECT col1,col2,col3,col4 FROM messages WHERE col1 =
0
>> >AND
>> >> >col2 = 0 limit 480;
>> >>
>>
>>>+----------+------+---------------+-------------+---------+-------+------
-
>> -
>> >> -
>> >> >+------------+
>> >> >| table | type | possible_keys | key | key_len | ref |
>rows
>> >> >| Extra |
>> >>
>>
>>>+----------+------+---------------+-------------+---------+-------+------
-
>> -
>> >> -
>> >> >+------------+
>> >> >| messages | ref | col1-2 | col1-2 | 4 | const | 1024320 |
>> where
>> >> >used |
>> >>
>>
>>>+----------+------+---------------+-------------+---------+-------+------
-
>> -
>> >> -
>> >> >+------------+
>> >> >
>> >> >I compiled the new row2sel.c into mysqld but I am able to get any
>debug
>> >> >information printed out. First I moved the old row2sel.c to another
>file
>> >> >name... but the row2sel.c that you provide me with in its place. Then
>> >> >deleted the .o file, and recompiled... installed. But when doing the
>> >query
>> >> >via the client I dont get any debuging info printed to the console. I
>> >> >checked the log files and nothing there either. So I started mysqld
>from
>> >> >safe_mysql (without putting it into the background) thinking I would
>get
>> >> the
>> >> >messages but I dont get them there either.
>> >> >
>> >> >Any idea what I am doing wrong? I also did a make clean, followed by
>> >> >rebuilding everything.. and then reinstalling.. still no debug
>messages.
>> >> >
>> >> >Thanks,
>> >> >ryan
>> >> >
>> >> >
>> >> >----- Original Message -----
>> >> >From: "Heikki Tuuri" <[EMAIL PROTECTED]>
>> >> >To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
>> >> >Sent: Saturday, June 23, 2001 7:35 AM
>> >> >Subject: Re:innobase bug w/ 3.23.29???
>> >> >
>> >> >
>> >> >> Ryan,
>> >> >>
>> >> >> what does
>> >> >>
>> >> >> EXPLAIN select * from tablename WHERE col1=0 and col2=0 limit 100;
>> >> >>
>> >> >> show?
>> >> >>
>> >> >> What about
>> >> >>
>> >> >> EXPLAIN select * from tablename WHERE col1=0 and col2=0;
>> >> >>
>> >> >> Have you run
>> >> >>
>> >> >> CHECK TABLE tablename;
>> >> >>
>> >> >> You have compiled your own mysqld. I can send you a new
>> >> >>
>> >> >> mysql/innobase/row/row0sel.c
>> >> >>
>> >> >> which will print trace information about processing of your query.
>> >> >> That will reveal what it does and if it has gone into a loop.
>> >> >>
>> >> >> Regards,
>> >> >>
>> >> >> Heikki
>> >> >> http://www.innodb.com
>> >> >>
>> >> >> ............................
>> >> >> Keep in mind the version of innobase that I am using is compiled
>with
>> a
>> >> >page
>> >> >> size of 64k.I have a table of 10million rows (give or take, select
>> >> >count(*)
>> >> >> from
>> >> >> tablename is slow for innobase tables so I dont do it often).
>Everyday
>> >I
>> >> >> need to run a query that selects rows that are marked for
>> processing...
>> >> >this
>> >> >> involves something like:
>> >> >>
>> >> >> select * from tablename WHERE col1=0 and col2=0 limit 100 ( there
is
>> an
>> >> >> index (col1,col2) on the table).
>> >> >>
>> >> >> The problem is say for instance there 88 rows that should match
that
>> >> >query,
>> >> >> and it is executed... mysql will peg the processor at 100% and go
on
>> >> >pretty
>> >> >> much forever.. (ie its ran for many many hours and still hasnt
>> stopped,
>> >I
>> >> >> killed it). However if I run the query with limit < number of rows
>> that
>> >> >> match the where clause, it executes in no time at all (as
>expected)...
>> >> but
>> >> >> once the limit is above the number of rows that match the where
>clause
>> >it
>> >> >> seems to loop forever (no disk activity, just eats away at the
cpu).
>> >> >> I tried creating a small test case to recreate this but I was not
>able
>> >to
>> >> >> succeed. I guess this is more of a message for Heikki but maybe
>> someone
>> >> >else
>> >> >> will benifit from reading this.
>> >> >>
>> >> >> Thanks,
>> >> >>
>> >> >> ryan
>> >> >>
>> >>
>>
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
