I haven't seen any traffic on this thread, so I figured I'd repost it with a slightly more catchy subject. :)
Has anyone looked into this? On Tue, Oct 08, 2002 at 09:12:13PM -0500, Shane Allen wrote: > >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 -- Shane Allen <[EMAIL PROTECTED]> sell.com : Buy & Sell Anything http://www.sell.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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
