ORDER BY total of multiple columns?
Hi, I have a mysql table with columns pts_t2, pts_ext and pts_other which hold each player's point total for that category. I have it displaying player's ranks by ordering by each of those points. However, I would like to have an overall ranking (all categories combined), and I don't want to add another column to just hold a total of these 3 columns. How can I order my select statement to list the players by order of their total points? Thanks --- Andrew Culver Level 2 DCI Judge Comp Sci 2 at UWO "The frontline is everywhere." --- - 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
creating databases in different folders
Hi all, I'm using MySQL 5.0 on Windows 2003. Problem background: We use the same server for different applications. All the applications share the same server as the database server. Each application uses their own database. In MSSQL we put each database in the corresponding application folder so that the application root folder contains everything (including the database). Now we're trying to do the same for MySQL as well, but I'm not sure how I can specify that a database should be created in a particular directory (and it's almost always not in C:\Program Files\MySQL Server). Can anyone advise? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: AW: creating databases in different folders
Benedikt Schackenberg wrote: > You can configure it in the my.cnf file: there you can set the data > directory. Thanks for the quick reply. My concern is that setting the data directory puts ALL databases in that folder. What I plan to do is to put databases in separate folders. Is that possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: AW: creating databases in different folders
John Daisley wrote: > MySQL represents each database by means of a database directory located > within the data directory. You can move a database directory to a location > outside the datadirectory and replace it with a symlink. Thanks for the tip John, esp. the detailed steps to take. It sounds scary (I did some reading and there's some concerns about deleting stuff via Windows Explorer), but I'd give it a shot and update you on the success. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: AW: AW: creating databases in different folders
>From the sound of things, apart from using symbolic links, all MySQL databases MUST reside within the same data root folder. Some further questions: 1. On the *NIX, *BSD platform, do you guys locate the databases in diff folders and link it back to the data root on production systems? 2. If I were to use Innodb, there is NO way to separate the files used by different databases? Benedikt Schackenberg wrote: > I think, that will not work with one database daemon. Then you have to > install for every database one daemon ;) > > > -Ursprüngliche Nachricht- > Von: Foo JH [mailto:jhfoo...@extracktor.com] > Gesendet: Dienstag, 2. Juni 2009 11:49 > An: schackenb...@termindoc.de > Cc: mysql@lists.mysql.com > Betreff: Re: AW: creating databases in different folders > > Benedikt Schackenberg wrote: >> You can configure it in the my.cnf file: there you can set the data >> directory. > Thanks for the quick reply. > > My concern is that setting the data directory puts ALL databases in that > folder. What I plan to do is to put databases in separate folders. > > Is that possible? > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Specific issues with MySQL 4.0.16 on Linux or Windows 2000/3 related to tmp tables?
Hi Everyone, I've been Googling unsuccessfully for specific issues relating to queries run on MySQL version 4.0.16 against "tmp" tables. I have witnessed several occurrences where queries running on various platforms hang in a "Copying to tmp table" state for hours or days at a time. When the same query is manually run from the MySQL command line client, the query returns, even on very large tables. Could someone please give me a hint as to possible tuning (or upgrade?) ideas to fix this situation? I can't find anything else on each problem system that would give me a hint as to why this problem randomly occurs. I've made attempts at adjusting the "tmp_table_size" limits to have the system try to do "order by" queries in memory, but this does not seem to help the situation. Any help would be appreciated! -Kevin "show full processlist" output (notice that the query has been running for 7.9 days!!!): | 33 | someuser | localhost:34329 | sometable | Query | 687465 | Copying to tmp table | select date_add( date_format(time, '%Y-%c-%d 00:00:00'), INTERVAL 0 HOUR) time, ... group by 1 order by 1 __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Specific issues with MySQL 4.0.16 on Linux or Windows 2000/3 related to tmp tables?
8192 range_alloc_block_size 2048 read_buffer_size131072 read_only OFF read_rnd_buffer_size262144 rpl_recovery_rank 0 server_id 0 slave_net_timeout 3600 skip_external_locking ON skip_networking OFF skip_show_database OFF slow_launch_time2 socket /tmp/mysql.sock sort_buffer_size2097144 sql_mode0 table_cache 64 table_type MYISAM thread_cache_size 0 thread_stack126976 tx_isolationREPEATABLE-READ timezonePST tmp_table_size 209715200 tmpdir /tmp/ transaction_alloc_block_size8192 transaction_prealloc_size 4096 version 4.0.16-pro-log wait_timeout28800 --- Mark Maunder <[EMAIL PROTECTED]> wrote: > Please include the full query you're running, the > table structure, and > the number of rows in the table. A dump of 'show > variables;' would be > helpful too. > > On Wed, 2004-11-10 at 21:44, foo bar wrote: > > Hi Everyone, > > > > I've been Googling unsuccessfully for specific > issues > > relating to queries run on MySQL version 4.0.16 > > against "tmp" tables. I have witnessed several > > occurrences where queries running on various > platforms > > hang in a "Copying to tmp table" state for hours > or > > days at a time. When the same query is manually > run > > from the MySQL command line client, the query > returns, > > even on very large tables. Could someone please > give > > me a hint as to possible tuning (or upgrade?) > ideas to > > fix this situation? I can't find anything else on > > each problem system that would give me a hint as > to > > why this problem randomly occurs. I've made > attempts > > at adjusting the "tmp_table_size" limits to have > the > > system try to do "order by" queries in memory, but > > this does not seem to help the situation. > > > > Any help would be appreciated! > > > > -Kevin > > > > "show full processlist" output (notice that the > query > > has been running for 7.9 days!!!): > > | 33 | someuser | localhost:34329 | sometable | > Query > > | 687465 > > | Copying to tmp table | select > > date_add( date_format(time, '%Y-%c-%d > 00:00:00'), > > INTERVAL 0 HOUR) time, > > ... > > group by 1 > > order by 1 > > > > > > > > __ > > Do you Yahoo!? > > Check out the new Yahoo! Front Page. > > www.yahoo.com > > > __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Specific issues with MySQL 4.0.16 on Linux or Windows 2000/3 related to tmp tables?
Hi Mark, The system in question has 1GB of RAM in it. As far as I can tell, the box does not get stuck swapping when the system has a query (or several) in this state. If I log in via the command line client and kill the query, the system continues on it's way like nothing was wrong in the first place. It almost seems to me that the server is getting into some strange state while writing to temporary table. For some reason, this fails and instead of erroring out, the query stays in a perpetual running state. On MySQL 4.0.16, a "key_buffer_size" of 134217728 translates into 134217728/1024 bytes per key = 131072 possible cached key values. Because this table (in addition to several others) may have several million rows, in order to cache say 3 million keys, I'd have to allocate about 3GB worth of ram just to this single buffer. Thanks for the info though! --- Mark Maunder <[EMAIL PROTECTED]> wrote: > In my experience, inserting into a table with a > unique key and more than > 5 million records can be very slow because (AFAIK) > it has to scan the > entire index to check if the new record is unique > from the PRIMARY KEY's > point of view. (I think) > > If you don't have much ram (your 128 meg key buffer > suggests you dont) > and your disk channel isn't particularly fast, this > may take a very long > time. > > Your tmp_table_size of 200 Megs is large considering > your key buffer is > 128M. If the machine doesn't have enough RAM, it may > end up swapping > heavily trying to keep the tmp table in memory and > get stuck they way > you've described - although I must admit I'm not > sure what mysql does in > a situation where tmp_table_size is too large. > > How much RAM does the machine have? Can you check > swap activity? > > On Thu, 2004-11-11 at 00:27, foo bar wrote: > > Here's the whole the query, table structure, table > > length and show variables output: > > > > mysql> desc summary; > > > +-+--+--+-+-++ > > | Field | Type | > Null | > > Key | Default | Extra | > > > +-+--+--+-+-++ > > | id | int(10) unsigned | > | > > PRI | NULL| auto_increment | > > | host_id | int(10) unsigned | > | > > MUL | 0 || > > | alias_domain_id | int(10) unsigned | YES > | > > MUL | NULL|| > > | domain_id | int(10) unsigned | YES > | > > MUL | NULL|| > > | alias_id| int(10) unsigned | YES > | > > MUL | NULL|| > > | sender_domain_id| int(10) unsigned | YES > | > > MUL | NULL|| > > | sender_alias_id | int(10) unsigned | YES > | > > MUL | NULL|| > > | time| datetime | > | > > MUL | -00-00 00:00:00 || > > | val1| int(10) unsigned | > | > >| 0 || > > | val2| int(10) unsigned | > | > >| 0 || > > | processed | int(10) unsigned | > | > >| 0 || > > > +-+--+--+-+-++ > > > > select count(*) from summary; > > +--+ > > | count(*) | > > +--+ > > | 34759085 | > > +--+ > > 1 row in set (0.05 sec) > > > > Query: > > create temporary table tmp (PRIMARY > > KEY(alias_id,domain_id)) > > select > >alias_id, > >domain_id, > >sum(val1) rank > > from summary > > where 1=1 > > and time >= '2004-11-01 11:00:00' > > and time <= '2004-11-09 11:00:00' > > group by 1, 2 > > order by rank desc > > limit 5 > > > > 'show variables;' output: > > Variable_name Value > > back_log50 > > basedir /opt/mysql-pro-4.0.16/ > > binlog_cache_size 32768 > > bulk_insert_buffer_size 8388608 > > character_set latin1 > > character_sets latin1 big5 czech euc_kr gb2312 > gbk > > latin1_de sjis tis620 ujis dec8 dos german1 hp8 > > koi8_ru latin2
RE: mysql.sock
Try running safe_mysqld, this should generate the sock file.
I came across the same issue when I installed mysql. Check and see if mysql
(as a user) has access to the mysql directory. You might want to do a
chown -R mysql:mysql {mysql dir}
to change the permissions for the mysql dir and all directories underneath
it.
Cheers.
> -Original Message-
> From: Ajay Nagarkar [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, 21 July 2001 10:30 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: mysql.sock
>
>
> Hi All,
> I am a new user of mysql. I too installed mysql on my
> linux box. but i get the same message. what is this
> message and how do we solve it.
> I had the client and server RPM and installed them.
> I once managed to start the server with mysqld. But
> now the server also doesnt start saying host.frm
> error. Please do let me know the details of linux
> installation. I need mysql very badly and I am seeking
> help from all of you.
> Thanks
> Ajay
> PS - Micheal , If you get to know how this problem can
> be solved do let me know. I shall do the same if I get
> the solution
> --- Michael Johnson
> <[EMAIL PROTECTED]> wrote:
> > I have been trying to install MySQL on my Cobalt
> > RAQ3 and I seem to have
> > lost my mysql.sock file. I get the following error
> > message when I tray to
> > carryout any mysql activity.
> >
> > ERROR 2002: Can't connect to local MySQL server
> > through socket
> > '/var/lib/mysql/mysql.sock' (111)
> >
> > Pls advise
> >
> >
> >
> > Michael Johnson
> > Director
> > BPEnet
> > Humphrey Consulting Limited
> > 13 Austin Frais
> > London EC2N 2JX
> > Tel +44(0)1323 438975
> > Fax +44(0)1323 738355
> > email [EMAIL PROTECTED]
> >
> > Also @ Carmichael House
> > 60 Lower Baggott Street
> > Dublin 2
> > Tel +353(0)1 602 4739
> >
> > Also @ 26 Boulevard Royal
> > L-2449
> > Luxembourg
> > Tel +352 22 99 99 55 07
> >
> >
> >
> >
> -
> > 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
> >
>
>
> __
> Do You Yahoo!?
> Make international calls for as low as $.04/minute with Yahoo! Messenger
> http://phonecard.yahoo.com/
>
> -
> 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
Can't find ./mysql/host.frm when starting mysqld
I have mysql-server-3.23.22.6 and mysql-3.23.22.6, packages installed on my PC during Linux RedHat 7.0 installation. The problem occurs when starting mysqld program : The command : mysql install db is OK but the command : safe mysqld starts the server mysqld witch stops immediately after. When I look at the file : /var/lib/mysql/my local host.err I find the lines : 010212 13:26:29 mysqld started 010212 13:26:30 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010212 13:26:30 mysqld ended The file host.frm is in : /var/lib/mysql/mysql/ I tried to copy all the files from /var/lib/mysql/mysql/ to /var/lib/mysql/ but the problem still remain. Can you help me please. Thank you. [EMAIL PROTECTED]
Multiple import of text files from a directory into mysql
Hi, I have a couple hundred text files in a directory that I want to import into a mysql table. I have successfully imported a file, data looks ok. Is there a way to automate the import of all files in a specified directory instead of doing it one by one? Kindly cc me on the reply when you send this to the mailing list. Regards, Shem Foo - 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
MySQL will timeout on connection handles
Hi all, I'd like to clarify on this odd issue. It seems to me that MySQL will automatically drop database handles, assumably if the handle has not been used for some time. Is this true? If so, how can I disable timeout? This is an unexpected behavior, because I have tried MSSQL and Postgre, and they don't exhibit this behavior. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Surviving MySQL crash
Hi all, I have a MySQL server servng low-load applications. Problem is, the environment is sometimes unstable, leading the entire OS to crash. I notice that even in low-load situations the MySQL tables can be corrupted during crashes. My question is: is there a way for MySQL to flush when idle for x minutes, or some other way to avoid MySQL crashing (other than moving it out of the environment)? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Surviving MySQL crash
Heikki Tuuri wrote: Ji-Haw, if the OS crashes do not corrupt files, then InnoDB tables would survive an OS crash without a problem. Thanks for the reply Heikki. Let me rephrase my problem: the data remains intact, but I suspect the indexes are corrupted. Basically I have to run myisamcheck and mysqlcheck to get my tables operational again. No data loss though. Logan (thanks Logan) suggested a periodic flush call. I am wondering if auto flushing is available? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Surviving MySQL crash
15 * * * * /usr/local/mysql/bin/mysql -u root -pxx -e 'FLUSH TABLES' This would run a FLUSH TABLES once every hour at 15 minutes past. If you are using Windows, sorry I'm not sure how to do it there. Easier to just set flush_time=900 in my.cnf :) Thanks guys. Will give it a shot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to convert InnoDB to MyISAM in 5.0?
hi guys, it's a simple thing (I think), but I can't find the docu on this from the mysql site. Thanks in advance for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
performance between InnoDB vs MyISAM
Hi all, Just want to share and confirm my findings on a performance issue I've been experiencing. My database is strictly non-transactional, but it's got about 200,000 records in this particular table. The table has a primary index, and 2 integers - one for the date and the other for the time. Among the other fields there's a text field which usually stores a huge amount of text. One thing I notice, is that under MyISAM running the following sql: select id from mytable where id in (#subselect to extract a set of ids) order by mydate desc, mytime desc The time taken is really bad, like > 90 secs. But in InnoDB it is usually <8 secs. The time difference is too crazy to ignore. Can anyone explain this? Is there something in InnoDB that creates the magic? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance between InnoDB vs MyISAM
Hey there Ady, Philip, Thanks for the suggestions for the phenomenon. I also notice something along the course of optimisation: 1. Sorting records with huge fields (ie: blobs, text) is significantly slower than if you extract the blobs/ text fields into a separate table. The record size makes a difference in the sorting performance? 2. Sorting by int desc, int desc is much slower than int asc, int asc, where 'int' is an integer field. If (1) is generally true, perhaps to accommodate the potential increase of the table into a million records, I have to break the table into 2 tables: the original table will hold only enough fields for sorting, and the huge fields in the other. What do you guys think? Ady Wicaksono wrote: Hi Foo, MyISAM impress me on insert speed, however on many case MyISAM is not better than Innodb. If you can't use combination of them, better your break down your need to decide which one to use. AFAIK, sub query is better in innodb rather than myisam, and if you have only 200.000 records with huge amount of text, innodb is good enough, just make sure you have enough memory to increase performance. Do you need fulltext SEARCH? If yes, myisam is support this :D not innodb. Foo Ji-Haw wrote: Hi all, Just want to share and confirm my findings on a performance issue I've been experiencing. My database is strictly non-transactional, but it's got about 200,000 records in this particular table. The table has a primary index, and 2 integers - one for the date and the other for the time. Among the other fields there's a text field which usually stores a huge amount of text. One thing I notice, is that under MyISAM running the following sql: select id from mytable where id in (#subselect to extract a set of ids) order by mydate desc, mytime desc The time taken is really bad, like > 90 secs. But in InnoDB it is usually <8 secs. The time difference is too crazy to ignore. Can anyone explain this? Is there something in InnoDB that creates the magic? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Discussion: the efficiency in using foreign keys
Hi all, This is a fundamental concept in RDBMS: the use of foreign keys in database design. I'd just like to poll the community here, on whether it is a best practice, or practically essential to 'link' related tables by use of foreign keys. For myself, I usually do all the validity checking when adding a new record that references a record id from another table. I understand that this may not be efficient because it becomes 2 database calls (and db calls are expensive in high-load environments). What are the advantages/ disadvantages in using foreign keys? In MySQL, this means one cannot use MyISAM. Do you place a lot of triggers as well? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is the sql command to export the whole database ?
tony vong wrote: What is the sql command to export the whole database ? I use: mysqldump -u [username] -p[password] [database name] > [filename] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is the sql command to export the whole database ?
tony vong wrote: What is the sql command to export the whole database ? I use: mysqldump -u [username] -p[password] [database name] > [filename] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Discussion: the efficiency in using foreign keys
Hello Keith,
Thanks for responding. I was actually referring to the subject of using
foreign keys, as opposed to leaving it to the calling application to do
the necessary checks. In particular issues of performance, efficiency
etc. IMHO, foreign keys add 'strictness' to the consistency of related
tables. But for me, I have not bothered to do so, preferring to do the
checking from the application instead.
Appreciate your detailed example on normalising the database, which I
agree with you that it is much preferred against a mega table.
[EMAIL PROTECTED] wrote:
IMHO I think you will find that there is a balance between
the speed of opening and reading/writing several related
smaller tables connected by FK's, rather than one mega-sized
gigantic table.
How do you normalise a table without using FK's.
Your right, MySQL does not currently do any checking for
FK's, but this does not mean that you cannot still use them
in MyISAM tables.
Eg.
/* table to store quiz questions */
CREATE TABLE `quiz_question` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`question_text` text NOT NULL,
PRIMARY KEY `ID` (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
/* table to store quiz answers */
CREATE TABLE `quiz_answer` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`answer_text` text NOT NULL,
`status` enum('wrong', 'right') NOT NULL,
`questionID` mediumint UNSIGNED NOT NULL default '0',
PRIMARY KEY `ID` (`ID`),
KEY `questionID` (`questionID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
/* table to track quiz questions with user answers */
CREATE TABLE `quiz_result` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`user` char(32) NOT NULL default '',
`questionID` mediumint UNSIGNED NOT NULL default '0',
`answerID` mediumint UNSIGNED NOT NULL default '0',
PRIMARY KEY `ID` (`ID`),
KEY `questionID` (`questionID`),
KEY `answerID` (`answerID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
So in the quiz_result table above questionID is a
column holding the primary key of each question_text column
in the quiz_question table. It is a foreign key.
answerID is a foreign key that points to the primary
key of the answer submitted by the user doing the quiz.
When the user has finished doing the quiz, the quiz_result
table is scanned for the user session ID, 'user', and then
the question and the user's chosen answer are picked from
the quiz_question and quiz_answer tables, using the foreign
keys in the result table.
I find it helps me to think of foreign keys as unique
pointers to rows in other related tables.
HTH
Keith
In theory, theory and practice are the same;
in practice they are not.
On Mon, 20 Mar 2006, Foo Ji-Haw wrote:
To: mysql@lists.mysql.com
From: Foo Ji-Haw <[EMAIL PROTECTED]>
Subject: Discussion: the efficiency in using foreign keys
Hi all,
This is a fundamental concept in RDBMS: the use of foreign keys in
database design.
I'd just like to poll the community here, on whether it is a best
practice, or practically essential to 'link' related tables by use of
foreign keys.
For myself, I usually do all the validity checking when adding a new
record that references a record id from another table. I understand that
this may not be efficient because it becomes 2 database calls (and db
calls are expensive in high-load environments).
What are the advantages/ disadvantages in using foreign keys? In MySQL,
this means one cannot use MyISAM. Do you place a lot of triggers as well?
Thanks.
--
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: Discussion: the efficiency in using foreign keys
I've always been a believer in avoiding sql procedures, for the main reason that I want to be as database-independent as possible. I know it is less efficient, but being able to switch between MySQL, Postgre, and the new freebies from IBM, Oracle, and Microsoft is a strong advantage from the business perspective (of total cost to the customer, and customer preference). Of course, this is a discussion point. I'd love to hear from the community on their experiences. [EMAIL PROTECTED] wrote: I'd also like to add that if you have a choice between doing something in the application logic vs. MySQL's SQL statements, then it is probably more efficient to use SQL statements, constructs and related functions, to get the job done, rather than in the application logic if possible. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help: recovering db from crash
Hi all, My Windows-based database server crashed (no fault of MySQL. probably OS or hardware), and I managed to copy out the data files. I am using version 5.0 of the Essentials package. I tried to install a similar setup on another server, then copy the data\ folder over. The MySQL service starts, but I am not able to login, even as root. Is there anyone who can advise me on the recovery steps? Appreciate your feedback! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help: recovering db from crash
Thanks for coming to the rescue, Mark and Bruce. Mark Leith wrote: This is actually for Linux/Unix, not Windows. What error do you get from MySQL when trying to log in? Does the mysqld(-nt) process show within Task Manager? What does the new error log say? You may need to reset permissions: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html Or you may need to set the appropriate datadir / basdir etc. depending on how you set up MySQL: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Best regards Mark Adrian Bruce wrote: not sure, but it may be worth trying the following run the script: mysql_install_db --user=root In the installation dir this should change ownership and make mysql recognise the data dir. good luck Ade Foo Ji-Haw wrote: Hi all, My Windows-based database server crashed (no fault of MySQL. probably OS or hardware), and I managed to copy out the data files. I am using version 5.0 of the Essentials package. I tried to install a similar setup on another server, then copy the data\ folder over. The MySQL service starts, but I am not able to login, even as root. Is there anyone who can advise me on the recovery steps? Appreciate your feedback! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connecyion error
My application cannot connect to the database and perform SQL qurey after usuualy after a day. I heard that mysql automatically close connection after 8 hrs of inactivity. Hope u can email me how to prevent the auto closing or any alternative method. Thanks in advance! __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ - 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 Connecyion error
i tried to catch the exception and recoonect to MySQL server and it dose not work, i also tried open and closing the connection only when i need it and that does not work too so i had to resort to not this alternative. anyway thanks fr your solution.. and how to make disable auto closing indefinately instead of setting the timeout value? __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ - 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 Connection error
But my program works fine and all those java code i used to connect to mysql is what i have been taught by my lecturer it works fine..only things is that i never leave my application up for more than a day and that when i discover this problem. Usually i restart tomcat and it works again but the problem resurface the next day. __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ - 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
