Hi!
I didn't cc the bugs lists, because this isn't a repeatable bug.
>>>>> "nsabbi" == nsabbi <[EMAIL PROTECTED]> writes:
<cut>
mysql> select count(*) from keywords where keyword IN ('tubo');
nsabbi> +----------+
nsabbi> | count(*) |
nsabbi> +----------+
nsabbi> | 31210 |
nsabbi> +----------+
nsabbi> 1 row in set (4.12 sec)
mysql> select count(distinct codice),count(*) from keywords;
nsabbi> +------------------------+----------+
nsabbi> | count(distinct codice) | count(*) |
nsabbi> +------------------------+----------+
nsabbi> | 465128 | 677829 |
nsabbi> +------------------------+----------+
nsabbi> 1 row in set (1 min 0.32 sec)
>From your old email:
CREATE TABLE keywords (
keyword varchar(128) NOT NULL,
codice varchar(16) NOT NULL,
timestamp int NOT NULL,
soundekw varchar(32) NOT NULL,
PRIMARY KEY(keyword, codice),
) TYPE=InnoDB;
explain SELECT DISTINCT a.* FROM articoli a, keywords k WHERE a.codice =
k.codice AND keyword IN ('tubo') GROUP BY a.codice HAVING
count(k.keyword)=1 limit 1, 26;
+-------+--------+---------------+---------+---------+----------+--------+------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+--------+---------------+---------+---------+----------+--------+------------------------------------------+
| k | range | PRIMARY | PRIMARY | 128 | NULL | 437735 |
where used; Using index; Using temporary |
| a | eq_ref | PRIMARY | PRIMARY | 16 | k.codice | 1 |
|
+-------+--------+---------------+---------+---------+----------+--------+------------------------------------------+
2 rows in set (4.21 sec)
<cut>
The above shows where the problem is.
The InnoDB range estimator thinks that there is 437735 rows that
matches the WHERE part:
keyword IN ('tubo')
and this is what confuses the optimizer.
What do does the following query return:
explain SELECT count(*) FROM keywords WHERE keyword IN ('tubo');
If this also returns 437735 in the rows column, then this is the
problem!
We did just release 3.23.44 which has some fixes in the InnoDB range
estimator (It will show up on our website within a few hours); If
this version doesn't fix this problem, we would like to have a copy of
your table so that we can repeat and fix this problem. If this is ok,
ftp it to ftp://support.mysql.com/pub/mysql/secret and send Heikki and
me a note about this and we will check this out!
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
