Re: Stop query on first match
Hello all, Thank you to everyone who assisted me. I'm wondering about my theory around the between as posted previously. Can anyone provide further insights regarding that theory? Kind regards Emmanuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stop query on first match
On Monday, June 07, 2004 20:23, Justin Swanhart wrote It can only ever return one row from table b, for each row on table a, due to the contents that is stored in table b. The table contains in excess of a million records. What happens as a result of the I assume that you have multiple rows in table b that match the primary key on table a, but only one of those rows in b will be satisfied by your between condition of the query. Hello Justin, fisrtly, thank you for your assistance. Then, as I posted previously, keys do not match, however, a property on table a matches only in respect of being between two properties on table b. First off, make sure that you have an index on table b that matches the primary key in table a. Make sure the data types and lengths are the same for both keys. Please see previous post. Secondly, make sure that your key cache is large enough so that the keys to the tables are buffered. I'm not sure it is this. If these assumptions are not correct, then you will need to post the query, the explain of the query, and the show create table for each table in the query so that we have a better idea what you are dealing with. Please see previous post. I've looked in several books and searched Google but cannot get a way of doing this. It seems Oracle has a 'FIRST' in their select which they use for such a use case. But I do not see anything for MySql anywhere. Oracle's FIRST_ROWS simply tells the optimizer to prefer index scans over a FTS where lots of rows may be returned by the query. It won't modify the behavior of a query in the way you want it to. Thank you for info. The only way I can think to implement the behavior that you are looking for is to: 1) get all the records from a that you need 2) execute a second select for each row in a on b with your between condition and a LIMIT clause so that only one row is returned. Until 4.1 when subselects become avaialable, I suspect this is a workaround. Still, if multiple rows exist in b that match the PK on a, those rows will probably be scanned in filesort order, which means that multiple rows will be looked at unless your between matches the first row inserted, except if you have an appropriate index. Please see previous post. Justin, again, thank you for your asistance. Kind regrds Emmanuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stop query on first match
On Monday, June 07, 2004 19:09, Michael Stassen wrote; This doesn't quite make sense. You seem to say that several rows will match but then you say only one will. It must be one or the other. Perhaps I've misunderstood you. Firstly, Micheal thank you for your assistance; I'll elaborate. Michael I'll attempt first without supplying the table definitions and actual sql and etc.. Table 'a' contains a property which does not match any property on table 'b' directly, but matches within a range. Example; table 'a' property 'num' num --- 1000 2000 3000 table 'b' properties 'fromNum' and 'toNum' fromNum toNum row 1. 1 1500 row 2.1501 2000 row 3.2001 4000 select... where 'num' between 'fromNum' and 'toNum'. As seen in this example, using between only one row actually matches, however the query engine would at first see more than one matching row i.e., with 'fromNum' (between is same as num = fromNum), value 2000 from table a would match row 1 2 on table b; and with 'toNum' (between is same as num = toNum), value 2000 from table a would match row 2 3 on table b; only once the range is taken together the result matches one row, viz., row 2 on table b. Now please remember table b has 1.4 million rows as in this example, with fromNum and toNum running consecutively, so the query takes 4 seconds to find a row in table b. With say 200 rows in table a, that means the query runs for a long time. What I did was to use limit 1, and ran query with 1 row, this took .01 second. You also seem to imply that with BETWEEN you get a full table scan even though there is only one match for each row. That sounds like an indexing problem, but it is hard to say without more information. Indexed individually on; fromNum toNum Also experimented by adding combining index on; fromNum/toNum Either way no difference, query runs 4 seconds. It would help us help you if you at least posted the query and the results of EXPLAIN. It would probably also help if you told us more about the tables, perhaps with SHOW CREATE TABLE. If above does not help you, I'll bring the whole lot to the post. Michael, again thank you for you assistance. Kind regards Emmanuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Production release of MySql 4.1
Daniel Kasak wrote on Tuesday, June 08, 2004 00:38; Emmanuel van der Meulen wrote: Hello all, Does anyone have an estimate of when MySql 4.1 will be released for production. MySql.cm says soon. Please advise, would that likely be weeks/months? Kind regards Emmanuel Months. I'd say at least 6 months. Thank you for the note and valuable feedback. Kind regards Emmanuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stop query on first match
On Monday, June 07, 2004 07:54, Jeff Smelser wrote; On Monday 07 June 2004 12:49 am, Emmanuel van der Meulen wrote: I've looked in several books and searched Google but cannot get a way of doing this. It seems Oracle has a 'FIRST' in their select which they use for such a use case. But I do not see anything for MySql anywhere. If your using a later mysql, you could use sub query with limit. Other then that, splitting the query is the only think off hand. Thank you for you note and valuable feedback. Unfortunately I'm planning to go live before MySql 4.1 is production release so I need to find a work around. Do I understand your suggestion about splitting the query; I would first select all required rows from table a, then take each retrieved row from table and (in a loop in my java program) select from table b, using limit? Kind regards Emmanuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Production release of MySql 4.1
Hello all, Does anyone have an estimate of when MySql 4.1 will be released for production. MySql.cm says soon. Please advise, would that likely be weeks/months? Kind regards Emmanuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stop query on first match
Hello all, I see a similar question was asked before, but it was not answered. I hope someone can assist me. My query uses two tables. The query selects one row on table b for each row on table a, but uses between in the select. It can only ever return one row from table b, for each row on table a, due to the contents that is stored in table b. The table contains in excess of a million records. What happens as a result of the between is that for the query, several rows seem to be candidates on table b, but once the query evaluates and sifts through the candidate rows on table b, only one row will ever match. So if I could inform MySql to stop the query for the particular row, once one row on table b matches the row on table a, the query would return hundreds of times faster. As an experiment I took one example and used limit and the query reduced from 4 secs to .01 sec. However, when doing the 'live' query, I cannot use limit because, I do not want overall only 1 row returned, I want one row returned for each of the rows from table a which has 1 match each on table b. I've looked in several books and searched Google but cannot get a way of doing this. It seems Oracle has a 'FIRST' in their select which they use for such a use case. But I do not see anything for MySql anywhere. Could someone please assist me. Kind regards Emmanuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Manuals status
Hello all, When downloading the pdf manual, I do not get sections; 6.9.1 How The Query Cache Operates 6.3.6.2 Miscellaneous Functions (or there is a 6.3.5.2 Miscellaneous Functions) but its different) Could someone please advise how the manual sections obtained by serching on topics and the downloaded pdf manual relates to one another? Kind reagrds Emmanuel - 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 3.23.46 - 4.01
Hello all, Could someone please advise what is further required to upgrade from 3.23.46 - 4.0.01. Here are the steps I followed; 1. Download mysql-4.0.1-alpha-win.zip 2. unzipped 3. ran set-up installed into d:\mysql-4.0.1\ 4. start mysql-max with a bat file, maxstart and this is what happens; d:\mysql-4.0.1\batmaxstart d:\mysql-4.0.1\batd: d:\mysql-4.0.1\batcd\mysql-4.0.1\bin D:\mysql-4.0.1\binmysqld-max --defaults-file=E:/Docs/DEPLOY/org/funforlife/ web/ funsa/Db/my.cnf 020121 22:51:36 Error message file 'd:\mysql\share\english\errmsg.sys' had only 218 error messages, but it should contain at least 226 error messages. Check that the above file is the right version for this program! 020121 22:51:36 Aborting D:\mysql-4.0.1\bind: D:\mysql-4.0.1\bincd\mysql-4.0.1\bat D:\mysql-4.0.1\bat I was hoping I could keep 3.23.46 on PC until I have 4.0.1 working. Seems I can't. Did I have to first uninstall 3.23.46 before installing 4.0.1? Thank you for any assistance. Kind regards Emmanuel - 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: Understanding throughput with JDBC
Hello Monty, I downloaded and installed 4.0.1 for the SQL_CALC_FOUND_ROW option. However, the query runs 600% longer, so the saving I get with FOUND_ROWS() running in 0.0 secs does not serve me. Am I maybe doing something wrong? Here is what I did; I ran the query without SQL_CALC_FOUND_ROW and it runs in 2.61 secs. mysql select * from memberships,membershipstracking where memberships.email=mem bershipstracking.email order by membershipstracking.activitytimestamp desc limit 1; +--+-+---+-- -+-- --+--+--+---+--- ---+ -+-+---+---+ +--+--+---+---++---+ | EMAIL| NAME| MIDDLEINITIAL | SURNAME | N EWSLETTER | USERNAME | PASSWORD | COUNTRY | EMAIL | ACTIVITYTIMESTAMP | NAME| MIDDLEINITIAL | SURNAME | NEWSLETTER | USERNAME | PASSWORD | COUNTRY | ACTIVITY | BUTTON | REMOTEADDRESS | +--+-+---+-- -+-- --+--+--+---+--- ---+ -+-+---+---+ +--+--+---+---++---+ | [EMAIL PROTECTED] | firstname94 | M | surname94 | Y | v94 | v94 | country94 | [EMAIL PROTECTED] | 2002/01/03 22:54:11:768 | firstname94 | M | surname94 | Y | v94 | v94 | country94 | voltest94 | submit | 127.0.0.1 | +--+-+---+-- -+-- --+--+--+---+--- ---+ -+-+---+---+ +--+--+---+---++---+ 1 row in set (2.61 sec) mysql Then I add SQL_CALC_FOUND_ROW, and the exact same query runs for 16.95 secs. mysql select sql_calc_found_rows * from memberships,membershipstracking where m emberships.email=membershipstracking.email order by membershipstracking.activity timestamp desc limit 1; +--+-+---+-- -+-- --+--+--+---+--- ---+ -+-+---+---+ +--+--+---+---++---+ | EMAIL| NAME| MIDDLEINITIAL | SURNAME | N EWSLETTER | USERNAME | PASSWORD | COUNTRY | EMAIL | ACTIVITYTIMESTAMP | NAME| MIDDLEINITIAL | SURNAME | NEWSLETTER | USERNAME | PASSWORD | COUNTRY | ACTIVITY | BUTTON | REMOTEADDRESS | +--+-+---+-- -+-- --+--+--+---+--- ---+ -+-+---+---+ +--+--+---+---++---+ | [EMAIL PROTECTED] | firstname94 | M | surname94 | Y | v94 | v94 | country94 | [EMAIL PROTECTED] | 2002/01/03 22:54:11:768 | firstname94 | M | surname94 | Y | v94 | v94 | country94 | voltest94 | submit | 127.0.0.1 | +--+-+---+-- -+-- --+--+--+---+--- ---+ -+-+---+---+ +--+--+---+---++---+ 1 row in set (16.95 sec) mysql Please advise? Kind regards Emmanuel -Original Message- From: Michael Widenius [mailto:[EMAIL PROTECTED]] Sent: 21 January 2002 00:44 To: Emmanuel van der Meulen Cc: MySQL General List; MySQL Java List Subject: RE: Understanding throughput with JDBC Hi! Emmanuel == Emmanuel van der Meulen [EMAIL PROTECTED] writes: Emmanuel Hello Mark, Emmanuel Thank you for the note and feedback. BTW, it was not over a network. Both Emmanuel on local PC. So all the time went into building the resultset in memory. Emmanuel I'm surprised at the time that takes. Emmanuel Further to why I'm desirous to do this query, twofold. One I get the record Emmanuel count (rows in the table); since realised there are other ways to get he Emmanuel number of rows. For the second requirement I cannot see an alternative. Emmanuel Not on this table, but on another where I keep a timestamp, I'd like to get Emmanuel the 50 most recent inserts. So what I do is; (SELECT * FROM Emmanuel MEMBERSHIPSTRACKING ORDER BY ACTIVITYTIMESTAMP DESC) thus get all the rows, Emmanuel ordered desc on timestamp and then I have
RE: MySQL 3.23.46 - 4.01
Hello all, Oops, I overlooked to change the my.cnf. All is working 100% Thank you. However, please advise whether I can run 3.23 and 4.0.1 interchanging on same data without putting the data at risk? BTW, I use InnoDb. Kind regards Emmanuel -Original Message- From: Emmanuel van der Meulen [mailto:[EMAIL PROTECTED]] Sent: 21 January 2002 22:57 To: MySQL General List Subject: MySQL 3.23.46 - 4.01 Hello all, Could someone please advise what is further required to upgrade from 3.23.46 - 4.0.01. Here are the steps I followed; 1. Download mysql-4.0.1-alpha-win.zip 2. unzipped 3. ran set-up installed into d:\mysql-4.0.1\ 4. start mysql-max with a bat file, maxstart and this is what happens; d:\mysql-4.0.1\batmaxstart d:\mysql-4.0.1\batd: d:\mysql-4.0.1\batcd\mysql-4.0.1\bin D:\mysql-4.0.1\binmysqld-max --defaults-file=E:/Docs/DEPLOY/org/funforlife/web/ funsa/Db/my.cnf 020121 22:51:36 Error message file 'd:\mysql\share\english\errmsg.sys' had only 218 error messages, but it should contain at least 226 error messages. Check that the above file is the right version for this program! 020121 22:51:36 Aborting D:\mysql-4.0.1\bind: D:\mysql-4.0.1\bincd\mysql-4.0.1\bat D:\mysql-4.0.1\bat I was hoping I could keep 3.23.46 on PC until I have 4.0.1 working. Seems I can't. Did I have to first uninstall 3.23.46 before installing 4.0.1? Thank you for any assistance. Kind regards Emmanuel - 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: Version 4 Schedule RE: Roadmap
Hello Jeremy, Thank you for keeping correspondence. Kind regards Emmanuel -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: 20 January 2002 08:22 To: Emmanuel van der Meulen Cc: [EMAIL PROTECTED] Subject: Re: Version 4 Schedule RE: Roadmap On Sat, Jan 19, 2002 at 09:51:39AM +0200, Emmanuel van der Meulen wrote: Hello all, On 19 January 2002 09:28, Jeremy Zawodny wrote; On Sat, Jan 19, 2002 at 09:22:38AM +0200, Emmanuel van der Meulen wrote: Hello all, To both above topics there have been answers, thank you, but these answers are not clear at all. That's because preicting the future is rather difficult. It's a fuzzy system, so there are few clear answers. Jeremy Then let me phrase my question differently; Version 4.1 is earmarked to have 'stored procedures'. So I'm keen to know when that version is planned for release. Some indication based on previous timings would suffice, and I understand this to be a difficult question, thus in the spirit of all of above, I would not hold such timing indication against anyone if it turns out before or later. Would MySQL consider to publish something like the following as found at Netbeans; http://www.netbeans.org/articles/roadmap.html and even though their roadmap is not specific, some indication of timing is derive-able? Hey, that's pretty cool. There's a more low-tech version for MySQL here, I guess: http://www.mysql.com/doc/T/O/TODO.html It's the same idea. If MySQL policy is to refrain from getting into timing questimates, it is 100% understandable and acceptable, then please merely state it as such. Well, I can say that they've given dates in the past, but Murpy's Law tends to get in the way. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 17 days, processed 406,765,826 queries (272/sec. avg) - 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: Understanding throughput with JDBC
Hello Mark, Thank you for the note and feedback. BTW, it was not over a network. Both on local PC. So all the time went into building the resultset in memory. I'm surprised at the time that takes. Further to why I'm desirous to do this query, twofold. One I get the record count (rows in the table); since realised there are other ways to get he number of rows. For the second requirement I cannot see an alternative. Not on this table, but on another where I keep a timestamp, I'd like to get the 50 most recent inserts. So what I do is; (SELECT * FROM MEMBERSHIPSTRACKING ORDER BY ACTIVITYTIMESTAMP DESC) thus get all the rows, ordered desc on timestamp and then I have the most recent 50 inserts. If anyone could assist me with this, I'd use other means, rather than retrieving the full resultset. Kind regards Emmanuel -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED]] Sent: 19 January 2002 16:25 To: Emmanuel van der Meulen Cc: [EMAIL PROTECTED] Subject: Re: Understanding throughput with JDBC - Original Message - From: Emmanuel van der Meulen [EMAIL PROTECTED] To: MySQL Java List [EMAIL PROTECTED]; MySQL General List [EMAIL PROTECTED] Sent: Saturday, January 19, 2002 7:43 AM Subject: Understanding throughput with JDBC Hello all, Could anyone please assist me to understand this. I want to understand where the time is going to and whether there is something I can do about it, with the following query when using JDBC; Here is the table declaration; CREATE TABLE Memberships (EMAIL CHAR(60) NOT NULL, NAME CHAR(50) NOT NULL, MIDDLEINITIAL CHAR(1) NULL, SURNAME CHAR(50) NOT NULL, NEWSLETTER CHAR(1) NOT NULL, USERNAME CHAR(15) NOT NULL, PASSWORD CHAR(15) NOT NULL, COUNTRY CHAR(50) NOT NULL, PRIMARY KEY (EMAIL)) TYPE=InnoDB MySQL table memberships has 110080 rows. Here is the select statement I use with JDBC; SELECT * FROM memberships ORDER BY EMAIL Same select statement I use from the MySQL 'commandline'; mysql select * into outfile 'mysql20.txt' from memberships order by email limit 20; My observations; A. When running the SQL from the MySQL 'commandline' with above statement this is the response; mysql select * into outfile 'mysql20.txt' from memberships order by email l imit 20; Query OK, 110080 rows affected (1.27 sec) B. When running the exact same select as stated above in a java program using JDBC, from when issuing, ResultSet rs = stmt.executeQuery(query) until the java program gets control back, to create the resultset, takes 23-25 seconds; I ran program several times. Please advise whether this is to be expected, or whether I could alter anything to get comparable results via JDBC as with MySQL 'commandline'? No network traffic in the first case, always network traffic in the second. No memory allocation in the first case, needing to allocate space for the entire result set in the second (the MySQL protocol doesn't mesh really well with the JDBC spec's requirements at this point in time, so the JDBC driver is required to read the entire result set in to memory to allow things like thread safety and multiple concurrent queries without being overly complex). With JDBC, it is usually not a good idea (with any driver) to select a whole slew of rows, because you won't get the performance you desire. In most cases it isn't necessary, and isn't the most efficient way of doing things either. When developing client server applications, you should use the database for what it's good for, manipulating, aggregating and transforming large amounts of data, and then look at the results (which will hopefully be smaller). Only in very special cases do you need to return large result sets (like OLAP for example). I guess it comes down to, what exactly are you trying to do selecing 110,000 rows, and do you need to return the whole result set at once...If you're just trying to dump it to a file, why are you doing that with Java when MySQL can do it directly? -Mark - 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: Understanding throughput with JDBC
Hello Mark, Thank you for the note and feedback. BTW, it was not over a network. Both on local PC. So all the time went into building the resultset in memory. I'm surprised at the time that takes. Further to why I'm desirous to do this query, twofold. One I get the record count (rows in the table); since realised there are other ways to get he number of rows. For the second requirement I cannot see an alternative. Not on this table, but on another where I keep a timestamp, I'd like to get the 50 most recent inserts. So what I do is; (SELECT * FROM MEMBERSHIPSTRACKING ORDER BY ACTIVITYTIMESTAMP DESC) thus get all the rows, ordered desc on timestamp and then I have the most recent 50 inserts. If anyone could assist me with this, I'd use other means, rather than retrieving the full resultset. Kind regards Emmanuel -Original Message- From: Mark Robson [mailto:[EMAIL PROTECTED]] Sent: 19 January 2002 16:49 To: MySQL Java List Subject: Re: Understanding throughput with JDBC B. When running the exact same select as stated above in a java program using JDBC, from when issuing, ResultSet rs = stmt.executeQuery(query) until the java program gets control back, to create the resultset, takes 23-25 seconds; I ran program several times. 23-25 seconds sounds like quite a reasonable time for selecting 110,000 records - remember that they need to be transferred over the network and allocated in local memory. Mark - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - 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: [OT] Re: Understanding throughput with JDBC
Hello Nick,
Does ROWNUM exist in MySQL?
Kind reagrds
Emmanuel
-Original Message-
From: Nick [mailto:[EMAIL PROTECTED]]
Sent: 19 January 2002 20:41
To: Shankar Unni
Cc: [EMAIL PROTECTED]
Subject: [OT] Re: Understanding throughput with JDBC
With Oracle, the *only* solution is to issue a query for the whole
resultset and scroll through it - it doesn't support anything
*like* the
LIMIT concept, though I suppose you *could* imitate it with
WHERE ROWNUM
blah.
With Oracle, you can retrieve a part of a result set by using inline
views:
SQL select d.c1,d.c2,d.c3
2 from (select c1,c2,c3,rownum c4 from foo order by c3) d
3 where d.c4 =15 and d.c4 = 19
4 /
C1 C2 C3
115 30 15
116 32 16
117 34 17
118 36 18
119 38 19
Not as elegant as I would like, but it works.
--
Nick
Email: [EMAIL PROTECTED]
-
Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before
posting. To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail [EMAIL PROTECTED] instead.
-
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: Understanding throughput with JDBC
Hello Dave, Thank you. This works. Kind regards Emmanuel -Original Message- From: Adrian Monea [mailto:[EMAIL PROTECTED]] Sent: 20 January 2002 15:08 To: 'Emmanuel van der Meulen' Cc: [EMAIL PROTECTED] Subject: RE: Understanding throughput with JDBC Why not try to use SELECT * FROM MEMBERSHIPSTRACKING ORDER BY ACTIVITYTIMESTAMP DESC LIMIT 0,50 It will have the same effect, and only the 50 results you need will be built into memory. Regards, Adrian. -Original Message- From: Emmanuel van der Meulen [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 20, 2002 1:31 PM To: MySQL General List; MySQL Java List Subject: RE: Understanding throughput with JDBC Hello Mark, Thank you for the note and feedback. BTW, it was not over a network. Both on local PC. So all the time went into building the resultset in memory. I'm surprised at the time that takes. Further to why I'm desirous to do this query, twofold. One I get the record count (rows in the table); since realised there are other ways to get he number of rows. For the second requirement I cannot see an alternative. Not on this table, but on another where I keep a timestamp, I'd like to get the 50 most recent inserts. So what I do is; (SELECT * FROM MEMBERSHIPSTRACKING ORDER BY ACTIVITYTIMESTAMP DESC) thus get all the rows, ordered desc on timestamp and then I have the most recent 50 inserts. If anyone could assist me with this, I'd use other means, rather than retrieving the full resultset. - 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: Understanding throughput with JDBC
Hello Monty, Thank you for your note and feedback. After getting excellent assistance, I'm using JDBC setMaxRows rather than LIMIT, which gives me portability. I'll investigate SQL_CALC_FOUND_ROW option further. I notice I would need to first upgrade to MySQL 4.x, though and then find / work out how to use it with JDBC. It would be great to do only one query, which means the processing would be reduced from approx. 25 to 4 seconds. I'd say, that is a great reduction. Kind regards Emmanuel -Original Message- From: Michael Widenius [mailto:[EMAIL PROTECTED]] Sent: 21 January 2002 00:44 To: Emmanuel van der Meulen Cc: MySQL General List; MySQL Java List Subject: RE: Understanding throughput with JDBC Hi! Emmanuel == Emmanuel van der Meulen [EMAIL PROTECTED] writes: Emmanuel Hello Mark, Emmanuel Thank you for the note and feedback. BTW, it was not over a network. Both Emmanuel on local PC. So all the time went into building the resultset in memory. Emmanuel I'm surprised at the time that takes. Emmanuel Further to why I'm desirous to do this query, twofold. One I get the record Emmanuel count (rows in the table); since realised there are other ways to get he Emmanuel number of rows. For the second requirement I cannot see an alternative. Emmanuel Not on this table, but on another where I keep a timestamp, I'd like to get Emmanuel the 50 most recent inserts. So what I do is; (SELECT * FROM Emmanuel MEMBERSHIPSTRACKING ORDER BY ACTIVITYTIMESTAMP DESC) thus get all the rows, Emmanuel ordered desc on timestamp and then I have the most recent 50 inserts. If Emmanuel anyone could assist me with this, I'd use other means, rather than Emmanuel retrieving the full resultset. Why can't you use 'LIMIT 50' to get the latest 50 rows ? (As Mark asked, why do you have to use LIMIT 1) You can also use SQL_CALC_FOUND_ROW option to avoid to do two queries, if you want to know how many rows the WHERE statement actually matched (see the MySQL manual for more information about this topic). Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.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
Overwhelmed with MySQL People
Hello all, I've been with MySQL for approx. a month now and have asked assistance with several things, and; I'm overwhelmed and acknowledge MySQl and the people of the mailing lists. Thank you. Kind regards Emmanuel - 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
Understanding throughput with JDBC
Hello all, Could anyone please assist me to understand this. I want to understand where the time is going to and whether there is something I can do about it, with the following query when using JDBC; Here is the table declaration; CREATE TABLE Memberships (EMAIL CHAR(60) NOT NULL, NAME CHAR(50) NOT NULL, MIDDLEINITIAL CHAR(1) NULL, SURNAME CHAR(50) NOT NULL, NEWSLETTER CHAR(1) NOT NULL, USERNAME CHAR(15) NOT NULL, PASSWORD CHAR(15) NOT NULL, COUNTRY CHAR(50) NOT NULL, PRIMARY KEY (EMAIL)) TYPE=InnoDB MySQL table memberships has 110080 rows. Here is the select statement I use with JDBC; SELECT * FROM memberships ORDER BY EMAIL Same select statement I use from the MySQL 'commandline'; mysql select * into outfile 'mysql20.txt' from memberships order by email limit 20; My observations; A. When running the SQL from the MySQL 'commandline' with above statement this is the response; mysql select * into outfile 'mysql20.txt' from memberships order by email l imit 20; Query OK, 110080 rows affected (1.27 sec) B. When running the exact same select as stated above in a java program using JDBC, from when issuing, ResultSet rs = stmt.executeQuery(query) until the java program gets control back, to create the resultset, takes 23-25 seconds; I ran program several times. Please advise whether this is to be expected, or whether I could alter anything to get comparable results via JDBC as with MySQL 'commandline'? Kind regards Emmanuel - 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: Version 4 Schedule RE: Roadmap
Hello all, On 19 January 2002 09:28, Jeremy Zawodny wrote; On Sat, Jan 19, 2002 at 09:22:38AM +0200, Emmanuel van der Meulen wrote: Hello all, To both above topics there have been answers, thank you, but these answers are not clear at all. That's because preicting the future is rather difficult. It's a fuzzy system, so there are few clear answers. Jeremy Then let me phrase my question differently; Version 4.1 is earmarked to have 'stored procedures'. So I'm keen to know when that version is planned for release. Some indication based on previous timings would suffice, and I understand this to be a difficult question, thus in the spirit of all of above, I would not hold such timing indication against anyone if it turns out before or later. Would MySQL consider to publish something like the following as found at Netbeans; http://www.netbeans.org/articles/roadmap.html and even though their roadmap is not specific, some indication of timing is derive-able? If MySQL policy is to refrain from getting into timing questimates, it is 100% understandable and acceptable, then please merely state it as such. Kind regards Emmanuel - 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
Roadmap
Hello all, Is there a roadmap for MySQL releases, and in particular when it the eta for 4.1? Kind regards Emmanuel - 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: Jeremy's MySQL Book
Hello all, Please advise location of the toc. Kind reagrds Emmanuel -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: 10 January 2002 16:59 To: Rick Emery Cc: '[EMAIL PROTECTED]' Subject: Re: Jeremy's MySQL Book On Thu, Jan 10, 2002 at 08:32:03AM -0600, Rick Emery wrote: Jeremy, Late last year, you were creating the Table of Contents for a book about MySQL...how's it coming? I know that many on this list were anxious to get a sneak peak, and perhaps suggest additional topics, such as advantages and conditions for using INNODB and BDB tables versus MYISAM. And how to install, configure and use MySQL ODBC drivers. Perhaps even how to access MS Access databases through MySQL. Hi Rick, I'll more information about the book on the web site I'm setting up for it (advanced-mysql.com). The domain is being registered today, so it'll be up over the weekend. In the meantime, here's the last TOC outline I had put together. Comments are welcome, of course. ---snip--- Title: Advanced MySQL (tentative) Chapter 0: Introduction Goals of this book * explain how mysql works under the hood * understand why it is fast what can slow it down (at all levels) * discuss real-world problems and solutions * expose good practices for use in large or demanding environments * discuss MySQL's weaknesses and how to work around them What you need to know (and why) * basic sql * mysql installation and administration * a scripting language (such as perl or python) What you won't read about (and why) * programming languages * the mysql api * extending or embedding mysql * clustering * commercial load-balancing solutions (there's a book on that) Chapter Overview Chapter 1: Configuration Basics (see Questions below) my.cnf, MySQL's Configuration File MySQL's Startup Options SHOW VARIABLES and SHOW STATUS Chapter 2: MySQL's Table Types (or Handlers?) MySQL's Modular Architecture * core services - sql parser - functions - logging * table handlers - data access - functionality differences * design benefits Locking and Concurrency * table vs. page vs. block vs. row (granularity) * read vs. write (exclusivity) * multi-versioning Transactional vs Non-Transactional * acid features - atomicity - consistency - isolation - durability * tradeoffs - concurrency - performance * simulating transactions with table locks (maybe a sidebar?) * isolation levels The Table Handlers * MyISAM (compressed MyISAM, and MERGE too) * HEAP * BDB (Berkeley DB) * InnoDB * Gemini * (possibly include a table which summarizes the features of all?) Selecting the Right Table Type Chapter 3: Indexes What are Indexes? * unique / non-unique indexes * unique non-indexes (in 4.x according to monty) * primary keys * partial indexes * compound (multi-part) indexes * packed keys How MySQL Stores and Maintains Indexes * storage requirements * common prefixes and space compression * where indexes are stored (for each table type) * hash indexes on heap tables * the myisam key buffer and innodb/gemini buffer pools * performance impact: updates vs. reads - discuss delayed key writes for myisam When Does MySQL Use an Index? * the most specific index * exact matches * ranges * the 40% rule * left-most prefixes * finding unused indexes * order by and group by queries * distinct queries * queries using only indexed columns When MySQL Never Uses and Index * regular expressions * wildcard prefix matches Understanding and Maintaining Your Indexes * Index File Sizes * SHOW KEYS * Cardinality * ANALYZE TABLE * Index Performance Counters * Index Buffers Full-text Indexing Chapter 4: Making Queries Fast How MySQL Processes Queries * query cache (new in 4.0 tree) * parsing * optimization planning * execution Identifying Slow Queries * the slow query log * using mysqldumpslow * using explain_log (new in 4.0) Using and Understanding EXPLAIN * single table select with index * single table select with no index * multi-way joins Influencing MySQL's Choices * straight joins * index hints in queries * analyze
Re: Multiples instances of MySQL
Hello all, I'm in a spot, I'm managing to successfully run several instances of MySQL, say on port=3306 and port=3308. When I use shutdown, the instance which started first shuts down. To shutdown the instance started on port 3306, I use; mysqladmin -P 3306 -u root -p2000 shutdown And to shutdown the instance started on port 3308, I use; mysqladmin -P 3308 -u root -p2000 shutdown However, the instance which started first is always the instance which shuts down, irrespective of the port number I provide. Could someone please advise and/or point me. Thank you provisionally for any assistance. Kind regards Emmanuel - 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: Multiples instances of MySQL
Hello Sammy Martin, Thank you for your notes and assistance. ** Martin; Try using the -S option to select the instance via the Unix domain socket rather than the port number (assuming this is a Unix installation). ** Sammy; use --socket instead of -P would solve your problem. without -h, socket file is used for connection. since you've not specified the socket file, the default socket file path is used (in some case, /tmp/mysql.sock). that's why you're always shutting down the first one. As suggested, I used -S instead of -P, but still the first instance shuts down. I'm running on Win2K Pro. Is there a different way for Windows? Please advise any further pointers? Kind regards and all the best for 2002! Emmanuel - 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: Multiples instances of MySQL
Hello Martin, [snip] Ok. Try to explicitly set the host on the command line: eg: mysqladmin -h 127.0.0.1 -P 2000 . Note that you should use the ip address of your host or its network name. Using 'localhost' won't do what you want. I include commands of two instances which I cannot shutdown correctly, the first instance running shuts down first no matter which shutdown command is activited. --- Instance1 - startup; mysqld-max --standalone --console -h 127.0.0.1 --port=3306 --socket=e:/docs/p3306.sock --pid-file=E:/Docs/DEPLOY/ org/funforlife/web/funsa/Db/p3306.pid --datadir=E:/Docs/DEPLOY/org/funforlif e/web/funsa/Db --basedir=d:/mysql --innodb_data_file_path=ibdata1:50M;ibdata 2:50M;ibdata3:50M;ibdata4:50M;ibdata5:50M --innodb_data_home_dir=E:/Docs/DEP LOY/org/funforlife/web/funsa/Db/innodb/ibdata --set-variable=innodb_mirrored _log_groups=1 --innodb_log_group_home_dir=E:/Docs/DEPLOY/org/funforlife/web/ funsa/Db/innodb/iblogs --set-variable=innodb_log_files_in_group=3 --set-vari able=innodb_log_file_size=30M --set-variable=innodb_log_buffer_size=30M --in nodb_flush_log_at_trx_commit=1 --innodb_log_arch_dir=E:/Docs/DEPLOY/org/funf orlife/web/funsa/Db/innodb/iblogs --innodb_log_archive=0 --set-variable=inno db_buffer_pool_size=70M --set-variable=innodb_additional_mem_pool_size=50M - -set-variable=innodb_file_io_threads=8 --set-variable=innodb_lock_wait_timeo ut=50 Instance1 - shutdown; mysqladmin -P 3306 -h 127.0.0.1 --socket=e:/docs/p3306.sock shutdown --- Instance2 - startup; mysqld-max --standalone --console -h 127.0.0.1 --port=3308 --socket=e:/docs/p3308.sock --pid-file=E:/Docs/DEPLOY/ com/lifecanbedifferent/web/mysql/Db/p3308.pid --datadir=E:/Docs/DEPLOY/com/l ifecanbedifferent/web/mysql/Db --basedir=d:/mysql --innodb_data_file_path=ib data1:20M --innodb_data_home_dir=E:/Docs/DEPLOY/com/lifecanbedifferent/web/m ysql/Db/innodb/ibdata --set-variable=innodb_mirrored_log_groups=1 --innodb_l og_group_home_dir=E:/Docs/DEPLOY/com/lifecanbedifferent/web/mysql/Db/innodb/ iblogs --set-variable=innodb_log_files_in_group=3 --set-variable=innodb_log_ file_size=20M --set-variable=innodb_log_buffer_size=20M --innodb_flush_log_a t_trx_commit=1 --innodb_log_arch_dir=E:/Docs/DEPLOY/com/lifecanbedifferent/w eb/mysql/Db/innodb/iblogs --innodb_log_archive=0 --set-variable=innodb_buffe r_pool_size=20M --set-variable=innodb_additional_mem_pool_size=10M --set-var iable=innodb_file_io_threads=8 --set-variable=innodb_lock_wait_timeout=50 Instance2 - shutdown; mysqladmin -P 3308 -h 127.0.0.1 --socket=e:/docs/p3308.sock shutdown --- Can you maybe see something causing the shutdown anomaly? Kind regards Emmanuel - 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
Multiples instances of MySQL
Hello all, Could someone please advise, what is the procedure to start and run more than one instance of MySQL? Kind regards Emmanuel - 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: General database questions
Hello Roger, [snip] Roger, I attempted adding additional space with a new file under innodb_data_file_path as follows; Before: innodb_data_file_path = ibdata1:50M;ibdata2:50M After : innodb_data_file_path = ibdata1:50M;ibdata2:50M;ibdata3:50M Stopped and restarted MySQL, but this did nothing. Could you please point me! I have no idea, try URL: http://www.mysql.com/doc/I/n/InnoDB.html Maybe someone on the list who have actually used the inndb tables could give you a hint...? I got this to work, I suspect a typo resulted that it did not work the first time. It was a matter of adding the extra ibdata file (as above), stop/start, and viola, space was added. [snip-1] This is also my understanding. It scares me. Thus when using MySQL with InnoDB, all data of all databases on my different website stages, viz., PROD, QA, DEV would share the same InnoDB dataspace for data. yes, but this is not so bad, is it...? [snip-2] Thus PROD data is at risk. why? [snip-3] And furthermore, data cannot be backed up separately. Yes, it can, the mysqldump utility will address the _database_, but you can not do backups of individual databases by simply copying the files on the OS level, like you can with myisam tables. [snip-4] What exactly do you see as a risk? Well, i.r.o. the matter of risk, if different websites's data is in one set of files, and especially with DEV, where programs which still have bugs or with finger trouble, could damage the data in general for PROD system; also they would interfere with PROD throughput etc. I have an extremely good backup program which I would stick to; now it'll be fine with different databases each at a separate location. At some point, I'll also look at mysqldump, thank you. NB. In the meantime, I also posted this issue to InnoDB's Heikki Tuuri, who confirmed my concerns - but only if used in one MySQL server instance; he advised the actual way to handle this, is to run separate MySQL server instances. I'm busy setting this up. With different instances, each instance could have its dedicated datadir etc. I use Win2k Pro; struggling with multiple instances; MySQL does not see the /mysql/data/my.cnf - for some reason it only sees c:/winnt/my.ini and c:/my.cnf. I started another thread, 'Multiples instances of MySQL' asking for assistance. BTW, Heikki's response; [snip] It is better to run a different instance altogether of mysqld for production and development systems. Development will inevitably cause disturbance to a production system. The same with co-hosting: high load on one database will make others freeze. [snip-1] It is not a very big difference between one 'tablespace' (or multiple, possibly spanning multiple disks) sharing multiple databases, and one (or more) disks with separate databases in separate files. The database partition can be seen as a kind of filesystem... [snip-2] It is an issue, but I don't think it is a big issue. Sorted with finesse with multiple instances. Anyway, Roger thank you for all your assistance. And BTW, the more I work with MySQL, the more I'm enjoying it. Kind regards Emmanuel - 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: Multiples instances of MySQL
Hello Tony, Thank you for your note and assistance. However, I'm stuck on a particular point; I use Win2k Pro; struggling with 3rd option below, 'server-specific options', for multiple instances; MySQL does not see the \mysql\data\my.cnf options file, for some reason it only sees c:\winnt\my.ini and c:\my.cnf. Got this from the manual - 4.1.2; Filename Purpose windows-system-directory\my.ini Global options C:\my.cnfGlobal options C:\mysql\data\my.cnf Server-specic options MySQL tries to read option files in the order listed above. If multiple option files exist, an option specified in a file read later takes precedence over the same option specified in a file read earlier. Options specified on the command line take precedence over options specified in any option file. Some options can be specified using environment variables. Options specified on the command line or in option files take precedence over environment variable values. I cannot see where I'm going wrong; is this possibly a version 4.0.1 option. I'm using, 3.23.46-max. I also looked at 4.7.3, cannot figure out where and how to use mysqld# outside the my.cnf options file. Tony, could you please point me? Kind regards Emmanuel - 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: General database questions
Hello Heikki, Thank you very much for this note and your valued assistance. I have the different instances of MySQL running with your proposal to add the lot to a bat file. And yes, if such a option as you propose exists, others would possibly get multiple instances / my.cnf files working more easily. BTW, I wasn't aware that I could place the InnoDB options as options on the command line. They are not mentioned as command line options in chapter 4.1.1. So it was extremely helpful that you gave me that pointer. Either way thank you again for an excellent feature and also for your clear assistance. Kind regards Emmanuel - 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: General database questions
Hello Roger, [snip] Yes, but is there really a big difference as long as the data is on the same disk computer? I would keep PROD on a separate physical machine... Yes, either on a separate physical machine or a different HDD (to curtail costs, I'd first go for HDD). If the databases are in the same physical files or not will only matter if the inndb format itself is insecure, as far as I can see... and if you actually could destroy one table by doing some illegal operation to another table with innodb, I would simply not use it in PROD... :) I'm certain InnoDB is 100% safe. My concern is probably related to being unfamiliar with MySQL/InnoDB. However, I take Heikki's point of the access impact of DEV on PROD - never thought of that. BTW, Heikki also assisted me to get multiple instances of MySQL going. So all is exactly as per my requirements. Until another time. Kind regards Emmanuel - 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: Multiples instances of MySQL
Hello Heikki, Note, this is a duplicate posting - erroneously posted against 'General database questions'. Thank you very much for this note and your valued assistance. I have the different instances of MySQL running with your proposal to add the lot to a bat file. And yes, if such a option as you propose exists, others would possibly get multiple instances / my.cnf files working more easily. BTW, I wasn't aware that I could place the InnoDB options as options on the command line. They are not mentioned as command line options in chapter 4.1.1. So it was extremely helpful that you gave me that pointer. Either way thank you again for an excellent feature and also for your clear assistance. Kind regards Emmanuel - 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: General database questions
Hello Roger; [snip] * Emmanuel van der Meulen I'm certain InnoDB is 100% safe. My concern is probably related to being unfamiliar with MySQL/InnoDB. However, I take Heikki's point of the access impact of DEV on PROD - never thought of that. This impact also very much applies to the cpu... I would highly recomend spending a few $ on a DEV server... doesn't need to be expensive, any old box capable of running linux and mysql will do. Point taken, also got me thinking, thank you. Kind regards Emmanuel - 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
Several general questions
Hello all, I have several questions in general since I recently started using MySQL. From the manual I cannot find where these questions are covered. I hope someone can assist me. Firstly though, if this is not the correct mailing list for my questions, please advise correct one. Secondly, thank you in advance for any assistance offered. Lastly; Background; I'm developing a website to be hosted when ready. The HSP I'm planning to use, provides MySQL facilities. I chose MySQL because I can develop said website and become familiar with it, then make use of the exact same technology once ready and live. Questions; 1. Assuming the website grows very large with high volumes and a database exceeds disk space, please advise what is the remedy? 2. Seeing as for the said website I use transactions, thus also the innodb options, please advise whether I can place individual databases at different locations. Note; it seems the filename.sym, does not apply to the innodb files, therefore only the database table definitions are placed in the filename.sym redirecting location. 3. When using innodb options (for transactions), please advise whether data is stored in the tables or the innodb files? 4. Please advise, with different databases do they all share one set of innodb files - thus if this is the case, how is data for different databases backed up separately? Over to you. Kind regards Emmanuel - 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
General database questions
Hello all, I have several questions in general since I recently started using MySQL. From the manual I cannot find where these questions are covered. I hope someone can assist me. Firstly though, if this is not the correct mailing list for my questions, please advise correct one. Secondly, thank you in advance for any assistance offered. Lastly; Background; I'm developing a website to be hosted when ready. The HSP I'm planning to use, provides MySQL facilities. I chose MySQL because I can develop said website and become familiar with it, then make use of the exact same technology once ready and live. Questions; 1. Assuming the website grows very large with high volumes and a database exceeds disk space, please advise what is the remedy? 2. Seeing as for the said website I use transactions, thus also the innodb options, please advise whether I can place individual databases at different locations. Note; it seems the filename.sym, does not apply to the innodb files, therefore only the database table definitions are placed in the filename.sym redirecting location. 3. When using innodb options (for transactions), please advise whether data is stored in the tables or the innodb files? 4. Please advise, with different databases do they all share one set of innodb files - thus if this is the case, how is data for different databases backed up separately? Over to you. Kind regards Emmanuel - 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: General database questions
Hello Roger, Thank you for your note and replies. Please see my further points inline; On 29 December 2001 16:21, Roger Baklund wrote; * Emmanuel van der Meulen 1. Assuming the website grows very large with high volumes and a database exceeds disk space, please advise what is the remedy? Add HW or remove data...? Not sure if I understand your question... Roger, assuming the database space as allocated is used up and I do not want to remove the data, but I have lots more space to allocate, is there a way to extend allocated space in flight? Or, does MySQL automatically (and inflight) extend beyond allocated space upto as much space which is physically available; I'm referring to the space for the ibdata files. 2. Seeing as for the said website I use transactions, thus also the innodb options, please advise whether I can place individual databases at different locations. Yes, you can. Thank you, please see next point. Note; it seems the filename.sym, does not apply to the innodb files, therefore only the database table definitions are placed in the filename.sym redirecting location. I don't understand this, sorry. Roger, I managed to place different databases each at alltogether different locations using the filename.sym option. However, no matter how much data I loaded, the table sizes stay at 9kb. This lead me to assume that when using the innodb option (for transactions), that the data is loaded in the ibdata files. If this is the case then how do I place the ibdata files (related to the innodb option) at different locations for different databases? 3. When using innodb options (for transactions), please advise whether data is stored in the tables or the innodb files? ehhh... don't know if I understand this... the data is stored in tables (db teminology) and the tables (thus also the data) are stored in files (os terminology). Roger, it seems you are not familiar with the innodb option when using transactions. Note from the MySQL PDF format manual, P. 58. MySQL supports transactions with the InnoDB and BDB Transactional table handlers. See Chapter 7 [Table types], page 441. Or let me rephrase the question; when using the innodb option, ibdata files get used, and my question is; when using the innodb option is the data stored in the table files in the database folder (which do not seem to increase in size no matter how much data I load), or is the data stored in the ibdata files for which I cannot see a way to declare them individually per database. 4. Please advise, with different databases do they all share one set of innodb files - No, each database is stored in separate file folders. Roger again I'm referring to the ibdata files, and if they are stored in a different location, please advise how their location is declared (say each database on a different HDD)? thus if this is the case, how is data for different databases backed up separately? Not a problem. Please advise how I can declare the ibdata files to be at different locations. Thank you again for your assistance. Kind regards Emmanuel - 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: General database questions
Hello Roger, Thank you for your note and feedback. With your assistance and reading up on InnoDB, I'm getting closer. Further inline; On 29 December 2001 22:21, Roger Baklund wrote; [snip-1] I think you would need to restart the mysqld server daemon, but this is very fast, and can be done with virtually no downtime. (1-2 seconds is my experience, I suppose this depends on a number of factors, so you should test it on your system.) [snip-2] I should also tell you that I have never used the Innodb table handler. [snip-3] innodb_data_file_path is used to define database partitions or 'tablespace' and define a size for those partitions. This must be changed when your tables are growing beyond the predefined size, and I suppose a restart is needed, but I don't know for sure. Roger, I attempted adding additional space with a new file under innodb_data_file_path as follows; Before: innodb_data_file_path = ibdata1:50M;ibdata2:50M After : innodb_data_file_path = ibdata1:50M;ibdata2:50M;ibdata3:50M Stopped and restarted MySQL, but this did nothing. Could you please point me! Roger, I managed to place different databases each at altogether different locations using the filename.sym option. However, no matter how much data I loaded, the table sizes stay at 9kb. I don't understand this... how do you know what the size of the table is? As I said, I have never used Inndb, but the tables are stored within the ibdata files, are they not? So, how do you know how many kb? Knowing almost nothing about MySQL InnoDB, I looked at the .frm file, thinking they are the data files, and they have a file size of 9kb. This lead me to assume that when using the innodb option (for transactions), that the data is loaded in the ibdata files. If Yes, all data, including indexes, are stored in the ibdata files. Thank you for clarifying. this is the case then how do I place the ibdata files (related to the innodb option) at different locations for different databases? By setting innodb_data_home_dir to the root path and use a relative path in the innodb_data_file_path parameter, as shown in the example above. This will however not give you different locations for different databases... see below. This is also my understanding. It scares me. Thus when using MySQL with InnoDB, all data of all databases on my different website stages, viz., PROD, QA, DEV would share the same InnoDB dataspace for data. Thus PROD data is at risk. And furthermore, data cannot be backed up separately. I feel this is an oversight of MySQL InnoDB. And let me add further, my concern is that when I eventually go live, with a HSP, that my data would thus be shared further with other websites hosted on the same server. Surely I'm overlooking something. Could this please be logged as a major issue? [snip] That is correct, I have never used Innodb, and I don't use transactions with mysql. This being the case, a further thank you for assisting. [snip] In that case, I would guess the data is not stored there. :) Maybe you are looking at the .frm files? This is the table definitions only. Yes I was. Thank you for clarifying. [snip] ok, I think I understand now... :) Your _database_ is not innodb, your tables are: type=innodb is an option to the CREATE TABLE statement, not the CREATE DATABASE statement. As far as I can tell from the manual, you can not instruct mysql to keep one innodb table in one particular tablespace, in other words: you can _not_ put different databases on different locations, thus my answer to your questions #2 and #4 was wrong. Sorry! (again, I have never used innodb, there may be some way to this that I don't know about.) Again thank you for confirming and clarifying. Roger, thank you for all your assistance so far. I'm clearer on several things. Could you possibly assist me in taking the issue further; that with InnoDB sharing the same dataspace, and therefore different unrelated databases's data being at risk. Kind regards - 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
