What does this sql query mean?
Hi, Im just a new comer on mysql and i was in the middle of debugging some codes that aint mine. I was stuck here. I could not figure what does this select do? Its complicated.. Can anyone help me out? select * from shopcart inner join items on shopcart.itemId = iip.itemId where shopcart.cookieId = '4bfa673ee4c544d4352e2c70a78b70b5' order by iip.itemName asc Thanks -- - Louie Miranda http://www.axishift.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What does this sql query mean?
Louie, The inner-join is just joining the two tables, shopcart and items. I think iip is an alias for items, but the alias would normally come after the name of the tiems table. Another way to write the query is (assuming iip is an alias for items), SELECT * FROM shopcart, items iip WHERE shopcart.itemID = iip.itemID AND shopcart.cookieId = '4bfa673ee4c544d4352e2c70a78b70b5' order by iip.itemName asc The inner-join is just an alternative to what's above. I read on some site that there were two advantages: 1) Kept the the WHERE-clause a bit simpler 2) Was a bit faster, as the tables were pre-matched; I doubt this, and the source wasn't particularily trustworth. The MySQL manual doesn't mention any performance gains from using the INNER-JOIN functionality Here's the relevant MySQL manual page: http://dev.mysql.com/doc/mysql/en/JOIN.html The relevant section: INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both will produce a Cartesian product between the specified tables (that is, each and every row in the first table will be joined onto all rows in the second table). By comma, they mean the comma seperating the two tables in the FROM clause, as I've written it above (FROM shopcart, items). David Louie Miranda wrote: Hi, Im just a new comer on mysql and i was in the middle of debugging some codes that aint mine. I was stuck here. I could not figure what does this select do? Its complicated.. Can anyone help me out? select * from shopcart inner join items on shopcart.itemId = iip.itemId where shopcart.cookieId = '4bfa673ee4c544d4352e2c70a78b70b5' order by iip.itemName asc Thanks -- - Louie Miranda http://www.axishift.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subscription
Tom Murdock [EMAIL PROTECTED] wrote: Good evening, I would like to subscribe to MySQL mailing list. Thank you. If you want to subscribe to MySQL mailing list, visit http://lists.mysql.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What does this sql query mean?
Great David, many thanks. I just solve another problem with the code. Thank you very much for the information. Now the iip is not an alias of items, the past programmer type it wrong. I was able to get all of the errors out too. Thanks -- - Louie Miranda http://www.axishift.com - Original Message - From: David Griffiths [EMAIL PROTECTED] To: Louie Miranda [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, June 04, 2004 3:44 PM Subject: Re: What does this sql query mean? Louie, The inner-join is just joining the two tables, shopcart and items. I think iip is an alias for items, but the alias would normally come after the name of the tiems table. Another way to write the query is (assuming iip is an alias for items), SELECT * FROM shopcart, items iip WHERE shopcart.itemID = iip.itemID AND shopcart.cookieId = '4bfa673ee4c544d4352e2c70a78b70b5' order by iip.itemName asc The inner-join is just an alternative to what's above. I read on some site that there were two advantages: 1) Kept the the WHERE-clause a bit simpler 2) Was a bit faster, as the tables were pre-matched; I doubt this, and the source wasn't particularily trustworth. The MySQL manual doesn't mention any performance gains from using the INNER-JOIN functionality Here's the relevant MySQL manual page: http://dev.mysql.com/doc/mysql/en/JOIN.html The relevant section: INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both will produce a Cartesian product between the specified tables (that is, each and every row in the first table will be joined onto all rows in the second table). By comma, they mean the comma seperating the two tables in the FROM clause, as I've written it above (FROM shopcart, items). David Louie Miranda wrote: Hi, Im just a new comer on mysql and i was in the middle of debugging some codes that aint mine. I was stuck here. I could not figure what does this select do? Its complicated.. Can anyone help me out? select * from shopcart inner join items on shopcart.itemId = iip.itemId where shopcart.cookieId = '4bfa673ee4c544d4352e2c70a78b70b5' order by iip.itemName asc Thanks -- - Louie Miranda http://www.axishift.com -- 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]
Writing MySQL setup files for ECperf
Has anyone try to setup ECperf benchmark using MySQL? Am having problem on the mysql setup files for ECperf. Anyone can provide information on this? Thank you: meileng __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Run MySQL with ANSI mode
I try to run MySQL with ansi mode but failed. This was what I did: mysql SET GLOBAL sql_mode='ansi'; ERROR 1064: You have an error in your SQL syntax near 'sql_mode='ansi'' at line 1 Why I can't manage to change to ansi mode? Thanks: meileng __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing data
Hello.. Im a new sql user so if this is a common prob Please be kind Im trying to set up mysql 4.0.13 With a database that contains 60k records The client wants to import 6 records a week With near 4mill records in a database My problem is I dont want to give ssh access to The machine So iv tried phpmyadmin And access to import the data but it seems to get to 40k records And then fails or times out What methods do you all use to import super large amounts of data? Thanks in advance Paul --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.690 / Virus Database: 451 - Release Date: 5/22/2004
Three quick questions about using MySQL
Three quick questions, I hope you can help me. Using InnoDB on version 4.0.18 Firstly. It's possible to get information on a table. Which includes the number of rows. This returns instantly. However, if I do a SELECT COUNT(*) on the same table, this can take a number of minutes to return. (about 1 minute per 1,000,000 rows.) I was wondering why this is? Is the count from the table information accurate? Secondly, the table stats return the size of the records and the size of the index. Is this the true size of the table in bytes? Lastly and most important. I want to store and access a large amount of sequential binary data. Fixed record size. Is it faster to access one data item per row. Or is it faster to access many data items (120+) aggregated into one row? Thanks in advance, Ben. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Run MySQL with ANSI mode
yau meileng [EMAIL PROTECTED] wrote: I try to run MySQL with ansi mode but failed. This was what I did: mysql SET GLOBAL sql_mode='ansi'; ERROR 1064: You have an error in your SQL syntax near 'sql_mode='ansi'' at line 1 What version do you use? The above syntax is supported since 4.1.1. Why I can't manage to change to ansi mode? Start MySQL server with --ansi option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
This is just my experience. But if you are doing this sort of work, it may well pay to construct your own import program. There are many ways of doing this, like parsing an email message, or using a XML/SOAP server. This will probably pay on the long run, as you can introduce filters, data checks, security and pre-processing. Ben. Paul Kruger wrote: Hello.. Im a new sql user so if this is a common prob Please be kind Im trying to set up mysql 4.0.13 With a database that contains 60k records The client wants to import 6 records a week With near 4mill records in a database My problem is I dont want to give ssh access to The machine So iv tried phpmyadmin And access to import the data but it seems to get to 40k records And then fails or times out What methods do you all use to import super large amounts of data? Thanks in advance Paul --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.690 / Virus Database: 451 - Release Date: 5/22/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Three quick questions about using MySQL
See answers in the message below. Marc. -Message d'origine- De : Ben Clewett [mailto:[EMAIL PROTECTED] Envoyé : vendredi 4 juin 2004 10:37 À : [EMAIL PROTECTED] Objet : Three quick questions about using MySQL Three quick questions, I hope you can help me. Using InnoDB on version 4.0.18 Firstly. It's possible to get information on a table. Which includes the number of rows. This returns instantly. * It is an estimate number of rows, Not the real one. However, if I do a SELECT COUNT(*) on the same table, this can take a number of minutes to return. (about 1 minute per 1,000,000 rows.) I was wondering why this is? Is the count from the table information accurate? * Yes, This is the real number of rows. Secondly, the table stats return the size of the records and the size of the index. Is this the true size of the table in bytes? * Yes it is. Lastly and most important. I want to store and access a large amount of sequential binary data. Fixed record size. Is it faster to access one data item per row. Or is it faster to access many data items (120+) aggregated into one row? * I should personnaly choose one row per data item, instead of an aggregate. Thanks in advance, Ben. -- 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]
Advice on Database Scheme
I put a screenshot of my database organization online at http://www.geoworld.org/database.gif It features four tables, focusing on Continents, Nations, States and Counties. Notice that Continents and Nations share a CCode (continent codes) column, Nations and States share a NCode (nation codes) field, and States and Counties share a SCode (state codes) field. The Counties table in the screen shot has no code of its own, but I've just added one that consists of the state code plus some digits. Thus, every row under Arizona will have a field with az for joining with Arizona, plus something like az10048 for a county's ID. I also want to work in some natural areas - physiographic provinces, ecological regions, etc. - somehow. The Realms column in the Continents table is a beginning. The other scheme I had in mind was to create a central table that features ID columns from each table. For example, a header row and some sample row might look something like this: Continent Codes | Nation Codes | State Codes | County Codes cna | us | ak | ak10022 cna | mx | chi | NULL opa | fp | NULL | NULL These rows represent continent-North America U.S. Alaska an Alaskan borough continent-North America Mexico Chihuahua ocean-Pacific French Polynesia With this scheme, instead of linking Continents directly to Nations, each table would be joined to the central column, which would match ALL the jurisdictions. Which scheme do you think is best? __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub query on mySQL 4.0.18
Oooo...Oo. thank you very much for you information. I better migrate my database to postgres. I'll use MySQL back after MySQL 4.1 stable version released on SuSE Distro. --- Jigal van Hemert [EMAIL PROTECTED] wrote: I don#t understand why subquery on my MySQL 4.0.18-Mas does not valid. Simple reason: v. 4.0.18 does not support subqueries yet. Use 4.1 or later. Regards, Jigal. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL not finidng openssl/opensslv.h
I'm configuring mysql-4.0.20 source and I have openssl (OpenSSL 0.9.7d 17 Mar 2004) installed in /usr/local/ssl (default) Below is the output from a make after configuring as follows: ./configure --prefix=/usr/local/mysql --with-openssl I also tried --with-openssl --with-openssl-includes=/usr/local/ssl/include/ --with-openssl-libs=/usr/local/ssl/lib/ and it breaks at the same point. When it's configuring and spurting out the output it does find OpenSSL: checking for OpenSSL... yes Any other people in the same boat? I've checked online and some other people have had the same issue and putting -I/usr/local/ssl/include in the path seems to work but shouldn't this work from the configure command? thanks, craig. - Making all in strings make[2]: Entering directory `/usr/src/mysql-4.0.20/strings' if gcc -DHAVE_CONFIG_H -I. -I. -I.. -I./../include -I../include-O3 -DDBUG_OFF -MT strxmov.o -MD -MP -MF .deps/strxmov.Tpo \ -c -o strxmov.o `test -f 'strxmov.c' || echo './'`strxmov.c; \ then mv -f .deps/strxmov.Tpo .deps/strxmov.Po; \ else rm -f .deps/strxmov.Tpo; exit 1; \ fi In file included from strxmov.c:33: ../include/my_global.h:1129:30: openssl/opensslv.h: No such file or directory make[2]: *** [strxmov.o] Error 1 make[2]: Leaving directory `/usr/src/mysql-4.0.20/strings' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/mysql-4.0.20' make: *** [all] Error 2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Checking for FK constraints only after the transaction commited ????
Hi, i`m using mysql in a j2ee application with JBoss. For best design practices i cannot add the foreign-keys to the tables in the same insert as i add the table data. Basically, i`ll add it a few milliseconds latter (in the same transaction), but anyway it`s not in the same insert. Then, i had to configure my foreign-keys to be allow null, something that i really don`t want to do. So, is there a way to only check for the FKs constraint at the end of the transaction, and not at the rigth moment of the insert Thanks, = beginner __ Participe da pesquisa global sobre o Yahoo! Mail: http://br.surveys.yahoo.com/global_mail_survey_br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking for FK constraints only after the transaction commited ????
On Fri, 4 Jun 2004 09:39:44 -0300 (ART)
Leandro Melo [EMAIL PROTECTED] wrote:
Hi,
i`m using mysql in a j2ee application with JBoss.
For best design practices i cannot add the
foreign-keys to the tables in the same insert as i
add the table data. Basically, i`ll add it a few
milliseconds latter (in the same transaction), but
anyway it`s not in the same insert.
This doesn't sound like best design practices. What exactly are you trying to do?
Why not:
START TRANSACTION;
INSERT INTO table (nonfk_col1, nonfk_col2) VALUES ('x', 'y');
UPDATE table SET fk_col3 = fk_values WHERE whatever;
COMMIT;
Then, i had to configure my foreign-keys to be allow
null, something that i really don`t want to do.
Allowing foreign key columns to be NULL is a perfectly acceptable and reasonable
practice depending on your data model.
So, is there a way to only check for the FKs
constraint at the end of the transaction, and not at
the rigth moment of the insert
You could enable and disable foreign keys in your transaction but this seems to be
hacking a solution onto a design problem.
Josh
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Self-Join Query
Perhaps I got my syntax wrong. IF() has been available since 3.23 as far as I know. I find it more readable than case if I'm only doing a single test, but either accomplishes what you need. Here is the documentation for CASE and IF http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html On Jun 3, 2004, at 8:33 PM, James KATARSKI wrote: Harold and Brent, Thanks for your help. I tried Brent's solution first, but the MySQL client didn't seem to like the IF statements. The case statements worked sweet though. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Off Topic: Search in this list not are functioning
Hi, I tried search numerous times and not get: either return all messages or neither. Is one known problem? Regards, Renato Cramer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on Database Scheme
David Blomstrom wrote: I put a screenshot of my database organization online at http://www.geoworld.org/database.gif It features four tables, focusing on Continents, Nations, States and Counties. Notice that Continents and Nations share a CCode (continent codes) column So how are you going to put Russia in both Asia and Europe? How are you going to put Turkey in both Asia and Europe? Egypt in Africa and Asia? Nations and States share a NCode (nation codes) field, and States and Counties share a SCode (state codes) field. My country does not have counties, and instead of states it has provinces. How are you going to deal with that? Answering my questions is answering your own question :-) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regd Updating my Database schemas
the problem that i am likely to face is the following. We have a database in our developement server . we keep making changes to the db very often like adding few feilds , deleting etc ... now whenever i make the change in the developement server . i just do the changes in the productions server also. earliers there was just one production database to update so was not a problem . rite now i have 6 so i wrote a program to make these changes into all the production dbs. But this with the program i could only add or modify tables and i have to add these things manually . i was wondering if there is any tool which would compare 2 dbs and update the other db with all the changes without affecting the data in each of the dbs. Sandeep Sesahdri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking for FK constraints only after the transaction commited ????
Leandro Melo wrote: So, is there a way to only check for the FKs constraint at the end of the transaction, and not at the rigth moment of the insert Not in MySQL. Other databases have functionality named deferred constraints, where constraint checking is deferred to transaction commit, but MySQL doesn't have them. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update SQL
Rows matched=1 but rows changed=0. :-( What is wrong with my query? mysql update records, audit_log, audit_log_records - set records.name=audit_log_records.name, - records.type=audit_log_records.type, - records.content=audit_log_records.content, - records.ttl=audit_log_records.ttl, - records.prio=audit_log_records.prio - where audit_log.tracker_id=audit_log_records.tracker_id - and records.id=audit_log_records.id - and audit_log.operation='C' - and audit_log.completed is null; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Run MySQL with ANSI mode
Friday, June 04, 2004, 1:24:35 PM, you wrote: ym Hi Victoria, ym Am running mysql Ver 11.18 Distrib 3.23.54, for ym redhat-linux-gnu (i386) ym Yes. In the documentation it asked us to specify ym option --ansi.I try to type this at the shell: ym $ mysql --ansi ym But not working. Because you try to start mysql client with --ansi option, not the server. Run mysqld with the above option or put to the my.cnf file: [mysqld] ansi ym --- Victoria Reznichenko ym [EMAIL PROTECTED] wrote: yau meileng [EMAIL PROTECTED] wrote: I try to run MySQL with ansi mode but failed. This was what I did: mysql SET GLOBAL sql_mode='ansi'; ERROR 1064: You have an error in your SQL syntax near 'sql_mode='ansi'' at line 1 What version do you use? The above syntax is supported since 4.1.1. Why I can't manage to change to ansi mode? Start MySQL server with --ansi option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replicator thread dying
It's happened to me four times the past few weeks that in a very lightly used QA environment replication is dying. The symptoms are that Exec_master_log_pos gets stuck somewhere, while Read_Master_Log_Pos gets incremented as the master has new updates. There is always a max-mysqld process that would not die on a restart, only kill -9 makes it go away, after which replication is hosed. The funny thing is that the production environment with 100 times the load is working OK. MySQL 4.0.16 on linux 2.4.x Any ideas ? Thanks, Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update SQL
On 06/04/04 11:28 Bob Lockie spoke: Rows matched=1 but rows changed=0. :-( What is wrong with my query? mysql update records, audit_log, audit_log_records - set records.name=audit_log_records.name, - records.type=audit_log_records.type, - records.content=audit_log_records.content, - records.ttl=audit_log_records.ttl, - records.prio=audit_log_records.prio - where audit_log.tracker_id=audit_log_records.tracker_id - and records.id=audit_log_records.id - and audit_log.operation='C' - and audit_log.completed is null; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 Sometimes it works, sometimes not. :-( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Force the use of an index
Is there a way to force the use of a specific index when issuing a select querie? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update SQL
At 11:28 -0400 6/4/04, Bob Lockie wrote: Rows matched=1 but rows changed=0. :-( What is wrong with my query? mysql update records, audit_log, audit_log_records - set records.name=audit_log_records.name, - records.type=audit_log_records.type, - records.content=audit_log_records.content, - records.ttl=audit_log_records.ttl, - records.prio=audit_log_records.prio - where audit_log.tracker_id=audit_log_records.tracker_id - and records.id=audit_log_records.id - and audit_log.operation='C' - and audit_log.completed is null; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 Perhaps nothing. If the statement WHERE clause selects a row to update, but the column assignments do not actually change any values (e.g., they set the columns to the values they already have), the Changed count will be zero. -- 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: Advice on Database Scheme
Hi, Can I ask what you used to render that .gif ? Looks like phpMyAdmin but I have never seen that feature in phpMyAdmin.. Thanks, Andrew -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Friday 04 June 2004 16:19 To: David Blomstrom Cc: [EMAIL PROTECTED] Subject: Re: Advice on Database Scheme David Blomstrom wrote: I put a screenshot of my database organization online at http://www.geoworld.org/database.gif It features four tables, focusing on Continents, Nations, States and Counties. Notice that Continents and Nations share a CCode (continent codes) column So how are you going to put Russia in both Asia and Europe? How are you going to put Turkey in both Asia and Europe? Egypt in Africa and Asia? Nations and States share a NCode (nation codes) field, and States and Counties share a SCode (state codes) field. My country does not have counties, and instead of states it has provinces. How are you going to deal with that? Answering my questions is answering your own question :-) Jochem -- 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: Force the use of an index
Jeff McKeon [EMAIL PROTECTED] wrote on 04/06/2004 16:52:48: Is there a way to force the use of a specific index when issuing a select querie? Yes. From the Fine Manual As of MySQL 3.23.12, you can give hints about which index MySQL should use when retrieving information from a table. By specifying USE INDEX (key_list), you can tell MySQL to use only one of the possible indexes to find rows in the table. The alternative syntax IGNORE INDEX (key_list) can be used to tell MySQL to not use some particular index. These hints are useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes. From MySQL 4.0.9 on, you can also use FORCE INDEX. This acts likes USE INDEX (key_list) but with the addition that a table scan is assumed to be very expensive. In other words, a table scan will only be used if there is no way to use one of the given indexes to find rows in the table. USE KEY, IGNORE KEY, and FORCE KEY are synonyms for USE INDEX, IGNORE INDEX, and FORCE INDEX. Note: USE INDEX, IGNORE INDEX, and FORCE INDEX only affect which indexes are used when MySQL decides how to find rows in the table and how to do the join. They do not affect whether an index will be used when resolving an ORDER BY or GROUP BY. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
average in Group By
Hi, I am trying to find average price of the shares in the portfolio table. I thought something like this should work... SELECT symbol, ((sum(buyrate*quantity))/quantity) as average from portfolio group by symbol; It does work, but wrong results. What is the correct query? Shantanu Oak __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: average in Group By
On Fri, Jun 04, 2004 at 09:25:58AM -0700, Shantanu Oak wrote: I am trying to find average price of the shares in the portfolio table. I thought something like this should work... SELECT symbol, ((sum(buyrate*quantity))/quantity) as average from portfolio group by symbol; It does work, but wrong results. What is the correct query? Why not use the AVG() function? It does exactly what you want. http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html Jim Winstead MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: average in Group By
If buyrate and quantity are per row fields then SELECT symbol, ((sum(buyrate*quantity))/sum(quantity)) as average from portfolio group by symbol; Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Shantanu Oak [mailto:[EMAIL PROTECTED] Sent: 04 June 2004 17:26 To: [EMAIL PROTECTED] Subject: average in Group By Hi, I am trying to find average price of the shares in the portfolio table. I thought something like this should work... SELECT symbol, ((sum(buyrate*quantity))/quantity) as average from portfolio group by symbol; It does work, but wrong results. What is the correct query? Shantanu Oak __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replicator thread dying
Balazs Rauznitz [EMAIL PROTECTED] wrote: It's happened to me four times the past few weeks that in a very lightly used QA environment replication is dying. The symptoms are that Exec_master_log_pos gets stuck somewhere, while Read_Master_Log_Pos gets incremented as the master has new updates. There is always a max-mysqld process that would not die on a restart, only kill -9 makes it go away, after which replication is hosed. The funny thing is that the production environment with 100 times the load is working OK. MySQL 4.0.16 on linux 2.4.x What is the value of Slave_SQL_Running in the SHOW SLAVE STATUS output? Is there anything in the error log? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed differences between joins and subqueries?
Daniel Ek wrote: Hi all, I am wondering if anyone have any knowledge if there is speed difference between joins (inner | outer | left | right) and subqueries. Would I actually gain preformance using subqueries (new since 4.1) against using the old joins? Would be interesting to hear comments on this matter, and also a detailed explanation on which is to prefer and why. Subquery optimizer is still is a very early stage in 4.1. Much more often than not, a carefully crafted join, union, or a sequence of queries using a temporary table will be faster. In fact, I wonder if it is even possible to write a machine subquery optimizer that will outperform a good human optimizer that re-writes them into something better digestible. Kind of like no super-smart JIT will ever beat a good C programmer on execution speed, although it might help with the development time. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.2 myisamchk chokes on a fulltext
Hi. I'm having this problem on 4.1.2, when I run myisamchk -o on my table I get a bunch of these lines: Duplicate key 3 for record at 56134200 against new record at 244828223 then it segfaults. key 3 is a fulltext. I tried the same thing on the same data with 4.0.14 and it worked fine, I also tried dropping the fulltext and adding it again. myisamchk -e doesn't complain for some reason. Any help very appreciated /thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replicator thread dying
On Fri, Jun 04, 2004 at 08:03:03PM +0300, Egor Egorov wrote: Balazs Rauznitz [EMAIL PROTECTED] wrote: It's happened to me four times the past few weeks that in a very lightly used QA environment replication is dying. The symptoms are that Exec_master_log_pos gets stuck somewhere, while Read_Master_Log_Pos gets incremented as the master has new updates. There is always a max-mysqld process that would not die on a restart, only kill -9 makes it go away, after which replication is hosed. The funny thing is that the production environment with 100 times the load is working OK. MySQL 4.0.16 on linux 2.4.x What is the value of Slave_SQL_Running in the SHOW SLAVE STATUS output? Is there anything in the error log? Slave_SQL_Running is 'Yes'; nothing in the error log... Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on Database Scheme
--- Jochem van Dieten [EMAIL PROTECTED] wrote: David Blomstrom wrote: --- Jochem van Dieten [EMAIL PROTECTED] wrote: So how are you going to put Russia in both Asia and Europe? How are you going to put Turkey in both Asia and Europe? Egypt in Africa and Asia? Egypt is in Africa, not Asia. Usually the part on the eastern side of the Suez canal is regarded as being part of the Middle East, hence Asia. Russia and Turkey pose a problem, but if I can't figure it out, I'll just choose Europe OR Asia, then explain it in the text. How about having just 3 tables: AreaTypes: AreatypeID AreaType 1 Continent 2 Country 3 City 4 etc. Areas: AreaID Area AreaTypeID 1 Europe1 2 France2 3 Germany 2 4 Paris 3 AreaHierargy: AreaID ParentID 1 NULL 2 1 3 1 4 2 Just walk the tree :) Wow, that looks like a great scheme. I'll probably replace the numerals with ISO codes, FIPS codes and letter abbreviations, so your last table might look something like this: AreaHierargy: AreaID ParentID eu eurasia fr eu gm eu fr001 fr And then when I want to add data, like the names of capital cities, population, etc. I can just create additional tables and join them to these using shared ID fields, right? Thanks; I think I'll give this a try right now. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Humor: Take a break and watch something stupid ;-)
Ok, you need a break from programming. Take a look at one programmer has come up with. http://www.lebonze.co.uk/stuff/move.htm Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble with Query
I'm query for a list of offers from a table, but am trying to do a Left Join on the table that keeps track of which members have completed which offers (so that the query will not return offers that the member has already completed). Query: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') order by ol.weight desc limit 3; Results: +++ | id | date | +++ | 1 | 2004-06-04 | | 2 | NULL | | 3 | NULL | +++ So, member_id 1 has completed offer_id 1, but not offer_id's 2 or 3. I would assume that I simply need to add one more where clause to only return results with a non-null date. I tried: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') and (mo.date IS NULL) order by ol.weight desc limit 3; However, this does not work - I get an empty result. Which is *really* weird, because if I change the query to only return non-null values, like so: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') and (mo.date IS NOT NULL) order by ol.weight desc limit 3; It works perfectly: +++ | id | date | +++ | 1 | 2004-06-04 | +++ Of course, this is the opposite of what I want, so I'm quite confused. Can anyone point me in the right direction? TIA! __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backing Up a Database
For the time I've been testing, I've used the procedures outlined in the help to take my backups, which entails doing a FLUSH TABLES WITH READ LOCK in my MySQL monitor, then going to a shell prompt and executing the mysqldump utility, then issuing the UNLOCK TABLES from my MySQL monitor. Now I'm trying to schedule all this and I have a question about using just mysqldump. If I use the --lock-tables parm, am I getting the same functionality? The reason I'm concerned is because the help says: The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup. Can I make sure that happens without moving back and forth between the MySQL monitor and the mysqldump utility? Thanks, Lou
RE: vpopmail installed on MySQL Client
Hello List: I had posted this message on vpopmail list but no one can help. I am posting this message on MySQL hoping that someone on this list is using vpopmail MySQL!!! Please consider me a newbie in the area of LINUX, MySQL, vpopmail, etc. I have setup two servers as follows: (1) A master mysql server: data.tib.com Master MySQL server has RH9 MySQL 4.0.20 installed. (2) A mail server: mail.tib.com For the mail server, I want to install RH9, Apache, qmail, vpopmail, etc., by following the toaster: http://www.pipeline.com.au/staff/mbowe/isp/webmail-server.htm There is only one exception: On the data.tib.com server, I have setup MySQL as Master Server on the mail.tib.com server. I have setup MySQL as Client MySQL server. I have tested the MySQL connection from the MAIL (Client MySQL) to the DATA (Master MySQL) server it connects without problem. When I installed vpopmail software, I changed the reference to MySQL from localhost to Master MySQL Server (data.tib.com). I added the vpopmail user/pw/db from the MAIL Server without problem. However; when I tried to add a domain on the MAIL server, I get an error (it seems that it does not find the MySQL data). I can list the error if needed. My question is: Has anyone installed vpopmail client mysql where all the data resides on a master mysql server? Is it even possible? If it is not possible, then what are the alternatives? FYI, I have looked in vpopmail/mysql archives GOOGLE, without success. HELP, I have spent over 10 days reading, researching pulling my hair. Kirti TIB P. O. Box 49 Mountain City, TN 37683 Tel: (423) 727-30001 Fax: (423) 727-3002 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing Up a Database
On Fri, 4 Jun 2004 18:16 , Lou Olsten [EMAIL PROTECTED] sent: For the time I've been testing, I've used the procedures outlined in the help to take my backups, which entails doing a FLUSH TABLES WITH READ LOCK in my MySQL monitor, then going to a shell prompt and executing the mysqldump utility, then issuing the UNLOCK TABLES from my MySQL monitor. Now I'm trying to schedule all this and I have a question about using just mysqldump. If I use the --lock-tables parm, am I getting the same functionality? The reason I'm concerned is because the help says: The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup. I cant answer your question directly, but I suggest looking at mysqlhotcopy Can I make sure that happens without moving back and forth between the MySQL monitor and the mysqldump utility? Thanks, Lou --- Chris McKeever If you want to reply directly to me, please use cgmckeever--at--prupref---dot---com A href=http://www.prupref.com;www.prupref.com/A Prudential Preferred Properties A href=http://www.prupref.com;Chicago and Illinois NorthShore Real Estate Experts/A Prudential Preferred Properties www.prupref.com Success Driven By Results Results Driven By Commitment Commitment Driven By Integrity We Are Prudential Preferred Properties -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble with Query
I think you want to try 'having mo.date IS NULL' after the order by. Daren wrote: I'm query for a list of offers from a table, but am trying to do a Left Join on the table that keeps track of which members have completed which offers (so that the query will not return offers that the member has already completed). Query: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') order by ol.weight desc limit 3; Results: +++ | id | date | +++ | 1 | 2004-06-04 | | 2 | NULL | | 3 | NULL | +++ So, member_id 1 has completed offer_id 1, but not offer_id's 2 or 3. I would assume that I simply need to add one more where clause to only return results with a non-null date. I tried: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') and (mo.date IS NULL) order by ol.weight desc limit 3; However, this does not work - I get an empty result. Which is *really* weird, because if I change the query to only return non-null values, like so: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') and (mo.date IS NOT NULL) order by ol.weight desc limit 3; It works perfectly: +++ | id | date | +++ | 1 | 2004-06-04 | +++ Of course, this is the opposite of what I want, so I'm quite confused. Can anyone point me in the right direction? TIA! __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Off Topic: Search in this list not are functioning
On Fri, Jun 04, 2004 at 10:33:27AM -0300, Renato Cramer wrote: I tried search numerous times and not get: either return all messages or neither. Is one known problem? The search box in the sidebar was simply not going to the right place. This has been fixed. Thanks for the report. Jim Winstead MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question
Where do you run the verify programs from? Are they ran from the command prompt? Do I copy the keys? Thanks - Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger
