Re: HELP --- Slow SP

[Hardi OK]

Guys,

So many thanks for you kind help.  I was able to find the culprit, just
adding 1 index i can get as fast as 30 thousands record per hour.  So that I
can proccess 2 million data in about 4 days only.

But now, after my main tables loaded with more than 2 million data (2,9
million), i have another problem with the same SP.  After some time (about
each 25 execution loops), i always got this error message:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

How can this be happening since I am the only who is doing anything with the
DB?

Rgds/Hardi

RE: HELP --- Slow SP

[Quentin Bennett]

The THEORY behind both statements is fine

1. Have a primary, single column integer index, auto incrementing (probably) 
for every record
2. Have a primary index that uniquely identifies your data.

The advantage of 2 is that it is (usually) obvious what the unique 
characteristics of your data are, and so a natural PK emerges.

The advantages of 1 are based on two premises:

a. Indices are all about efficiency, and it the efficiency of comparing 4-byte 
integers is greater than the efficiency of comparing 51 characters of a 
combined key.

b. Relationships need to be simple to allow point a. to work - if the author 
wishes, some time in the future, to create a relationship to this table, s/he 
has two choices - create the child table with a foreign key containing all 
three elements of the original primary key or add a new auto-increment primary 
key to his_msisdn_imei_activ_hist at that stage. If, at that time, the table is 
involved in a 24x7x52 system with 100s millions of records, then adding a new 
column and index may not be practical.

So, if your system is a small, stable one and will remain that way, index 
efficiency is less of an issue, and the use of a 51-byte multi column index is 
not a problem. However, if you want to design in future proofing, get in to the 
habit of putting a single column integer, auto_increment primary key on every 
table (or at least considering doing so!)

The speed of MySQL can lead to some bad habits that don't transfer well to 
other DBMS products, and good practice is good practice anywhere.

Have an awesome day.

Quentin

P.S. 51 bytes assumes DATETIME is 8 bytes, but it may be 6?

-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Friday, 28 April 2006 8:56 p.m.
To: mysql@lists.mysql.com
Subject: Re: HELP --- Slow SP



  CREATE TABLE `his_msisdn_imei_activ_hist` (
  `MSISDN` varchar(23) NOT NULL,
  `ACTIV_IMEI` varchar(20) NOT NULL,
  `ACTIV_PHONE_TYPE` varchar(100) NOT NULL,
  `PREV_IMEI` varchar(20) default NULL,
  `PREV_PHONE_TYPE` varchar(100) default NULL,
  `ACTIV_TIME` datetime NOT NULL,
  PRIMARY KEY (`MSISDN`,`ACTIV_IMEI`,`ACTIV_TIME`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 
 This primary key is a bad idea. A VERY VERY bad idea. For starters, a 
 primary key should have ONE field, not THREE. While it is allowed, it's 
 not going to help performance at all. Next is that the primary key 

Care for a fight over this one? :-)

A primary key should be the primary key. If this is 3 columns, or 1
varchar column, it's all fine.

I agree with your point of the ACTIV_TIME being a bad candidate
for being part of a PK though.

Oh, and having multiple columns in a PK does not mean you cannot
create additional indices as/if required.

All in all, your statement about multiple columns in a PK is a very very
bad statement ;-)

 should be a numeric field. You've got varchars and datetimes! Yuck! If 
 you want to enforce a rule such as restricting duplicate values, then 
 start by creating yourself a sane primary key ( an unsigned int, for 
 example ), and *THEN* put an index ( with your don't allow duplicates 
 rule ) across your (`MSISDN`,`ACTIV_IMEI`,`ACTIV_TIME`) fields.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The information contained in this email is privileged and confidential and
intended for the addressee only. If you are not the intended recipient, you
are asked to respect that confidentiality and not disclose, copy or make use
of its contents. If received in error you are asked to destroy this email
and contact the sender immediately. Your assistance is appreciated.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: HELP --- Slow SP

