RE: help optimizing log table deletes
You can use the LIMIT clause to break up a long DELETE into a series of shorter ones,
executed in a loop.
So, in pseudo-code:
Set delete_row_max = 1000 (or another appropriate value)
do
{
DELETE FROM outgoing
WHERE timestamp last_time
LIMIT delete_row_max;
row_count = no. of affected rows returned by MySQL
(other processing, perhaps a sleep)
} while (row_count = delete_row_max;
-Original Message-
From: Viraj Alankar [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 9:57 AM
To: [EMAIL PROTECTED]
Subject: help optimizing log table deletes
Hello,
We have an 'outgoing' table being used to store email header information
defined as follows:
CREATE TABLE outgoing (
rpath varchar(80) default NULL,
auth varchar(80) NOT NULL default '',
ip varchar(80) default NULL,
hfrom varchar(80) default NULL,
hto varchar(80) default NULL,
subject varchar(80) default NULL,
messageid varchar(80) default NULL,
timestamp timestamp(14) NOT NULL,
rcpts smallint(5) unsigned default '0',
KEY at_ind (auth,timestamp,rcpts)
) TYPE=MyISAM;
On average we have about 300k records in this table. Every day we archive
data older than 7 days to another table, with a query similar to:
$last_time = select now() - interval 7 day;
insert into newtable select * from outoing where timestamp '$last_time';
delete from outgoing where timestamp '$last_time';
The problem is the delete query can take up to 1 minute to execute. This
prevents any other inserts from taking place since the table is locked.
I was wondering how I can design this better to avoid this problem. My thought
was to go to InnoDB but this seems overkill for this slight issue.
sql
Viraj.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: fast,efficient query for counting
$dbh =
DBI-connect(DBI:mysql:database=$database;host=$host,$user,$mysqlpassword,{'RaiseError'=1});
$update_number = UPDATE $table set total_clicked = total_clicked + 1;
$sth = $dbh-prepare($update_number);
if (!$sth) { die Error: . $dbh-errstr . \n; }
if (!$sth-execute) { die Error: . $sth-errstr . \n; }
--Greg Johnson
-Original Message-
From: rory oconnor [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 20, 2002 10:44 AM
To: [EMAIL PROTECTED]
Subject: fast,efficient query for counting
I'm setting up some tracking stats for (opt-in) e-mail campaigns, and
one of the things I'm tracking is click-thrus. I'm going to re-direct
traffic thru a script that will just count the number of clicks, and
store that number in a mysql table.
It needs to be fast and efficient, and I'm somewhat of a mysql newbie,
so I was wonderinf if there is any more efficient way to simply add a
number to the existing number in that table with mysql and perl. My way
seems like a lot of code to do a little task. Any help is appreciated!
# set up the db connection and SQL
$dbh =
DBI-connect(DBI:mysql:database=$database;host=$host,$user,$mysqlpassword,{'RaiseError'=1});
$select_number = select total_clicked from $table;
# actually execute the checking query
$sth = $dbh-prepare($select_number);
if (!$sth) { die Error: . $dbh-errstr . \n; }
if (!$sth-execute) { die Error: . $sth-errstr . \n; }
# loop thru the query and set the result variables
while (my $ref = $sth-fetchrow_arrayref) {
$total_clicked = $$ref[0];
}
# add 1 to total_clicked
$total_clicked=$total_clicked+1;
# update the number
$update_number = UPDATE $table set total_clicked = $total_clicked;
# actually execute the update query
$sth = $dbh-prepare($update_number);
if (!$sth) { die Error: . $dbh-errstr . \n; }
if (!$sth-execute) { die Error: . $sth-errstr . \n; }
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Sub-select look-alike?
SELECT f.language as From, t.language as To FROM language f, language t, language_pairs lp WHERE f.id = lp.from AND t.id = lp.to; --Greg Johnson -Original Message- From: Andreas Frøsting [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 13, 2002 10:13 AM To: [EMAIL PROTECTED] Subject: Sub-select look-alike? Hi, I have two tables: languages: id tinyint(3) unsigned not null, language varchar(30) not null language_pairs: from tinyint(3) unsigned not null, to tinyint(3) unsigned not null language_pairs.from and language_pairs.to are linked with languages.id (both tables are simplified in this mail and contains a lot more columns, but they are not relevant) Now I want to do a query giving me the names of the languages (languages.language) instead of language_pairs.from og .to. I can only see a solution requiring the use of sub-selects which MySQL doesn't (yet :) has support for. Anyone smarter than me who can see a solution? I want to avoid use of sub-queries if possible, and my emergency plan is to extract all records from `languages` into an array in PHP and simply use PHP to join .from and .to with the matching language. That's not in any way optimal, that's why I'm asking you guys :) regards, //andreas http://phpwizard.dk (in Danish only) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Getting Data From One Table Based On Another - Agh!
SELECT Table1.orderid, Table2.otherfield1, Table2.otherfield2 FROM Table1, Table2 WHERE Table1.orderid = Table2.orderid; --Greg Johnson -Original Message- From: SpyProductions Support Team [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 12:31 PM To: [EMAIL PROTECTED] Subject: Getting Data From One Table Based On Another - Agh! Looking through the MySQL stuff online, I still can't come up with a statement to pull data from one table based on data from another. Table1 Contains: orderid Table2 Contains: orderid otherfield1 otherfield2 I want to get the data from table 2 based on a matching orderid first pulled from table1. Anyone have any ideas? Either this isn't able to be done, or I am (and I'm not) not advanced enough to understand the SQL statement needed. So far, I have tried a number of them that haven't worked. Any ideas? Thanks! -Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: insert select in ONE statement?
SELECT LAST_INSERT_ID() will return the last auto_increment key written. This value is connection-specific, so that inserts performed on other connections will not affect it (in other words, you'll get the last key generated by user 1, regardless of what user 2 does in the meantime). --Greg Johnson -Original Message- From: Lee P Reilly [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 2:50 PM To: MySQL Subject: insert select in ONE statement? Hi, I wonder if anyone can offer me some advice with this one: I have a table called 'iq_data' holding just a primary key and a field called 'iq_data'. +++--+-+-++ | Field | Type | Null | Key | Default | Extra | +++--+-+-++ | iqid | int(11)| | PRI | NULL| auto_increment | | iqdata | mediumtext | | | NULL|| +++--+-+-++ 'iq_data' contains the contents of a plain text file in the following format: .010 .1083649E+03 .2186916E+02 .0013470 .1993729E+03 .2738670E+02 == + approx. 100-1000 more lines. After I insert data into the table, I need to get the iqid that was generated. One solution is to do something like: SELECT iqid from iq_data where iqdata=the contents of the file; // ^ this will give me the correct answer, but it may be a // little inefficient as there may be thousands of records, and the // search string is very large or SELECT iqid from iq_id order by iqid (and get the last iqid generated) // ^ querying like this immediately after inserting data to the table, // but assumes that another record has not yet been added A problem will arise if say, data is inserted by user 1, and then by user 2, and then the iqid for user1 is requested, but the iqid for user2 will be returned. So... Is there any way I can e.g. insert the data into the table and return the value of the PK that was generated in one statement? If not, of the 2 solutions above what is more efficient? Is there are more elegant solution? Thank you very much for your time; hope someone can help ;-) - Best regards, Lee Reilly /My SQL query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Run a file from mysql prompt
cat thefilename | mysql -uuser -ppassword dbname --Greg Johnson -Original Message- From: David Turner [mailto:[EMAIL PROTECTED]] Sent: Friday, February 01, 2002 2:13 PM To: [EMAIL PROTECTED] Subject: Run a file from mysql prompt How do I get a file full of sql commands to run from the mysql prompt? In oracle I would type @thefilename I know how to do run the file from the unix prompt mysql thefilename.sql Thanks, Dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL and 3NF
Create two association tables: Contributor ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Contributor_ID | tinyint(3) | | PRI | 0 | auto_increment | | Name | varchar(100) | | | | | | Street_Address | varchar(50) | YES | | NULL| | | City | varchar(20) | YES | | NULL| | | State | varchar(5) | YES | | NULL| | | Zip| mediumint(8) | YES | | NULL| | ++--+--+-+-++ New tables: Contributor_Contact ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Contributor_ID | tinyint(3) | | PRI | 0 | | Contact_ID | tinyint(3) | | PRI | 0 | ++--+--+-+-++ Contributor_Volunteer ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Contributor_ID | tinyint(3) | | PRI | 0 | | Volunteer_ID | tinyint(3) | | PRI | 0 | ++--+--+-+-++ -- Greg Johnson -Original Message- From: David S. Jackson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 11:15 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: MySQL and 3NF Hi, I came across a relationship between entities that I hadn't counted on, and I'm trying to adjust my database tables to handle this new relationship. I need some help with visualizing and implementing this relationship into the database design. The database is for an inventory of contributions to be auctioned off for a Montessori school. Here are my tables: mysql show tables; +--+ | Tables in vmscatalog | +--+ | Category | | Contact | | Contributors | | Delivery | | Groups | | Item | | Volunteer| +--+ I've assumed that each contributor (business, individual, whatever) would have only one volunteer from the Montessori school that they would be dealing with. so I've got the following structure for the contributors table: mysql describe Contributors; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Contributor_ID | tinyint(3) | | PRI | 0 | auto_increment | | Name | varchar(100) | | | | | | Street_Address | varchar(50) | YES | | NULL| | | City | varchar(20) | YES | | NULL| | | State | varchar(5) | YES | | NULL| | | Zip| mediumint(8) | YES | | NULL| | | Contact_ID | tinyint(3) | YES | | NULL| | | Volunteer_ID | tinyint(3) | YES | | NULL| | ++--+--+-+-++ 8 rows in set (0.01 sec) Likewise, the Volunteer_ID ties in with a Volunteer table and a unique row in the volunteer table. Now I've got a situation where a large Museum actually has two people from the school each talking to different departments in the Museum, each donating a different set of gift certificates. So I have to figure out some way to let the contributors' table handle more than Contact_ID and more than one Volunteer_ID. Don't I want each Contact_ID field for each record to be a single discrete ID number? How would you guys handle this? TIA! -- David S. Jackson[EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= It's hard to get ivory in Africa, but in Alabama the Tuscaloosa. -- Groucho Marx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: unique problem?
SELECT REPLACE(DIRECTIONS, '#', 'No.') FROM shpr_rcvr WHERE etc. -- Greg Johnson -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED]] Sent: Monday, December 31, 2001 1:23 PM To: [EMAIL PROTECTED] Subject: unique problem? I have a table shpr_rcvr that stores info about locations where things are shipped to and from. Each record has a text field called DIRECTIONS that contains directions on how to get to the location. Often in the directions column the # sign is used to help describe and exit number off the highway. For example Take I-55 south to exit # 269. When I put text with # sign in into a latex document, latex rejects it ( won't format it ). I cannot find a work around in latex so I was wondering if someone can recommend a query that will search each directions column in the table and replace # with No.. Can anyone help? Richard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Can this be done?
UPDATE myTable SET DueField = 0, StatusField = IF(StatusField = 'O', 'C', StatusField) WHERE PrimaryKeyField = XYZXYZ; -- Greg Johnson -Original Message- From: Chris Boget [mailto:[EMAIL PROTECTED]] Sent: Friday, December 07, 2001 1:42 PM To: [EMAIL PROTECTED] Subject: Can this be done? Is there a way to do this? If so, I've not been able to find it in the documentation... I have 3 fields: PrimaryKeyField StatusField DueField I want to update the DueField to 0 (zero) where the PrimaryKeyField = XYZXYZ. I also want to update the StatusField for those records to be equal to C when it's equal to O but not any other value. Is there a way to do this all in one query? pseudocode UPDATE myTable SET DueField = 0, ( StatusField = C WHEN StatusField = O) WHERE PrimaryKeyField = XYZXYZ; /pseudocode Can something like that be done? Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help with aggregate query
SELECT SUM(d.AcctSessionTime) + IFNULL(m.Minutes, 0) FROM detail d LEFT OUTER JOIN monthly_usage m ON d.UserName = m.UserName WHERE d.UserName = 'foo'; Or, to summarize for all users: SELECT d.UserName as user, SUM(d.AcctSessionTime) + IFNULL(m.Minutes, 0) FROM detail d LEFT OUTER JOIN monthly_usage m ON d.UserName = m.UserName GROUP BY user; --Greg Johnson -Original Message- From: John Morrissey [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 10:45 AM To: [EMAIL PROTECTED] Subject:Help with aggregate query I'm logging RADIUS detail records to a MySQL database. Currently, I crunch the the detail table (containing individual records) once a month into another table that contains aggregate usage (monthly_usage). CREATE TABLE monthly_usage ( UserName varchar(32) NOT NULL, Realm varchar(64) NOT NULL, UsageDate date NOT NULL, Minutes mediumint unsigned, Logins mediumint unsigned, PRIMARY KEY (Username, Realm, UsageDate) ); The problem is, the detail table is getting to be very large toward the end of the month. I'd like to crunch statistics weekly (or even nightly) to keep disk usage at a reasonable level. I've thought of doing something like: SELECT SUM(detail.AcctSessionTime) + monthly_usage.Minutes FROM detail,monthly_usage WHERE detail.UserName = 'foo' AND monthly_usage.UserName = 'foo'; but this won't work because a user might not always have a row in the monthly_usage table (they might not have logged in yet this month), which makes this query return NULL. My question is this: is there a way to add SUM(detail.AcctSessionTime) to a corresponding row from montly_usage, even if that row from monthly_usage doesn't exist (e.g., add 0 to SUM() if the monthly_usage row doesn't exist). I've considered breaking this up into two queries; one to select all usernames from the detail table and insert dummy rows into monthly_usage with 0 usage, then run the query above, which will then work as expected. This seems kind of kludgy; is there a way to do what I want in a single query? thanks, john -- John Morrissey _o/\ __o [EMAIL PROTECTED]_- \_ / \ \, www.horde.net/__(_)/_(_)/\___(_) /_(_)__ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Beginner question - getting last inserted ID
If you are using the Mark Matthews JDBC driver (MM.MySQL), there's another way to get the last insert id: instead of using a generic Statement object to execute the INSERT command, use an org.gjt.mm.mysql.Statement object. After executing an INSERT, the method getLastInsertID() will return the value of LAST_INSERT_ID. This value is sent to the client along with other status info after the INSERT command is executed, so the method does not require another call to the server. --Greg Johnson -Original Message- From: Carl Troein [mailto:[EMAIL PROTECTED]] Sent: Friday, November 09, 2001 10:41 AM To: [EMAIL PROTECTED] Subject:Re: Beginner question - getting last inserted ID Anna Åhnberg writes: Thanks, I actually already found the chapters but now I also now how to use the function too! Let me quote from the manual: LAST_INSERT_ID([expr]) Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. mysql select LAST_INSERT_ID(); - 195 Thus you'd follow these steps: 1) INSERT into the table, and leave out the auto column or supply a value of 0 or NULL 2) Verify that the query succeeded 3) SELECT LAST_INSERT_ID() 4) Get the result of the SELECT. On success, the SELECT will return one row with one value in it. That is the number you're interested in knowing. If you were using MySQL's C API there is a function that returns the ID without the need for steps 3-4, but since you are communicating over JDBC I don't think there's a simpler solution available. The query in step 3 is extremely fast, so it doesn't really matter, except that it's a bit more work to make an extra query. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: bad practice to have a primary key field whose value changes?
Use a join query, rather than separate single-table queries: SELECT a.*, n.url FROM articles a, news_sites n WHERE a.news_site_key = n.news_site_key; -- Greg Johnson -Original Message- From: Bennett Haselton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 30, 2001 3:11 PM To: [EMAIL PROTECTED] Subject:bad practice to have a primary key field whose value changes? I'm creating a database where one of the tables stores data about news Web sites, and I'm using the URL of the site as a primary key field. This field value might change occasionally. I'm wondering if this is bad practice, especially since foreign keys in other tables might point to the news site table. MySQL doesn't enforce referential integrity, so if I change the URL of a particular site, then another table in the database might have a reference to a row that no longer exists. So I assume the right way to do this would be to use an auto-incremented number as the primary key field, and then have other tables refer to that as their foreign key. My problem with this is that I like to be able to dump the contents of the table and see something meaningful without having to refer to other tables. Say I have an articles table in the database, and one of the fields is a foreign key referring to the news site in the news sites table where the article was found. If I dump the contents of the articles table, all that I'll see is a number; then I have to go look in the news sites table to find out which actual site that number corresponds to. From this point of view, it's a lot easier just to use the news site URL as the key field in the news sites table -- then that's what can be used as the foreign key in the articles table. What would be ideal would be to use auto-incremented numeric fields as primary key fields, and then have a special field in each table designated as the user-friendly field. That way, when you want to view the contents of a table, the table viewing algorithm can take each field marked as a foreign key, go to that table, look up the user-friendly string for that row, and display that instead. This would satisfy the requirements in both paragraphs above. Is there already a way to do this, and if not, which of the two options above do people usually use? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Left Join problem
It looks to me as though the tracked.window=137 condition is suppressing output of any available rows which do not have a target match in tracked - tracked.window would = NULL in those cases. --Greg Johnson -Original Message- From: George Eric R Contr AFSPC/CVYZ [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 03, 2001 11:50 AM To: [EMAIL PROTECTED] Subject:Left Join problem I'm not getting the behavior I expect from this select. SELECT available.target, tracked.sensor FROM available LEFT JOIN tracked ON available.target=tracked.target WHERE available.window=137 AND tracked.window=137 AND available.sensor=8; Now, I know for this window, that there are 25 records for sensor 8 in the available table: SELECT count(*) FROM available WHERE window=137 AND sensor=8; count(*) 25 Now in the tracked table, some of the targets that are available to sensor 8 are actually tracked by another sensor, and some are not tracked at all. I expected the left join to show me which targets that are available to target 8 are tracked by which sensor, with NULL values in the right column for targets that are left untracked. I get half of that. My result set has 20 records. It correctly shows which sensor tracked which target. It doesn't show the 4 untracked targets at all? Ideas? Thanks Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Left Join problem
Or, you could try this: SELECT available.target, tracked.sensor FROM available LEFT JOIN tracked ON available.target=tracked.target AND tracked.window=137 WHERE available.window=137 AND available.sensor=8; -Original Message- From: George Eric R Contr AFSPC/CVYZ [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 03, 2001 12:08 PM To: [EMAIL PROTECTED] Subject:RE: Left Join problem Ok, I can see that now. If the record doesn't exist in the tracked table, it can't meet the window criteria. The one way I see to get around this, in the absense of subselects, is to retrieve the tracked data that meets the window criteria into a temporary table. Right? -Original Message- From: Johnson, Gregert [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 03, 2001 10:03 AM To: [EMAIL PROTECTED] Subject: RE: Left Join problem It looks to me as though the tracked.window=137 condition is suppressing output of any available rows which do not have a target match in tracked - tracked.window would = NULL in those cases. --Greg Johnson -Original Message- From: George Eric R Contr AFSPC/CVYZ [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 03, 2001 11:50 AM To: [EMAIL PROTECTED] Subject:Left Join problem I'm not getting the behavior I expect from this select. SELECT available.target, tracked.sensor FROM available LEFT JOIN tracked ON available.target=tracked.target WHERE available.window=137 AND tracked.window=137 AND available.sensor=8; Now, I know for this window, that there are 25 records for sensor 8 in the available table: SELECT count(*) FROM available WHERE window=137 AND sensor=8; count(*) 25 Now in the tracked table, some of the targets that are available to sensor 8 are actually tracked by another sensor, and some are not tracked at all. I expected the left join to show me which targets that are available to target 8 are tracked by which sensor, with NULL values in the right column for targets that are left untracked. I get half of that. My result set has 20 records. It correctly shows which sensor tracked which target. It doesn't show the 4 untracked targets at all? Ideas? Thanks Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e
RE: Help!
Mariacust must be included in you FROM list: SELECT ... FROM customers, mariacust WHERE ... -Original Message- From: Armando Cerna [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 18, 2001 12:16 PM To: [EMAIL PROTECTED] Subject:Help! I try the following command, and get that error every time: mysql select company, tele, contact, mariacust.comments - from - customers where tele = mariacust.tele GROUP BY company; ERROR 1109: Unknown table 'mariacust' in field list mysql And yes... the talbe exists. mysql show tables; +-+ | Tables_in_maria | +-+ | cathycust | | customers | | inventory | | invoices| | mariacust | | salesid | +-+ 6 rows in set (0.00 sec) I don't quite understand why this happens since everything exists Armando - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select where A is not a member of B
SELECT DISTINCT u.userid, IF( ul.listid, true, false) FROM user u LEFT OUTER JOIN userlist ul ON u.userid = ul.userid AND ul.listid = listid; -Original Message- From: David Otton [mailto:[EMAIL PROTECTED]] Sent: Monday, September 10, 2001 5:39 PM To: [EMAIL PROTECTED] Subject:Select where A is not a member of B Hi - I've got an annoying problem here, I've checked books, web, archives, etc, but can't find anything suitable so far. I have 2 data tables (ignoring the other columns, not important) : ++ | user | ++ | userid | ++ ++ | list | ++ | listid | ++ and a joining table : +--+ | userlist | +--+ | listid | | userid | +--+ As you can see, users can belong to many lists, lists can contain many users. I need to find, for each user, whether they are a member of list n : ++--+ | userid | memberoflist | ++--+ | 1 | true | | 2 |false | | 3 | true | | 4 |false | ++--+ It seems simple, but I've been banging my head against this all weekend. When I break it down, I need to find 3 things : Users that are members : SELECT user.userid FROM user, userlist WHERE listid=1 AND user.userid=userlist.userid Users that belong to NO lists : SELECT user.userid FROM user LEFT JOIN userlist ON user.userid=userlist.userid WHERE listid IS NULL Users that belong to some lists, but not the one I'm interested in : this is the one that I'm stuck on. Any thoughts? Suggestions? djo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: check if an index exists?
show index from tablename; This returns a result set listing all indexes on the table. --Greg Johnson -Original Message- From: Christopher Teli [mailto:[EMAIL PROTECTED]] Sent: Friday, August 17, 2001 12:32 PM To: Mysql (E-mail) Subject:check if an index exists? How can I check if an index exists on a table??? Is there some where I can query the admin tables? Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Does not match
Try something like SELECT fid, Family_Last_Name, Phone, playertable.Family_ID FROM familytable LEFT OUTER JOIN playertable ON playertable.Family_ID = familytable.fid AND playertable.SportRegistered = 'O' GROUP BY fid ORDER BY Family_Last_Name ASC; The rows from familytable which are not matched by rows in playertable will be recognized by NULL values of playertable.Family_ID in the result set. --Greg Johnson -Original Message- From: Dave Carter [mailto:[EMAIL PROTECTED]] Sent: Friday, June 22, 2001 10:19 AM To: mysql Subject:Does not match Can I show records from my SQL statement where records both have a match and do NOT have a match. I'm having trouble with the syntax for no matching records in a related table, my current SQL statment looks like this: SELECT fid,Family_Last_Name,Phone FROM familytable,playertable WHERE playertable.Family_ID = familytable.fid AND playertable.SportRegistered = '0' GROUP BY fid ORDER BY Family_Last_Name ASC; This works fine, but I also want to show Families with NO matching records in the player field, which the above statement does not do. TIA, Dave Carter Chief Web Architect Accelerated Business Technologies, Inc. http://www.abti.cc 717.464.2970 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT question.
SELECT l.load_no, l.date FROM loads l LEFT OUTER JOIN invoiced i ON l.load_no = i.load_no WHERE l.dlvr_date 0 AND i.load_no IS NULL; -- Greg Johnson -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 30, 2001 12:42 PM To: [EMAIL PROTECTED] Subject:SELECT question. I am stuck on a select query and was wondering if someone could help. I have I've written a database app. that helps me run my business (trucking). I need however to write a query that shows me all of the loads that are delivered but not billed (invoiced). Which means that I have to select the loads that are delivered but do not have an entry in the INVOICED table -- since an entry is made in the INVOICED table whenever a load is billed. I know the query below won't work. Can someone please help me fix it? SELECT l.load_no l.date FROM loads l, invoiced i WHERE l.dlvr_date 0 AND l.load_no != i.load_no Thanks, Richard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: LIMIT
SQL Server will allow you to limit a SELECT to the first n rows, but not (to my knowledge) to skip a number of rows before returning the desired rows. The mechanism used is SET ROWCOUNT (6.5 and 7.0) or TOP (7.0). The difference is that SET ROWCOUNT limits the output rows before applying an ORDER BY sort. TOP applies the ORDER BY before limiting output. --Greg Johnson -Original Message- From: Toby Miller [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 19, 2001 10:31 AM To: [EMAIL PROTECTED] Subject:LIMIT Hey all, I've been using the LIMIT feature for paginating in MySQL for a long time now. It's really nice and makes paginating extremely simple. Up until now I've been using a dictionary object to get the same effect out of Microsoft SQL Server. Do any of you know a LIMIT equivalent for SQL Server? I would much rather grab the number of records I need rather than grabbing a reference to all of the records and only displaying the ones that I need. It seems redundant and I really hate the fact that LIMIT is not supported. Any ideas? By the way, suggestions for SQL Server 6.5 or SQL Server 7.0 are both appreciated. Thanks, Toby - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql speed :)
What would really help would be to use multiple row inserts, i.e.
INSERT INTO speed1 VALUES (a,b,c),(d,e,f),(g,h,I),...
So, prepare a series of inserts, each with a few hundred (or even thousand) row value
sets.
--Greg Johnson
-Original Message-
From: Martin Hubert [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 9:52 AM
To: Tim Bunce; Heikki Tuuri
Cc: [EMAIL PROTECTED]; FileCopyMartin
Subject:RE: Mysql speed :)
But does that really change anything on the database server side ?
In other words is MySQL doing anything with respect to using prepared
statements ?
-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 3:01 AM
To: Heikki Tuuri
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Mysql speed :)
On Thu, Apr 05, 2001 at 07:50:24PM +0300, Heikki Tuuri wrote:
for ($j = 0; $j 10; $j = $j + 1) {
$dbh-do(insert into speed1 values ($j, $j, $j));
}
That would run faster if you do a prepare with placeholders outside
the loop and then just use $sth-execute($j, $j, $j) inside.
That would save you the DBI statement handle creation/destruction
overhead that you're paying for each insert when using do().
Tim.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: table handler
The valid range for an 'int' data type (your "id" column) is -2147483648 to 2147483647. The value you are attempting to set (37647438380) is not within this range. --Greg Johnson -Original Message- From: Andreas Gietl [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 13, 2001 9:46 AM To: [EMAIL PROTECTED] Subject:table handler I've got a nasty problem with my mysql-database. On the following INSERT (and on others too and on some SELECTs) i get the following error. mysql INSERT INTO hyperseek_keys SET keyword='meiner_asiatinne',id='37647438380',price='0.01',xfactor='7.50'; ERROR 1030: Got error -1 from table handler I can't explain this error, i looked at the Documentation and found neither error 1030 (not in perror nor online) nor error -1. In the LOGFILE there's nothing special about these failing transactions. These are my table definitions: mysql DESCRIBE hyperseek_keys; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | keyword | char(50) | | MUL | | | | id | int(11) | | MUL | 0 | | | price | decimal(2,2) | | MUL | 0.00| | | xfactor | decimal(4,2) | | | 0.00| | +-+--+--+-+-+---+-+ | Field | Type | Null | Key | Default | Extra | Privileges | +-+--+--+-+-+---+-+ | keyword | char(50) | | MUL | | | select,insert,update,references | | id | int(11) | | MUL | 0 | | select,insert,update,references | | price | decimal(2,2) | | MUL | 0.00| | select,insert,update,references | | xfactor | decimal(4,2) | | | 0.00| | select,insert,update,references | +-+--+--+-+-+---+-+ +++---+--+-+---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++---+--+-+---+-+--++-+ | hyperseek_keys | 1 | hsl_keys1 |1 | keyword | A |NULL | NULL | NULL | | | hyperseek_keys | 1 | hsl_keys2 |1 | price | A |NULL | NULL | NULL | | | hyperseek_keys | 1 | hsl_keys2 |2 | xfactor | A |NULL | NULL | NULL | | | hyperseek_keys | 1 | hsl_keys3 |1 | id | A |NULL | NULL | NULL | | +++---+--+-+---+-+--++-+ Perhaps you have an idea? thanx andreas -- ACHTUNG NEUE ADRESSE + Telefonnummer andreas gietl gietl internet services roter-brach-weg 124a / 93049 Regensburg fon +49 941 3810884 fax +49 941 3810891 mobile +49 171 60 70 008 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/
