Re: mysqldump and foreign keys
Nico Rittner wrote: Hi Frank what I meant was: are you sure that the tables which are in your database are defined correctly, i.e. have the actions? Yes, if i try to delete or update a record which is referenced by another i get mysql error #1217 , which should be correct. might have been lost somewhere. Can you post the statement which has been used to create the groups table in the first place. First, i created them without foreign key clauses. After that i added them using 'alter table', but i think this does not matter. Thanks, Nico Hi Nico, the 1217 error means that your foreign key constraints are working, but not necessarily that the action clauses are defined properly (or at all). Anyway, either I don't get what your problem really is, or there is something wrong with 4.1.13. I've tried an on delete clause on 4.1.14 and it worked fine (also using alter table to add the foreign key). Maybe you can upgrade your server? If you want to, I can try the steps you've used to create the tables. Just post the SQL or send it to me. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HELP --- Slow SP
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: need help for my jointure
On 4/26/06, Shawn Green [EMAIL PROTECTED] wrote: --- Patrick Aljord [EMAIL PROTECTED] wrote: On 4/26/06, Patrick Aljord [EMAIL PROTECTED] wrote: I have a table confs like this: id int 5 auto_increment primary key; conf text; and another table conf_ip like this: id int 5 auto_increment primary key; conf_id int 5; ==foreing key of confs ip varchar 150; ok, sorry all for not being specific enough. I figured a query which is half working like that: select c.id, c.conf from confs as c inner join conf_ip as i on c.id = i.conf_id where i.ip!='someip I would like the select to return all the c.id that are in conf and all that don't have a c.ip='someip'. The problem is that if there is a record that have the c.id=i.conf_id but an ip different than localhost, my query will still return the corresponding c.id and I don't want that. I tried something like that but couldn't get it to work: select c.id, c.conf from confs as c inner join conf_ip as i on c.id = i.conf_id where (select i.ip from conf_ip where ip='$ip')=0 hope yo usee what I mean Thanx in advance Pat Yes, I think I do. What you have is a table of 'conf' (whatever they are) that can have multiple IP addresses (see table `conf_ip`). What I think you want to know is which confs do not have a particular IP address. There are several ways to write this query. One of the more straightforward ways to write this is to use a temporary table (depending on your version, this should work well written as a subquery, too). CREATE TEMPORARY TABLE tmpMatches (key(conf_id) SELECT DISTINCT conf_id FROM conf_ip WHERE ip = 'some_ip_value'; SELECT c.id, c.conf FROM confs c LEFT JOIN tmpMatches m ON m.conf_id = c.id WHERE m.conf_id IS NULL; DROP TEMPORARY TABLE tmpMatches; What we do is generate a list of all of the conf's that do have the target IP and save that list into a temporary table. Then we run a query that finds every conf EXCEPT those we just located in the first step. Last we clean up after ourselves by getting rid of the temp table. Make sense? Yes it makes sense, thanx a lot Shawn. As I'm using it my php code, I think I'd rather do it with a sub query. Here is how I do it: SELECT c.id, c.conf FROM confs c LEFT JOIN conf_ip i ON i.conf_id = c.id WHERE c.id NOT IN (SELECT DISTINCT conf_id FROM conf_ip WHERE ip = 'some_ip_value); is that correct? I can't test it right now thanx in advance Pat
Update not working in a script, but going fine running with MySQL Browser
Hi guys. Got a problem when adding records to an existing table, as part of a sql script. There are a couple of fields that don't get added as they should (respaccstopframe and acstpradiusid). This is the update command: insert into temp_s1 (accsesid, respaccstopframe, acstpradiusid, sgsnip, sgsnip_subnet, sgsnip_last, ggsnip, subip, startime, stoptime) select r1.radius_acct_session_id, t.frame_number, r1.radius_id, r1.radius_XXX_Address, substring_index(r1.radius_XXX_Address,.,3), substring_index(r1.radius_XXX_Address,.,-1),r1.radius_NAS_IP_Address, r1.radius_Framed_IP_Address, f.frame_time, r1.frame_time from radius r1, temp_respaccstopframe t, frame f where r1.frame_number = t.r1_fn and f.frame_number= t.syntstart_number and t.noacstart=1 and t.started_in =0; I didn't received any error, but fields were not added. I added an update command for those two fields just right after in the script. # no preguntes por que lo hago dos veces...los duendes del sw... update temp_s1 s1, temp_respaccstopframe t set s1.respaccstopframe= t.frame_number, s1.acstpradiusid=t.radius_id where s1.accsesid=t.radius_acct_session_id and s1.ggsnip=t.radius_NAS_IP_Address and t.noacstart=1 and t.started_in =0; Same result. Then I run the update from MyQSL Browser and then worked fine...!!? I've run same script and update command thru Browser with same table in both MySQLv4.1 and v5.0. No change. I know it sounds crazy, but can anyone add some light on this? TIA. Luis. _ ¿Estás pensando en cambiar de coche? Todas los modelos de serie y extras en MSN Motor. http://motor.msn.es/researchcentre/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update not working in a script, but going fine running with MySQL Browser
Hi Luis. It sounds like your script may not be initialising those two variables correctly. Are the variables being set properly in your script before sending the insert query to mysql? MySQL will quite happily insert an empty string value, '' into a char field without generating an error message. Try echoing the content of the two variables that are not being set in mysql to screen, just before you send the query to mysql. Does that shed any light on the problem? Keith In theory, theory and practice are the same; in practice they are not. On Thu, 27 Apr 2006, luis perez wrote: To: mysql@lists.mysql.com From: luis perez [EMAIL PROTECTED] Subject: Update not working in a script, but going fine running with MySQL Browser Hi guys. Got a problem when adding records to an existing table, as part of a sql script. There are a couple of fields that don't get added as they should (respaccstopframe and acstpradiusid). This is the update command: insert into temp_s1 (accsesid, respaccstopframe, acstpradiusid, sgsnip, sgsnip_subnet, sgsnip_last, ggsnip, subip, startime, stoptime) select r1.radius_acct_session_id, t.frame_number, r1.radius_id, r1.radius_XXX_Address, substring_index(r1.radius_XXX_Address,.,3), substring_index(r1.radius_XXX_Address,.,-1),r1.radius_NAS_IP_Address, r1.radius_Framed_IP_Address, f.frame_time, r1.frame_time from radius r1, temp_respaccstopframe t, frame f where r1.frame_number = t.r1_fn and f.frame_number= t.syntstart_number and t.noacstart=1 and t.started_in =0; I didn't received any error, but fields were not added. I added an update command for those two fields just right after in the script. # no preguntes por que lo hago dos veces...los duendes del sw... update temp_s1 s1, temp_respaccstopframe t set s1.respaccstopframe= t.frame_number, s1.acstpradiusid=t.radius_id where s1.accsesid=t.radius_acct_session_id and s1.ggsnip=t.radius_NAS_IP_Address and t.noacstart=1 and t.started_in =0; Same result. Then I run the update from MyQSL Browser and then worked fine...!!? I've run same script and update command thru Browser with same table in both MySQLv4.1 and v5.0. No change. I know it sounds crazy, but can anyone add some light on this? TIA. Luis. _ ¿Estás pensando en cambiar de coche? Todas los modelos de serie y extras en MSN Motor. http://motor.msn.es/researchcentre/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple primary keys
Can someone figure out what's going on. This is the only change I made to this table. Basically I don't want any duplicate rows, so I setup 4 fields to be my primary key. When I do a simple select query it takes nearly 30 seconds to complete. This is affecting my websites and taking a very long time to query the Products. Have I setup this table right? Thank You!! +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | itemID | int(6) | | PRI | 0 | | | classA | int(3) | | PRI | 0 | | | classB | int(3) | | PRI | 0 | | | classC | int(3) | | PRI | 0 | | | picture | varchar(10) | YES | | NULL| | | sex | char(2) | YES | | NULL| | +-+-+--+-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple primary keys
Quoting nngau [EMAIL PROTECTED]: Can someone figure out what's going on. This is the only change I made to this table. Basically I don't want any duplicate rows, so I setup 4 fields to be my primary key. If you don't want any duplicate rows, use UNIQUE, all those primary keys will just take up unwanted space. If you still think these fields should be indexed due to the large number of hits, then use an INDEX. Chris White -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple primary keys
On 4/27/06, nngau [EMAIL PROTECTED] wrote: Can someone figure out what's going on. This is the only change I made to this table. Basically I don't want any duplicate rows, so I setup 4 fields to be my primary key. When I do a simple select query it takes nearly 30 seconds to complete. This is affecting my websites and taking a very long time to query the Products. Have I setup this table right? Thank You!! +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | itemID | int(6) | | PRI | 0 | | | classA | int(3) | | PRI | 0 | | | classB | int(3) | | PRI | 0 | | | classC | int(3) | | PRI | 0 | | | picture | varchar(10) | YES | | NULL| | | sex | char(2) | YES | | NULL| | +-+-+--+-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] You can avoid duplicate rows with only one primary key, unless you have a specific reason for having your primary key span on four columns. Your query being slower depends on how you have indexed your columns relating to your queries. Please give us a sample query which is running slowly.. Kishore Jalleda http://kjalleda.googlepages.com/projects
RE: Multiple primary keys
Thanks all. The query I run is a subquery. I noticed joined query run a lot faster than the sub. This is the subquery: select * from class_c where detail_id in (select classC from item_classification where classb=216) order by detail; This query takes nearly 3 minutes, before it did not take that long. I guess I should use a primary key As an index. I want to be able to add items that I can classify into different classa, classb or classc. Example: Itemid 1025 ClassA: 101 classB: 218 classC: 356 Same item can be put into another class. Itemid 105 ClassA: 101 classb: 218 classC: 357 So not having a primary key/index will slow my queries? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Thursday, April 27, 2006 10:49 AM To: nngau Cc: mysql@lists.mysql.com Subject: Re: Multiple primary keys On 4/27/06, nngau [EMAIL PROTECTED] wrote: Can someone figure out what's going on. This is the only change I made to this table. Basically I don't want any duplicate rows, so I setup 4 fields to be my primary key. When I do a simple select query it takes nearly 30 seconds to complete. This is affecting my websites and taking a very long time to query the Products. Have I setup this table right? Thank You!! +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | itemID | int(6) | | PRI | 0 | | | classA | int(3) | | PRI | 0 | | | classB | int(3) | | PRI | 0 | | | classC | int(3) | | PRI | 0 | | | picture | varchar(10) | YES | | NULL| | | sex | char(2) | YES | | NULL| | +-+-+--+-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] You can avoid duplicate rows with only one primary key, unless you have a specific reason for having your primary key span on four columns. Your query being slower depends on how you have indexed your columns relating to your queries. Please give us a sample query which is running slowly.. Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple primary keys
MySQL will not use the primary key unless you use the left most columns. For a 1 column primary key then it's easy. For a 2 column primary key you can either use the 1st column in the primary key or both columns. If you only reference the 2nd column the query will not use the primary key and will do a full table scan. In your case you are referencing classb which is not the left most collumn in the primary key set. Put the word explain preceding the statement and execute the query. it will show you what keys are used in the query and in your case it is none. You either need to define another KEY with classb as the 1st column in the definition or if all of your queries at least reference classb then you could rebuild the primary key and put classb as the 1st entry in the definition. - Original Message - From: nngau [EMAIL PROTECTED] To: 'Kishore Jalleda' [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, April 27, 2006 9:11 AM Subject: RE: Multiple primary keys Thanks all. The query I run is a subquery. I noticed joined query run a lot faster than the sub. This is the subquery: select * from class_c where detail_id in (select classC from item_classification where classb=216) order by detail; This query takes nearly 3 minutes, before it did not take that long. I guess I should use a primary key As an index. I want to be able to add items that I can classify into different classa, classb or classc. Example: Itemid 1025 ClassA: 101 classB: 218 classC: 356 Same item can be put into another class. Itemid 105 ClassA: 101 classb: 218 classC: 357 So not having a primary key/index will slow my queries? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Thursday, April 27, 2006 10:49 AM To: nngau Cc: mysql@lists.mysql.com Subject: Re: Multiple primary keys On 4/27/06, nngau [EMAIL PROTECTED] wrote: Can someone figure out what's going on. This is the only change I made to this table. Basically I don't want any duplicate rows, so I setup 4 fields to be my primary key. When I do a simple select query it takes nearly 30 seconds to complete. This is affecting my websites and taking a very long time to query the Products. Have I setup this table right? Thank You!! +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | itemID | int(6) | | PRI | 0 | | | classA | int(3) | | PRI | 0 | | | classB | int(3) | | PRI | 0 | | | classC | int(3) | | PRI | 0 | | | picture | varchar(10) | YES | | NULL| | | sex | char(2) | YES | | NULL| | +-+-+--+-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] You can avoid duplicate rows with only one primary key, unless you have a specific reason for having your primary key span on four columns. Your query being slower depends on how you have indexed your columns relating to your queries. Please give us a sample query which is running slowly.. Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to select the last entry per item
Hi, I hope this is the right list for this question. If not, I'm happy to get help on where to post this question. Apologies in advance if this is an old question. We are designing a simple a tracking database with a table of entries showing the current location of each item in the system. Something simple like this. Table_xyz item_id | location | status | time_stamp As the items move new time stamped entries are added to the database. How would you query to find the current location of all the items currently in the system. As you might expect we don't want to replace the entry for an item when a location update is made because we need to keep the history. We plan on removing items after a suitable delay when they reach their destination. Thanks -Brian
RE: How to select the last entry per item
Invert the problem ;-) Sort descending by the time_stamp field and limit the result to 1, i.e. SELECT * FROM table_xyz ORDER BY time_stamp DESC LIMIT 1 Tim -Original Message- From: Brian J. Matt [mailto:[EMAIL PROTECTED] Sent: Thursday, April 27, 2006 1:37 PM To: mysql@lists.mysql.com Subject: How to select the last entry per item Hi, I hope this is the right list for this question. If not, I'm happy to get help on where to post this question. Apologies in advance if this is an old question. We are designing a simple a tracking database with a table of entries showing the current location of each item in the system. Something simple like this. Table_xyz item_id | location | status | time_stamp As the items move new time stamped entries are added to the database. How would you query to find the current location of all the items currently in the system. As you might expect we don't want to replace the entry for an item when a location update is made because we need to keep the history. We plan on removing items after a suitable delay when they reach their destination. Thanks -Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import from unknown format (.bdd .ind .mor .ped)
Greetings. I need to import data to mysql from a db format I don't know. Unfortunately the person in charge of the data won't be reachable for the next 2 weeks and I want to continue my work. Does anyone knows the db format extensions like: .bdd .ind .mor .ped Thanks in advance. Apologies for my bad English. Pedro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Import from unknown format (.bdd .ind .mor .ped)
Pedro, I haven't ever seen those file types - but just a thought. Maybe they are Just flat files and the extension relates to the table contained there in. If you have unix shell access, try the file command - ie file file.bdd If it reports plain text - chances are its just a flat file - try opening it in A text editor. If the file command reports data - then it is probably some type of database file. Just a thought :) -- George -Original Message- From: Pedro mpa [mailto:[EMAIL PROTECTED] Sent: Thursday, April 27, 2006 2:05 PM To: mysql@lists.mysql.com Subject: Import from unknown format (.bdd .ind .mor .ped) Greetings. I need to import data to mysql from a db format I don't know. Unfortunately the person in charge of the data won't be reachable for the next 2 weeks and I want to continue my work. Does anyone knows the db format extensions like: .bdd .ind .mor .ped Thanks in advance. Apologies for my bad English. Pedro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory (Needed xxx ...
As far as i remember you must set that variable in order to send huge blobs, and the error message can vary because it can means that the current memory space for that connection has been filled. Why not do you try to set it up, as mentioned in the manual and check the results. Im forwarding this to the java list, maybe Mark can reserve a liitle time from the Mysql Users Conference and give us some feedback. Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: It's not set, but I am streaming the LOG to the server, would max packet impact this situation? Also, wouldn't I get a different error, i.e. Packet Too Large? From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:59 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... And whats the size of your max_allowed_packet variable ? Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: I am, the java heap is set to 2G. But I don't think it is my java process that is running out of memory, I believe it is the MySQL server. -Original Message- From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:53 PM Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
selecting all records inserted last 1 minutes?
Hi, I was trying to list all registeed users they are online last 15 minutes. I can do it by using timestamp: ?php $current_time = time(); $start_time = ($current_time - 15*60); SELECT * FROM members WHERE last_access = '$start_time' ? But, I know there is something like: SELECT * FROM members WHERE last_access BETWEEN(CURTIME(), INTERVAL 15 MIN) ?!? Thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I select from remote tables in mysql 5.0.18-standard-log?
Hello Listers, Is it possible to run a query on one mysql server to another database on a different server ? eg creating an alias in database A on server A to table B on database B on server B ? Like a federated nickname on db2 udb or synonym on informix ? Thanks Pierre - Yahoo! Mail goes everywhere you do. Get it on your phone.
Re: Can I select from remote tables in mysql 5.0.18-standard-log?
At 14:38 -0700 4/27/06, P. Evans wrote: Hello Listers, Is it possible to run a query on one mysql server to another database on a different server ? eg creating an alias in database A on server A to table B on database B on server B ? Like a federated nickname on db2 udb or synonym on informix ? You can use FEDERATED to access tables on other MySQL servers. http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting all records inserted last 1 minutes?
At 20:52 +0200 4/27/06, [EMAIL PROTECTED] wrote: Hi, I was trying to list all registeed users they are online last 15 minutes. I can do it by using timestamp: ?php $current_time = time(); $start_time = ($current_time - 15*60); SELECT * FROM members WHERE last_access = '$start_time' ? But, I know there is something like: SELECT * FROM members WHERE last_access BETWEEN(CURTIME(), INTERVAL 15 MIN) ?!? Thanks for any help. If you mean what is the syntax of BETWEEN?, it's in this section of the manual: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I select from remote tables in mysql 5.0.18-standard-log?
On 4/27/06, P. Evans [EMAIL PROTECTED] wrote: Hello Listers, Is it possible to run a query on one mysql server to another database on a different server ? eg creating an alias in database A on server A to table B on database B on server B ? Like a federated nickname on db2 udb or synonym on informix ? Thanks Pierre - Yahoo! Mail goes everywhere you do. Get it on your phone. Yes there is, please look at the FEDERATED Storage Engine starting from Mysql 5.0.3, but you need the MySQL-Max Binary distribution for the engine.. http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html Kishore Jalleda http://kjalleda.googlepages.com/projects
Problem with bk tree of MySQL 4.1 for two days.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have been unable to use the bk client to update my bk trees. I just hangs /usr/local/bin/bk-update bk://mysql.bkbits.net/mysql-4.1 mysql-4.1 I usually run this every 4 hours to keep my local trees up to date. The process usually completes in a few minutes. I have one that is now 2 days old. - -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFEUUUrVtBjDid73eYRArSFAJ9FozUW1nMFmG/mLJF12Y/1locsqgCfZjg2 FNXf7prY56Ph3QnZZsd5cK8= =3PXS -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select the last entry per item
On 4/27/06, Brian J. Matt [EMAIL PROTECTED] wrote: As the items move new time stamped entries are added to the database. How would you query to find the current location of all the items currently in the system. As you might expect we don't want to replace the entry for an item when a location update is made because we need to keep the history. We plan on removing items after a suitable delay when they reach their destination. If you are looking to obtain a result set the represents the current location of all items in the system you can use a sub-select as follows: SELECT item_id AS lid,location,status,timestamp FROM xyz WHERE timestamp=( SELECT MAX(timestamp) FROM xyz WHERE item_id=lid ) For the sake of efficiency make sure you have a key on timestamp. Hope this helps, Tez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Out of memory (Needed xxx ...
Setting max_allowed_packet does not seem to make a difference on how large the blob to the database can be. The MySQL server still gives the OOM exception after streaming ~600mb to a LOB regardless of how max_allowed_packet is set. R. From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Thursday, April 27, 2006 11:31 AM To: Robert DiFalco Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Out of memory (Needed xxx ... As far as i remember you must set that variable in order to send huge blobs, and the error message can vary because it can means that the current memory space for that connection has been filled. Why not do you try to set it up, as mentioned in the manual and check the results. Im forwarding this to the java list, maybe Mark can reserve a liitle time from the Mysql Users Conference and give us some feedback. Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: It's not set, but I am streaming the LOG to the server, would max packet impact this situation? Also, wouldn't I get a different error, i.e. Packet Too Large? From: Carlos Proal [mailto: [EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:59 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... And whats the size of your max_allowed_packet variable ? Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] wrote: I am, the java heap is set to 2G. But I don't think it is my java process that is running out of memory, I believe it is the MySQL server. -Original Message- From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:53 PM Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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 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: How to select the last entry per item
Brian Hi, I hope this is the right list for this question. If not, I'm happy to get help on where to post this question. Apologies in advance if this is an old question. We are designing a simple a tracking database with a table of entries showing the current location of each item in the system. Something simple like this. Table_xyz item_id | location | status | time_stamp As the items move new time stamped entries are added to the database. How would you query to find the current location of all the items currently in the system. As you might expect we don't want to replace the entry for an item when a location update is made because we need to keep the history. We plan on removing items after a suitable delay when they reach their destination. An item is in the most recent location for that item_id, right? Then ... SELECT item_id, location,MAX(timestamp) FROM table_xyz GROUP BY item_id; PB Thanks -Brian No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.0/325 - Release Date: 4/26/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.0/325 - Release Date: 4/26/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: HELP --- Slow SP
[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
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
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
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
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.
Re: selecting all records inserted last 1 minutes?
No, not exactly. More as there is a solution... What would be the best way to do? -afan Paul DuBois wrote: At 20:52 +0200 4/27/06, [EMAIL PROTECTED] wrote: Hi, I was trying to list all registeed users they are online last 15 minutes. I can do it by using timestamp: ?php $current_time = time(); $start_time = ($current_time - 15*60); SELECT * FROM members WHERE last_access = '$start_time' ? But, I know there is something like: SELECT * FROM members WHERE last_access BETWEEN(CURTIME(), INTERVAL 15 MIN) ?!? Thanks for any help. If you mean what is the syntax of BETWEEN?, it's in this section of the manual: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting all records inserted last 1 minutes?
At 22:29 -0500 4/27/06, Afan Pasalic wrote: No, not exactly. More as there is a solution... What would be the best way to do? -afan What's wrong with WHERE last_access = NOW() - INTERVAL 1 HOUR ? You don't want CURTIME(), I think, because that has only time, not the date. Paul DuBois wrote: At 20:52 +0200 4/27/06, [EMAIL PROTECTED] wrote: Hi, I was trying to list all registeed users they are online last 15 minutes. I can do it by using timestamp: ?php $current_time = time(); $start_time = ($current_time - 15*60); SELECT * FROM members WHERE last_access = '$start_time' ? But, I know there is something like: SELECT * FROM members WHERE last_access BETWEEN(CURTIME(), INTERVAL 15 MIN) ?!? Thanks for any help. If you mean what is the syntax of BETWEEN?, it's in this section of the manual: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]