[Martijn Tonies]

  CREATE TABLE `his_msisdn_imei_activ_hist` (
  `MSISDN` varchar(23) NOT NULL,
  `ACTIV_IMEI` varchar(20) NOT NULL,
  `ACTIV_PHONE_TYPE` varchar(100) NOT NULL,
  `PREV_IMEI` varchar(20) default NULL,
  `PREV_PHONE_TYPE` varchar(100) default NULL,
  `ACTIV_TIME` datetime NOT NULL,
  PRIMARY KEY (`MSISDN`,`ACTIV_IMEI`,`ACTIV_TIME`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 
 This primary key is a bad idea. A VERY VERY bad idea. For starters, a 
 primary key should have ONE field, not THREE. While it is allowed, it's 
 not going to help performance at all. Next is that the primary key 

Care for a fight over this one? :-)

A primary key should be the primary key. If this is 3 columns, or 1
varchar column, it's all fine.

I agree with your point of the ACTIV_TIME being a bad candidate
for being part of a PK though.

Oh, and having multiple columns in a PK does not mean you cannot
create additional indices as/if required.

All in all, your statement about multiple columns in a PK is a very very
bad statement ;-)

 should be a numeric field. You've got varchars and datetimes! Yuck! If 
 you want to enforce a rule such as restricting duplicate values, then 
 start by creating yourself a sane primary key ( an unsigned int, for 
 example ), and *THEN* put an index ( with your don't allow duplicates 
 rule ) across your (`MSISDN`,`ACTIV_IMEI`,`ACTIV_TIME`) fields.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

HELP --- Slow SP

[Hardi OK]

Hi Forums,

I have a frustrating problem on my Stored Procedure. It can only proccess
about 100 records in 10 minutes. I have 2 million initial records that need
to processed. Meaning that with this speed i will around 200 days to finish
all of them.
To make it worse, the data itself grows at least another 100 records per
hour.

Really appreciated if anybody can help to speed this up.

Rgds/Hardi


---

Here's the SP definition:

DELIMITER $$

DROP PROCEDURE IF EXISTS `hisdb`.`SP_HIS_NTMS_MSISDN_EDUM_REQ_PROVISIONING`
$$
CREATE PROCEDURE `SP_HIS_NTMS_MSISDN_EDUM_REQ_PROVISIONING`(
IN inMSISDN VARCHAR(23),
IN inIMEI VARCHAR(20),
IN inIMSI VARCHAR(20),
IN inPHONE_TYPE VARCHAR(100),
IN inIMEI_SOURCE SMALLINT(5),
IN inREQ_TIMESTAMP BIGINT(20))
BEGIN

DECLARE vIS_DELETED ENUM('Yes', 'No');
DECLARE vIMEI VARCHAR(20);
DECLARE vLAST_ACTIV_IMEI VARCHAR(20);
DECLARE vLAST_ACTIV_PHONE_TYPE VARCHAR(100);

DECLARE FLAG_LABEL INT DEFAULT 0;
DECLARE tmpLSTMODIF_TIME DATETIME;

DECLARE vNO_OF_IMEI_CHANGES SMALLINT(5);
DECLARE vNO_OF_TAC_CHANGES SMALLINT(5);
DECLARE stmt VARCHAR(200);
DECLARE done INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

SET tmpLSTMODIF_TIME = null;
SELECT LSTMODIF_TIME INTO tmpLSTMODIF_TIME FROM HIS_MSISDN_IMEI WHERE MSISDN
= inMSISDN;
IF tmpLSTMODIF_TIME is null THEN
SET FLAG_LABEL = 1;
ELSE
BEGIN
IF (tmpLSTMODIF_TIME  FROM_UNIXTIME(inREQ_TIMESTAMP)) THEN
SET FLAG_LABEL = 1;
ELSE
SET FLAG_LABEL = 2;
END IF;
END;
END IF;

SET vIS_DELETED = null;

SELECT IS_DELETED_FRM_NTMS INTO vIS_DELETED FROM HIS_MSISDN_IMEI_PROV_HIST
WHERE MSISDN = inMSISDN AND IMEI = inIMEI;
IF vIS_DELETED is not null THEN
BEGIN
IF vIS_DELETED = 'Yes' THEN
INSERT INTO HIS_MSISDN_IMEI_PROV_HIST(MSISDN, IMEI, IMSI, PHONE_TYPE,
PROV_SOURCE, PROV_TIMESTAMP, IS_DELETED_FRM_NTMS)
VALUES(inMSISDN, inIMEI, inIMSI, inPHONE_TYPE, inIMEI_SOURCE,
FROM_UNIXTIME(inREQ_TIMESTAMP), 'No');
END IF;

END;

ELSE
BEGIN

INSERT INTO HIS_MSISDN_IMEI_PROV_HIST(MSISDN, IMEI, IMSI, PHONE_TYPE,
PROV_SOURCE, PROV_TIMESTAMP, IS_DELETED_FRM_NTMS)
VALUES(inMSISDN, inIMEI, inIMSI, inPHONE_TYPE, inIMEI_SOURCE,
FROM_UNIXTIME(inREQ_TIMESTAMP), 'No');


END;

END IF;

IF (FLAG_LABEL = 1) THEN
BEGIN
SET vIMEI = null;
SELECT IMEI INTO vIMEI FROM HIS_MSISDN_IMEI WHERE MSISDN = inMSISDN;
IF vIMEI is not null THEN
BEGIN
IF vIMEI = inIMEI THEN
UPDATE HIS_MSISDN_IMEI SET LSTMODIF_TIME = FROM_UNIXTIME(inREQ_TIMESTAMP);
ELSE
BEGIN
SELECT COUNT(DISTINCT(IMEI)), COUNT(DISTINCT(SUBSTRING(IMEI, 1, 6))) INTO
vNO_OF_IMEI_CHANGES, vNO_OF_TAC_CHANGES
FROM HIS_MSISDN_IMEI_PROV_HIST WHERE MSISDN = inMSISDN;
UPDATE HIS_MSISDN_IMEI
SET IMEI = inIMEI, PHONE_TYPE = inPHONE_TYPE, LSTMODIF_TIME =
FROM_UNIXTIME(inREQ_TIMESTAMP), NO_OF_IMEI_CHANGES = vNO_OF_IMEI_CHANGES,
NO_OF_TAC_CHANGES = vNO_OF_TAC_CHANGES
WHERE MSISDN = inMSISDN;
END;
END IF;
END;
ELSE
BEGIN

SELECT COUNT(DISTINCT(IMEI)), COUNT(DISTINCT(SUBSTRING(IMEI, 1, 6))) INTO
vNO_OF_IMEI_CHANGES, vNO_OF_TAC_CHANGES
FROM HIS_MSISDN_IMEI_PROV_HIST WHERE MSISDN = inMSISDN;

INSERT INTO HIS_MSISDN_IMEI (MSISDN, IMEI, IMSI, PHONE_TYPE, LSTMODIF_TIME,
NO_OF_IMEI_CHANGES, NO_OF_TAC_CHANGES)
VALUES (inMSISDN, inIMEI, inIMSI, inPHONE_TYPE,
FROM_UNIXTIME(inREQ_TIMESTAMP), vNO_OF_IMEI_CHANGES, vNO_OF_TAC_CHANGES);
END;

END IF;

SET vLAST_ACTIV_IMEI = null;
SET vLAST_ACTIV_PHONE_TYPE = null;
SELECT ACTIV_IMEI, ACTIV_PHONE_TYPE INTO vLAST_ACTIV_IMEI,
vLAST_ACTIV_PHONE_TYPE FROM HIS_MSISDN_IMEI_ACTIV_HIST WHERE MSISDN =
inMSISDN ORDER BY ACTIV_TIME DESC LIMIT 1;
INSERT INTO HIS_MSISDN_IMEI_ACTIV_HIST (MSISDN, ACTIV_IMEI,
ACTIV_PHONE_TYPE, PREV_IMEI, PREV_PHONE_TYPE, ACTIV_TIME)
VALUES (inMSISDN, inIMEI, inPHONE_TYPE, vLAST_ACTIV_IMEI,
vLAST_ACTIV_PHONE_TYPE, FROM_UNIXTIME(inREQ_TIMESTAMP));

END;
END IF;

IF (FLAG_LABEL = 2) THEN
BEGIN

SET vLAST_ACTIV_IMEI = null;
SET vLAST_ACTIV_PHONE_TYPE = null;
SELECT ACTIV_IMEI, ACTIV_PHONE_TYPE INTO vLAST_ACTIV_IMEI,
vLAST_ACTIV_PHONE_TYPE
FROM HIS_MSISDN_IMEI_ACTIV_HIST WHERE MSISDN = inMSISDN AND ACTIV_TIME 
FROM_UNIXTIME(inREQ_TIMESTAMP) ORDER BY ACTIV_TIME DESC LIMIT 1;
IF (vLAST_ACTIV_IMEI is null AND vLAST_ACTIV_PHONE_TYPE is null) THEN
BEGIN
IF EXISTS(SELECT MSISDN FROM HIS_MSISDN_IMEI_ACTIV_HIST WHERE MSISDN =
inMSISDN AND ACTIV_TIME  FROM_UNIXTIME(inREQ_TIMESTAMP)) THEN
BEGIN
INSERT INTO HIS_MSISDN_IMEI_ACTIV_HIST (MSISDN, ACTIV_IMEI,
ACTIV_PHONE_TYPE, PREV_IMEI, PREV_PHONE_TYPE, ACTIV_TIME)
VALUES (inMSISDN, inIMEI, inPHONE_TYPE, NULL, NULL,
FROM_UNIXTIME(inREQ_TIMESTAMP));
UPDATE HIS_MSISDN_IMEI_ACTIV_HIST SET PREV_IMEI = inIMEI, PREV_PHONE_TYPE =
inPHONE_TYPE
WHERE MSISDN = inMSISDN AND ACTIV_TIME  FROM_UNIXTIME(inREQ_TIMESTAMP)
ORDER BY ACTIV_TIME ASC LIMIT 1;

