-help
Terry Spencer Haigh Consultancy Services Tel: +44 (0)116 262 3966 Fax: +44 (0)116 262 3946 (Leciester Office) Fax: +44 (0)870 052 4572 (Terry) Mob: +44 (0)7796108244 www.haigh-cs.co.uk http://www.haigh-cs.co.uk
join question
Hi All, I have a question for clearer brains than mine. I would like to join two tables,. There may be many possible joins in table B to table A, but I only want to join one row from B to table A - the row with the closest, but lesser date. TABLE A Row Id date 1 46 3 Jan 7 20 10 Jan TABLE B Row Id date 4 46 1 Jan 5 46 2 Jan 6 46 4 Jan 8 20 8 Jan 10 20 7 Jan 11 20 9 jan Result Row 1` in A is joined to row 5 in B Row 7` in A is joined to row 11 in B SELECT a.row, b.row FROM A a LEFT JOIN B b ON (a.id = b.id AND a.date b.date AND the row with the max dates from the possible join in b?) Any suggestions would be appreciated. Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connect issues
I can connect on the command line, but have problems connecting using DBI/D on the same server. # ./bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.0-alpha-standard Mysql is running # ps -ef | grep mysql root 10626 9589 0 11:57 pts/341 00:00:00 /bin/sh ./bin/mysqld_safe mysql10642 10626 0 11:57 pts/341 00:00:00 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --bag ... I can connect at the command line # ./bin/mysql -username=hcspt Welcome to the MySQL monitor. Commands end with ; or \g. When I connect using perl on the same server the following error occurs ...failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'... Ive noted that mysql.sock is not in the location in the same directory as the above error message # find / -name mysql.sock /tmp/mysql.soc Setting the location of mysql.sock in my.conf [client] socket = /tmp/mysql.sock Causes error Any suggestions? Thanks Terry
RE: Hour counts
There are a few options, for more information see
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
Returns the integer difference between the date or datetime expressions
datetime_expr1 and datetime_expr2. The unit for the result is given by the
interval argument. The legal values for interval are the same as those
listed in the description of the TIMESTAMPADD() function.
mysql SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
- 3
mysql SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
- -1
TIMESTAMPDIFF() is available as of MySQL 5.0.0.
It appears you require the answer in fraction hours. Set the interval to
seconds and divide the result by 3600 (60*60 = seconds in an hour)
---
UNIX_TIMESTAMP() , UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp (seconds since
'1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is
called with a date argument, it returns the value of the argument as seconds
since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME
string, a TIMESTAMP, or a number in the format YYMMDD or MMDD in local
time.
mysql SELECT UNIX_TIMESTAMP();
- 882226357
mysql SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
- 875996580
Convert both dates to seconds using UNIX_TIMESTAMP() and subtract one form
the other. Divide the result by 3600 (60*60 = seconds in an hour) to obtain
the fractional hours.
Terry
-Original Message-
From: Gyurasits Zoltán [mailto:[EMAIL PROTECTED]
Sent: 27 July 2005 17:12
To: mysql@lists.mysql.com
Subject: Hour counts
Hello All!
I would like to calculate the hour counts from 2 'datetime'.
Example: 2005-07-27 18:00 and 2005-07-27 19:30 = 1,5 hour
I try this but not good!
R1 : munkaido_end-munkaido_start /simple substract/
R2 : ROUND(ROUND((end-start)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is in one
day/
R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't
understand/
start end R1R2 R3
07-14 15:00 07-14 17:30 23000 2.5 -74
07-14 23:00 07-15 01:30 783000 78.5 2
07-14 15:00 07-15 02:30 873000 87.5 11
07-14 15:00 07-14 16:00 1 1 -75
Please help me...(exist a function for this situation?)
Tnx!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Explain and indexes
Im trying to speed up a query. select project_id from timesheet ts where ts.del is null and signoff = 'A' The output of explain is detailed below. ++-+---+--+-+--+ | id | select_type | table | type | possible_keys | key | key_len ++-+---+--+-+--+ | 1 | SIMPLE | ts| ALL | signoff,del,del_signoff | NULL |NULL ++-+---+--+-+--+ | ref | rows | Extra | ++-+---+--+-+--+ | NULL | 3907 | Using where | ++-+---+--+-+--+ An index exists on all three columns referred to, in addition to a combination of del and signoff. The indexes are listed as possible keys, but none used by the query; key = null. Can anyone suggest why? How can I optimise this? Thanks Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regular expresion replace possibility?
From the manual:
REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str replaced
by
the string to_str:
mysql SELECT REPLACE('www.mysql.com', 'w', 'Ww');
- 'WwWwWw.mysql.com'
This function is multi-byte safe.
Terry Spencer
Haigh Consultancy Services
+44 (0)2073007329
www.haigh-cs.co.uk
-Original Message-
From: Dean Householder [mailto:[EMAIL PROTECTED]
Sent: Monday, July 21, 2003 9:49 PM
To: [EMAIL PROTECTED]
Subject: Regular expresion replace possibility?
Is it possible to run a query that will just alter text possibly using a
regular expression? I have about 250 rows that I want to strip quotes
out of. Does anyone know of an easy way to do this?
Dean
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Faster reindexing
Check out the EXPLAIN command EXPLAIN tbl_name is a synonym for DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name. When you precede a SELECT statement with the keyword EXPLAIN, MySQL explains how it would process the SELECT, providing information about how tables are joined and in which order. With the help of EXPLAIN, you can see when you must add indexes to tables to get a faster SELECT that uses indexes to find the records. http://www.mysql.com/doc/en/EXPLAIN.html Terry Spencer Haigh Consultancy Services +44 (0)2073007329 www.haigh-cs.co.uk -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 5:23 AM To: Dominicus Donny; [EMAIL PROTECTED] Subject: Re: Faster reindexing At 11:23 +0700 7/9/03, Dominicus Donny wrote: Try analyze your table(s). What information will this yield to make indexing faster? Me fail English? That's unpossible ###___Archon___### - Original Message - From: electroteque [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED]; Florian Weimer [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 10:23 AM Subject: RE: Faster reindexing when reimporting or reinserting or whatever from a huge db i usually drop all the indexes reimport then create them again much quicker -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 1:09 PM To: Florian Weimer; [EMAIL PROTECTED] Subject: Re: Faster reindexing At 9:39 +0200 7/7/03, Florian Weimer wrote: I've got a table with 100 million rows and need some indexes on it (one row is 126 bytes). I'm currently using MyISAM and the indexing proceeds at an astonishingly low rate: about 200 MB per hour. This is rate is far too low; if we had to recover the database for some reason, we'd have to wait for days. The table looks like this: CREATE TABLE flows ( versionCHAR NOT NULL, router CHAR(15) NOT NULL, src_ip CHAR(15) NOT NULL, dst_ip CHAR(15) NOT NULL, protocol TINYINT UNSIGNED NOT NULL, src_port MEDIUMINT UNSIGNED NOT NULL, dst_port MEDIUMINT UNSIGNED NOT NULL, packetsINTEGER UNSIGNED NOT NULL, bytes INTEGER UNSIGNED NOT NULL, src_if MEDIUMINT UNSIGNED NOT NULL, dst_if MEDIUMINT UNSIGNED NOT NULL, src_as MEDIUMINT UNSIGNED NOT NULL, dst_as MEDIUMINT UNSIGNED NOT NULL, src_netCHAR(1) NOT NULL, dst_netCHAR(1) NOT NULL, direction CHAR(1) NOT NULL, class CHAR(1) NOT NULL, start_time CHAR(24), end_time CHAR(24) ); Indexes are created using this statement: mysql ALTER TABLE flows - ADD INDEX dst_ip (dst_ip, src_ip), - ADD INDEX dst_port (dst_port, start_time), - ADD INDEX src_ip (src_ip, start_time), - ADD INDEX time (start_time); In theory, we could represent the columns router, src_ip, dst_ip, start_time, end_time using integers of the appropriate size, but this would make ad-hoc queries harder to type (and porting our applications would be even more difficult). Perhaps, but as a test, you might add a couple of extra columns to the table, then populate them like this after loading the table: UPDATE flows SET int_src_ip = INET_ATON(src_ip), int_dst_ip = INET_ATON(dst_ip); Then try creating the indexes using int_src_ip and int_dst_ip rather than src_ip and dst_ip. If it's significantly faster, you may want to reconsider whether it might not be worth using INET_ATON(X) in your queries rather than X. Should I switch to another table type? It's easy enough to convert the table to, e.g., InnoDB and then create the indexes, so an empirical test should not be difficult. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- 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]
KEYS error 1216
Hi All, Im altering a number of table from MyISAM to innoDb and adding foreign keys. The alteration of the table type works. Adding the row as an index works. Adding the foreign key fails, generating the error: alter table project add FOREIGN KEY (company_id) references company (id) [mySQL] ERROR 1216: Cannot add or update a child row: a foreign key constraint fails CREATE TABLE company ( id int NOT NULL auto_increment, PRIMARY KEY (id) ) CREATE TABLE project ( id int NOT NULL auto_increment, company_id int default NULL, ) Would anyone have any idea what causing this error? Thanks Terry Terry Spencer Haigh Consultancy Services +44 (0)2073007329 www.haigh-cs.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update in select
Im attempting to update a table. We perform a select on the table to determine what row to update. update test a set visit_date = now() where a.id in (select b.id from test b where code ='Z') Running this generates an error. You cant specify target table 'test' for update in FROM clause. I can locate documentation on the constraints on referring to the target table in the FROM clause. Can anyone point me to any? In my example the rows the select is the select are not being updated. Is there anyways to perform this type of statement? Thanks. Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Update in select
Thanks for the help, that close to what Im after. I however simplified the problem too much in my previous question and Im still not there. :) Im imitating a 'tree' structure. The table has rows - ID and parent_ID. The depth of the tree is only three layers (parent - child - grandchild). There may be any number of parents. Various solution exists to the tree problem that involve adding a additional row (or two) to the table. This is not an option available to me at this point. Under certain circumstances if the parent is updated, I also need to update the children and grandchildren. So far I have (using a select while testing :) select t1.*, t2.*, t3.* from tree t1, tree t2, tree t3 where t1.id = 10 and t2.parent_id = t1.id and t3.parent_id = t2.id However this only returns the parent row. If I instead use select t2.* the children are returned If I instead use select t3.* the grandchildren are returned So my problem is 1. why is only the result selected for the front table; t1 returns only parent, t2 return only the children 2. this fails for parent that have no children, and children that have no grandchildren. Any thought, suggestion, points are greatly appreciated. Im using 4.1. Thanks Terry -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 2:53 PM To: Terry Spencer; [EMAIL PROTECTED] Subject: RE: Update in select Subqueries are only available in MySQL 4.1. However, you should be able to write this as follows: UPDATE test1 a, test2 b SET a.visit_date = NOW() WHERE a.id = b.id AND b.code = 'Z'; Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Terry Spencer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 7:26 AM To: [EMAIL PROTECTED] Subject: Update in select Im attempting to update a table. We perform a select on the table to determine what row to update. update test a set visit_date = now() where a.id in (select b.id from test b where code ='Z') Running this generates an error. You cant specify target table 'test' for update in FROM clause. I can locate documentation on the constraints on referring to the target table in the FROM clause. Can anyone point me to any? In my example the rows the select is the select are not being updated. Is there anyways to perform this type of statement? Thanks. Terry -- 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]
