Re: secure user name for MySQL account?
On this note, one thing that really bugs me about MySQL passwords is the inability to use special characters. In the SQL Server world, I let users choose their own passwords, but obeying these rules: It cannot be a dictionary word or sequence of words. It must contain at least one numeric digit. It must contain a mix of upper and lower case. It must contain at least one special character. That combination makes a password very difficult to crack. I don't know why MySQL falls so short in this respect. Arthur www.artfulsoftware.com
Re: secure user name for MySQL account?
Thanks for the update, Shawn. I'll check it out right now. On Mon, Sep 24, 2012 at 4:40 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Hello Arthur, On 9/24/2012 4:25 PM, Arthur Fuller wrote: On this note, one thing that really bugs me about MySQL passwords is the inability to use special characters. In the SQL Server world, I let users choose their own passwords, but obeying these rules: It cannot be a dictionary word or sequence of words. It must contain at least one numeric digit. It must contain a mix of upper and lower case. It must contain at least one special character. That combination makes a password very difficult to crack. I don't know why MySQL falls so short in this respect. MySQL continues to improve in this respect. While it's true that our last big security change was the enhanced password hash function introduced in 4.1 we have not been completely insensitive to the needs of our customers. For example, check out the list of account and security improvements arriving in MySQL 5.6 http://dev.mysql.com/doc/**refman/5.6/en/mysql-nutshell.**htmlhttp://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html In particular, the password complexity threshold can be configured using the new Password Validation plugin: http://dev.mysql.com/doc/**refman/5.6/en/validate-**password-plugin.htmlhttp://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html Yours, -- Shawn Green
Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
Tim, I think you misunderstood the question. Daniel wants to block Select queries that ask for all rwows, and permit only queries that ask for some rows, as restricted by the Where clause. Unfortunately, I don't think that can be done. But I'm not certain of that; there might be a trick. Arthur www.artfulsoftware.com On Sun, Sep 23, 2012 at 3:50 PM, Tim Pownall pownall...@gmail.com wrote: select * from table where column=value means it will return only rows that match. as long as you have proper indexing there should not be any issues. On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz luis.daniel.lu...@gmail.com wrote:
Re: Removing Data Duplicacy
I agree with the testicular remedy, but in the case of the iron codpiece, I can think of another approach which may work for you. It still uses Select, but reads a one-row table, so it shouldn't hurt performance much. The table serves no other purpose than storing the next available PK; call the table NextPK, say. The algorithm might go like this: 1. Lock the table NextPK. 2. Select its value. 3. Update the column with current value + 1. 4. Unlock the table. 5. Do your inserts. The lock will be very brief, perhaps brief enough to satisfy your requirement. -- Arthur Cell: 647.710.1314 Only two businesses refer to their clientele as users: drug dealing and software development. -- Arthur Fuller
Re: strange select/join/group by with rollup issue....
I'm not sure your method isn't working, but try changing changing the to date part to '2012-02-08' and see what you get. HTH, Arthur
Re: Editing/form interface
You might want to have a look at our free utility called The_Usual, so named because it's what a regular customer says in a bar or restaurant. It's written in PHP and it does all the usual things such as Add, Edit, Search, and goes a bit further (master-detail forms, etc.) -- all automatically, based on the table definitions and declared foreign keys. See www.artfulsoftware.com. -- Arthur Cell: 647.710.1314 Prediction is difficult, especially of the future. -- Niels Bohr
Re: Parent/Child - Linked List
See the piece on trees at www.artfulsoftware.com. It goes into several variations of how to handle hierarchies. HTH, -- Arthur Cell: 647.710.1314 Prediction is difficult, especially of the future. -- Neils Bohr
Re: MySQL 5.1: Views, queries, updates and performance issues
At first blush, your problem would appear to concern the lack of index-use. That's where I would begin my investigation. It might be painstaking, but I would do something like this: For each view Look at the Join(s) and see what columns are being joined Look at the tables and see what columns are being indexed If any columns are not indexed that should be, create said index(es) Next view Of course, this process could be optimized by looking at the views in terms of their frequency of use. Finally, you didn't mention what sort of front end you're using. It's possible that you might benefit by using stored procedures rather than views. That switch would of course cost you some time invested in changing the front end to pass explicit parameters. Hope this helps, Arthur On Thu, Dec 29, 2011 at 12:50 PM, Bruce Ferrell bferr...@baywinds.orgwrote: Hi all, I've got some semi-general questions on the topics in the title. What I'm looking for is more in the line of theory than query specifics. I am but a poor peasant boy. What I have is an application that makes heavy use of views. If I understand views correctly (and I may not), views are representations of queries themselves. The guy who wrote the app chose to do updates and joins against the views instead of against the underlying tables themselves. I've tuned to meet the gross memory requirements and mysqltuner.pl is saying that 45% of the joins are without indexes. With the slow query logs on and queries_without_indexes, I'm frequently seeing updates that often take more that 2 seconds to complete... Often MUCH longer (how does 157 seconds grab you?). So, with that background, what would you do next and is it possible this use of views, in this way is a significant contributor to the problem? Bruce Ferrell -- Cell: 647.710.1314 Thirty spokes converge on a hub but it's the emptiness that makes a wheel work -- from the Daodejing
Re: SQL DATA
I am not sure that the db engineers should look into supporting recursive optimizations. That sounds to me like a waste of their time, and conversely an investment in your (my) time. This kind of thing is far too app-specific to generalize into an all-encompassing algorithm, IMO, and even if it could be done, I would rather the engineers spend their time on more significant problems. Just my $0.02. Arthur 2011/12/9 Halász Sándor h...@tbbs.net 2011/12/09 20:37 -0500, Singer X.J. Wang When the procedure is executed, each query in the procedure is obviously run through the query optimizer. But the flags are symbolic only for humans. Obviously? As I wrote, someone said that the optimizer does _not_ look into procedures. Symbolic is not right: do you mean meaningful? If meaningful, that is to say that the flags are completely useless. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Cell: 647.710.1314 Thirty spokes converge on a hub but it's the emptiness that makes a wheel work -- from the Daodejing
Re: Query query
You should still lose the pointless WHERE 1. Arthur On Sun, Dec 4, 2011 at 1:38 AM, Jan Steinman j...@bytesmiths.com wrote: DOH! Brain unfroze, and I realized I needed an aggregate: SELECT COUNT(lib.Dewey) AS Have, ddn.Dewey AS DDN, ddn.Classification AS Classification FROM s_library_dewey ddn LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey) WHERE 1 GROUP BY ddn.Dewey
Re: How to get a specific number of entries per one key ?
A quick guess, or at least a starting point: SELECT key, data FROM myTable GROUP BY key LIMIT 2 HTH, Arthur On Sat, Dec 3, 2011 at 11:41 AM, Blog Tieng Viet blogtiengv...@yahoo.comwrote: Hellow everybody. I have a problem difficult for me, please give me an advice. I want to get a specific number of entry (rows) from a table by each Key. If the number is 1, I can use select dinstinct, but the number is not 1, I don't know how to select. For example: Key 0 | data 0-0 Key 0 | data 0-1 Key 0 | data 0-2 Key 1 | data 1-0 Key 1 | data 1-1 I want to select 2 data for each key. Key 0 (data 0-0, data 0-1), Key 1 (data 1-0, data 1-1). How to describe in query ? Best regards.
Re: Index question
The difference is based on the relative frequency of queries where a is important vs. where b is important. Either way, what will happen is that the index scan will isolate the first item mentioned, then scan the result set to isolate the second term. e.g. SELECT * FROM someTable WERE a = someValue and b = someOtherValue Step one isolates the matching a values. Step two walks through that resultset and examines each value of b. Since the values of b are already in memory, all it has to do is examine the index keys to find the matches of b. No additional disk read is required. Arthur
Re: optimising for 100000 entries
Forgive my bluntness, but IMO it is silly to attempt to retrieve a 100,000 rows, except for reporting purposes, and in that case, said reports ought to run against a replica, not the OLTP instance. Far better, IMO, is to present (in the UI) an alphabet as buttons, plus a textbox for refinements. The alphabet buttons cause the recordSource to change to something like SELECT * FROM Clients WHERE ClientName LIKE 'A*'. Click the B button and the RecordSource changes to SELECT * FROM Clients WHERE ClientName LIKE 'B*'. IMO, such an interface gives the user all the power she needs, and costs the system as little as possible. To accomplish this, all you need is a sproc that accepts one parameter, that being the letter corresponding to the letter-button the user pressed. I have implemented exactly this solution on a table with only half the number of rows you cite, but it works beautifully and it is quick as lightning. HTH, Arthur On Wed, Sep 14, 2011 at 9:24 AM, Ananda Kumar anan...@gmail.com wrote: Dr. Doctor, What kind of 10 entries? Is it insert,update delete etc. regards anandkl On Wed, Sep 14, 2011 at 6:30 PM, The Doctor doc...@doctor.nl2k.ab.ca wrote: Question: How can you optimise MySQL for 10 entires? Just running OSCemmerce and it is slow to pull up a who catalogue. -- Member - Liberal International This is doc...@nl2k.ab.ca Ici doc...@nl2k.ab.ca God, Queen and country! Never Satan President Republic! Beware AntiChrist rising! https://www.fullyfollow.me/rootnl2k Ontario, Nfld, and Manitoba boot the extremists out and vote Liberal! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: optimising for 100000 entries
Call me stupid, but IMO this is just plain Bad Design. Maybe your masters are making you want to deliver this, but it's frankly insane. Go instead with the Sally Rand Principle: show them just enough to pique their interest. Given that you've narrowed it down to the WHERE clause, try again. WHERE someField LIKE 'xyz', and narrow your list from there. Nobody wants to view 100K buttons or choices or whatever. The most I want to see is about 20, at any given take. On Wed, Sep 14, 2011 at 12:19 PM, Ananda Kumar anan...@gmail.com wrote: So, You want to have 100,000 buttons for 100,000 entries or just have one filter column, which allows you to specify any type of WHERE CONDITION regards anandkl
Re: Arrays
Another approach to this is to create a concatenated string from the PKs of the result set and then parse that within a stored procedure elsewhere in your Front End (FE). For more information on this, visit www.artfulsoftware.com and check out the Queries page. Arthur
Re: a lesson in query writing and (maybe) a bug report
I agree 110%. It is completely pointless to index a column with that amount of NULLs. In practical fact I would go further: what is the point of a NULLable column? I try to design my tables such that every column is NOT NULL. In practice this is not realistic, but I try to adhere to this principle whenever I can. For example, it's possible to add a new Hire while not yet having determined which department s/he will work in, and hence which manager s/he will report to, but typically I deal with such scenarios by creating an Undetermined value in the corresponding lookup table. Arthur
Re: Hungarian Notation [Was Re: Too many aliases]
I despise this sort of notation, and have instead adopted what have cheerfully named Hungarian Suffix notation, the reason being Signal-To-Noise ratio. Instead of prefacing everything with some form of prefix, just do the opposite: Customer_tbl Customer_Dead_boo Customer_DOB_date Customer_qs (that means Query Select) Customer_qu (that means Query Update) Customer_qd (that means Query Delete) CustomerOrders_tbl Customer_frm (a form that opens the Customer table; could involve subforms, but in that case they are named Customer_Orders_fsub, Customer_Payments_fsub, and so on. Easy to read, obvious the intent, and easily sortable. Just my opinion. Arthur
Re: MySQL RAND() Issues [was Re: How to Shuffle data]
This would be sooo much simpler to solve in MS-SQL, given the function NewID(), which is guaranteed to return a unique value. I have used this in a few web sites and it works splendidly; something along the lines of SELECT TOP 10 *, NewID() FROM User_Messages ORDER BY NewID which is guaranteed to produce a new GUID for each row, and then order by said values. It is not guaranteed to produce 10 results different than the previous SELECT, but nevertheless works extremely well on a web site. I have Googled this and that but not yet succeeded in finding the equivalent in the MySQL world. In theory, this should be relatively easy, since the GUID docs are available, but I'm out of my depth here on how to write the MySQL equivalent function. In case anyone has devised a MySQL equivalent for the NewID() function, we could all massively benefit from your posting of same. TIA, Arthur On Fri, Jul 15, 2011 at 1:40 PM, Jan Steinman j...@bytesmiths.com wrote: From: Reindl Harald h.rei...@thelounge.net do not use any random-functions of mysql even if they exists http://bugs.mysql.com/bug.php?id=59253 Of course, it depends on the desired quality of randomness needed. I'm using RAND() to select random quotations to put at the end of emails. I can easily repeat the process by re-selecting the Signature: menu in Apple Mail. Problem is, I often notice that doing so cycles through several similar signatures in a decidedly non-random way! (You can demo this by sending email to qu...@bytesmiths.com, with a search term in the Subject: line.) But of course, a nuclear plant is not going to melt down because of my signature line. So RAND() can be useful, but it is not really very random, and should be used with caution. My guess is that it's using Knuth's linear congruential algorithm that has well-known problems, but that unfortunately has been hidden deep in system code libraries since Fundamental Algorithms was published in 1968. World events tend to be driven by loose coalitions of economic, political, and military interests, which function like guilds of species in an ecosystem. These guilds generate patterns of events that meet the interests of these coalitions, without there being any unity of purpose or clear plan. When powerful players accept they are not all-powerful, they increase their effectiveness, but are also able to deny and cover any responsibility for the adverse outcomes of those actions. -- David Holmgren Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com
Re: SQL book recommendation?
Visit our site (www.artfulsoftware.com) for lots of tips, especially on queries. Arthur -Original Message- From: Philip Riebold [mailto:p.rieb...@ucl.ac.uk] Sent: Tuesday, October 26, 2010 7:31 AM To: MikeB Cc: mysql@lists.mysql.com Subject: Re: SQL book recommendation? On 26 Oct 2010, at 11:49, MikeB wrote: I'm finding the MySQL online manuals hard going in figuring out how to construct SQL queries. Can anyone perhaps recommend a good book that can shed light on the subject? Thanks.
Re: Best method to keep totals
100% agreed. Arthur The other exception is also where financial data is being stored. If you have, say, a database containing sales order records, then as well as storing the individual values of each item in each order, you also need to store the total value of the order, the total price charged to the customer and the total paid by the customer. These three should, of course, be not only identical to each other but also to the sum of the individual items, so there is not only duplication but the potential for skew. But that, of course, is precisely *why* you store them, as any discrepancy indicates an error which needs to be investigated. Mark
Re: Best method to keep totals
While I agree with the general take on this subject (Never store totals without a good reason and where there is duplication there is the opportunity for skew), I must say that there are exceptions. A couple of years ago I worked on an inherited database in which the operant principle was sum don't store; the problem was that many of the rows summed dated back a year or two or more, and as an accountant friend of mine loved to say, A paid transaction is history; an unpaid transaction is fiction. In other words, repeatedly summing amounts dating from last year or the year(s) before is a waste of time and energy. An approach much superior in performance is to store History in one summary table and Current in the actual transaction table. Then all no sums or other calculations are required for the History portion of the final calculation or presentation or whatever it is. You grab and sum this (fiscal) year's rows, and then look up the numbers for previous year(s). When you're dealing with say a million rows per year, this change can dramatically improve performance. Just my $0.02. Arthur
Re: How Set Up This Table
The ProductPackages table is what is known as an associate table, and is used to implement a many-to-many relationship. You only need it if a given product can be in multiple packages. If not, then you can eliminate the associative table and just add a PackageID column to the Products table. There's also a possible tiny wrinkle that may require another new column. I once did an app similar to yours, with the many-to-many requirement. The wrinkle was that a given package might contain several instances of a given product (i.e. four jars of jam, for example). Because one of the goals of the app was to generate a packing list, and the packages were assembled only upon demand, the packing list told the shippers what to assemble. To meet this requirement, I added a quantity column to the associative table, so that in Package A there might be four jars of jam and in Package B only two. hth, Arthur
Re: How Set Up This Table
Hi Victor. I think that the first thing you need to consider is whether a product can be in more than one package, and second is whether a package can be in another package. Also, I don't know why you need to auto-generate in either case. It's pretty simple DDL. Case 1: product can only be in one package: 1. Add a Packages table with columns PackageID and PackageName and probably PackagePrice. 2. Add a PackageID column to the Products table and make it a foreign key referencing Packages. Case 2: product can be in multiple packages: 1. Same as above. 2. Create a ProductPackages table that contains PackageID and ProductID, both as foreign keys into Products and Packages. 3. Decide whether you want a compund PK on this new table, or you want instead an auto-increment column that would be the PK. (There are differences of opinion on this one, so I'm leaving it alone; I don't want to start a religious war :) hth, Arthur On Fri, Jan 1, 2010 at 5:09 AM, Victor Subervi victorsube...@gmail.comwrote: Hi; I have a table with products for a store to sell. I need to autogenerate from code a table or series of tables into which I can enter (and from which I can retrieve) the ID numbers of products which I am going to associate together and their package price. Product associations will vary, in that one association may have 2 products and another 20. What is the best way to MySQL this? TIA, Victor -- The Logos has come to bear http://logos.13gems.com/
Re: Is It Possible to create a Join with Multiple Tables
I don't see why not, but another choice is to create a view that encompasses all these tables and Select * From my_view. Arthur On Fri, Sep 25, 2009 at 11:28 PM, c...@hosting4days.com c...@hosting4days.com wrote: :Newbie: Short Question: Is it possible to create an inner join (or another join) with multiple tables? something like $query_list1 = SELECT table1.id,table1.status, table2.id, table3.id (more here - etc.) FROM table1 INNER JOIN table2 ON table1.id = table2.id INNER JOIN table3 ON table2.id = table3.id WHERE table1.status = 'open'; Q: Is something like this possible? Thanks, c...@hosting4days.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com
Re: Retrieving info from 2 tbls ordering it
I think that you can do what you want with a simple union query: select * from incoming where user_id = 123 union select * from outgoing where user_id = 123 order by datetime_received, datetime_sent hth, Arthur On Wed, Sep 23, 2009 at 10:02 AM, Warren Windvogel war...@fontera.comwrote: Hi I have 2tables. 1 for incoming the other for outgoing messages. They both have columns for the userid datetime_received/sent. I'd like to retrieve all records from both tables for a specific user id order all the records returned by the two datetime_received/sent fields. Is this possible if so could someone help me out as to how I could achieve this. Kind regards Warren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com -- Semi-retired SQL guru, interested in interesting projects not YAFOES (yet another friendly order entry system).
Re: How to compare 2 columns in different tables.
You may be able to get what you need with three queries. Join the tables on the column of interest. By default you'll get a list of the matches. Then you can modify the join by adding a WHERE clause that looks for table2.nameis null, and again by modifying the WHERE clause so it looks for table1.name is null. hth, Arthur On Tue, Sep 15, 2009 at 6:52 AM, Mark Goodge m...@good-stuff.co.uk wrote: John Furlong wrote: My question is, can the argument in AGAINST() refer to a column in another table or does it have to be a specific string you are searching for? If the MATCH() function won't work, any suggestions on how else to compare table1.name against table2.name? The columns are defined as VARCHAR. Does anyone have an answer to this? I, too, would like to know how to use fulltext to compare data between two columns (as opposed to comparing data between a column and a pre-defined string). Is there any way to do this? Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com -- Semi-retired SQL guru, interested in interesting projects not YAFOES (yet another order entry system).
Re: database design
Storing it directly will cause problems when you want to add a new Article Type. IMO it's better to have an ArticleTypes table (AutoIncrement) and store its values in the ArticleTypeID column in the Articles table. A. On Sat, Sep 12, 2009 at 1:19 AM, AndrewJames andrewhu...@gmail.com wrote: thank you all, i think You probably wouldn't need Article_Type table if you're going to store Article_Type value directly. is my answer.
Re: database design
I agree with Claudio. You have your design correct. The only other thing you need is the uid qualifier. Presumably you are using PHP or some other front end to present your data. Your front end would request the user's name and password, saving the uid in a variable and then issuing the select with a WHERE clause that passes the uid in: select * from articles A left joing article_types AT on A.article_type = AT.Arcticle_types_id WHERE A.uid = insert your variable here hth, Arthur On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni claudio.na...@gmail.comwrote: A.J., It sounds good to me! You can be a little confused but you did it well, It seems you have all you need there. A) Yes B) select * from articles A left join article_types AT on A.article_type = AT.article_types_id Claudio 2009/9/11 AndrewJames andrewhu...@gmail.com This is a bit of a long shot, but i really need some help and or directed to the best reading resources. as i begun building my database (as i went along), i now realise i have to stop coding and sit back and design the database properly before i can go on. However i am still unable to wrap my head around what data to put into what tables, and which columns i need to link to make the relationships. so far, here is what i have. TABLES: users -uid(pk) -username -password articles -article_id(pk) -uid(fk) -article_type(fk) -article_subject -article_body article_types -article_types_id(pk) -article_type So i want the user to be able to login and add articles. I then want to be able to view all the articles the user has submitted. So in my understanding i need to link the users.uid(pk) to the articles.uid(fk) (so i know which user the article belongs to, please correct and update me if i am wrong) I am stuck at this point. A) Have i created the right tables and columns for each table, AND B) How do i link the articles.article_type to articles_type.type? (IF in fact that is even the correct linkage)?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Re: Can a MyISAM DB hold /everything/?
I second that emotion (don't store the images in the data file: just store the paths to said images). Why? Because in the event of an updated image it is more hassle than it is worth, to drop the old image and replace it with the new one; if what you store is a simple pointer to an image file, lots of this work goes away. A.
Re: I thin'k MySQL will be the 'Oracle Personal Edition'
The revenue that MySQL has accrued to date comes (obviously) from the support contracts. Oracle has no interest in derailing this revenue stream. It may well slow down the version cycle, which may be a good thing, but that aside, I cannot see Oracle killing the MySQL stream. There's no argument that I can see in favor of it, and abundant arguments against. Why kill a revenue stream unless you're some sort of neo-Marxist? The large players all buy support contracts and that's the revenue stream. Why kill that? A.
Re: Sun bought by Oracle
I too am a big entusiast of Sun's VirtualBox, and I hope that nothing goes sideways on this product. A. effects and that is VirtualBox. I've used MySQL for years and hope Oracle does not stop it, but you never know with Ellison encharge! Just my 2 cents. -- -- OpenSUSE 11.1 KDE 4.1.3, Intel DX48BT2 Core 2 Dual E7200. 4 GB DDR III GeForce 8400 GS, 320GB Disc (2) --- Russ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com I know this is the MySQL list but there is another Sun product that this
Re: Oracle , what else ?
I hereby bet the farm that this shall not occur. I have $10 to say that this shall not occur. a) Who is going to challenge the deal? b) What possible purpose would it serve to interr MySQL? c) Assuming there is some reason for b) above, why incur the wrath of the MySQL community and their possible bail-outs? Nothing gained and everything lost, in such a move. d) If we know anything, we know that Scott and Larry are not fools. e) In the grand scheme of things, the MySQL piece of this pie is peanuts and perhaps less. This acquisition is about the big picture (hardware platform + existing Sparc base + Java, etc.). MySQL, as much as we love it, is a tiny teensy part of this acquisition, and my guess is that Scott and Larry are much more focussed on the other parts (e.g. end-to-end solutions extending from the hardware to the middleware to the Oracle apps, etc.) and in this ballpark MySQL is an interesting tidbit but not at all the focus of their efforts. Think big, baby. MySQL in this context is a tiny little ripple in the pond, having little or nothing to do with Scott/Larry's plans. Viewed from this perspective, MySQL becomes a viable alternative to such offerings as SQL Express from MS. If for no other reasons than marketing imperatives, I am confident that Scott and Larry will choose not to kill MySQL but rather regard it as both an entry platform and a position from which to upgrade to Oracle. Make no mistake about this. There are very sound reasons to upgrade to Oracle. Cost is of course a serious issue. But Oracle can do things, and has various top-end vehicles, that MySQL cannot approach. Consider, to take just one example, Trusted Oracle, upon which numerous banks bet their bottom dollar. Add to this the numerous Oracle Apps. I am no champion of Oracle in particular, but I do rtheecognize what platforms X and Y can do. If the game is defined as retrieval amongst several GB of data, then MySQL has a chance. If the game is retrieval amongst several PB of data, with security, then I bet on Oracle. Granted, this move requires a team of DBAs etc., but if you are dealing with PetaBytes then I suggest that you think carefully about which vendor is prepared to take you there. Just my $0.02 in this debate. I don't see MySQL and Oracle as competitive products. In fact I see the opposite: Oracle gets to occupy a space in the open-source community while simultanwously offering an upgrade path to multi-petabyte solutions, serious security, and so on. I don't think that Scott and Larry are out to hurt the MySQL community, and I'm prepared to bet that they will invest in the next version of MySQL, You might disagree but I challenge you to answer Why? Sheer rapaciousness? That doesn't make sense. MySQL has garnered numerous big-time players, and in what possible interest would Oracle jeapordize these investments? As several writers on this thread have said, if Oracle muddies the waters then they are prepared to move to PostGres and/or several other alternatives, not least to take the MySQL sources to a new playpen. It is clearly not in the interests of Oracle to let this happen. Far more interesting is to fold the MySQL project into Oracle's overall Linux project. Continue to offer MySQL for free, work on transport vehicles that let MySQL people migrate effortlessly to Oracle, etc. I don't mean to pretend to read Scott and Larry's minds here. But I think that the MySQL part of this acquisition, while interesting, is a small part of the rationale for buying Sun. The serious interest is in acquiring an end-to-end solution, as yet offered by nobody, including IBM and MS. This is the most significant part of this acquisition. Imagine being the salesperson of said stack. We have the hardware and the operating system and the middleware and the front-end. Click and go. IMO this is a truly formidable argument. In practice, it could be delivered as an appliance and/or a blade. And if you don't think this is formidable, then wake up and smell the coffee. This could well leap-frog certain other competitors -- which is not to say they won't catch up eventually, but it is to say that Oracle has raised the bar and it's time for competitors such as MS to jump through several flaming hoops. On Tue, Apr 21, 2009 at 6:57 PM, John Daisley john.dais...@mypostoffice.co.uk wrote: MySQL will live on regardless of who owns the brand. First and foremost MySQL is a community and that community will continue to develop MySQL and take it in the direction they want it to go. Sure Oracle could try and force some 'features' or changes through but if the community didn't like them the community would just keep developing 'pre-oracle' MySQL, even if that happens to be under a different name. Personally I would be surprised if the Oracle deal goes unchallenged. I don't think Oracle really 'want' MySQL as it makes very little money and it raises competition concerns. I wouldn't be surprised if Oracle were to look at offloading
Re: Sun bought by Oracle
I think that you'e being paranoid. IMO, Oracle will continue to support and develop mySQL. Further, I think that these concerns about the future of mySQL overlook the points behind the purchase: 1. To obtain the Sun hardware and thus provide a complete hardware and software solution. 2. To further optimize Oracle to take full advantage of the Solaris OS. 3. To continue to support Linux. 4. To get Java and thus penetrate the mobile device marketplace. 5. And finally, to grow Oracle revenues by $1B+ a year and growing. Given the purchase price, the acquisition will pay for itself within 5 years. Compared to all these reasons, the mySQL part of the acquisition is small potatoes. Arthur On Mon, Apr 20, 2009 at 11:44 AM, Curtis Maurand cur...@maurand.com wrote: I figure that they'll either kill mysql or they'll limit the commnunity version in ways that will make you purchase a commercial version if you want to continue to use it. I figure there will be heavy migrations to open source alternatives. --C Andy Shellam wrote: I've just been made aware by a client that Oracle have purchased Sun Microsystems. The article below on Sun's website mentions that Oracle are committed to Linux and other open platforms and mentions the fact that Java touches practically every business system around. http://www.sun.com/third-party/global/oracle/index.jsp I wonder what Oracle's plans are when it comes to MySQL? There is no mention of MySQL in the above article. Will it eventually come under the Oracle umbrella, much like BerkeleyDB did? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com
Re: Are equi-joins faster than left joins?
IIRC it does not matter. But you can double-check my opinion with EXPLAIN. A. On Thu, Mar 12, 2009 at 11:41 AM, mos mo...@fastmail.fm wrote: I have 3 tables that are 1:1 and will always have a row for a given product,_code date. If I want to join them together, is it going to be faster to use an equi join or a left join, or does it matter? TIA Mike
Re: Search based where claused and stored proc
An approach that has worked for me in the past and may work for you. Declare as many parms as you can possibly need and then code the statement like this: sql SELECT * FROM someWhere WHERE @parm1 = 123 OR @parm1 IS NULL AND @parm2 = 345 OR @parm2 IS NULL -- etc. This has the desired effect and eliminates the need for a temp table or actual table (inno or otherwise). The trick here is to pass the maximum number of parms, even if they are Nulls. HTH, Arthur
Re: Data structure for matching for company data
My esteemed friend, partner and co-author has laid it out perfectly for you. Just follow the instructions table-wise. One thing that may not be obvious from Peter's prescription is that you need to enter a bunch of rows into the industry table first, so that the foreign keys will make sense in the bridge table. But presumably this is not onerous. Over here we commonly use a table called SIC (standard industry codes) that has sub-industries etc. Whenever possible I try to use such standards rather than make up a new one. For example, there is the ISO country codes standard, which provides unique two and three letter codes for every country. Yes, thanks to politics, envy, revenge instinct etc. such country codes from time to time require updating, but I choose to stick with the ISO standards rather than rely on my self-invented one and all attendant hassles whenever a country commits seppukku. You might consider looking into the SIC code scheme before bothering to invent your own. You can download this from a number of sources and in a number of formats. Just Google SIC and you should get there. Hope this helps, Arthur
Re: Separate customer databases vs all in one
You only confirm my first-blush opinion: keep separate databases. Regarding your second issue (joining across databases for reports), I would suggest that these operations are best done against an OLAP database not the numerous OLTP databases. That is, create an additional database whose sole purpose is reporting, and which accumulates all the data from the numerous OLTP databases, on some scheduled basis (schedule dictated by stakeholders -- how recent should the reports be? Will one week do? One day? One hour? The basic idea here is that reporting does aggregates and therefore necessarily does table scans, especially in your case. To place this burden on the OLTP databases is an error in design and more important, a bottleneck in performance. My advice would be to separate the reporting tasks from the data-entry tasks. Aggregate the data periodically in the OLAP database and base all your reports on this, not on the OLTP databases. This way you maximize data-entry and update speed, while also maximizing the reporting speed (since it won't cause contention with the data-entry activities). This would mean that the aggregate db is very large, but OTOH interrogating it won't impair the OLTP databases in the slightest. So the big problem this scenario suggests is the granularity of the updates to the OLAP version of the data. That's not for me to decide. Ask the stakeholders how recent the data must be and proceed from there. Arthur
Re: Separate customer databases vs all in one
Are these databases identical or merely similar? If they are structurally identical, I'd go for one database per customer. Then you have isolation, easy structure updates and above all, consistent front-end code, in whatever language that occurs. Just obtain the customer ID and then use the appropriate database. Everything else can remain the same. The only fly in the ointment concerns whether you'd ever have the customer need to cross databases. I would imagine that sort of thing is for internal use, not the customers. In that case, the performance hit if any won't impact upon the customer, just you. hth, Arthur On Tue, Mar 17, 2009 at 8:21 PM, Daevid Vincent dae...@daevid.com wrote: I'm writing a report tool wherein we have many customers who subscribe to this SaaS. There are millions of rows of data per customer. All customers are islands from each other (of course). Are there any major issues or benefits between storing each customer in their own database (with their own tables), or all lumped into a single database? At first thought, it seems that by separating them, queries should be faster no (as there is less data to sift though per customer)? It of course makes upgrading table schema a wee bit more cumbersome, but a simple loop and script can handle that easily enough. And since you can query across databases, we can still make internal aggregate reports for our own usage. For example: SELECT * FROM customerA.foo.bar JOIN customerB.foo.bar; or we can use UNIONS etc. too. Consolidating them into one would seem to bloat the tables and slow things down (or is the fact that mySQL uses B-Trees invalidate that theory)? It also makes us have to have a customer_id entry in every table basically (or some FK to distinguish who's data is who's). It also feels like it could leak data if a malformed query were to get through, although I'm not terribly worried about this as we do some heavy UAT before pushing from DEV to TEST to PROD. Performance is a major factor concern here given our huge data sets involved. Does joining across databases impose any speed/performance hits vs. just joining across tables within a single database? http://daevid.com
Re: Getting single results per (left) record with INNER JOIN
Won't a simple LIMIT 1 do what you want? Or am I missing something? Arthur On Fri, Mar 13, 2009 at 3:24 PM, Nigel Peck nigel.p...@miswebdesign.comwrote: Nigel Peck wrote: SELECT `People`.`person_id`, `People`.`name` FROM `People` INNER JOIN `Person_postal_addresses` ON `Person_postal_addresses`.`person_id` = `People`.`person_id` WHERE `People`.`name` REGEXP 'example' OR `Person_postal_addresses`.`address` REGEXP 'example' ;
Fwd: avoiding use of Nulls
Exactly the point. Michael, NULL *is* information. It means unknown and that is in itself useful information. A common example: A new employee is hired but which department she will work in is unknown. So the data entry person enters all the known information and leaves the rest until it has been clarified. The alternative is even worse than the so-called NULL problem and the alleged difficulty of querying against NULLable columns: in a case like that described above, the only way to handle it is to create a fake row in the foreign-key table, for Department Zero or somesuch. That immediately turns every query into a more complex beast that it would otherwise have been. Every single query must exclude this zeroth row; join a few tables all exhibiting this problem and things get really crazy. Not to mention the fact that these zeroth rows falsify reality and combine fiction with fact. Even worse, the -9 approach means that you can't impose a constraint on the column (such as must be a positive integer. And finally, I cannot believe that you really mean no NULLS ever. Surely you mean only FKs. Otherwise, how would you handle fax numbers for people with no fax, or middle names for people with none? Arthur
Re: avoiding use of Nulls
On Fri, Mar 13, 2009 at 3:20 PM, Andy Wallace awall...@cisdata.net wrote: ggghh mich...@j3ksolutions.com wrote: On Fri, 13 Mar 2009, mich...@j3ksolutions.com wrote: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less complex it will be to retrieve reliable information out of it. Furthermore, (and this is probably what makes me crazy when Nulls are evolved) after a ten year stretch of software development, where I and a team designed our own databases, I did a nine year stretch of statistical programming, using databases designed by other people, and Nulls in the data made the results unpredictable, and yeah, made me crazy! I had to write nightly processes to resolve inconsistencies in the data, if at least report inconsistencies. You know the old saying Garbage in = Garbage out, to me Nulls are garbage, and if there is a good reason for nulls to be a part of good clean data then someone please help me understand that. Hi I'm in a argumentative mood today too. :-) I have a database logging weather data. When a station does not report a temperature, it is set to NULL. It would be a very bad idea to set it to 0 as this would ruin the whole statistics. NULL is a perfectly valid information in many cases. Cheers Thomas OK! I do understand, thank you. But hypothetically speaking, what value would you use if you didn't have a I don't what this is value like null? I ask this because I started programming when NULL was really zero, and part of the ASCII collating sequence. I'd use -9., I'd never allow a i don't know what it is value like Null in my database. Mike. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com
Fwd: Codd's rule 8 (physical data idependence)
-- Forwarded message -- From: Arthur Fuller fuller.art...@gmail.com Date: Fri, Feb 13, 2009 at 3:36 PM Subject: Re: Codd's rule 8 (physical data idependence) To: Yusuf Khan yousuf.kh...@gmail.com Sad to say, you cannot casually switch engines and hope that everything shall continue to work. This is after all the real world, in which politics is the art of compromise. Some things port, some don't. C'est la vie, c'est l'amour, c'est la mort. Life is tough. Not one single vendor has made (nor even bothered) to make this possible. I don't vilify them for this. One sets one's priorities, and in the case of numerous for-profit vendors, one introduces enhancements which are guaranteed to be non-portable. In other words, vendor lock-in. In the real world, the late Dr. Codd (much as I admire him) doesn't hold much weight. The marketeers win out. I hate that this has happened and I am one of the biggest champions of Dr. Codd and Mr. Date, but in the face of marketeering that means squat. In the case of MySQL, this is decidedly a double-edged sword. The MySQL team has introduced several very powerful extensions to the language, none of which are accepted in the SQL 92+ syntax. That doesn't dismiss their power. It just means that the syntax is not portable to other SQL implementations. (Case in point -- the multiple-insert syntax in MySQL has resulted in an equivalent syntax in MS SQL 2008, so now both of them break the mold.) Arthur On Fri, Feb 13, 2009 at 6:28 AM, Yusuf Khan yousuf.kh...@gmail.comwrote: Hello all Does MySQL 5 conform to Codd's rule 8, i.e. physical data independence, which says that: *Applications should not be logicaly impaired when the physical storage or access ethods change.* Any help would be greatly appreciated. Thanks
Re: Foreign Keys
So you are talking about parent-child relationships in a single table, or in the technical jargon reflexive relationships. See www.artfulsoftware.com for detailed examples of how this is done, but here is the thumbnail sketch: The table has to have a ParentID column (call it what you want) that points, in the case of a Friend, to the Friend Of Whom UserID column. This simple statement avoids the complexity of the situation where a Friend can have Friends and so on. That problem requires detailed explanation, which you can obtain from the site mentioned above. See also Joe Celko's books. Arthur On Wed, Oct 8, 2008 at 3:33 PM, Ben A.H. [EMAIL PROTECTED] wrote: I know that this is the standard means of dealing with a many to many relationship, I'm just not sure it fits here. USER: emailID (PK) userName Password Address Etc FRIEND: emailID (PK) USER_FRIEND user_emailID (PK) friend_emailID (PK) So if I want a list of USER [EMAIL PROTECTED]'s friends: SELECT friend_emailID from USER_FRIEND WHERE user_emailID=[EMAIL PROTECTED] In this (and pretty much every case), the FRIEND table is useless and doesn't make sense logically. *I THINK I EXPLAINED THINGS INCORRECTLY* Let me try again: * I'm not sure if it's even a true many to many relationship as this is actually a relationship between ONE RECORD in a TABLE and a ANOTHER RECORD in THE SAME TABLE! A USER is: USER emailID userName A friend is really just another RECORD in the USER table. i.e. IF I HAD TO MAP THE RELATIONSHIP LOGICALLY, IT WOULD BE: USER: emailID userName |1 |many FRIEND: emailID |many |1 USER: emailID username (i.e. it's two records in the same USER table) How are relationships between records in the same table usually dealt with in terms of design? Implementation? ThanX, Ben Jim Lyons [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Indexes speed up joins. Foreign keys should be indexes themselves, so they can also speed up joins. If the FK is not an index, it won't help. So, index your FKs On Wed, Oct 8, 2008 at 10:43 AM, Ben A.H. [EMAIL PROTECTED] wrote: Does using foreign keys simply enforce referential integrity OR can it also speed up JOIN queries? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Normalization vs. Performance
Yours is the classic case of the distinction between OLTP and OLAP, and in my opinion you are doing it exactly right. For analysis purposes, de-normalization is a good thing because you eliminate the joins and increase performance, but even more important, you don't slow down the data-entry process while analyzing the data. Personally, I like the OLAP data to live in another database, but your approach of rebuilding the de-normalized table nightly works equally well. Arthur On Fri, Aug 29, 2008 at 11:11 AM, Jerry Schwartz [EMAIL PROTECTED] wrote: -Original Message- From: Kevin Hunter [mailto:[EMAIL PROTECTED] Sent: Thursday, August 28, 2008 10:59 PM To: John Smith Cc: MySQL General List Subject: Re: Normalization vs. Performance At 2:11pm -0400 on Tue, 26 Aug 2008, John Smith wrote: So how bad is this? The mentioned query will be the query which is used the most in my application (yes, it is going to be a forum). Should I break normalization and save the date of the root in each node row? My recommendation is no. Normalization is a Good Thing, and you'll be hard-pressed to convince me otherwise. In the long run, you (or following programmers) will thank yourself if you maintain a normalized core data model. Seriously. But speed and performance are the here-and-now issues while DB technology catches up to demands. Have you looked at temporary tables or materialized views? These might help you in this endeavor. [JS] You can sometimes cheat. Our database is normalized, but many of our users want to use MS Access to get at the data in read-only mode to extract data into Excel. Rather than trying to teach them how to define the necessary JOINs, I periodically build a non-normalized table for them. For example, I take the values from a dependent (1:n) table and use GROUP_CONCAT to stuff them into a single field in the unnormalized table. True, the data isn't up-to-the-minute. I truncate the table and reload it daily. It is good enough for them. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert into...on duplicate key problem
I think that you need to select old.a otherwise you cannot group by it. Arthur On 7/9/08, Phil [EMAIL PROTECTED] wrote: Is it possible to do an insert into with subselect and group by with an additional on duplicate insert ? CREATE TABLE NEW_TABLE ( `a` varchar(10), `b` double ) engine=MyISAM; INSERT INTO NEW_TABLE (select old.x,sum(old.y) from OLD_TABLE old group by old.a) on duplicate key update b=sum(old.y); I get invalid group by clause on that. Currently I achieve the same thing using two seperate queries, but wondered if I could consolidate as they take ~ 30mins in total (much more complex tables). Anyway, more curious than anything on why it's disallowed and if it's just something silly I'm missing. Phil
Re: trouble with group by and similar tables
What you need, basically, is to combine two queries, one of which addresses t1 and t2 while the other addresses t1 and t3. Create two views similar to your first query and then join the two views on t1.id and you'll get what you're after. hth, Arthur On Thu, Jun 5, 2008 at 3:44 PM, Eben [EMAIL PROTECTED] wrote: I have the following tables: table1 --- id1 some_field table2 --- id id1 score table3 --- id id1 score I then have the following query: SELECT table1.id,SUM(table2.score) as table2_score FROM table1, table2 WHERE table1.some_field = 'value' AND table2.id1 = table1.id GROUP BY table1.id This works fine and returns each record in table1, grouped by id, with the sum of scores from table2. However, when I do this query: SELECT table1.id,SUM(table2.score) as table2_score, SUM(table3.score) as table3_score FROM table1, table2, table3 WHERE table1.some_field = 'value' AND table2.id1 = table1.id AND table3.id1 = table1.id GROUP BY table1.id The sum'd score values go crazy, reflecting #s that aren't logical. Is the issue that table2 and table3 are identical table structures, or that I simply don't understand how the group by is really working here...? Any advice is appreciated, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed
I tested the program before running it and it is virus-clean. I ran it through AVG first and it's clean. I ran it in Windows. I don't know whether there is a Linux version. Arthur On Mon, May 12, 2008 at 11:31 AM, Velen [EMAIL PROTECTED] wrote: Hi Arthur, Could you please tell this guy that it was not a virus and you tested it without any problem? Thanks. Velen
Re: Starting a 2nd MySQL instance on UNIX
Would you kindly supply the changes you made, for our collective education? Thanks. Arthur On Mon, Apr 28, 2008 at 11:54 AM, Mark-E [EMAIL PROTECTED] wrote: Hi Ian, Thanks for the reply. I was specifying the new port of 3307. I actually got it working over the weekend. Turns out I had to add a few entries in the mysqld section of the my.cnf file and I was able to connect. Regards, Mark
Re: Display more than 2500 rows
The first question that occurs to me is, Why on earth would you want an app to display 2500 rows? You must have one incredible monitor with a resolution beyond my wildest dreams! I would look into the LIMIT predicate and use it to grab say 50 rows at a time, or fewer, and post a marker so you know how to interpret the Next and Previous commands that you provide on the form. Just a thought. Arthur On Thu, Apr 24, 2008 at 12:44 PM, Velen [EMAIL PROTECTED] wrote: Hi, May be it's not the right forum i'm posting to. I have a Mysql Query : Select a.code,b.description, b.other_details,a.qty,a.price from xyz a, bcd b where a.code=b.code and a.id='5' order by a.id This is running fine but when using VB6 to display it in a Msflexgrid, it's a nightmare! It will take about 3-5 mins to display around 2500 rows. Can anyone suggest a better alternative to Msflexgrid or how to improve the speed on msflexgrid? Thanks. Regards, Velen
Re: insert select
The beauty of this language is exactly as Johan says, you can skip the obvious, Just insert all the other non-obvious columns. In the event that you have numerous defaulted columns, though, it's best to supply a NULL so the syntax is parallel (IMO), or alternatively to name the columns. But either way, for your auto-increment column, I would suggest skipping it completely and just worry about the other columns. As Johan has suggested, you can even substitute functions' return values for what you wish to insert. You can do almost anything you want in the MySQL Restaurant. A. On Thu, Apr 3, 2008 at 10:37 AM, Johan Höök [EMAIL PROTECTED] wrote: Hi Hiep, you can put in either xxx = NULL or you can skip it completely: insert into tbl_1(fld2,fld3) select fld_b, NOW() from tbl_2; Regards, /Johan Hiep Nguyen skrev: hi all, i have a question on insert ... select statement. tbl_1(fld1,fld2,fld3, ) fld1 int primary key auto_increment not null tbl_2(fld_a,fld_b,fld_c,...) how do i construct my select statement so that fld1 is auto increment? insert into tbl_1(fld1,fld2,fld3) select xxx, fld_b, NOW() from tbl_2 where fld_a = '5'; what should 'xxx' be??? my goal is to get fld1 = auto increment fld2 = fld_b fld3 = NOW() i saw someone used '1', other used null for xxx. i'm confused. thanks. t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] When MySQL Bites: Quirks to Watch Out For
Dis-information not mis-information. Someone has an axe to grind, methinks. Or just perhaps is puntificating to see wha' 'appen. The objections PB has raised all apply here in my installation. Arthur
Re: Indexing question
What is the size of the text field you're fulltext indexing? How often is that index used? You might be best off to create a table containing only that column and a PK that is equal to the PK in the original table. You might also keep a portion of the text field (say 50 characters) in the original table, for head-first indexing. Arthur On Tue, Mar 25, 2008 at 9:56 AM, J. Christian Hesketh [EMAIL PROTECTED] wrote: Hi, I have created a rather large table containing about 16M records. Most of the indexed fields are smallint, but there is one field that is a text field that I am using fulltext indexing on. The total size of the smallint indexes is only about 30 MB, but the fulltext index brings the total index size to about 2 GB. My question is: When mysql adds indexes to the keycache, does it add each index individually or does it dump the entire .MYI file to the keycache. If it is the latter, I should move the fulltext index to another table. Thanks in advance, Christian -- J. Christian Hesketh M.Sc. CEO - Ion Channel Media Group 2028 Harvard Avenue, Suite 103 Montreal, QC CANADA H4A 2V9 Tel: +1(514)245-8107 http://www.IonChannelMedia.com http://www.ionchannelmedia.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use index with order by here
I love when this happens. I woke in the middle of the night with an idea for you. It now occurs to me that the query you want is dead simple. It just took me a while to see: SELECT * FROM messages WHERE id_from = 1 AND id_to = 2 UNION SELECT * FROM messages WHERE id_from = 2 AND id_to = 1 ORDER BY create_time Assuming an index on id_from (or id_from, id_to), it will be used. This will be very quick. hth, Arthur On 3/16/08, Rob Wultsch [EMAIL PROTECTED] wrote: On Sun, Mar 16, 2008 at 10:36 AM, Velen [EMAIL PROTECTED] wrote: I would suggest u use SELECT * FROM messages WHERE id_from between 1 and 2 AND id_to between 1 and 2 ORDER BY time That would only be applicable if he mandated that his users only send messages to other users with user_id's +/- 1 of their own, or they will break their message search app. I bet that would go over well. Even if this were not an issue changing from the IN to BETWEEN does not help performance. In fact performance is worse. With the index I had suggested above and using the sample data I created his original query examines 28 rows, while yours examines 713, and takes several (4 to 6) times longer to retrieve the rows on 5.0.51. -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow-query log analysis
Wow! 70k files in /tmp. Hell of a mistake :) I hope it doesn't happen often. Arthur On 3/17/08, Soenke Ruempler - NorthClick [EMAIL PROTECTED] wrote: Hi Baron, There were about 70k files in /tmp (caused by a mistake). the web application on this server had many lookups to tmp and those were slowed down. -soenke
RE: Licence Free Import/Export for Access?
I have used dbScripter a lot for this kind of work. It is definitely NOT necessary to own Access to use it. One of its nice features is its database profiles. These function as data and syntax translation tables. For example, if a column type doesn't exist in the target, substitute another column type. It only took a small amount of experimentation to create a MySQL profile, and using that I have frequently moved databases from SQL 2000, Oracle and Access to MySQL. dbScripter will work fine with virtually any ODBC-compliant back end. Hth, Arthur -Original Message- From: David T-G [mailto:[EMAIL PROTECTED]] Sent: February 12, 2003 10:45 PM To: mysql users Cc: j.urban; Christensen, Dave Subject: Re: Licence Free Import/Export for Access? -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dave J, et al -- [re DBScripter and urSQL] Those are nice tools, but if I read you correctly they only work if one has MS Access. The original poster, if I read *him* correctly, wants a way to read Access DB files and turn them into MySQL files -- but without having to have MS Access. That would be my desire if I ever had to touch an MS Access file, since I don't have Access almost don't run Windows at all. Did I misunderstand anyone? HTH TIA HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+SxTLGb7uCXufRwARAhvNAKCDaezt+gSN4BQinKadUoWzvw2wWACg5hrn apGMD7+rA8vIqlbp2ZkBS4Y= =N2rx -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: [ sub select in 4.0.9-gamma? ]
In the meantime, you can almost always perform the equivalent of a sub-select using an inner join (and sometimes a temp table). For example, a query to select all customers in the province of Ontario: SELECT * FROM Customers WHERE CityID IN( SELECT CityID FROM Cities WHERE ProvinceID = ON ) Can be re-written as: SELECT * FROM Customers INNER JOIN Cities ON Customers.CityID = Cities.CityID WHERE Cities.ProvinceID = ON Hth, Arthur -Original Message- From: Stefan Hinz [mailto:[EMAIL PROTECTED]] Sent: February 13, 2003 8:36 AM To: Elby Vaz Cc: [EMAIL PROTECTED] Subject: Re: [ sub select in 4.0.9-gamma? ] Elby, The MySQL-4.0.9-gamma to windows supports sub select? Nope. Subselect are supported as of 4.1. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Create database via ODBC
It's dead simple to create a MySQL database -- it's just a directory under mysql\data. If you want to do it from Access, just determine where the mysql\data directory is and then create the subdirectory. Hth, Arthur -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: February 13, 2003 9:01 AM To: [EMAIL PROTECTED] Subject: Create database via ODBC I have an application which is being converted from access to mysql, and have the need to create a second database under the mysql implementation. Is there a way that i can create this second database via an ODBC object, or do I need to at least specify the database be created as part of the conversion process? Thanks Carlin Anderson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto increment question
Yes you have neither triggers or stored procedures. Instead you'll have to remember to use the same code everywhere in your app that can update the table: Update mytable SET col1 = 'somevalue', col2 = 3456, col3 = now(), Viewed = Viewed + 1 hth, Arthur - Original Message - From: Adolfo Bello [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 03, 2003 7:15 PM Subject: RE: auto increment question It sounds to me that a trigger would help, but mysql doesn't has them yet. Adolfo -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 8:04 PM To: Kevin; [EMAIL PROTECTED] Subject: Re: auto increment question At 15:28 -0800 1/3/03, Kevin wrote: Hello, I have a table with an ID column and a viewed column. I would like the viewed column to increment by one each time the row is updated. Stats === ID views is this possible? Sure. Since you're updating the row anyway, set the column value to one more than its current value. :-) No, it won't happen automatically, which I would guess is what you're really asking. -k sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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: style question: drop database in an install script
I don't know about anyone else, but for this sort of thing I love the program called DeZign, which is a data-modeling tool. You can draw your tables and their columns and set up relations and so on. DeZign will then write your code for you. Personally, I like to keep all my drop statements in one script, all my create scripts in another, and all my populate scripts in yet another. Arthur -Original Message- From: David T-G [mailto:[EMAIL PROTECTED]] Sent: Friday, December 27, 2002 8:15 PM To: mysql users Subject: style question: drop database in an install script -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- I'm working up the schema for my application and, as you can imagine, starting over and reloading numerous times :-) In fact, I intend for the script to be an install script, run on a new installation of the software to set things up. The script starts out drop database if exist dbname ; create database dbname ; use dbname ; create table tablename ( ... ) ; ... to set things up and then continues insert into tablename (field,field,field) ( data , data , data ) , ... ( data , data , data ) ; ... to set up the predefined things (credit card types and so on). To reload the database and try something new, all I have to do is mysql -udroot -p script.sql and what was there goes away and is rebuilt fresh. That's mighty convenient as I'm building up a table's design and trying it out. Soo... When you experienced folks write software that's meant to be installed from a script or such, do you lead off with a 'drop database' command, or is that just too dangerous to put in a script and you make your users clean up any old installation by hand? This will have to be run by a root user, so we can figure that this user might have read the docs and know that he's going to start [over] from scratch, but then, again, users are users :-) Meanwhile, is there if/then/else functionailty in SQL so that I can say if exist dbname exit some error ; create database dbname ; ... and not try to create and populate a database on top of an existing one? Or is there perhaps a RENAME DATABASE command (I saw RENAME TABLE) so that I could rename the old one to get it out of the way but not toss it? TIA HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+DPsqGb7uCXufRwARAsp8AKC7BuVdyO7Dl5fkbvEM51o+i/BAEgCeI5iI dI7HQb5oywLHuZIjLxXYZwY= =QTHL -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication
Is there in *nix an implementation of GUIDs? If the MS math is too be believed, GUIDs are guaranteed unique for the next century. No matter how many monkeys are at the typewriters. Supposing a) that there is a *nix GUID implementation, what's the right column type to use within MyISAM and InnoDB? Assuming that anyone besides me finds this interesting, will MySQL ever support GUID column types? Arthur - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Maxime LEMAIRE [EMAIL PROTECTED] Cc: Mysql [EMAIL PROTECTED] Sent: Monday, December 30, 2002 10:56 AM Subject: Re: replication On Mon, Dec 30, 2002 at 08:37:13AM +0100, Maxime LEMAIRE wrote: Hi, I would like to know how mySQL manage the identity (auto increment counter) in a replication environment when 2 servers are both master on the same database. In that situation, you really need to generate your own primary keys. Using auto-increment with a dual-master setup is asking for trouble. It'll fail sooner or later, leaving you two servers that no longer agree on primary keys. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 15 days, processed 549,176,147 queries (411/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 - 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: InnoDB != FULLTEXT?
In the meantime, Jan, you don't have to commit your whole database to one table type. Consider using MyISAM tables where you need fulltext search and InnoDB for your other data. Arthur -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 19, 2002 12:02 PM To: [EMAIL PROTECTED] Subject: Re: InnoDB != FULLTEXT? Jan, - Original Message - From: Jan Steinman [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, November 19, 2002 4:37 AM Subject: InnoDB != FULLTEXT? I'm interested in using InnoDB, but am loathe to give up FULLTEXT indexing. Anyone (Heikki?) know why this is so, and if it is likely to be changed in the future? there is no technical hinder in porting fulltext indexes on top of InnoDB. But so far, the demand from commercial customers has not been enough to assign the resources for it. The port is in the long-term goals of the InnoDB TODO list. -- SQL SQL SQL SQL SQL SQL SQL SQL : Jan Steinman -- nature Transography(TM): http://www.Bytesmiths.com Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://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 - 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: delete where column = null
Nothing can be compared to null, not even null, which implies that MySQL is wrong in returning two rows in the SELECT. Your query needs to say IS NULL: delete from t where id = 1 and num is null; Arthur -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 19, 2002 12:26 PM To: [EMAIL PROTECTED] Cc: '[EMAIL PROTECTED]' Subject: RE: delete where column = null David, I get the same behaviour with mysql 3.23.47 (not max) and with mysql v 4.0.4-beta Without the index it works fine. Must be a bug Cheers, Andrew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 19 November 2002 16:45 To: [EMAIL PROTECTED] Subject: delete where column = null Hi, I'm trying to delete from a table where col = null, but it doesn't seem to work. I don't see anything in the manual that says that = can't be used in the where clause of a delete statment, but: mysql select * from t; ++--+ | id | num | ++--+ | 1 | NULL | | 1 | NULL | ++--+ 4 rows in set (0.00 sec) mysql delete from t where id = 1 and num = null; Query OK, 0 rows affected (0.00 sec) mysql select * from t where id = 1 and num = null; ++--+ | id | num | ++--+ | 1 | NULL | | 1 | NULL | ++--+ 2 rows in set (0.00 sec) This behavior seems very bizarre and counter-intuitive to me, but it's such an obvious bug that I'm afraid it must be a feature and not a bug. Can anyone help/explain? Using is null in the where clause works, of course, but would be relatively cumbersome to use. Db is 3.23.53-max-nt Thanks, -David p.s. in case it's relevant, there is a unique index on (id,num): create table t ( id integer not null, num integer, unique index id_num_idx(id,num) ); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problems with simple(?) query
SELECT * FROM foo WHERE id2=1 AND id2=2 (for example)... Given your structure, no row can satisfy your criteria. You are asking ID2 to have two values on one row, which is clearly impossible. Did you by chance mean: SELECT * FROM foo WHERE id2=1 OR id2=2 or alternatively SELECT * FROM foo WHERE id1=1 AND id2=2 Both these queries could return rows. hth, Arthur -Original Message- From: John Ragan [mailto:[EMAIL PROTECTED]] Sent: Monday, November 11, 2002 9:38 PM To: [EMAIL PROTECTED] Subject: Re: Problems with simple(?) query Hi, I'm trying to write what I am sure should be a simple query, but just can't get it to work! If I create a table called foo like this: create table foo ( id1 int(11) , id2(int 11) ); populate it with data and then try: SELECT * FROM foo WHERE id2=1 AND id2=2 (for example), I just get an empty set returned. An explain on the query gives an 'Impossible WHERE' message. Both id1 and id2 can have duplicate values in them, though the same combination of id1 and id2 cannot appear in the data (ie: id1=1, id2=1 ; id1=1, id2=2 is possible, id1=1, id2=1 ; id1=1, id2=1 is not possible), and it is possible that the values I search for will not be in the table at all. All I really want is to get the value for id1 where there are corresponding records which match all of my search parameters for id2. Anyone got any ideas how to manage this? It's beginning to drive me mad! Thanks, James to give the man a fish, or to teach him to fish. if you have a windows box for a front end, download corereader from http://corereader.com it connects to any data source, and it likes mysql a lot. it does quick point and click queries so you can, hopefully, find for yourself the logic errors such as has been pointed out in your query. have fun. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.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
RE: Newbie: interfacing to MySQL?
IMO you should begin with the command-line client mysql and get to know the various commands such as SHOW. Import some data from somewhere and play around with SELECT and UPDATE and joins. Once you have a basic understanding of these commands, then a GUI front end will be a lot more useful, IMO. Some good ones include DB Tools, MySQLFront and mysqlyog. But these are all interactive clients. It depends on what sort of application you have in mind, as well as what OS(es) you may be running it from. If you're on Windows then Access is a great front end. If you want to do web-apps, java or php or perl or even vbscript and IIS are viable. hth, Arthur -Original Message- From: Brynley [mailto:[EMAIL PROTECTED]] Sent: Monday, November 18, 2002 12:50 AM To: [EMAIL PROTECTED] Subject: Newbie: interfacing to MySQL? Hi all, Brand new to these parts so go easy on me! :) Right - I want to use MySQL - but doing things backward. What are you people using as a front end? I mean MySQL just sits in the background churning out stuff - but obviously the instructions have to come from somewhere. Someone suggested using Java (I want to use this using a browser over my intranet) but Java seems pretty tricky. Sorry if this is a how-long-is-a-piece-of-string type questions. If you need more detail just ask. All comments appreciated. Thanks for reading. Regards Brynley - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: MySql 4.1 Sub Selects and not stored procedures
I'm not sure where you're getting your information (Typically, db vendors recommend you use an exists clause, not a join when testing for the presence of child data because it's faster). Not to say that you're incorrect, just that in working for years with MS-SQL and Oracle and before that Sybase, I have never seen such advice. I just opened Query Analyzer to compare two queries similar to yours (in MS-SQL). Table sizes: Customers: 50151 SalesTravellers: 51195 Q1: select surname, givenName from salestravellers where not exists( select 1 from customers where salestravellers.customerid = customers.customerid) Q2: select salestravellers.surname, salestravellers.givenName from salestravellers left join customers on salestravellers.customerid = customers.customerid where customers.customerid is null For both queries: Rows returned: 2751 Time for Q1: 0:0:02 Arthur -Original Message- From: Greg Matthews [mailto:greg55;ozemail.com.au] Sent: Monday, November 11, 2002 7:38 AM To: [EMAIL PROTECTED] Subject: Re: RE: MySql 4.1 Sub Selects and not stored procedures hey, can i hijack my message back?...this thread is about the performance of subselects, not stored procedures. go write your own message :-)... sothe original question is if someone would be nice enough to answer.. Do any MySql coders writing subselects in 4.1 know whether EXISTS will outperform an equivalent query written as a join. = Typically, db vendors recommend you use an exists clause, not a join when testing for the presence of child data because it's faster. e.g. 4.1 version (faster?) - select person.person_id, person.name from person where exists ( select 1 from invoice where invoice.paid is null and invoice.person_id = person.person_id) vs 4.0 version (slower?) -- select DISTINCT person.person_id, person.person_name from person, invoice where person.person_id = invoice.person_id and invoice.paid is null Thanks, Greg. - Original Message - From: Dan Rossi [EMAIL PROTECTED] To: Victoria Reznichenko [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, November 11, 2002 11:04 PM Subject: RE: RE: MySql 4.1 Sub Selects damn , i read it was 4.1 i guess we have to wait a bit then, i wish i could program some c ++ to hurry it along a bit, i dont really have access to DB's like oracle to learn stored procedure stuff -Original Message- From: Victoria Reznichenko [mailto:victoria.reznichenko;ensita.net] Sent: Monday, November 11, 2002 11:00 PM To: [EMAIL PROTECTED] Subject: re: RE: MySql 4.1 Sub Selects daniel, Monday, November 11, 2002, 3:51:25 AM, you wrote: d will 4.1 hopefully have stored procedure functionality ? Nope. Stored procedures will be implemented around v5.0 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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: query crushes server
A Cartesian product will generate the number of rows in Table 1 times the number of rows in Table 2. For example if T1 has 1,000 rows and T2 has 10,000 rows your result set will contain 10,000,000 rows. Arthur - Eugene Bendersky wrote: May be this was reported already. I have windows 2000 using mysql version 4.0.3-beta-nt When I ran a query like select distinct a.* from tasks a, gnrlasoc b; mysqld-nt.exe crashes. I know this query makes no sense but it should not crush server Eugene Bendersky - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Distributing table-files to other machines
You can move the files and then set up symlinks to them in the /mysql/data/dbName directory. Arthur -Original Message- From: Michelle de Beer [mailto:michelledebeer;yahoo.com] Sent: Monday, November 04, 2002 8:21 AM To: [EMAIL PROTECTED] Subject: Distributing table-files to other machines At the moment, the files for tables in a database are all stored in mysql/data/databasename/. Is it possible to distribute the table-files to different discs, or even better, different machines, and still keep the flexability of having everything in one database. Example 1: Prefs: 1 Database 5 tables 5 discs Explanation: Put the first table on the same DISC as the database. The other tables have one DISC each. Example 2: Prefs: 1 database 5 tables 5 servers Explanation: Put the first table on the same SERVER as the database. The other tables have one SERVER each. Any thoughts? // Michelle __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.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
RE: self relation query help
Yes, at first it may seem that groups are just another form of artist, and that your searches would be simpler, but if you put them both in a single table, how do you model someone like say Paul McCartney, who was in the Beatles and Wings? Or Yo Yo Ma, who has guested with every important symphony orchestra in the world? Particularly in the jazz world, your model poses enormous problems. How could you model Charlie Haden, Miles Davis, the Brecker brothers, and so so? Peter's right. If you want a robust model, you have to break out groups and artists. If you want a historically accurate model, you may need a junction table GroupArtists, with FKs into Groups and Artists, and a pair of date columns indicating from and to -- which could overlap, again especially in jazz. Arthur -Original Message- From: Peter Brawley [mailto:peter.brawley;artfulsoftware.com] Sent: Saturday, October 26, 2002 6:43 AM To: Franklin Williams Jr.; [EMAIL PROTECTED] Subject: Re: self relation query help Franklin, the basic relational rules (http://hometown.aol.com/mbaddenda/art120.html, http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node1.html) say, decompose relationships like your artists groups to separate tables, and your example fits right in. PB - - Original Message - From: Franklin Williams Jr. [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 25, 2002 9:44 PM Subject: self relation query help I have a table with names in it where the names can function as 1 of 2 things - either an artist or a group. In order to make searching easy I wished to keep these names in one table. EG: searching for artist name, whether a group or soloist you would have to search only one table. The problem arises if the person wishes to get the names of the artists in the group or an artist also performs in multiple groups. I thought a self relationship of sorts solved this but then I could not figure out how to write the query. For instance the simplest query is: show me all the groups and artists in each group, or Show me the artists names in group U2. Easy if groups and artist names were in 2 tables, however... A table B table -- idNameid grp_id art_id -- 1 Joe 1 2 1 2 U2 2 2 3 3 Mike 3 2 4 4 John -- 5 Jane --- In the above sample data, table B is a join of sorts where the A.id functions as both the B.grp_id and B.art_id. EG: B.grp_id = A.id AND B.art_id = A.id I thought I had come up with a nifty little trick (using the same id in different fields to designate a distinction ) but alas. I can only select one column to get the names of the group and artists. I thought nested selects would solve this - maybe not - but no nested selects in mysql anyway. So..can this be done with this design or must I split the artists and groups into separate tables, forcing a multiple table search? I hoped column alias would solve this but I could not make it work using them. Confused? Good...so am I! Thanks in advance for any help Franklin Williams [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: how do you define a relationship?
Perhaps what is confusing you is that you never explicitly define a relationship as such: there are no relationship objects. Rather, at the table leve you define a foreign key that references the primary key of another table. Second, you must use the InnoDB or BDB table types to get foreign keys. Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the first columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. Finally, here is the (relevant part of the) syntax: [CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...) REFERENCES table_name (index_col_name, ...) [ON DELETE CASCADE | ON DELETE SET NULL] Just tack a clause like this onto the end of your CREATE TABLE statement. If your tables are already built, then you can use ALTER TABLE instead. Look in the manual under CREATE TABLE and ALTER TABLE. hth, Arthur -Original Message- From: Hammons Randy G SSgt 612 ACOMS/SCXX [mailto:Randy.Hammons;dm.af.mil] Sent: Monday, October 21, 2002 3:48 PM To: [EMAIL PROTECTED] Subject: RE: how do you define a relationship? Unfortunately this goes well beyond the scope of my original question. Although the inputs that you all have provided thus far have been a very good read, I still don't grasp the concept of defining a relationship. Is it simply referring to a primary key in a field other than the one where the key was defined? Or is there some other way to define a key that I don't see? All I'm looking for is a simple example, syntax excerpt, etc...that shows me (only the slow learner level) exactly how to define a relationship. -Original Message- From: Jan Steinman [mailto:Jan;Bytesmiths.com] Sent: Monday, October 21, 2002 12:07 PM To: [EMAIL PROTECTED] Subject: RE: how do you define a relationship? From: Arthur Fuller [EMAIL PROTECTED] Unfortunately that oversimplifies the situation. A least a few movies have more than one director. My motto: Generalize for the norm; specialize for the exception. The vast majority of movies have but one director. And I suspect that movies with multiple directors have but one who is primary. Keep a single director field, with a second NULL FULLTEXT field for additional directors. The NULL test for the normal case is much cheaper than what you have to go through by assuming that ALL movies have multiple directors! This is how books are handled in large databases. Each has a primary author, and may contain secondary authors. There is no assumption that large numbers of books have multiple, equal authors. This way, you can easily and conveniently list multiples as Speilberg (et. al.) without doing joins. An interested browser can then go further to find out who the others are. In the other case, each request for a director requires a JOIN. Of course, your particular application may be director-centric, like if you're building a special database to support research on directors. But if it's just a general-purpose movies database, why bog the whole thing down just to suit a few exceptional cases? SQL SQL SQL SQL SQL SQL SQL SQL -- : Jan Steinman -- nature Transography(TM): http://www.Bytesmiths.com : Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services : Newsletters now on-line at http://www.Bytesmiths.com/Newsletter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: ERD, Scripts, and Reverse Engineering
I work with DeZign as well and chose it for basically the same reason as you. I particularly love the fact that it supports domains. I have been communicating with the author and encouraging him to add specific InnoDB table types. I also suggested another feature to him that he really liked; we just have to come up with the right name for it. It's like a Domain except that it contains multiple columns, each defined like a Domain; for the moment I call it a meta-domain. For example, a meta-domain called CreditCardInfo would contain columns for CardNumber, CardHolder, ExpiryDate, DVV number and so on. Then you could add these columns to a table simply by adding the CreditCardInfo meta-domain to a table. Rick is considering it for inclusion in his next release. One other product I use is called dbScripter, from dkgAdvancedSolutions.com. It generates create and drop scripts as well as populate scripts for Oracle, Access and MySQL databases, using syntax profiles. I wrote a profile for MySQL that will be included in the next release. It costs around $95 US. An important feature that competitors do not have is the ability to create index scripts. Arthur -Original Message- From: Andy Blackshaw [mailto:support;getme.co.uk] Sent: Monday, October 21, 2002 8:16 AM To: [EMAIL PROTECTED] Subject: Re: ERD, Scripts, and Reverse Engineering I did the same seach and evaluation a couple of weeks back. Ended up buying Dezign for Databases, partly on cost (compared to Case Studio which seems the most comprehensive product), but also because the evaluation version allowed enough tables/releationships to do a realistic eval + good documentation. Reverse engineering is possible if you buy the additional Importerscripts, or ImporterMysql, and you can define your own logical types (called domains) or additional Mysql types if the set is not complete. As far as I can see there is no way to define the table type, but the rest is covered. So far I'm finding it stable and very easy to work with. Andy On 20 Oct 2002 at 19:07, Randy Garland wrote: Hello, all: I'm looking for a well-designed Windows-based database design tool that: 1) (Most importantly) Supports MySQL 4.x: a) supports all of its datatypes, including MEDIUMINT, ENUM, BLOB, AND TEXT b) lets the user decide between table types (ISAM v. InnoDB v BDB) 2) Creates ERDs (Entity Relationship Diagrams) with standard 'crow's feet' notation 3) From the ERDs, generates 'load' and 'drop' sql scripts 4) Can reverse engineer an existing MySQL database, creating an ERD from the database 5) Is, preferably, free or cheap I've looked and looked. I'm currently evaluating three products: 1) Dezign for Databases (out of the Netherlands) 2) CASE Studio version 2.10.1, by CHARONWARE (www.casestudio.com) 3) Database Design Studio, by chillisource (www.chillisource.com) All of them are commercial, and I have reservations about each one. None is MySQL-specific (they cover many db's), and as such, don't really make it easy for the user to easily define MySQL-specific data types. Also, only CASE includes Reverse Engineering. CASE is big bucks ($325), and the others are in the $100 range. I just don't want to have to create my ERDs in Visio, document the details in Excel, manually create scripts, and then have to update all three when the db needs change. Is there one, good, unified tool for MySQL that can help? Thanks in advance, Randy Garland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: how do you define a relationship?
Unfortunately that oversimplifies the situation. A least a few movies have more than one director. AI, for example: first half by Kubrick, second (lousy) half by Speilberg. Another example is The Twilight Zone, which had four directors. This calls for a junction table between Movies and Directors, called MovieDirectors and having MovieID and DirectorID. Perhaps this isn't refined enough either. Consider Clint Eastwood, who stars, directs and produces. Woody Allen, who does all that and also writes. A fuller (heh heh) resolution of the problem might look like this: Create a new table called Roles, which contains rows for Actor, Director, Producer, D.O.P. and whatever else is of interest. Combine the existing Directors and Actors tables into a single table called Talent. Create a junction table called TalentRoles, with FKs referencing Talent and Roles. Clint would have rows for Writer, Director and Actor. Now create a table called MovieRoles with foreign keys into TalentRoles and Movies. Then you add as many people to a movie as you want, nw whatever roles are legitimate for those people. I.e. you couldn't add Clint as a writer. hth, Arthur Fuller -Original Message- From: bwarehouse [mailto:bwarehouse;yahoo.com] Sent: Sunday, October 20, 2002 5:38 PM To: Randy Hammons; [EMAIL PROTECTED] Subject: RE: how do you define a relationship? You are on the right track.. but kill your relationships as you have them currently defined... think of it this way... (one to many).. you have movies and you have directors.. it should be one director to many movies seeing that a movie normally only has one director.. so take you primary key from directors and place that foreign key in the movies table. Remember: one director has many movies, not one movie has many directors.. BOTTOM LINE. Make a field in your movies table exactly the same as the primary key in the directors table, but don't autonumber it if that is what is in the primary key of the directors table, just make it the same type( ie. Number, text, date, etc). and make sure it has the exact same specs as the directors primary key field except the autonumber type; and make sure the perimeter is allow duplicate key is set to yes, if you index the field in the movies table), but you don't have to index the foreign key in the movie table if the primary key of the directors table is already indexed. Just make the foreign key, allow duplicates 'YES' in the movie table.. Hope this helps.. Later b.ware -Original Message- From: Randy Hammons [mailto:rathlon1;cox.net] Sent: Sunday, October 20, 2002 8:43 AM To: [EMAIL PROTECTED] Subject: how do you define a relationship? I think I've firmed my theoritical understanding of relationships enough to be dangerous. Now for the logical parthow do you actually define a relationship in mysql? For instance... table movies columns (movie id(pk) , movie title , year filmed) table directors columns (director id(pk), movie id(fk), total movies) Here I have a primary key in each table which I have defined in my table structure. I also have a foreign key in the directors table, but where did I define that to the database? Is this a sort of relationship? Like a one to many? I've noticed in some reading that certain keys are refered to by their respective names ( column - whatever ) and the relatioship column is defined as ( column - relwhatever ), is this the proper way to define a relationship? Is the primary/foreignkey, relationship concept something that is used by the database to replicate data or are they design tools used to develope an independent, non repetitive data structure? Someone please help...I'm so confused. Thanks so much - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Suggestions for breaking large table into smaller?
If your contacts are all North American, and this is a typical query, you may well profit significantly from normalizing the address data. To do so, create a Cities table and a States table (perhaps call it Regions if you need to account for CDN provinces). The Cities table would look like: CityID int auto-increment primary key CityName char(30) or whatever RegionID int (references Regions) The Regions table would look like: RegionID int auto-increment primary key RegionName char(30) or whatever CountryID int (references Countries if you need it) CountryID int auto-increment primary key CountryName char(30) Now you can move all the city, state and country info out of your contacts table, leaving only the CityID behind. Index the column. In this scheme your query would do a simple join and find the 'NJ' rows much more quickly. hth, Arthur -Original Message- From: James Taylor [mailto:jtx;hatesville.com] Sent: Thursday, October 17, 2002 10:08 PM To: [EMAIL PROTECTED] Subject: Suggestions for breaking large table into smaller? Curious if this is normal behaviour, or if my table is just not put together very well. I have a table consisting of approximately 4.5 million contacts, listed by company name/city/state/zip - The system this is running on is a PowerEdge 1400sc, 1ghz p3, 1 gig ram, 18gb scsi drive. These are the results of the following query: SELECT COUNT(*) from usa_phone_list where state = 'NJ'; +--+ | COUNT(*) | +--+ | 157104 | +--+ 1 row in set (29.62 sec) That seems like an awfully long time to me... When doing an explain on that query, the 'type' is coming back as index, and 'rows' is reporting 4886146.. Gr. Currently, I'm using 1 large table to store basically everything. I'm trying to figure out whether I should use some sort of normalization, and also I was thinking of basically creating 50 different tables, one for each state... Is there any downside to having that many tables, or is it unnecessary and should a table with millions of entries be able to run this query just as fast as one with a couple hundred thousand. Has anyone done anything like this before, and if so... What turned out to be the best route to take? I've never worked on a database this large before, and am not a professional DBA in the least. Just looking for some suggestions here on how to handle this. Thanks for all your help. James Taylor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Normalization sql
Ooops! You just threw a new wrench into the works! A video may have multiple titles. In this case you must distinguish the original from its presumably foreign-translations (or whatever). In other words, you might store the master as La Cage aux Folles and the English subtitled version as something else, which implies another 1:M table, residing above Titles in the hierarchy. For simplicity let's call it Videos, and then rename Titles to VideoTitles, giving it a foreign key referencing Videos. Thus in the Videos table we have entry 12345, La Cage Aux Folles and in the VideoTitles table we have an entry The Cage of Fools or whatever, with an FK referencing 12345. A title search would then require interrogation of two tables, which in itself isn't terrible since one could UNION the titles from both tables, but possibly this is not the best model. Perhaps the Videos table should contain no Title info, and all that data should be exported to the Titles table. I'd have to give that some thought before deciding. How do I create a junction table? Example, between Genres and Titles, how do I add the to PK fields to TitleGenres? Do I just create the TitleGenres table and add the TitleID GenreID field and does the database know they are linked? The database knows nothing except what is permitted. (Hope Dostoevsky didn't design the database:-) You front end application should anticipate this and present pretty controls for the user. The table TitleGenres implies a reference to both Titles and Genres, so each of these could be presented as a dropdown list, or in a web app you could pass in the PK from the Titles table and have your insert code write the received parameter into the table. (By sticking to Titles rather than shifting the relationship to Videos, you could account for the strange belief in France that Jerry Lewis is funny :-) Creating a junction table is simple. Just add the columns you need and declare them as foreign keys, specifying their reference-tables (InnoDB). If you're using MyISAM tables the responsibility falls entirely on your front end. You may want to modify the Stars table to reference Videos rather than Titles, since the stars remain the same even if you translate the film into Russian or Swahili. hth, Arthur - Original Message - From: John Chang [EMAIL PROTECTED] To: Arthur Fuller [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 14, 2002 7:26 PM Subject: Re: Normalization sql Studios (StudioName, StudioID) Genres (GenreName, GenreID) Titles (VideoTitle, Details, StudioID, GenreID, BitRateID, TitleID) Actors (F_Name, L_Name, ActorID) Stars (TitleID, ActorID) TitleGenres (TitleID, GenreID) So, if I just use theses tables I will be OK if the video has multiple titles, multiple actors, multiple genres? How do I create a junction table? Example, between Genres and Titles, how do I add the to PK fields to TitleGenres? Do I just create the TitleGenres table and add the TitleID GenreID field and does the database know they are linked? Thank you. At 09:50 AM 10/14/2002 -0400, Arthur Fuller wrote: You're starting off ok, just missing the links between the tables. I would suggest for simplicity though that you change the names of your PKs to reflect their table, i.e. Title (VideoTitle, Details, TitleID). Otherwise once you do multi-table queries joins you will have to specify the table names every time. A studio makes zero or more titles (videos). A film belongs to zero or more genres (your answer may be that a film belongs to precisely one genre) A title stars one or more actors (you may plan on storing only one, but how will you decide in a case such as Eight Women?) I would say you need a junction table between Titles and Actors so you can handle multiple stars. I'm not sure what BitRate means but I guess it is an attribute of the Title. Studios (StudioName, StudioID) Genres (GenreName, GenreID) Titles (VideoTitle, Details, StudioID, GenreID, BitRateID, TitleID) Actors (F_Name, L_Name, ActorID) Stars (TitleID, ActorID) If you decide that a Title can belong to more than one Genre, then you need a junction table there too: TitleGenres (TitleID, GenreID) This change would involve removing the GenreID from the Titles table. This design lets you easily find all the titles made by a studio, all the titles featuring a given actor (even if she is the co-star rather than the star), all the titles in a genre and even compound queries such as all the MGM thrillers featuring actor XYZ. hth, Arthur - Original Message - From: John Chang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 14, 2002 8:49 AM Subject: Normalization sql I've read a bunch about normalization in MySQL and still can't do it very well. What I want to normalize is videos (Title, Studios, Actors, Genre, bitrate). These are the tables and fields I think it needs. Is this normalized? Thank
Re: Why must I quote fields now?
It's always a bad idea to name your columns using keywords. - Original Message - From: olinux [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 14, 2002 5:42 AM Subject: Why must I quote fields now? Hi all, I have just added a field (order) to a table with about 12,000 records. Now I cannot select or insert unless I quote the field names in the query i.e. This returns - you have an error near 'order ...' SELECT id,pub_date,last_update,type,region,author,title,summary,content,keywords,fi lename,order FROM articles WHERE id = 23692 but this works fine. SELECT `id`,`pub_date`,`last_update`,`type`,`region`,`author`,`title`,`summary`,`co ntent`,`keywords`,`filename`,`order` FROM `articles` WHERE `id` = 23692 I have no idea why, has the table been corrupted? I'm running MySQL 3.23.48 Thanks, olinux __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.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
Re: Type
The MyISAM table type should be fine for what you are doing, and is faster than the InnoDB type. Basically, you only need transactions in situations where a) money is involved or b) you will be updating multiple tables and all updates must be guaranteed successful, or none. hth, Arthur - Original Message - From: John Chang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 14, 2002 7:51 AM Subject: Type 1) Is there a de facto type that is used? All I will be doing is creating a database for use on the web for dynamic webpages. Basically doing searches to the back-end MySQL database. I figure I don't need to use transactional type since I will not be adding any information via a front-end. I will probably load a file whenever I need to add more data. Am I correct? Or is it always best to use a transactional type? And which transactional type is better/reliable/robust to use? 2) If later on I change my mind and create a webpage as the front-end and start inputing data this way will I need a transactional type? Thank you. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Normalization sql
You're starting off ok, just missing the links between the tables. I would suggest for simplicity though that you change the names of your PKs to reflect their table, i.e. Title (VideoTitle, Details, TitleID). Otherwise once you do multi-table queries joins you will have to specify the table names every time. A studio makes zero or more titles (videos). A film belongs to zero or more genres (your answer may be that a film belongs to precisely one genre) A title stars one or more actors (you may plan on storing only one, but how will you decide in a case such as Eight Women?) I would say you need a junction table between Titles and Actors so you can handle multiple stars. I'm not sure what BitRate means but I guess it is an attribute of the Title. Studios (StudioName, StudioID) Genres (GenreName, GenreID) Titles (VideoTitle, Details, StudioID, GenreID, BitRateID, TitleID) Actors (F_Name, L_Name, ActorID) Stars (TitleID, ActorID) If you decide that a Title can belong to more than one Genre, then you need a junction table there too: TitleGenres (TitleID, GenreID) This change would involve removing the GenreID from the Titles table. This design lets you easily find all the titles made by a studio, all the titles featuring a given actor (even if she is the co-star rather than the star), all the titles in a genre and even compound queries such as all the MGM thrillers featuring actor XYZ. hth, Arthur - Original Message - From: John Chang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 14, 2002 8:49 AM Subject: Normalization sql I've read a bunch about normalization in MySQL and still can't do it very well. What I want to normalize is videos (Title, Studios, Actors, Genre, bitrate). These are the tables and fields I think it needs. Is this normalized? Thank you. Table (Fields) Title (VideoTitle, details, id) Studio (Name, id) Actors (F_Name, L_Name, id) Genre (Name, id) Bitrate (rate, id) - 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
Linux GUIDs
Does MySQL support GUIDs? Is there any code around that is equivalent to the built-in M$-SQL function NewID(), which returns a GUID? TIA, Arthur - 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: Is there Examples and Documents on joining tables?
Once you understand the logic of joins the rest is pretty straightforward, since in terms of execution the sequence in which you do the joins is irrelevant. (Performance is another issue, quickly resolved by specifying the smallest tables first.) Supposing that you have tables Products, Suppliers and Categories, which Products having foreign keys referencing Suppliers and Categories... SELECT ProductID, ProductName, SupplierID, SupplierName, CategoryID, CategoryName FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID; You can just keep building up such a query according to the number of tables you need to drag into the operation. Given the above query, you may well want to change the order of table references, but that doesn't intrude upon the logic and unless you have a very large number of rows in one or more tables, the performance difference will barely be noticeable. There are several GUI tools that let you perform multi-table selects using drag-and-drop techniques (c.f. DBTools, IMO one of the finest, with the added benefit that it remembers relatinships that you declare within it). Then you can inspect the code it writes and tweak it to suit using EXPLAIN. hth, Arthur - Original Message - From: damovand [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Thursday, October 03, 2002 12:31 PM Subject: Is there Examples and Documents on joining tables? Is there a document that compiles examples on different ways of joining two or more tables? I found a few on http://www.mysql.com/doc/en/JOIN.html but they do not cover joining more than two tables. Thanks for any suggestions. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is there Examples and Documents on joining tables?
Yes there are. First do as you have written, although your syntax is a bit wrong but the email compiler forgives such stuff :-) SELECT table1.bunch, table2.of, table3.stuff FROM table1 INNER JOIN table2 ON table2.some_id INNER JOIN table3 ON table3.some_id WHERE table1.some_id = table2.some_id AND table1.some_id = table3.some_id; should have been: SELECT table1.bunch, table2.of, table3.stuff FROM table1 INNER JOIN table2 ON table2.some_id = table1.FK INNER JOIN table3 ON table3.some_id = table1.FK WHERE table1.some_id = table2.some_id AND table1.some_id = table3.some_id; IMO you confuse the logic if you combine joins with wheres. You make it very hard to understand what's occurring and even harder to debug it. Store all your join logic in the join clauses, then reserve the where for precisely what you mean. For example: SELECT Table1.Stuff INNER JOIN Table2 ON Table1.FK = Table2.PK INNER JOIN Table3 ON Table2.FK = Table3.PK (etc.) WHERE Table1.PK = myVar In this sort of construction, the logic is far more apparent than in a construction which blurs the joins and wheres. Just my $.02. Arthur - Original Message - From: Josh Trutwin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 03, 2002 3:57 PM Subject: Re: Is there Examples and Documents on joining tables? Is there a document that compiles examples on different ways of joining two or more tables? I found a few on http://www.mysql.com/doc/en/JOIN.html but they do not cover joining more than two tables. Thanks for any suggestions. I wish there were more examples as well! I use this for multi-table joins: SELECT table1.bunch, table2.of, table3.stuff FROM table1 INNER JOIN table2 ON table2.some_id INNER JOIN table3 ON table3.some_id WHERE table1.some_id = table2.some_id AND table1.some_id = table3.some_id; Not sure if this the best way or not, but it works... Anyone with more experience care to contribute? Are there optimum ways to join multiple tables? Josh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is there Examples and Documents on joining tables?
Send me create-table scripts and population scripts and I'll do this for you. But on rereading your message I'm guessing that your normalization is wrong. You have more than one multi-valued dependency. Your example description is either wrong or insufficient. T1 (presumably the parent of all parents) has a PK of let's say A. T2 has an FK into T1 := A. T3 should not have any trace of A or B or C, all of which are derivable. I hope this won't strike you as pedantic, but you get into severe problems when you store multiple multi-valued keys in any given table. This is the difference between 3NF and 4NF+BCNF. Jargon aside, suppose the following: T1 = Customers, with columns CityID, RegionID and CountryID Should you choose this model, you either: a) force the front end to intelligently preclude absurd options; or b) face the prospect of such entries as Toronto, New York, Japan. IMO this is obviously incorrect. The most you need to store is the least you need to store. Instead of this method, consider a cascading alternative: Cities -= Regions -= Counties All you need to store in this model is the CityID, since this is enough to distinguish Springfield, Ohio from Springield, Nebraska. a) you don't need to carry the cascading FKs and b) they are counterproductive even should you choose to carry them, for they either permit nonsense or you force the front end to carry mucho additional logic that is not properly part of the front end. Instead consider intelligent multi-table queries that retrieve for example Vancouver, BC, Canada and Vancouver, WA, USA. This makes your front end smarter and your back end more resilient. Examples of this are abundant. A front end that permits you to transfer employee 123 from department 234 to department 234 are IMO bad, beneath consideration of serious programming. We have to be a lot smarter than that. Given a screen that allows employee-transfer, the least it could do is preclude transfer to the same department! Lest I sound like some proselitizer, I only do so because I have made this mistake a dozen times and paid the price. At this late stage in the game, I now fully appreciate the virtues of BCNF+4NF and hope never to make this mistakes again. Normalize to the max and you're guaranteed to be correct. Codd is no fool. He is a genious and the more you read him the more sense he makes. I wish I were an order of magnitude close to his perceptions. I look back on my errors and realize I should have read him more closely, and obeyed his edicts even if I couldn't comprehend them at the time! - Original Message - From: damovand [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql [EMAIL PROTECTED] Sent: Thursday, October 03, 2002 8:08 PM Subject: Re: Is there Examples and Documents on joining tables? Hi Arthur, Thanks for your answer and your example. Your example is very good but I can't map to what I'm trying to do. As you suggested I have to understand how inner join is implemented in MySQL. The query that I'm working on selects fields from three tables, in this way, Table_1 has columns A,B,C supplies fields ( A,B, C) Table_2 has columns A,T,U supplies fields (T,U) Table_3 has columns A,B,C,X,Y,Z supplies fields (X,Y,Z) Table_1 and table_2 have a strange relationship with each other, one row in table_3 could match with a number of rows in table_1 if table_1.A = table_3.A and table_1.B=table_3.B and (table_1.C=table_3.C or table_3.C = *). That's what I can't get to work. I worked on this on MSSQL and wrote a query that does the above but I cannot find the equivalent query for it in MySQL. For I've split the query in two parts and put the results back together in my application. But I would look at DBTools and see if I can learn something from it. Thanks again for you advice. [EMAIL PROTECTED] wrote: Once you understand the logic of joins the rest is pretty straightforward, since in terms of execution the sequence in which you do the joins is irrelevant. (Performance is another issue, quickly resolved by specifying the smallest tables first.) Supposing that you have tables Products, Suppliers and Categories, which Products having foreign keys referencing Suppliers and Categories... SELECT ProductID, ProductName, SupplierID, SupplierName, CategoryID, CategoryName FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID; You can just keep building up such a query according to the number of tables you need to drag into the operation. Given the above query, you may well want to change the order of table references, but that doesn't intrude upon the logic and unless you have a very large number of rows in one or more tables, the performance difference will barely be noticeable. There are several GUI tools that let you perform multi-table selects using drag-and-drop techniques (c.f.
Re: MySQL as a desktop DB
I don't think Access is crap at all. In fact I think it's a) the best RAD front end going for SQL Server and perhaps for MySQL too. I use Access 2000 and 2002 + MyODBC to create front ends to MySQL databases and the combination works great. In less than one morning I successfully ported the Northwind sample application to MyOBC+MySQL. I had to fix a couple of queries that wouldn't translate, but other than that it worked like a charm. And one of the best reasons to go with such a combination is that Access can save named queries, which can then be combined. For example: qryProductsList: SELECT products.ProductID, products.ProductName, products.UnitPrice, products.SupplierID FROM products ORDER BY products.ProductName; qrySuppliersList: SELECT suppliers.SupplierID, suppliers.CompanyName FROM suppliers ORDER BY suppliers.CompanyName; qryCombinedProductsAndSuppliersList: SELECT qryProductsList.ProductName, qryProductsList.UnitPrice, qrySuppliersList.CompanyName FROM qrySuppliersList INNER JOIN qryProductsList ON qrySuppliersList.SupplierID = qryProductsList.SupplierID; MySQL by itself doesn't provide this kind of capability. I find it enormously valuable. Just my $.02. Arthur - Original Message - From: David Lloyd [EMAIL PROTECTED] To: Adam Parker [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, September 24, 2002 8:37 PM Subject: Re: MySQL as a desktop DB Adam, Obviously MySQL would have advantages if I intended to use it as a server database with concurrent users. But is it faster than Access in the single-user environment, when dealing with large databases? MySQL in a single user environment is a pain in the arse because of its lack of an Access Like front end that is actually useful and featureful (Access is crap, but it's a better database frontend than currently exists). (mysql, query) DSL -- I reniad lin ne mor, nuithannen In gwidh ristennin, i fae narchannen I lach Anor ed ardhon gwannen (Soundtrack LOTR - and it's not LATIN) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Copying a Table
To where? Another table? More information is required. - Original Message - From: Shaun Bramley [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, September 28, 2002 1:18 PM Subject: Copying a Table Hello all, I have yet to find this bit of information within the documentation. Is it possible to copy the contents of a table? If so how? Shaun mysql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Basic SQL join question
SELECT * FROM Projects INNER JOIN KeywordLink ON Projects.Id = KeywordLink.Pid INNER JOIN Keywords KeywordLink.Kid = Keywords.Id WHERE Keyword LIKE '%historical%' AND Keyword like '%scenic%'; 1. Note the single quotes. 2. You can place the join logic in the WHERE clause but I prefer the clarity obtained by keeping it in JOIN clauses and using the WHERE only to contain the include if logic. hth, Arthur -Original Message- From: Adam Randall [mailto:[EMAIL PROTECTED]] Sent: Saturday, September 21, 2002 5:18 AM To: [EMAIL PROTECTED] Subject: Basic SQL join question Okay, I've thought about this every way I can conceive of, but I cannot figure out the sql query logic involved in joining three tables together. Here is what I am trying to do: I have three tables: keywords keywordlink projects keywords has these fields: id keyword keywordlink has these fields: id pid kid projects has a lot of fields, but it's primary key is ID What keywords holds is the keywords used in the various different tables in the database. keywordlink associates a project with several keywords: example keywords: id | keyword 1 | landscape 2 | historical 3 | scenic example keywordlink: id | pid | kid 1 | 1 | 2 2 | 1 | 3 3 | 2 | 1 4 | 2 | 3 example projects: id | name 1 | example 2 | extra Now, what I am trying to do is basically search the keywords database for keyword names, and then return a list of project names that are associated with those keywords. If the keywords were stored in the projects database, this is basically what I would want it to do (assume all the keywords are stored in a field called keywords in the projects table): SELECT * FROM projects where keywords like %historical% and keywords like %scenic%; This would return to me the projects that have historical and scenic in the keywords field. Now, how do I do this same operation with it broken out like I have above. The reason I am not storing the keywords in the projects table is that it would be quite a chore in changing the keywords in the system if I did that (modify one keyword, modify all the projects, etc). Anyone have any words of advice for me? Adam. -- --- Adam Randall http://www.xaren.net/ [EMAIL PROTECTED] http://nt.xaren.net/ [EMAIL PROTECTED] Macintosh users are a special case. They care passionately about the Mac OS and would rewire their own bodies to run on Mac OS X if such a thing were possible. -- Peter H. Lewis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Basic SQL join question
Oops! Quite right -- ON is necessary after the word JOIN. That was written in the email compiler :-) Sorry I should have proofread it before hitting Send. Arthur - Original Message - From: Josh Trutwin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 24, 2002 12:46 PM Subject: RE: Basic SQL join question SELECT * FROM Projects INNER JOIN KeywordLink ON Projects.Id = KeywordLink.Pid INNER JOIN Keywords KeywordLink.Kid = Keywords.Id WHERE Keyword LIKE '%historical%' AND Keyword like '%scenic%'; Out of curiousity, does the order matter? I have a JOIN with about 6 tables, some are very small, some are quite large. I know with LEFT JOIN if I switched the order of the tables around, the queries could speed up or slow down dramatically. Is the same true with INNER JOIN? Still trying to completely grasp JOINs, getting closer though... Also, shouldn't the second INNER JOIN have an ON keyword? Josh 1. Note the single quotes. 2. You can place the join logic in the WHERE clause but I prefer the clarity obtained by keeping it in JOIN clauses and using the WHERE only to contain the include if logic. hth, Arthur -Original Message- From: Adam Randall [mailto:[EMAIL PROTECTED]] Sent: Saturday, September 21, 2002 5:18 AM To: [EMAIL PROTECTED] Subject: Basic SQL join question Okay, I've thought about this every way I can conceive of, but I cannot figure out the sql query logic involved in joining three tables together. Here is what I am trying to do: I have three tables: keywords keywordlink projects keywords has these fields: id keyword keywordlink has these fields: id pid kid projects has a lot of fields, but it's primary key is ID What keywords holds is the keywords used in the various different tables in the database. keywordlink associates a project with several keywords: example keywords: id | keyword 1 | landscape 2 | historical 3 | scenic example keywordlink: id | pid | kid 1 | 1 | 2 2 | 1 | 3 3 | 2 | 1 4 | 2 | 3 example projects: id | name 1 | example 2 | extra Now, what I am trying to do is basically search the keywords database for keyword names, and then return a list of project names that are associated with those keywords. If the keywords were stored in the projects database, this is basically what I would want it to do (assume all the keywords are stored in a field called keywords in the projects table): SELECT * FROM projects where keywords like %historical% and keywords like %scenic%; This would return to me the projects that have historical and scenic in the keywords field. Now, how do I do this same operation with it broken out like I have above. The reason I am not storing the keywords in the projects table is that it would be quite a chore in changing the keywords in the system if I did that (modify one keyword, modify all the projects, etc). Anyone have any words of advice for me? Adam. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Gui Install and Setup for Win2K
We were told by David Axmark of MySQL AB to stop using MySQLGui and switch to MyCC. MySQLGUI is apparently dead. - Original Message - From: David Kramer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 16, 2002 12:52 PM Subject: MySQL Gui Install and Setup for Win2K Can some one point me in the direction of some good install and setup Doc for MySQL GUI. Im running the GUI on Win2K, but my DB server is RH7.2 w/ stock Mysql(not sure version number). Also is the FLTK absolutely necessary to run the GUI on Win2K? Any other hints or insights is greatly appreciated. Thanks, DK David Kramer Software Developer Reflect.com Direct: 415.369.4856 Cell: 650.302.7889 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Thanks to MySql and it's Developers
I can only think that's because you haven't used a Windows system. My principal client's Windows system has been up for over a year. Win2K Server. I brought the server down a couple of times to update table structures etc., but the OS has never failed us. Not once. Arthur - Original Message - From: Terence [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, September 15, 2002 9:38 PM Subject: Thanks to MySql and it's Developers To MySql AB and it's developers, I am from Sunway College in Malaysia, and we implemented MySql about a year and a half ago. It was a shift from Access. Since then we have never looked back. MySql runs our intranet (4-5000 hits per day), our website (2000-6000 hits per day), our hostel booking and reservations system, HRMS systems and a host of others. Today we reached 100days uptime, thanks to your excellent product. Uptime: 8693908 Threads: 18 Questions: 102664100 Slow queries: 543 Opens: 110507 Flush tables: 1 Open tables: 64 Queries per second avg: 11.809 OS: Redhat Linux 7.3 (Couldn't imagine 100 days on a windblows system) MySql: 3.23.49a-log Thanks guys! Rgds Terence Web Developer Sunway College - Original Message - From: Miguel Angel Solórzano [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, September 16, 2002 7:07 AM Subject: Re: mysql-max-4.0.3beta on win98. mysqld-max.exe just hangs! At 21:56 15/9/2002 +0300, Heikki Tuuri wrote: Hi, Heikki, The problem here seem to me that the InnoDB keys set are under the Winmysqladmin section instead of [mysqld]!. Hi! - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Sunday, September 15, 2002 3:33 PM Subject: mysql-max-4.0.3beta on win98. mysqld-max.exe just hangs! Hi, I want to use InnoDB but on my win98 using binary mysql-max-4.0.3beta the mysqld-max.exe just hangs. no output. I have read the entire Manual for install InnoDB table type instructions. set up my.ini as follows.. #---start of my.ini [WinMySQLAdmin] Server=C:/MYSQL4/bin/mysqld-max.exe innodb_data_home_dir=C:/MYSQL4/data innodb_data_file_path=C:/MYSQL4/data/ibdata/ibdata1:100M:autoextend:max:100 0 M #---end on my.ini I have also tried with [mysqld] starting from the command promt. no use. but mysqld.exe as well as mysqld-opt.exe both work fine. Except that i want InoDB. any help is much appreciated. Thanks in advance. it would help if some Windows 98 user who has this problem would compile MySQL from source using the Microsoft Visual C++. It is relatively easy. The main thing is to got to the subdir \mysql\strings\noMASM and copy the files there to the parent directory \mysql\strings. Use the Developer Studio or whatever it is called nowadays and open the workspace mysql.dsw in the \mysql directory. Then build all. You will get dfifferent mysqld.exe's to \mysql\client_release and \mysql\client_debug. Run mysqld-max.exe inside the Dev Studio debugger and look where it hangs. A MySQL AB employee reported that SHOW INNODB STATUS crashed in 3.23.52 and Win 98 + Netware. When we find out what is the problem there, that might help other Win 98 users. I have the impression that Microsoft never got 95/98/ME very stable. Using NT/2000/XP is recommended. NT and XP are rock-solid in my own experience. Kale Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel Angel Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ São Paulo - Brazil ___/ 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 - 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
Re: Re: MS Access and mySQL]
You didn't mention whether they are planning on keeping the NT box, but I assume that they are, and that what you want to do is leave the Access app working, but point it to the Linux-MySQL QUBE. I have been there and done that (well, except for the QUBE part -- I run Mandrake 8 on an old P-233. I second the choice of DB Tools. There are other tools with import wizards but this one works very well. The only problem is that neither DB Tools nor any other similar tool that I came across is capable of importing the index information. All you get is the data. In one of my apps there are 400+ tables, and it's a considerable pain to have to manually create the indexes. However, there is a Windows-based shareware tool called dbScripter (from DKG Advanced Solutions). It only costs something like $50 US for the full version. I may have the price wrong since I bought it a couple of years ago (and have received two or three free updates since). dbScripter doesn't do direct imports. As the name implies, it generates scripts which you can run to achieve the same end. It breaks down a job into 3 possible scripts: structure, data and indexes. It works with profiles so that you can direct it to change a currency or yes/no column to the appropriate MySQL data type. It took me about 10 minutes to create a profile for MySQL. I generated the script to create all the indexes for me and then ran it in MySQL, no problem. I have several applications that I ported from Access MDBs to MySQL, and I continue to use Access with them, thanks to MyODBC. I simply set up a DSN on my Windows box that points to the Linux box and the rest was a piece of cake. I dispute Nicholas's contention that a lot of Access queries will not work in MySQL. Technically, he is correct: if you try to run a query that says SELECT TOP 10 * FROM mytable, that of course won't work in MySQL; nor will Access's crosstab queries. However, if you leave them in Access and execute them against a MySQL database, almost all of them continue to work. There is an added advantage to this route, as well. Leaving them in Access preserves their existence as objects with names, meaning that you can create a couple of queries and use them in subsequent queries. This is a very powerful feature of Access, and there is no reason to abandon it when you move the data to MySQL -- unless of course you are also abandoning Access. If you have any specific questions, feel free to ask. Arthur Original Message From: Nicholas Stuart [EMAIL PROTECTED] Subject: Re: MS Access and mySQL To: [EMAIL PROTECTED], [EMAIL PROTECTED] I have been there done that with old access data, but no forms. Your plans should be focused around what you know and what your systems are tied to. Since its an NT box you could go with pretty much any language ie java/vb/c++. VB would be the obvious choice for ease of transfering the Access VBA forms to straight VB. The only thing you have to be careful is the access querys. A lot of them will not work on MySQL and the funny thing is a lot of them will not work on M$'s on SQL server :) go figure. As far as the data goes the easist thing I found to transfer it into mysql is a program called dbtools. You can find it at: http://www.dbtools.com.br/EN/ It's fairly easy to use and has a great import wizard. If you have an specific questions let me know and I'll try my best to answer them. -Nick MySQL Newsgroup (@Basebeans.com) wrote: Subject: MS Access and mySQL From: =?iso-8859-1?q?Jonathan=20Coleman?= [EMAIL PROTECTED] === Hi. I have a situation where a church I am doing some volunteer work for has a MS Access (office 97) database running on NT. They are (woohoo) upgrading to a QUBE (linux box) for a server which has mySQL installed (and I can upgrade it etc..) My job therefore is to a) port MS Access data and queries to mySQL database b) use the MS Access forms to access the data and queries through ODBC. Is there anyone who can point me in the right direction, either to where the searchable discussion archives are, OR even better, someone who has gone through this before? Cheers Jon http://digital.yahoo.com.au - Yahoo! Digital How To - Get the best out of your PC! - 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:
Re: MS Access and mySQL
That has not been my experience, in fact my experience has been completely the opposite. As my first test case I ported the Northwind sample database to MySQL. I had to make a few changes here and there, most notably to some queries, but also including (and I forgot to mention this in my previous reply on this subject) adding a column of type TimeStamp to every table in the database. This is necessary so that you can read auto-increment values. Next, I took my current app, with about 94 tables, and ported it. The production app is in MS SQL 2000, but I wanted to port it because I wanted a proof-of-concept to show management. The absence of sprocs was a big problem (come on, 4.1 :-) but I worked around it. No one but me and a few testers is using this version. The production app remains in MS SQL. Finally, I took my killer-database, whose tables number 400+. Fortunately, with dbScripter in hand I didn't have to create say 1000 indexes by hand! It ported well and my Access front end talks blissfully to it. The MySQL part resides on a P233 running Mandrake, set up for InnoDB. This may simply be high praise for MyODBC, but my Access app has no difficulty at all talking to the database. Arthur - Original Message - From: [EMAIL PROTECTED] To: Arthur Fuller [EMAIL PROTECTED] Sent: Wednesday, August 28, 2002 1:57 PM Subject: [Fwd: RE: MS Access and mySQL] wodya think? Original Message From: Nicholas Stuart [EMAIL PROTECTED] Subject: RE: MS Access and mySQL To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED], [EMAIL PROTECTED] I would strongly urg you not to use Access as the front end. The biggest problem is that the Jet Engine/Access backend is pretty much completly different then MySQL and you will run into problems with table structure and the like. Also, using access as the front end has been extremly slow when ever I tried it. You will however need to download the MyODBC driver if you wish to access MySQL through VB. -Nick -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jonathan, If you are going to be completely converting the Access db to MySQL, and just using Access as the frontend, you will need to download MyODBC: http://www.mysql.com/downloads/api-myodbc.html You can download either the stable or development release. There is also a FAQ that will answer your questions there about how to setup almost exactly what you are referring to. http://www.mysql.com/products/myodbc/faq_toc.html This FAQ is for the development release (3.51.03) but can also be used for the stable release (2.50.xx). I recently had to do the same thing with a Paradox database for a client, and have had relatively few issues following the documentation. After downloading and installing MyODBC you will be able to export the database directly to your Linux MySQL, however you will lose your table structures for the most part and will have to do some ALTER TABLE statements to put your keys back and the majority of your fields set to their proper type. HTH, Bryant Hester Juxtapose, inc. - -Original Message- From: Jonathan Coleman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 12:40 AM To: [EMAIL PROTECTED] Subject: MS Access and mySQL Hi. I have a situation where a church I am doing some volunteer work for has a MS Access (office 97) database running on NT. They are (woohoo) upgrading to a QUBE (linux box) for a server which has mySQL installed (and I can upgrade it etc..) My job therefore is to a) port MS Access data and queries to mySQL database b) use the MS Access forms to access the data and queries through ODBC. Is there anyone who can point me in the right direction, either to where the searchable discussion archives are, OR even better, someone who has gone through this before? Cheers Jon http://digital.yahoo.com.au - Yahoo! Digital How To - - Get the best out of your PC! - - 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 -BEGIN PGP SIGNATURE- Version: PGPfreeware 7.0.3 for non-commercial use http://www.pgp.com iQA/AwUBPWzH6UlWu7/HFp4nEQJOGwCgr5af7wIi1AcbpP1rih/vABg/zb8An13w 4Tm8I6eugdNnVhLYZ+CcTYq2 =Z0ov -END PGP SIGNATURE- - 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
Re: Php query from sql
In the simplest case, LanguageSkill and City are columns in the Persons table. (That's the wrong way to do, IMO, but it is the simplest case.) Suppose Language Skill is an integer column and City a char(50). So your query becomes: SELECT * FROM Persons WHERE LanguageSkill = 5 AND City = 'London'; The more complicated answer presupposes that you have normalized the tables Persons - fairly obvious, PK is PersonID, with a CityID integer column referencing the table Cities Languages - PK is LanguageID integer, languagename char(30) PersonLanguages - n rows for every person who speaks a language. FKs PersonID and LanguageID, with a tinyint or ENUM column for SkillLevel. Cities - PK is CityID Now you need to join the four tables to get your answer: SELECT * FROM Persons INNER JOIN PersonLanguages ON Persons.PersonID = PersonLanguages.PersonID INNER JOIN Languages on PersonLanguages.LanguageID = Languages.LanguageID INNER JOIN Cities ON Persons.CityID = Cities.CityID WHERE Languages.Language = 'English' AND PersonLanguages.SkillLevel = 5 AND Cities.CityName = 'London'; I find that a good way to build such queries for PHP is first get everything right in myCC or MySQL-Front etc., and then to paste the query into the php script. After that, it's simple to massage it into an appropriate statement. If you begin with a standard php template, most of the work is already done for you. hth, Arthur - Original Message - From: Eve [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, April 28, 2002 3:48 PM Subject: Php query from sql Hello everybody, I'm quite new with all this php mysql cooperation thing but somehow I already managed to set up mysql database and I'm able to also make query's based on any keyword... Now I wonder how to add to this basic query also option to select some concrete data. For example in the database I have section Languages, where people can choose is their English laguage skill 'poor', 'good', 'very good'. Now I would like to add to the general query additional field where it could be possible to select only data of persons with very good English language skills. For example I would like to be displayed only persons who are living in London (this will be keyword) and are speaking English very well... Please can anyone help me? Best regards, Eva - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ORDER BY RAND() not working
I don't have your exact setup installed so cannot verify this, but how about rewriting your statement a little? I just tried this in mysql and it works fine, so it may from php. Let me know :-) Change the query to: SELECT *, Rand() as MyOrder FROM products WHERE sale_price IS NOT NULL ORDER BY MyOrder; hth, Arthur - Original Message - From: Jamie Tibbetts [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 11, 2002 1:14 PM Subject: Re: ORDER BY RAND() not working $id = mysql_query(SELECT * FROM products WHERE sale_price IS NOT NULL ORDER BY RAND() LIMIT 3,$link) or die(mysql_error()); PHP code: $result = mysql_query(SELECT * FROM products WHERE sale_price IS NOT NULL ORDER BY RAND() LIMIT 3) or die(mysql_error()); The error message is as follows: You have an error in your SQL syntax near 'RAND() LIMIT 3' at line 1 Jamie Jamie Tibbetts.[EMAIL PROTECTED] Epigroove .http://www.epigroove.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
Size of BLOB and TEXT columns
In SQL DDL, what are the maximum sizes of the various BLOB and TEXT column types? TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB and the corresponding TEXT types? I've searched the manual but couldn't find these specs. At what point should I choose BLOB over TINYBLOB, and so on? TIA, Arthur - 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
Maximum JOINED tables
I have searched the manual without success. I want to know the maximum number of tables MySQL permits to be JOINed in a single SELECT statement. TIA, Arthur - 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: Get the next AUTO INCREMENT value for a column
IMO the desire to know this answer suggests that something is wrong with the design. Again IMO, a PK must be absolutely meaningless. Any attempt to impost meaning on it is by definition wrong. By my lights, the only PK you want to know is the one just added. That's enough information to let you start adding rows to child tables. And that value you can easily obtain. Arthur - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Harrison C. Fisk [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, May 04, 2002 12:34 PM Subject: Re: Get the next AUTO INCREMENT value for a column At 12:29 -0400 5/4/02, Harrison C. Fisk wrote: Actually I think the SHOW TABLE STATUS like 'TABLENAME' command can show you the next auto_increment value. There is no guarantee that this value will actually be used for the next AUTO_INCREMENT value. Suppose another client issues the following query: ALTER TABLE tbl_name AUTO_INCREMENT = 123456; If you were relying on what SHOW TABLE STATUS said, you'd be hosed. :-) Although I can say I wouldn't really ever use it except for personal use, not in an actual application. Harrison - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Son Nguyen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, May 04, 2002 12:16 PM Subject: Re: Get the next AUTO INCREMENT value for a column At 9:01 -0700 5/4/02, Son Nguyen wrote: CREATE table test1 (ID int NOT NULL AUTO INCREMENT, name text, value text, Primary Key (ID)); INSERT into test1 (name, value) values (test1, value1); INSERT into test1 (name, value) values (test2, value2); DELETE from test1 where ID=2; How can obtain the CURRENT value of next ID AUTO INCREMENT value (which is 3 right now) Son Nguyen mysql, query You can't. AUTO_INCREMENT values are not available until you generate them. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Migration from windows to linux
I use a shareware tool called dbScripter (www.dkgas.com), which is very cheap to license. It lets you generate scripts that will create table structures, indexes and table-loads. It comes with syntax profiles for SQL Server, Oracle and Access, and you can also create your own profiles. I created one for MySQL and can move data quite easily. I just generate the scripts in Windows against whichever back end, then run them in Linux. It's quite simple. I have licensed it for so long that I forget whether the free download is crippled in any way. It might just have a time limit. If that's all you have to do, I would suggest dbScripter. But is it really all you have to do? Somehow I suspect that this is only the beginning. Do you then have to connect existing Windows apps to the Linux back end? Arthur - Original Message - From: Sonia [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, April 18, 2002 5:00 AM Subject: Migration from windows to linux Hello list, I`m a newbie to mysql and linux and have at the moment a necessity concerning to both that I`m not able to handle due to my lack of knowledge in these fields. The fact is that I need to migrate a mysql database and its tables from windows to linux and haven`t found anything in the web that could give me a hint so could someone tell me something about how to migrate a database from one platform to another? I really would appreciate the help! Looking forward at least one answer ;-) , thanks a lot in advance. Sonia. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: dummy queries + csv dump
Maybe it would be enough if you created a little syntax window on your GUI and an Execute button beside it, so you could see what was going to be sent to the engine before clicking Execute. Just a thought, Arthur - Original Message - From: Petre Agenbag [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 17, 2002 8:43 AM Subject: dummy queries + csv dump Hi, I would like to know if it is possible to run a dummy qury on a table, check for the number of possibly affected rows, and then, according to specific conditions, execute the query for real. The reason why I ask this is as follow ( in case you can't do it, but you have another suggestion); I wrote a little generic table edit/view/browse utility in PHP and today something horrible happened, when I changed an entry in a row, the entire table was updated with that data, as if I had run an update set x=y , yet, I assure you, I did have a where id=$id caluse in the sql. That's why I would like to first run a dummy ( specially for update or delete sql's ) and only if the affected rows are 1, should it execute the query. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I thought MySQL Supported Relationships
Perhaps I fail to catch the humour in your response, or the flavour of this newsgroup, but I find your answer in no way helpful, and beyond that quite insulting to Access. 1. I have developed applications in Access comprising over 1000 tables, some of which contain millions of rows, with numerous complex relationships and C++ components that interfaced to instruments such as scales, scanners, etc. I am not the only one to push Access that far. 2. To answer Andrew's question, the answer is No. MySQL does not support defined relationships. The InnoDB extension provides this support. 3. Unfortunately, as of this writing neither MySQL nor InnoDB supports defined queries (Oracle and SQL Server call them Views). Allegedly, MySQL 4.1 will support stored procedures (and I for one hope that views make it into the build). Arthur - Original Message - From: Jonathan Hilgeman [EMAIL PROTECTED] To: 'Andrew Kuebler' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 2:43 PM Subject: RE: I thought MySQL Supported Relationships Actually, no. The addiction to MySQL has caused many relationship break-ups. There's probably even a database with that information out there somewhere. Probably a MySQL database, too. Meanwhile Access supports relationships because it blows so hard that people can't wait to get away from it and home to their families. - Jonathan -Original Message- From: Andrew Kuebler [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 23, 2002 11:35 AM To: [EMAIL PROTECTED] Subject: I thought MySQL Supported Relationships Hello Everyone. I'm still fairly a newbie to MySQL, but I could have sworn I read somewhere that MySQL supported relationships just like Access. I've searched the entire manual, and cannot find anything on the subject. Does MySQL support relationships, and if so does anyone know where I can find any info on the topic? Thanks! Andrew - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MANY to MANY] relationship with MySQL ???
This is the classic case for a junction table (aka bridge table, associative table). It sits between Artists and Songs; let's call it PlaysOn. Artists: ArtistID: autoincrement ArtistName: varchar DOB: date etc. Songs: SongID: autoincrement SongName: varchar Duration: in seconds, say etc. PlaysOn: SongID: FK into Songs ArtistID: FK into Artists Given this structure you can: a) add any number of players to a song; b) select any song and all its players; c) select any player and all the songs she played on; d) select a song title and see all the versions you have; e) add another layer atop this called Recordings without disturbing anything beneath (now the same song by the same artist(s) can appear on multiple recordings). You could add a ComposerID (FK into Artists) to the Songs table, but that corners you because what do you do about Lennon McCartney, Rogers Hammerstein and so on? To go to that level of detail you need another bridge table, SongComposers: SongComposers: SongComposerID: autoincrement SongID: FK into Songs ArtistID: FK into Artists With this table, you could add to the list of selects with a union that grabs any song Artist 123 either played on or helped compose. hth, Arthur - Original Message - From: Robbie Newton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 17, 2002 10:25 PM Subject: [MANY to MANY] relationship with MySQL ??? Hello all, [~~I guess I could do something like MySQLselect FROM Songs where artists contains $currentArtist~~] I have a sampling section on a site that I am working on and am trying to find out how to display a table of the artists featured in that song. The sample section works as follows: Go to the samples page and choose an album to sample. Returns the list of song names that result from the album you chose click on one of the song names to open a new window with the shockwave file that streams the song. The client has asked me to add the (one or many) artists featured in that song in the window that loads up. I am not sure how to set this up. It would be a many to many relationship I think... Artists Table (one given artist could be featured on many different songs) [many] to Songs Table (one given song could have many featured artists) [many] My first thout of how to set this up is to just enter comma delimited data into a field in the song database called, artists. But when I query it I don't know how I could make that work. I guess I could do something like MySQLselect FROM Songs where artists contains $currentArtist Would that work? Is there a such thing as contains. (kinda new to SQL). Thanks for your help, Robbie - 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