END;

ELSE
INSERT INTO HIS_MSISDN_IMEI_ACTIV_HIST (MSISDN, ACTIV_IMEI,
ACTIV_PHONE_TYPE, PREV_IMEI, PREV_PHONE_TYPE, ACTIV_TIME)
VALUES (inMSISDN, inIMEI, inPHONE_TYPE, NULL, NULL,
FROM_UNIXTIME(inREQ_TIMESTAMP));
END IF;
END;

ELSE
BEGIN
INSERT 

Re: HELP --- Slow SP

[Daniel Kasak]

Hardi OK wrote:

Hi Forums,

I have a frustrating problem on my Stored Procedure. It can only proccess
about 100 records in 10 minutes. I have 2 million initial records that need
to processed. Meaning that with this speed i will around 200 days to finish
all of them.
To make it worse, the data itself grows at least another 100 records per
hour.

Really appreciated if anybody can help to speed this up.
  


Without looking too much at the actual SP, I can tell you now that 
you're not using any indexes AT ALL.



CREATE TABLE `his_msisdn_imei_activ_hist` (
`MSISDN` varchar(23) NOT NULL,
`ACTIV_IMEI` varchar(20) NOT NULL,
`ACTIV_PHONE_TYPE` varchar(100) NOT NULL,
`PREV_IMEI` varchar(20) default NULL,
`PREV_PHONE_TYPE` varchar(100) default NULL,
`ACTIV_TIME` datetime NOT NULL,
PRIMARY KEY (`MSISDN`,`ACTIV_IMEI`,`ACTIV_TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  


This primary key is a bad idea. A VERY VERY bad idea. For starters, a 
primary key should have ONE field, not THREE. While it is allowed, it's 
not going to help performance at all. Next is that the primary key 
should be a numeric field. You've got varchars and datetimes! Yuck! If 
you want to enforce a rule such as restricting duplicate values, then 
start by creating yourself a sane primary key ( an unsigned int, for 
example ), and *THEN* put an index ( with your don't allow duplicates 
rule ) across your (`MSISDN`,`ACTIV_IMEI`,`ACTIV_TIME`) fields.


Next point is that MySQL will only make use of an index in a join or a 
where clause if ONLY that field is included in the index. If you pack 3 
fields into an index and then try to join on ONLY ONE field, the index 
can't be used. So look at your joins and where clauses and make sure 
your indexes match.



--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: HELP --- Slow SP

[Quentin Bennett]

[snip]

Next point is that MySQL will only make use of an index in a join or a 
where clause if ONLY that field is included in the index. If you pack 3 
fields into an index and then try to join on ONLY ONE field, the index 
can't be used. So look at your joins and where clauses and make sure 
your indexes match.

[\snip]

I think that you can use the left most columns of the index, without including 
the remainder.

So a join on 
MSISDN 
or
MSIISDN and ACTIV_IMEI

may use the index (depending on whatever else is going on)

but a join on ACTIV_IMEI only or MSISDN and ACTIV_TIME won't.

Quentin




-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The information contained in this email is privileged and confidential and
intended for the addressee only. If you are not the intended recipient, you
are asked to respect that confidentiality and not disclose, copy or make use
of its contents. If received in error you are asked to destroy this email
and contact the sender immediately. Your assistance is appreciated.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: HELP --- Slow SP

[Daniel Kasak]

Quentin Bennett wrote:

I think that you can use the left most columns of the index, without including 
the remainder.
  


That's wasn't my understanding of how things work, but I've just checked 
the documentation, and it looks like you're right:


docs

MySQL cannot use a partial index if the columns do not form a leftmost 
prefix of the index. Suppose that you have the |SELECT| statements shown 
here:


SELECT * FROM /|tbl_name|/ WHERE col1=/|val1|/;
SELECT * FROM /|tbl_name|/ WHERE col1=/|val1|/ AND col2=/|val2|/;

SELECT * FROM /|tbl_name|/ WHERE col2=/|val2|/;
SELECT * FROM /|tbl_name|/ WHERE col2=/|val2|/ AND col3=/|val3|/;

If an index exists on |(col1, col2, col3)|, only the first two queries 
use the index. The third and fourth queries do involve indexed columns, 
but |(col2)| and |(col2, col3)| are not leftmost prefixes of |(col1, 
col2, col3)|.


/docs

That's pretty strange. Anyway, what output do you get if you put 
'explain ' in front of your queries? Are the indexes being used?


--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: HELP --- Slow SP

[Hardi OK]

Hi,

When I used the EXPLAIN command, i see that all of my query are using the
correct index.  That's why i was quite sure that index won't be the cause of
my slow query problem.

So, i now should alter the table: remove primary key and recreate index?

Many thanks,
Hardi


On 4/28/06, Daniel Kasak [EMAIL PROTECTED] wrote:

 Quentin Bennett wrote:
  I think that you can use the left most columns of the index, without
 including the remainder.
 

 That's wasn't my understanding of how things work, but I've just checked
 the documentation, and it looks like you're right:

 docs

 MySQL cannot use a partial index if the columns do not form a leftmost
 prefix of the index. Suppose that you have the |SELECT| statements shown
 here:

 SELECT * FROM /|tbl_name|/ WHERE col1=/|val1|/;
 SELECT * FROM /|tbl_name|/ WHERE col1=/|val1|/ AND col2=/|val2|/;

 SELECT * FROM /|tbl_name|/ WHERE col2=/|val2|/;
 SELECT * FROM /|tbl_name|/ WHERE col2=/|val2|/ AND col3=/|val3|/;

 If an index exists on |(col1, col2, col3)|, only the first two queries
 use the index. The third and fourth queries do involve indexed columns,
 but |(col2)| and |(col2, col3)| are not leftmost prefixes of |(col1,
 col2, col3)|.

 /docs

 That's pretty strange. Anyway, what output do you get if you put
 'explain ' in front of your queries? Are the indexes being used?

 --
 Daniel Kasak
 IT Developer
 NUS Consulting Group
 Level 5, 77 Pacific Highway
 North Sydney, NSW, Australia 2060
 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
 email: [EMAIL PROTECTED]
 website: http://www.nusconsulting.com.au

Re: HELP --- Slow SP

[Daniel Kasak]

Hardi OK wrote:

Hi,
 
When I used the EXPLAIN command, i see that all of my query are using 
the correct index.  That's why i was quite sure that index won't be 
the cause of my slow query problem.
 
So, i now should alter the table: remove primary key and recreate index?
 
If your queries are using the index, then setting up a new primary key 
and indexes will not help. Personally, I would do it anyway, but this 
won't help out in your current case.


--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: HELP --- Slow SP

[Quentin Bennett]

Hi Hardi,
 
You may need to supply a bit more information - table formats, query string, 
output from EXPLAIN - to allow more detailed diagnosis.
 
Quentin

-Original Message-
From: Hardi OK [mailto:[EMAIL PROTECTED]
Sent: Friday, 28 April 2006 1:14 p.m.
To: Daniel Kasak
Cc: Quentin Bennett; mysql@lists.mysql.com
Subject: Re: HELP --- Slow SP


Hi,
 
When I used the EXPLAIN command, i see that all of my query are using the 
correct index.  That's why i was quite sure that index won't be the cause of my 
slow query problem.
 
So, i now should alter the table: remove primary key and recreate index?
 
Many thanks,
Hardi

 
On 4/28/06, Daniel Kasak  [EMAIL PROTECTED] wrote: 

Quentin Bennett wrote:
 I think that you can use the left most columns of the index, without 
 including the remainder. 


That's wasn't my understanding of how things work, but I've just checked
the documentation, and it looks like you're right:

docs

MySQL cannot use a partial index if the columns do not form a leftmost 
prefix of the index. Suppose that you have the |SELECT| statements shown
here:

SELECT * FROM /|tbl_name|/ WHERE col1=/|val1|/;
SELECT * FROM /|tbl_name|/ WHERE col1=/|val1|/ AND col2=/|val2|/;

SELECT * FROM /|tbl_name|/ WHERE col2=/|val2|/; 
SELECT * FROM /|tbl_name|/ WHERE col2=/|val2|/ AND col3=/|val3|/;

If an index exists on |(col1, col2, col3)|, only the first two queries
use the index. The third and fourth queries do involve indexed columns, 
but |(col2)| and |(col2, col3)| are not leftmost prefixes of |(col1,
col2, col3)|.

/docs

That's pretty strange. Anyway, what output do you get if you put
'explain ' in front of your queries? Are the indexes being used? 

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au




The information contained in this email is privileged and confidential and
intended for the addressee only. If you are not the intended recipient, you
are asked to respect that confidentiality and not disclose, copy or make use
of its contents. If received in error you are asked to destroy this email
and contact the sender immediately. Your assistance is appreciated.