Hi!
I checked that in the COUNT(*) below InnoDB does
not fetch the whole row if it does the query through
a secondary index. It may sometimes have to
fetch it to determine if the row should be visible
in the current consistent read, the trx id is
stored in the whole row in the primary key index.
But Ryan said that queries were CPU-bound,
and fetching a million rows through a primary key
should at most take 10 s.
Regards,
Heikki
http://www.innodb.com
-----Original Message-----
From: Michael Widenius <[EMAIL PROTECTED]>
To: ryc <[EMAIL PROTECTED]>; Heikki Tuuri <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Thursday, June 28, 2001 1:26 PM
Subject: Re: Re:innobase bug w/ 3.23.29???
>
>Hi!
>
>>>>>> "ryc" == ryc <[EMAIL PROTECTED]> writes:
>
>ryc> <snip>
>Heikki> Well, mystery solved :). Except that why MySQL uses hours to
>Heikki> sieve away the extraneous rows in the first query.
>>>
>>> To be able to answer the question why:
>>>
>>> SELECT COUNT(*) FROM table1 WHERE col1=0 and col2=0
>>>
>>> Is slow, I would need to get an EXPLAIN for this query.
>
>mysql> explain select count(*) from messages where clean=0 and
>ryc> parentmsgid=0;
>
>ryc>
+----------+-------+---------------+-------------+---------+------+---------
>ryc> +-------------------------+
>ryc> | table | type | possible_keys | key | key_len | ref |
rows
>ryc> | Extra |
>ryc>
+----------+-------+---------------+-------------+---------+------+---------
>ryc> +-------------------------+
>ryc> | messages | index | parentmsgid | parentmsgid | 5 | NULL |
7207120
>ryc> | where used; Using index |
>ryc>
+----------+-------+---------------+-------------+---------+------+---------
>ryc> +-------------------------+
>
>How many rows satisfies the query? Is it about 7207120 rows ?
>
>Heikki, could it be that InnoDB will read the original row through the
>primary key even if MySQL has marked that it would be happy if InnoDB
>only returned the found keys from the secondary index ?
>
>Regards,
>Monty
---------------------------------------------------------------------
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
