>Description: See "How-To-Repeat"
>How-To-Repeat: I uploaded a copy of the tables to support.mysql.com/pub/mysql/secret. Each of the tables below contains many rows (from 50-100k per table). We were unable to duplicate with a smaller result set, CREATE TABLE listing ( id int(10) unsigned NOT NULL default '0', seller_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (id), KEY seller_id (seller_id) ) TYPE=MyISAM; CREATE TABLE question ( id int(10) unsigned NOT NULL auto_increment, listing_id int(10) unsigned NOT NULL default '0', buyer_id int(10) unsigned NOT NULL default '0', last_modified timestamp(14) NOT NULL, PRIMARY KEY (id), KEY buyer_id (buyer_id), KEY listing_id (listing_id) ) TYPE=MyISAM; CREATE TABLE user ( id int(10) unsigned NOT NULL default '0', username char(15) NOT NULL default '', PRIMARY KEY (id) ) TYPE=MyISAM; SELECT COUNT(*) FROM question, listing, user ub, user us WHERE question.listing_id = listing.id AND question.buyer_id = ub.id AND listing.seller_id = us.id AND (ub.id = 2346 OR us.id = 2346) ORDER BY question.last_modified DESC; +----------+ | COUNT(*) | +----------+ | 4 | +----------+ SELECT question.id AS question_id, listing.id AS listing_id, ub.id AS buyer_id, ub.username AS buyer_name, us.id AS seller_id, us.username as seller_name FROM question, listing, user ub, user us WHERE question.listing_id = listing.id AND question.buyer_id = ub.id AND listing.seller_id = us.id AND (ub.id = 2346 OR us.id = 2346) ORDER BY question.last_modified DESC; +-------------+------------+----------+------------+-----------+-------------+ | question_id | listing_id | buyer_id | buyer_name | seller_id | seller_name | +-------------+------------+----------+------------+-----------+-------------+ | 6850 | 12909 | 2346 | user2346 | 4879 | user4879 | | 4823 | 39039 | 2346 | user2346 | 14831 | user14831 | +-------------+------------+----------+------------+-----------+-------------+ 2 rows in set (0.00 sec) # Adding SQL_CALC_FOUND_ROWS shows correct results SELECT SQL_CALC_FOUND_ROWS question.id AS question_id, listing.id AS listing_id, ub.id AS buyer_id, ub.username AS buyer_name, us.id AS seller_id, us.username as seller_name FROM question, listing, user ub, user us WHERE question.listing_id = listing.id AND question.buyer_id = ub.id AND listing.seller_id = us.id AND (ub.id = 2346 OR us.id = 2346) ORDER BY question.last_modified DESC; +-------------+------------+----------+------------+-----------+-------------+ | question_id | listing_id | buyer_id | buyer_name | seller_id | seller_name | +-------------+------------+----------+------------+-----------+-------------+ | 6850 | 12909 | 2346 | user2346 | 4879 | user4879 | | 4823 | 39039 | 2346 | user2346 | 14831 | user14831 | | 6084 | 49920 | 2346 | user2346 | 14704 | user14704 | | 6223 | 48014 | 2346 | user2346 | 7386 | user7386 | +-------------+------------+----------+------------+-----------+-------------+ 4 rows in set (0.00 sec) # Ordering by a indexed column shows correct results SELECT question.id AS question_id, listing.id AS listing_id, ub.id AS buyer_id, ub.username AS buyer_name, us.id AS seller_id, us.username as seller_name FROM question, listing, user ub, user us WHERE question.listing_id = listing.id AND question.buyer_id = ub.id AND listing.seller_id = us.id AND (ub.id = 2346 OR us.id = 2346) ORDER BY question.id DESC; +-------------+------------+----------+------------+-----------+-------------+ | question_id | listing_id | buyer_id | buyer_name | seller_id | seller_name | +-------------+------------+----------+------------+-----------+-------------+ | 6850 | 12909 | 2346 | user2346 | 4879 | user4879 | | 6223 | 48014 | 2346 | user2346 | 7386 | user7386 | | 6084 | 49920 | 2346 | user2346 | 14704 | user14704 | | 4823 | 39039 | 2346 | user2346 | 14831 | user14831 | +-------------+------------+----------+------------+-----------+-------------+ 4 rows in set (0.00 sec) Also, adding an index to the order by column fixes the problem. In other testing, adding a combined index: ALTER TABLE question ADD INDEX combined (last_modified, listing_id, buyer_id); fixed the problem, however, adding: ALTER TABLE question ADD INDEX combined (buyer_id, listing_id, last_modified); did not. >Fix: Adding indexes (See "How-To-Repeat") >Submitter-Id: >Originator: Shane Allen <[EMAIL PROTECTED]> >Organization: sell.com >MySQL support: none >Synopsis: >Severity: >Priority: >Category: mysql >Class: >Release: mysql-4.0.4-beta (Source distribution) >Server: /usr/local/bin/mysqladmin Ver 8.37 Distrib 4.0.4-beta, for pc-linux-gnu on >i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.4-beta-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 4 hours 31 min 20 sec Threads: 2 Questions: 18 Slow queries: 0 Opens: 229 Flush tables: 1 Open tables: 114 Queries per second avg: 0.001 >Environment: System: Linux aqua.sell.int 2.4.19 #1 SMP Mon Sep 2 15:38:05 CDT 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/3.1.1/specs Configured with: /mnt/data/gcc-3.1/gcc-3.1-3.1.1ds3/src/configure -v --enable-languages=c,c++,java,f77,proto,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.1 --enable-shared --with-system-zlib --enable-long-long --enable-nls --without-included-gettext --enable-clocale=gnu --enable-__cxa_atexit --enable-threads=posix --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.1.1 Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Sep 2 17:26 /lib/libc.so.6 -> libc-2.2.5.so -rwxr-xr-x 1 root root 1145456 Aug 14 11:26 /lib/libc-2.2.5.so -rw-r--r-- 1 root root 2376426 Aug 14 11:27 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Aug 14 11:27 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler --enable-thread-safe-client --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --enable-local-infile --with-other-libc=/usr/local/mysql-glibc-2.2.5 --prefix=/usr/local --with-extra-charsets=none --without-innodb --without-isam --------------------------------------------------------------------- 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
