Mailing List Archive


Back to the month index Back to the list index
Joseph Bueno (jbueno@magic.fr)
Sun, 25 Apr 1999 11:49:09 +0200 

Message-ID: <3722E515.6F0D9EB7@magic.fr>
Date: Sun, 25 Apr 1999 11:49:09 +0200
From: Joseph Bueno <jbueno@magic.fr>
Subject: BUG: Indexes are not used when 'where' condition contains parenthesis.

Hello,

You will find below the details of a problem that adversely
affect performance of queries on large tables.

Of course, when the problem is known, the workaround is simple
(just remove the parenthesis) but I lost a lot of time finding
why some of my requests where *very* slow on large tables.

Thanks for your help. 

-- 
Joseph Bueno
jbueno@magic.fr

*****************************************************************
msqlamin version ************************************************

Version Details :-

        msqladmin version       2.0.9
        mSQL server version     2.0.9
        mSQL protocol version   23
        mSQL connection         Localhost via UNIX socket
        Target platform         Linux-2.0.36-i586

Configuration Details :-

        Default config file     /usr/local/Hughes/msql.conf
        TCP socket              1114
        UNIX socket             /usr/local/Hughes/msql2.sock
        mSQL user               msql
        Admin user              root
        Install directory       /usr/local/Hughes
        PID file location       /usr/local/Hughes/msql2d.pid
        Memory Sync Timer       30
        Hostname Lookup         True

*****************************************************************
msqldump ********************************************************
#
# mSQL Dump  (requires mSQL 2.0 Beta 5 or newer)
#
# Host: localhost    Database: message
#--------------------------------------------------------

#
# Table structure for table 'messages'
#
CREATE TABLE messages (
  msg_no INT NOT NULL,
  exp CHAR(20) NOT NULL,
  dest CHAR(20) NOT NULL,
  msg TEXT(80) NOT NULL
) \g

CREATE   INDEX idx2 ON messages (
        exp,
        dest
) \g

CREATE UNIQUE  INDEX idx1 ON messages (
        msg_no
) \g

#
# Dumping data for table 'messages'
#

INSERT INTO messages  VALUES (1,'joe','jack','message 1')\g
INSERT INTO messages  VALUES (2,'jack','joe','message 2')\g

*****************************************************************
requests ********************************************************

request 1 : select * from messages where (exp='jack' and dest='joe')

request 2 : select * from messages where exp='jack' and dest='joe'

*****************************************************************
problem description *********************************************

Summary :
Indexes are not used when 'where' condition contains parenthesis.

Details :
It appears that request 1 does not use any index but request 2 does.

The example above is a small database that helps reproduce the
problem. I have observed that it happens with any request with
parenthesis on any database.

This test has been run just after installing msql 2.0.9 and
without any modification in mSQL default configuration files.

msql user has been added to /etc/passwd. This is the only system
file modified for the test.

Below is a trace of msql2d that shows the problem

*****************************************************************
msql2d trace ****************************************************

$ MSQL_DEBUG=query:access /usr/local/Hughes/bin/msql2d

Mini SQL Version 2.0.9
Copyright (c) 1993-94 David J. Hughes
Copyright (c) 1995-99 Hughes Technologies Pty Ltd.
All rights reserved.

        Loading configuration from '/usr/local/Hughes/msql.conf'.
        Server process reconfigured to accept 200 connections.

-------------------------------------------------------
MSQL_DEBUG found.  msqld started with the following :-

Debug level : query
Debug level : access

-------------------------------------------------------

        Server running as user 'msql'.
        Server mode is Read/Write.

Warning : No ACL file.  Using global read/write access.

[msqld] Query = select * from messages where exp='jack' and dest='joe'

[msqld] setupCandidate() : Using IDX 0 for messages
[msqld] getCandidate() : using IDX 'idx2' on messages
[msqld] getCandidate() : IDX key on messages = 'jack','1801675114'
[msqld] getCandidate() : IDX on messages => 1
[msqld] rowRead() : 1 of messages - Active
[msqld] getCandidate() : using IDX 'idx2' on messages
[msqld] getCandidate() : IDX key on messages = 'jack','1801675114'
[msqld] getCandidate() : IDX on messages => NO_POS

[msqld] Query = select * from messages where (exp='jack' and dest='joe')

[msqld] setupCandidate() : Using SEQ for messages
[msqld] getCandidate() : SEQ on messages => 0
[msqld] rowRead() : 0 of messages - Active
[msqld] getCandidate() : SEQ on messages => 1
[msqld] rowRead() : 1 of messages - Active
[msqld] getCandidate() : SEQ on messages => NO_POS
                                                                

*****************************************************************
From gary.wong@spingroup.com  Mon Apr 26 02:31:10 1999
Received: from mail.spingroup.com ([216.136.15.41])
	by services.bunyip.com (8.8.5/8.8.5) with SMTP id CAA08394
	for <msql-list@services.bunyip.com>; Mon, 26 Apr 1999 02:31:10 -0400 (EDT)
Received: from spingroup.com [207.250.190.2] by mail.spingroup.com [216.136.15.41] with SMTP (MDaemon.v2.7.SP4.R) for <msql-list@services.bunyip.com>; Mon, 26 Apr 1999 01:49:06 -0500
Message-ID: <37240818.E7C603BC@spingroup.com>
Date: Mon, 26 Apr 1999 01:30:48 -0500
From: Gary Wong <gary.wong@spingroup.com>
Reply-To: gary.wong@spingroup.com
X-Mailer: Mozilla 4.5 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
To: mblack@picard.csihq.com
CC: Multiple recipients of list <msql-list@services.bunyip.com>
Subject: Re: mSQL 2.0.9 is now available
References: <00ff01be872d$024e8150$32de11cc@csi.cc>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-MDaemon-Deliver-To: msql-list@services.bunyip.com
X-Return-Path: gary.wong@spingroup.com

THANK YOU Mike!

I would've probably spent another 2 hours on this if I didn't happen
to run across your message...

Gary Wong
gary.wong@spingroup.com

Mike Black wrote:
> 
> Small fix -- in msql/net.c need to add a check for EOF on the socket read
> (2.0.9 started spitting out bogus errors where 2.0.8 didn't have the
> fprintf()).
> 
>                 if (!readTimeout)
>                 {
>                         numBytes = NET_READ(fd,buf + offset,remain);
>                         if (numBytes == 0) return(-1); /* EOF has occurred
> */
>                         if (numBytes < 0)
>                         {
>                                 fprintf(stderr,"Socket read on %d for length
> failed : ",fd);
>                                 perror("");
>                                 return(-1);
>                         }
>                 }
> 
> ________________________________________
> Michael D. Black   Principal Engineer
> mblack@csi.cc  407-676-2923,x203
> http://www.csi.cc  Computer Science Innovations
> http://www.csi.cc/~mike  My home page
> FAX 407-676-2355