MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Shane Allen Date:October 10 2002 7:11pm
Subject:BUG: Only half of the results shown when using order by on unindexed field
View as plain text  
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 <shane@stripped>
> >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 <shane@stripped>

sell.com : Buy & Sell Anything
http://www.sell.com
Thread
Only half of the results shown when using order by on unindexed fieldShane Allen9 Oct
  • BUG: Only half of the results shown when using order by on unindexed fieldShane Allen10 Oct