Re: advice for blob tables?
This one time, at band camp, [EMAIL PROTECTED] wrote: I don't feel the implementation direction this article takes is good. It uses single row binary storage, which anyone who has had to deal with large files knows is a definate issue. According to your method of storing binary data in BLOB fields, rather than LONGBLOBs you would have over 11,000 rows for a single 700MB iso. I am not sure how MySQL handles blobs internally but I would assume it is like most other databases and adds some sort of delimiter to the data and has to seek to it to distinguish columns. This means a 700Mb file stored in 64k chunks would need over 11,000 file seeks per row read. LONGBLOBs are the way forward here I feel. A single table containing the relevant metadata along with the binary data. There is no need for any normalization as this is a one-to-one relationship. having over 11,000 rows for a single file I dunno, I would like to see some benchmarks on this. Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
I would love to see an implementation with 1 row for large data that works well. The main issues I had were that mysql has a default max packet size limit (I think it used to be like 16MB (mysql 3.23) - 1GB Mysql 4 - Not sure v5. Alot of people don't have control over those settings in their environment. If you try to do a single insert/query larger than that, the query would fail. Also it was causing the webserver to buffer a large amount of data. Here's some numbers to tickle your fancy.. I downlaoded a file from the ftp gateway to a storage implementation I've done (production). FTP CLIENT -- NETWORK FRONTEND VLAN (100FDX) -- FTP SERVER (JAVA) FRONTEND -- NETWORK BACKEND VLAN (100FDX) -- MYSQL STORAGE NODE I grabbed an approximately 230MB file from a different box (on the same frontend vlan) in 40 seconds, at a speed of about 5800K/sec .. Also I logged into the storage node to see how mysql was doing: 5179 mysql 15 0 12528 4644 2108 S10.8 0.5 0:04 mysqld It was bouncing between 9% and 19% during the transfer and the machine is a 2 X P3/933. There's very little chance a real internet user can download at that type of speed, so a single mysql storage node can handle a large amount of concurrent users/activity. [EMAIL PROTECTED]:~$ ftp ftp.dbfs Connected to ftp.dbfs 220 DBFS. Name (ftp.dbfs): XX 331 User name okay, need password for XX Password: 230 User logged in, proceed Remote system type is UNIX. ftp bin 200 Command TYPE okay ftp cd XX/filez/apps 200 Directory changed to /XXx/filez/apps ftp get Visio2K3.exe local: Visio2K3.exe remote: Visio2K3.exe 200 Command PORT okay 150 File status okay; about to open data connection 226 Closing data connection 235075910 bytes received in 39.9 secs (5.8e+03 Kbytes/sec) ftp Rules for testing: - it must be a double jump (as in real world implemenation) - you must push the data between the webserver and mysql server over a tcp/ip network - you must download the data from the webserver from another box via tcp/ip (no loopback/local socket connections - does not clearly represent a real scalable solution) Looking forward to some numbers. On Wed, 25 Apr 2007, Kevin Waterson wrote: This one time, at band camp, [EMAIL PROTECTED] wrote: I don't feel the implementation direction this article takes is good. It uses single row binary storage, which anyone who has had to deal with large files knows is a definate issue. According to your method of storing binary data in BLOB fields, rather than LONGBLOBs you would have over 11,000 rows for a single 700MB iso. I am not sure how MySQL handles blobs internally but I would assume it is like most other databases and adds some sort of delimiter to the data and has to seek to it to distinguish columns. This means a 700Mb file stored in 64k chunks would need over 11,000 file seeks per row read. LONGBLOBs are the way forward here I feel. A single table containing the relevant metadata along with the binary data. There is no need for any normalization as this is a one-to-one relationship. having over 11,000 rows for a single file I dunno, I would like to see some benchmarks on this. Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
I don't feel the implementation direction this article takes is good. It uses single row binary storage, which anyone who has had to deal with large files knows is a definate issue. On Sat, 21 Apr 2007, Kevin Waterson wrote: This one time, at band camp, Michael Higgins [EMAIL PROTECTED] wrote: So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? This tutorial deals with images and BLOBs. It should get you on the right path. http://www.phpro.org/tutorials/Storing-Images-in-MySQL-with-PHP.html Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: advice for blob tables?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] I don't feel the implementation direction this article takes is good. It uses single row binary storage, which anyone who has had to deal with large files knows is a definate issue. Just wanted to thank you all for your input thus far. I'm proceeding with caution, but with heightened confidence that I'll be moving forward with a bit of a clue. Cheers, -- Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: advice for blob tables?
-Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Friday, April 20, 2007 3:16 PM To: Michael Higgins Cc: mysql@lists.mysql.com Subject: Re: advice for blob tables? At any rate - based on my experience with the Sybase system I managed, I would advise you to consider this when designing your database: instead of having one gigantic table to store every document, try to design a system that allows for splitting the data across multiple identical tables. My company offers an archive service for our customers, and we do exactly this. The metadata is stored in a known location, include and index to a table which points to the database/table where the blob is. One such pointer is marked active so it receives all new documents. This allows us to roll over the databases at a certain size, or move them around as needed. Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
Hi Michael, You are not the only one with this problem. We work in the print/ publishing sector and have many customers that need this type of functionality as well. So this is not going to help you right away, but we have consulted with MySQL and are starting an initiative for the implementation of BLOB streaming for MySQL. In fact, the first announcement will go out on Monday, but you can already checkout some of the details of our plans at a web-site we have setup for the new project: http://www.blobstreaming.org I will also be holding a BoF at the MySQL Users Conference to discuss the project (http://www.mysqlconf.com/cs/mysqluc2007/view/e_sess/14125). We are hoping to get as many people from the community involved as possible (both potential users and developers). So if you are there, please join us! Best regards, Paul Hello, all -- I want to set up a database for document storage. I've never worked with binary files stored in tables. So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? I feel like I need to become a bit more expert in database design but I simply don't really know where to start. Like, what 'engine' should I use in this case? I haven't a clue Thanks! -- Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
Michael, here's what I can tell you - Some people will advise you against storing documents in tables - claim it makes the data harder to serve up, etc. I agree it's quite simple to set up a folder structure with your files on the filesystem and point Apache or IIS to it while tracking metadata in MySQL, but there may well be other destinations/uses for your documents where actually having them in BLOBs would be quite handy. There's no technical reason you can't do it, obviously, and I worked for several years at a midsize newspaper where we stored literally everything in gigantic Sybase databases. Story, ad, page layouts, postscript graphics files, etc. Everything. And by and large it worked quite well. Nearly 1TB by the time I left, and a colleague at another newspaper had near 3TB. A big plus was the centralized workflow and tracking it allowed, but that will depend largely on the quality of the application software you have. At any rate - based on my experience with the Sybase system I managed, I would advise you to consider this when designing your database: instead of having one gigantic table to store every document, try to design a system that allows for splitting the data across multiple identical tables. You could do this with the MERGE engine in MySQL; that has MyISAM tables underneath, with a view of sorts that presents all the underlying tables as one. You could also do it by having multiple DocumentTable001 tables structured identically, with another table to track the document tables as well as the current insert path. This is obviously more complex but doable. MyISAM is not transactional; InnoDB is, but doesn't offer MERGE. InnoDB can be configured to store one file per table in the latest versions of MySQL, and I'd recommend you go that route. Having the data split across multiple table files (in MyISAM or InnoDB) will allow you to check, optimize, and on bad days recover, your data in a more incremental fashion than a single large table. It would also potentially allow you to distribute the data across multiple physical storage devices for improved speed - and while that may not be a concern up front, some day it likely will be if you intend to store things for long. You could even distribute data across multiple database servers or clusters if you structured it properly. You could also take advantage of MySQL's compressed table type for archival data, which would save disk space and potentially improve read speed if your data compresses well. Anyway, hope this helps. Let me know if I can answer any other questions about such a setup. Dan On 4/20/07, Michael Higgins [EMAIL PROTECTED] wrote: Hello, all -- I want to set up a database for document storage. I've never worked with binary files stored in tables. So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? I feel like I need to become a bit more expert in database design but I simply don't really know where to start. Like, what 'engine' should I use in this case? I haven't a clue Thanks! -- Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
Here's a good php implementation, you can implement the concept in any language you like: http://www.dreamwerx.net/phpforum/?id=1 On Fri, 20 Apr 2007, Michael Higgins wrote: Hello, all -- I want to set up a database for document storage. I've never worked with binary files stored in tables. So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? I feel like I need to become a bit more expert in database design but I simply don't really know where to start. Like, what 'engine' should I use in this case? I haven't a clue Thanks! -- Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
This one time, at band camp, Michael Higgins [EMAIL PROTECTED] wrote: So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? This tutorial deals with images and BLOBs. It should get you on the right path. http://www.phpro.org/tutorials/Storing-Images-in-MySQL-with-PHP.html Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advice on multilingual databases?
Here is my suggestion, but like every other thing I post here I urge you to take it with a grain of salt. Set up the following tables (described in rough terms, not in SQL): topic_index topic_id autonumber ... Whatever else you need to keep track of that identifies a topic topic_detail topic_id long integer primary index topic_language indexed (perhaps) parent_topic_id indexed topic_language_text (utf-8, collate utf8_unicode_ci) ... Whatever else you need that is specific to this language / topic combo Now, whenever you add a topic you make an entry for a new topic, you make a record in topic_index that identifies the topic uniquely. Make corresponding entries in topic_detail using the topic_id that was just assigned to topic_index's topic_id: there will be one such record for each language. Now you can locate the topic itself in the topic_index table and quickly pull all of the corresponding records for the different languages from topic_detail. You can also easily find all topics that have entries in a particular language, or that do not have entries in a particular language, etc. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Zembower, Kevin [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 9:36 AM To: mysql@lists.mysql.com Subject: Advice on multilingual databases? I'd like some advice on setting up databases that contain entries for the same item in more than one language. For instance, here's what I currently do for a table that contains the same topics translated into English and Arabic: CREATE TABLE `TOPIC` ( `TopicID` int(11) NOT NULL auto_increment, `Topic-en` text NOT NULL, `Topic-ar` text character set utf8 collate utf8_unicode_ci NOT NULL, `ParentTopicID` int(11) NOT NULL default '0', PRIMARY KEY (`TopicID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Topics for text-based hierarchies' AUTO_INCREMENT=76 ; In this table, 'Topic-ar' is the Arabic translation of the English 'Topic-en.' If this were required to also be in Spanish and French, I'd add columns for 'Topic-es' and 'Topic-fr' similar to 'Topic-en' above. I'm wondering if there are any other ways to store and access multilingual data. Can anyone suggest other ways they've dealt with this task, and the pros and cons of their approach compared to mine? Thank you in advance for your advice and suggestions. -Kevin Kevin Zembower Internet Services Group manager Center for Communication Programs Bloomberg School of Public Health Johns Hopkins University 111 Market Place, Suite 310 Baltimore, Maryland 21202 410-659-6139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advice Required
What are specifications of your DB. How much of data you have. How mant transactions you will be getting daily. Without these details it will be difficult to answer ur question. But for a centralised database server, I guess 1G RAM and 2CPU will be a good configuration to start with. sujay -Original Message- From: Vinayak Mahadevan [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 27, 2005 9:25 AM To: mysql@lists.mysql.com Subject: Advice Required I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. Regards Vinayak -- Vinayak Mahadevan Systems Engineer Magtorq Pvt. Ltd. 58-C, Sipcot Industrial Complex Hosur - 635-126 Mobile: 98 94 90 61 61 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice Required
Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 04:55:13: I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. MySQL can run on almost nothing. The question is not what system you need, but what performance you want. I think you *could* run MySQL on a P200, Win 98, 128Mb ram, 40Mb disc. But the performance you would get would be seriously disappointing. You need to think what size of database you want, how many queries and updates per second you will need, and how complex your queries will be. However, since MySQL is freely available, why not just download it, install it on your development machine, and run a few tests. The only real measurement of performance is actual tests: predictions often err, both high and low. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice Required
[EMAIL PROTECTED] wrote: Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 04:55:13: I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. MySQL can run on almost nothing. The question is not what system you need, but what performance you want. I think you *could* run MySQL on a P200, Win 98, 128Mb ram, 40Mb disc. But the performance you would get would be seriously disappointing. You need to think what size of database you want, how many queries and updates per second you will need, and how complex your queries will be. Thanks for the response I am planning to run it on a x205 series IBM Server which right now has 256 mb ram but will be upgraded to 1 gb. And the maximum number of connections at any point of time will be say around 10 Regards Vinayak -- Vinayak Mahadevan Systems Engineer Magtorq Pvt. Ltd. 58-C, Sipcot Industrial Complex Hosur - 635-126 Mobile: 98 94 90 61 61 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice Required
Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 11:28:51: [EMAIL PROTECTED] wrote: Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 04:55:13: I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. MySQL can run on almost nothing. The question is not what system you need, but what performance you want. I think you *could* run MySQL on a P200, Win 98, 128Mb ram, 40Mb disc. But the performance you would get would be seriously disappointing. You need to think what size of database you want, how many queries and updates per second you will need, and how complex your queries will be. Thanks for the response I am planning to run it on a x205 series IBM Server which right now has 256 mb ram but will be upgraded to 1 gb. And the maximum number of connections at any point of time will be say around 10 That sounds reasonably competent hardware. But it is not the number of connections that matters, it is the number and complexity of queries. One connection can generate a massive query which will lock out others; idle connections consume a small amount of memory but no other resources. I can only suggest you try to set up a representative test load and see if the performance is adequate for you. If performance is not adequate, after having examined your slow queries carefully and checked your indexing, extra ram is the first hardware upgrade to do. However, I think newcomers to MySQL are frequently surprised by its performance once properly indexed; don't spend money on extra ram until you have tried out a real (or simulated) test. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on Database Schema Design
At 15:53 +1000 on 06/07/2004, Ligaya Turmelle wrote about Re: Advice on Database Schema Design: I think I get what you are trying to do and it sounds good (though I am still a beginner). The only thing I can think that might cause a problem is since you have more then a couple of tables you MAY have to write joins for all of them . Only you would know if you can do that and/or be comfortable doing it. To get a stronger idea though of your design I would need to see either a logical ER diagram or all of the creates (I am very visual so I like pictures). *shrugs* whether or not you want to pass those along is up to you. Respectfully, Ligaya Turmelle Thanks for the reply. I'm still at the stage where I am deciding what I want the tables to look like before doing the CREATE TABLE statements. I do not think I will need JOINS since it is going to be more of a WHERE t1.x=t2.x (and SELECT t2.y [from the t2.x selected row]) type situation where I am selecting the fields form the support tables based on the link (Foreign Key) field not actually merging/matching tables. Robert A. Rosenberg [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I am converting a form that was originally designed to be Email Submitted into a Submit-to-PHP-Page Form (which will then insert the data into a MySQL Database). I expect no problems in actually scanning the submission to extract the data but I have some questions on the design of the Tables that I will need to define to store the data. I have done some research and have come up with a tentative design that I want to post here for critiquing. Here is what I have come up with. Each form will get assigned a sequence number (SubmitterID) that will identify the form submission in all the tables (thus linking them). There are a number of TextAreas where the user is requested to enter free form replies to questions. I am thinking that these should go into a separate table as Text fields of the correct size with SubmitterID as the Primary Key. That keeps the data away from the main table and thus only accessed when needed/requested as well as not bloating the size of the main table or slowing its retrieval/processing. There are a number of blocks of CheckBoxes on the form. While I could, in theory, use a SET column type to store them, I get the impression that a better way is to create a Many-to-One table for each block with the total contents of each row being the CheckBoxID and SubmitterID (in that order) as the Primary Key. A Index for SubmitterID would also be defined. This way I can do a WHERE on either Column and get Index Usage as opposed to needing to do a row-by-row lookup. I have the impression that doing a WHERE over a SET Column (especially when I'm looking for more than one value) is not a good or efficient idea. The CheckBoxID would map to a 3rd table to get the actual CheckBoxName. There are also two Select Tables (one for US States and one for Countries). While the States are passing the USPS 2-Letter State Codes, the Countries are passing the full Country Name as their OPTION VALUE=. I want to make a State Table and a Country Table using respectively the 2-Letter Code and a sequential reference number (which I will revise the Country OPTION tags to use as their VALUE) as the Primary Key with the State or Country Name as the other column. To create the reference key and populate the OPTION pages with it I plan to take the current HTML for these tags and read them into a Text Editor where I will then alter them into MySQL Insert Commands to populate the Table. Then using a one-shot QuickDirty PHP page, I will read the table and recreate the Option Statements which would the be CutPasted into the original HTML code replacing the old versions of the tags. The states go though the same Turn into INSERT Commands but there is no need for post processing or HTML Tag replacement. Of course all the Table Pointers would be defined as Foreign Keys to insure Referential Integrity. Am I making any mistakes in my design or am I on the correct track. I welcome any critique of my design or advice on how to improve it. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on Database Schema Design
I think I get what you are trying to do and it sounds good (though I am still a beginner). The only thing I can think that might cause a problem is since you have more then a couple of tables you MAY have to write joins for all of them . Only you would know if you can do that and/or be comfortable doing it. To get a stronger idea though of your design I would need to see either a logical ER diagram or all of the creates (I am very visual so I like pictures). *shrugs* whether or not you want to pass those along is up to you. Respectfully, Ligaya Turmelle Robert A. Rosenberg [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I am converting a form that was originally designed to be Email Submitted into a Submit-to-PHP-Page Form (which will then insert the data into a MySQL Database). I expect no problems in actually scanning the submission to extract the data but I have some questions on the design of the Tables that I will need to define to store the data. I have done some research and have come up with a tentative design that I want to post here for critiquing. Here is what I have come up with. Each form will get assigned a sequence number (SubmitterID) that will identify the form submission in all the tables (thus linking them). There are a number of TextAreas where the user is requested to enter free form replies to questions. I am thinking that these should go into a separate table as Text fields of the correct size with SubmitterID as the Primary Key. That keeps the data away from the main table and thus only accessed when needed/requested as well as not bloating the size of the main table or slowing its retrieval/processing. There are a number of blocks of CheckBoxes on the form. While I could, in theory, use a SET column type to store them, I get the impression that a better way is to create a Many-to-One table for each block with the total contents of each row being the CheckBoxID and SubmitterID (in that order) as the Primary Key. A Index for SubmitterID would also be defined. This way I can do a WHERE on either Column and get Index Usage as opposed to needing to do a row-by-row lookup. I have the impression that doing a WHERE over a SET Column (especially when I'm looking for more than one value) is not a good or efficient idea. The CheckBoxID would map to a 3rd table to get the actual CheckBoxName. There are also two Select Tables (one for US States and one for Countries). While the States are passing the USPS 2-Letter State Codes, the Countries are passing the full Country Name as their OPTION VALUE=. I want to make a State Table and a Country Table using respectively the 2-Letter Code and a sequential reference number (which I will revise the Country OPTION tags to use as their VALUE) as the Primary Key with the State or Country Name as the other column. To create the reference key and populate the OPTION pages with it I plan to take the current HTML for these tags and read them into a Text Editor where I will then alter them into MySQL Insert Commands to populate the Table. Then using a one-shot QuickDirty PHP page, I will read the table and recreate the Option Statements which would the be CutPasted into the original HTML code replacing the old versions of the tags. The states go though the same Turn into INSERT Commands but there is no need for post processing or HTML Tag replacement. Of course all the Table Pointers would be defined as Foreign Keys to insure Referential Integrity. Am I making any mistakes in my design or am I on the correct track. I welcome any critique of my design or advice on how to improve it. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on Database Scheme
David Blomstrom wrote: I put a screenshot of my database organization online at http://www.geoworld.org/database.gif It features four tables, focusing on Continents, Nations, States and Counties. Notice that Continents and Nations share a CCode (continent codes) column So how are you going to put Russia in both Asia and Europe? How are you going to put Turkey in both Asia and Europe? Egypt in Africa and Asia? Nations and States share a NCode (nation codes) field, and States and Counties share a SCode (state codes) field. My country does not have counties, and instead of states it has provinces. How are you going to deal with that? Answering my questions is answering your own question :-) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advice on Database Scheme
Hi, Can I ask what you used to render that .gif ? Looks like phpMyAdmin but I have never seen that feature in phpMyAdmin.. Thanks, Andrew -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Friday 04 June 2004 16:19 To: David Blomstrom Cc: [EMAIL PROTECTED] Subject: Re: Advice on Database Scheme David Blomstrom wrote: I put a screenshot of my database organization online at http://www.geoworld.org/database.gif It features four tables, focusing on Continents, Nations, States and Counties. Notice that Continents and Nations share a CCode (continent codes) column So how are you going to put Russia in both Asia and Europe? How are you going to put Turkey in both Asia and Europe? Egypt in Africa and Asia? Nations and States share a NCode (nation codes) field, and States and Counties share a SCode (state codes) field. My country does not have counties, and instead of states it has provinces. How are you going to deal with that? Answering my questions is answering your own question :-) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on Database Scheme
--- Jochem van Dieten [EMAIL PROTECTED] wrote: David Blomstrom wrote: --- Jochem van Dieten [EMAIL PROTECTED] wrote: So how are you going to put Russia in both Asia and Europe? How are you going to put Turkey in both Asia and Europe? Egypt in Africa and Asia? Egypt is in Africa, not Asia. Usually the part on the eastern side of the Suez canal is regarded as being part of the Middle East, hence Asia. Russia and Turkey pose a problem, but if I can't figure it out, I'll just choose Europe OR Asia, then explain it in the text. How about having just 3 tables: AreaTypes: AreatypeID AreaType 1 Continent 2 Country 3 City 4 etc. Areas: AreaID Area AreaTypeID 1 Europe1 2 France2 3 Germany 2 4 Paris 3 AreaHierargy: AreaID ParentID 1 NULL 2 1 3 1 4 2 Just walk the tree :) Wow, that looks like a great scheme. I'll probably replace the numerals with ISO codes, FIPS codes and letter abbreviations, so your last table might look something like this: AreaHierargy: AreaID ParentID eu eurasia fr eu gm eu fr001 fr And then when I want to add data, like the names of capital cities, population, etc. I can just create additional tables and join them to these using shared ID fields, right? Thanks; I think I'll give this a try right now. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advice
Hi Alexander, Perhaps the console application would be better suited for you. That way the response is always ASCII text, and you only need to type the commands. In case you haven't tried the console before ... To start the console application: 1. Open a DOS prompt (in windows) or a command shell (in Linux) 2. type mysql and press enter. Regards, Matt -Original Message- From: Alexander Pfingstl [mailto:[EMAIL PROTECTED] Sent: Saturday, February 07, 2004 4:29 AM To: [EMAIL PROTECTED] Subject: Advice Hi, I am a blind user and for me it would be very helpful if I could use this GUI. At the moment I have got the problem, that you have to click on every item. There are no short cuts and it is not posible to navigate using the tab-key or context-menue. Or the arror-keys. Perhapts you can include this in future versions. It would make it much easier for blind people to use this GUI under windows. If you need more help or info about special problems of blind people or the screenreaders, than feal free to contact me. Best regards, Alexander -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.576 / Virus Database: 365 - Release Date: 1/30/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.576 / Virus Database: 365 - Release Date: 1/30/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice needed for high volume of inserts
-Forwarded by Peter J Milanese/MHT/Nypl on 01/20/2004 02:36PM - To: STE-MARIE, ERIC [EMAIL PROTECTED] From: Peter J Milanese/MHT/Nypl Date: 01/20/2004 02:31PM cc: [EMAIL PROTECTED] Subject: Re: Advice needed for high volume of inserts It'll work. I do slight less on the way of inserts. What I do is dynamically generate the tables within my entry code, and merge tables based on the query. Good for large log parsers. Be aware that this can break greatly if it's a non-redundant live feed (to mysql). I think that's a problem anywhere though. Mysql should not hold you back though. Peter J. Milanese -STE-MARIE, ERIC [EMAIL PROTECTED] wrote: - To: [EMAIL PROTECTED] From: STE-MARIE, ERIC [EMAIL PROTECTED] Date: 01/20/2004 02:20PM Subject: Advice needed for high volume of inserts Hello, This is my first message on this list. So I take the occasion to salute every one of you. I'm looking to implement a database server wich will mostly insert data. The data structure is not complexe. The data will be insterted in 1 table at burst rates of 13000 insert/seconds and sustained rates of about 5000/sec for about 8 hours a day. There will be selects from time to times, so let assume 100 selects /day. Note that the row size will be about 80 bytes. The data needs to be kept in the database for around 90 days after which, the data is dropped from the database. Being more familiar with Oracle, I was considering a partitioned table for the entries. Someone I work with told me that MySQL had merge tables that could serve the same purpose. The goal is to optimize selects and simplify the aging of the data by droping a partition and creating an other one every day. If I have a choice between MySQL and other products, I might push the MySQL solution as long as it can do the job. My hope, with this message is to get comments or suggestions for this kind of setup that could help me fit MySQL in this project. Any suggestion is welcomed, like hardware size, warnings, pros, cons, etc. etc. Finally, we hope to save a bundle in oracle licenses. Thank You in advance. -Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice needed for high volume of inserts
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On January 20, 2004 02:31 pm, Peter J Milanese wrote: It'll work. I do slight less on the way of inserts. What I do is dynamically generate the tables within my entry code, and merge tables based on the query. Good for large log parsers. Be aware that this can break greatly if it's a non-redundant live feed (to mysql). I think that's a problem anywhere though. Mysql should not hold you back though. Peter J. Milanese Thanks peter... Out of curiosity, what kind of hardware do you use and how what kind of I/O do you have? Thanks again. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFADYRdQR+WnN6TbikRAqp9AJ4ycP/8a81tQoENnq48GBN9KLhhtgCeNIZ5 3vAUgqv8GA9NseXYsJt8zW0= =w8HR -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice needed for high volume of inserts
Hardware is Dell PE2650/Dual Xeons (2G) 4G Ram I have a raid10 array for data volumes. Bottleneck would be Disk. That's the problem I had, so I went raid10 on the box. The most I push is about 10k, and it's not too bad. Complex queries may suffer during insert times, but you can get around a lot of that based on your table layout. I was using it to test live web stats in a web farm. Developed the application, then ran into caching issues and haven't looked at it since. MySQL did the right thing though. P -STE-MARIE, ERIC [EMAIL PROTECTED] wrote: - To: Peter J Milanese [EMAIL PROTECTED] From: STE-MARIE, ERIC [EMAIL PROTECTED] Date: 01/20/2004 02:41PM cc: [EMAIL PROTECTED] Subject: Re: Advice needed for high volume of inserts -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On January 20, 2004 02:31 pm, Peter J Milanese wrote: It'll work. I do slight less on the way of inserts. What I do is dynamically generate the tables within my entry code, and merge tables based on the query. Good for large log parsers. Be aware that this can break greatly if it's a non-redundant live feed (to mysql). I think that's a problem anywhere though. Mysql should not hold you back though. Peter J. Milanese Thanks peter... Out of curiosity, what kind of hardware do you use and how what kind of I/O do you have? Thanks again. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFADYRdQR+WnN6TbikRAqp9AJ4ycP/8a81tQoENnq48GBN9KLhhtgCeNIZ5 3vAUgqv8GA9NseXYsJt8zW0= =w8HR -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advice wanted on Data Structure
-Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: 30 July 2003 16:42 To: [EMAIL PROTECTED] Subject: Advice wanted on Data Structure I have a question that I hope I can explain well enough: I am trying to figure out a data structure for an inventory system. The system contains: Items Kits(Made from a collection of Items and/or other Kits) Now my question is: Is there any way to structure this in a database so that I could run a single query to get the contents of a kit, even though it contains other kits? My problems occur when I try to create the tables as so: ITEMS:KIT_CONTENTSKITS KIT_IDKIT_ID ITEM_ID-ITEM_ID Description Description Price Etc This is a classic problem known as a Bill of Materials explosion and unfortunately relational databases don't handle it very well. Storage is easy(ish). Fundamentally you have a recursive many to many relationship between components, resolved as Component: Component_Link id ---|---assembly_id name|---subcomponent_id That is 2 foreign keys back to the same master table, if the diagram isn't clear. In OO terms, both item and kit are subclasses of component. There are may ways to implement that in a relation database, but the simplest is to store them as a single table with a type field. Retrieval is harder. To get the contents of an assembly (kit), select * from component as assembly, component as subcomponent, component_link where assembly.name=? and component_link.assembly_id=assembly_id and subcomponent.id= componentLink.subcomponent_id BUT, this only goes down to one level which may be enough for most purposes, but for stock monitoring (e.g. I've sold 10 of kit ZZA102, what effect does that have on my item stocks), you need to do it recursively. With mysql ( and most other DBMS) the only alternative is to do the recursion in a program - ie get all first level children foreach get next level foreach get next level etc Oracle has an excellent CONNECT BY extension to standard sql which does this brilliantly, and I believe mysql AB are planning to imlpement it sometime. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice wanted on Data Structure
I try a solution like this. It's kind of a tree [so your warned that this structure will be a waste of time if you have a subkit to a subkit to a... ] KITS kt_id int kt_things. parent_kt_id_fk int constraint fk_KITS foreign key (parent_kt_id_fk) references kits(kt_id) For details consult the manual; it won't work this create table :) I reccomend this when u have something like a lot of kits, and some of them have only one level of accesories [other kits]. - Original Message - From: Donald Tyler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 6:42 PM Subject: Advice wanted on Data Structure I have a question that I hope I can explain well enough: I am trying to figure out a data structure for an inventory system. The system contains: Items Kits (Made from a collection of Items and/or other Kits) Now my question is: Is there any way to structure this in a database so that I could run a single query to get the contents of a kit, even though it contains other kits? My problems occur when I try to create the tables as so: ITEMS: KIT_CONTENTS KITS KIT_IDKIT_ID ITEM_ID-ITEM_ID Description Description Price Etc The only way I could think of to allow a kit to be contained within a kit: Add another field to KIT_CONTENTS called Sub_Kit Which seems like a dumb way of doing it, because first of all, most kits don't have sub kits, so that would be a wasted field in most cases. Secondly, as far as I know the application logic would have to do multiple queries's to get the sub_kits. I really hope there is a simple and more logical way to do this, preferably allowing me to run a SINGLE query that will show a kit with all its sub kits. Otherwise I would need to have repeated queries from the application logic, and I really want to avoid that. Please help, thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice wanted on Data Structure
Donald,
I've done this before - it wasn't nice. The best solution I came up with
was to store the kits in the item table as though they were items. So
the structure is: (Lets call a 'Product' an Item or a Kit depending on
the 'prod_type')
PRODUCT KIT CONTENTSPRODUCT (as item)
prod_id-kit_id
prod_type {Item | Kit}prod_id-prod_id
The product table also store the kit/item description.
This structure allows n levels of kit building, and (obviously) allows a
single query to retrieve a 1 level kit breakdown.
You question about a single query to access this tree structure is a bit
of a classic (and I'd be interested in other peoples answers) but in
reality I found that it was sufficient to report to 1 level to kit
breakdown only (since printing a tree structure on a report is messy).
So my report said, for example:
KIT1 contains
ITEM1
ITEM2
KIT2
KIT2 contains
ITEM3
The issue came when trying to decide if we had 3 of KIT1 in stock. I
couldn't find a single query (that allowed n levels) so I wrote a
function that iterated the tree.
Andy.
Donald Tyler wrote:
I have a question that I hope I can explain well enough:
I am trying to figure out a data structure for an inventory system. The
system contains:
Items
Kits(Made from a collection of Items and/or other Kits)
Now my question is:
Is there any way to structure this in a database so that I could run a
single query to get the contents of a kit, even though it contains other
kits?
My problems occur when I try to create the tables as so:
ITEMS: KIT_CONTENTSKITS
KIT_IDKIT_ID
ITEM_ID-ITEM_ID Description
Description
Price
Etc
The only way I could think of to allow a kit to be contained within a kit:
Add another field to KIT_CONTENTS called Sub_Kit
Which seems like a dumb way of doing it, because first of all, most kits
don't have sub kits, so that would be a wasted field in most cases.
Secondly, as far as I know the application logic would have to do multiple
queries's to get the sub_kits.
I really hope there is a simple and more logical way to do this, preferably
allowing me to run a SINGLE query that will show a kit with all its sub
kits. Otherwise I would need to have repeated queries from the application
logic, and I really want to avoid that.
Please help, thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on improving our current method
Hi, I'm not to familiar with MySQL, but as far as I can seem, you might want to look at programming. I.e a program in C++ or Perl, or Visual Basic. Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advice on improving our current method
On 01-Jun-2003 Daniel Crompton wrote: snip If for example we move song number 5 up to number 1, we then have to manually change the ' Chart Number' of all the ones below it, i.e. changing the old number 1 to a number 2, number 2 to a number 3, number 4 to a number 5 etc. Ideally we need to keep using mysqlcc because it is very simple to use, but it is very time consuming. Any suggestions? SET @rank:=5; UPDATE music SET chart=0 WHERE [EMAIL PROTECTED]; UPDATE music SET chart=chart+1 WHERE chart@rank; Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on improving our current method
SET @rank:=5; UPDATE music SET chart=0 WHERE [EMAIL PROTECTED]; UPDATE music SET chart=chart+1 WHERE chart@rank; Thanks Don Unfortunately though i think we need to be able to make the changes in something we can see which is why we are using mysqlcc. With mysqlcc any of us can simply reorder the chart by simply re-typing in all the position numbers. It is a pain typing in 1 2 3 4 5 6 etc up to 100 everyweek, but i guess this is possibly the easiest way for us to maintain the music chart. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on improving our current method
OK, here's a lengthy answer: I don't use MySQLCC, so I'm assuming that it has a way that you can enter and run SQL statements. Say our top 10 songs are: mysql [test] SELECT * FROM musicchart ORDER BY chartnumber LIMIT 10; +-+--++ | ChartNumber | Artist | Title | +-+--++ | 1 | Led Zepplin | All of My Love | | 2 | Jeff Foster | Volim Te (I Love You) | | 3 | Dave Brubeck Quartet | Brotherly Love | | 4 | U2 | Love Rescue Me | | 5 | Eurythmics | Miracle Of Love (Live) | | 6 | Charlie | Lovers | | 7 | Melissa Etheridge| My Lover | | 8 | The Call | For Love | | 9 | Jewel| Love and Affection (Vh1 Duets) | | 10 | Diana Krall | Peel Me a Grape| +-+--++ 10 rows in set (0.00 sec) Now we move #9 up to #1 and we have to close up the hole that was created by the move (there is no longer a #9 song (i.e. we have numbers 7, 8, 10, 11). mysql [test] UPDATE `MusicChart` - SET `ChartNumber` = 1 - WHERE `ChartNumber` = 9; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql [test] UPDATE `MusicChart` - SET `Chartnumber` = `ChartNumber` - 1 - WHERE `ChartNumber` 9; Query OK, 118 rows affected (0.01 sec) Rows matched: 118 Changed: 118 Warnings: 0 We now have: mysql [test] select * from musicchart order by chartnumber limit 10; +-+--++ | ChartNumber | Artist | Title | +-+--++ | 1 | Jewel| Love and Affection (Vh1 Duets) | | 1 | Led Zepplin | All of My Love | | 2 | Jeff Foster | Volim Te (I Love You) | | 3 | Dave Brubeck Quartet | Brotherly Love | | 4 | U2 | Love Rescue Me | | 5 | Eurythmics | Miracle Of Love (Live) | | 6 | Charlie | Lovers | | 7 | Melissa Etheridge| My Lover | | 8 | The Call | For Love | | 9 | Diana Krall | Peel Me a Grape| +-+--++ 10 rows in set (0.00 sec) Ooops! we have to number 1's. Now we have to shift the rest of the songs up one slot. mysql [test] UPDATE `MusicChart` - SET `ChartNumber` = `ChartNumber` + 1 - WHERE `Title` != 'Love and Affection (Vh1 Duets)'; Query OK, 126 rows affected (0.02 sec) Rows matched: 126 Changed: 126 Warnings: 0 mysql [test] select * from musicchart order by chartnumber limit 10; +-+--++ | ChartNumber | Artist | Title | +-+--++ | 1 | Jewel| Love and Affection (Vh1 Duets) | | 2 | Led Zepplin | All of My Love | | 3 | Jeff Foster | Volim Te (I Love You) | | 4 | Dave Brubeck Quartet | Brotherly Love | | 5 | U2 | Love Rescue Me | | 6 | Eurythmics | Miracle Of Love (Live) | | 7 | Charlie | Lovers | | 8 | Melissa Etheridge| My Lover | | 9 | The Call | For Love | | 10 | Diana Krall | Peel Me a Grape| +-+--++ 10 rows in set (0.00 sec) You may want to be careful about the column used in the last statement. You will want to use some combination of columns or a unique column to ensure against duplicates. With my example there is a possibility that you could have two songs with the same title. I think I would add an auto_increment column that way I'd only have to know the auto_increment number to use in the WHERE clause. The last statement would become: UPDATE `MusicChart` SET `ChartNumber` = `ChartNumber` + 1 WHERE `Song_ID` = 135; Where Song_ID is some number assigned by MySQL to the record. On Sun, 1 Jun 2003 17:23:13 +0100, Daniel Crompton wrote: Can anyone advise how we should improve this method of
Re: Advice on improving our current method
let the DB manage the sequencing before and after Make Chart # an Index Martin - Original Message - From: Don Read [EMAIL PROTECTED] To: Daniel Crompton [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, June 01, 2003 1:54 PM Subject: RE: Advice on improving our current method On 01-Jun-2003 Daniel Crompton wrote: snip If for example we move song number 5 up to number 1, we then have to manually change the ' Chart Number' of all the ones below it, i.e. changing the old number 1 to a number 2, number 2 to a number 3, number 4 to a number 5 etc. Ideally we need to keep using mysqlcc because it is very simple to use, but it is very time consuming. Any suggestions? SET @rank:=5; UPDATE music SET chart=0 WHERE [EMAIL PROTECTED]; UPDATE music SET chart=chart+1 WHERE chart@rank; Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on improving our current method
Perhaps I'm being a bit slow today. I don't see (or maybe I haven't found the magic syntax) that would allow a table index to handle the re-sequencing of information. On Sun, 1 Jun 2003 14:09:15 -0400, Martin Gainty wrote: let the DB manage the sequencing before and after Make Chart # an Index Martin - Original Message - From: Don Read [EMAIL PROTECTED] To: Daniel Crompton [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, June 01, 2003 1:54 PM Subject: RE: Advice on improving our current method On 01-Jun-2003 Daniel Crompton wrote: snip If for example we move song number 5 up to number 1, we then have to manually change the ' Chart Number' of all the ones below it, i.e. changing the old number 1 to a number 2, number 2 to a number 3, number 4 to a number 5 etc. Ideally we need to keep using mysqlcc because it is very simple to use, but it is very time consuming. Any suggestions? SET @rank:=5; UPDATE music SET chart=0 WHERE [EMAIL PROTECTED]; UPDATE music SET chart=chart+1 WHERE chart@rank; Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Listserv only address. Jeff Shapiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on improving our current method
Unfortunately though i think we need to be able to make the changes in something we can see which is why we are using mysqlcc. What is the final use for the database? Is it for a website, an application, or just mere data storage? If it's either of the first two, an admin form can be built into the website or application. If you're just using the DB to store data (and not archiving it), you're making life difficult. I find a good general rule to be: if the data isn't being searched, sorted, indexed, or archived, use a flat file (like Excel). Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Daniel Crompton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, 01 June, 2003 14:00 Subject: Re: Advice on improving our current method SET @rank:=5; UPDATE music SET chart=0 WHERE [EMAIL PROTECTED]; UPDATE music SET chart=chart+1 WHERE chart@rank; Thanks Don Unfortunately though i think we need to be able to make the changes in something we can see which is why we are using mysqlcc. With mysqlcc any of us can simply reorder the chart by simply re-typing in all the position numbers. It is a pain typing in 1 2 3 4 5 6 etc up to 100 everyweek, but i guess this is possibly the easiest way for us to maintain the music chart. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice please
Hi George, I am about to start the planning process in moving my FileMaker/Lasso/PHP site over to MySQL/PHP. Before I get too far down the road, should I be using InnoDB or MyISAM tables? The service is initially going to be hosted on WinNT but may move over to Linux later. =main response, below. =know that you can move MySQL files between OpSys at the file level - not sure about InnoDB... My main database has about 21000 records (but 400+ fields). The number of fields will decrease to less than 100 in the normalisation process with FileMaker's internal calculations/scrips being handled by eithe MySQL functions or PHP routines. =probably more to the point are metrics of the volume of usage, and the ratio of data update vs simple retrieval. The complexity of the interrelationships between data tables for update/deletion may also come into play. I built prototype about 9 months ago using MyISAM and although it was OK I am wondering if there are any benefits/drawbacks of using InnoDB instead. The prototype is pretty much out of date now as the FileMker dn has moved on in leaps and bounds since then. On top of that the requirement is now to interact more with the company's other databases (MySQL and Oracle). =a FileMker dn. Someone I should meet? =what do you mean by interact...MySQL and Oracle? Any comments would be welcome. =the classic advantage of MySQL is that it is lean for speed, and nothing is going to touch it. If you are retrieving information to drive a dynamic web site, there's nothing much else in the ball-game. =however one of the reasons for this speed, is the removal of the need to keep checking for various 'ifs' and 'buts' within the relational model. This brings a set of disadvantages. Each individual database interaction, eg INSERT data into tbl, is performed in isolation. So if you have a succession of operations that must be performed together, or upon the 'discovery' of any problem, all removed/not executed, ie a transaction, then MySQL has no programmer tool/facility to help you out. If the 'bankers problem' is a potential issue within your system then InnoDB's locking can save your life. Per the comment about 'metrics' (above), sometimes these matter and sometimes they don't... =onwards: the manual summarises InnoDB's talents thus: 7.5.1 InnoDB Tables Overview InnoDB provides MySQL with a transaction-safe (ACID compliant) table handler with commit, rollback, and crash recovery capabilities. InnoDB does locking on row level and also provides an Oracle-style consistent non-locking read in SELECTs. These features increase multiuser concurrency and performance. There is no need for lock escalation in InnoDB, because row level locks in InnoDB fit in very small space. InnoDB tables support FOREIGN KEY constraints as the first table type in MySQL. InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine. =Regards, =dn - 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: Advice please
From: George Pitcher [EMAIL PROTECTED] I am about to start the planning process in moving my FileMaker/Lasso/PHP site over to MySQL/PHP. Before I get too far down the road, should I be using InnoDB or MyISAM tables? InnoDB does not support FULLTEXT indeces. Since FileMaker searches are always full text, this may make a big difference in your particular case. If the user can do ad-hoc searches and the functionality must remain the same, you probably need to go with MyISAM. For example, searching for a last name of Henry will match von Henry and O'Henry in FileMaker and MyISAM (if last name is FULLTEXT), but will not in InnoDB. OTOH, MyISAM does not provide formal support for relations, like both FileMaker and InnoDB do. Your best bet may be to have a hybrid approach, where all your fields that require full text searching are segregated into MyISAM tables, and use InnoDB for everything else. SQL SQL SQL SQL -- : Jan Steinman -- nature Transography(TM): http://www.Bytesmiths.com : Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services : Buy My Step Van! http://www.Bytesmiths.com/van - 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: advice
João Borsoi wrote: I working in a project to be released 6 months from now. We are currently using mysql 3.23.49 with Innodb. I'm thinking about upgrading it to mysql 4.0.5a because of some deadlock bugs. What is the risk of going to a development version? Is it stable? I've been living on the edge of a knife, running a production system on 4.0.1/2/4/5 for the last few months, had some minor problems with 4.0.3 but every other release has been quite solid. So, no, I dont think you'll run into any problems ;-) regards, danny - 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: advice
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Joao -- ...and then João Borsoi said... % % I working in a project to be released 6 months from now. We are % currently using mysql 3.23.49 with Innodb. I'm thinking about upgrading % it to mysql 4.0.5a because of some deadlock bugs. What is the risk of % going to a development version? Is it stable? A development version is never stable, but the mysql folks tend to be pretty good about their releases. To quote the manual from 4.0.6-gamma: 2.2.3 Which MySQL Version to Use The MySQL naming scheme uses release numbers that consist of three numbers and a suffix. For example, a release name like mysql-3.21.17-beta is interpreted like this: ... * The suffix (beta) indicates the stability level of the release. The possible suffixes are: + alpha indicates that the release contains some large section of new code that hasn't been 100% tested. Known bugs (usually ... release. There should be no known bugs in any MySQL release. + beta means that all new code has been tested. No major new ... + gamma is a beta that has been around a while and seems to work fine. Only minor fixes are added. This is what many other companies call a release. + If there is no suffix, it means that the version has been run for a while at many different sites with no reports of bugs other than platform-specific bugs. Only critical bug fixes are applied to the release. This is what we call a stable release. Since the development branch is at 4.0.6-gamma, it's pretty stable. You might also check out section 1.2.3 How Stable is MySQL?. % % Thanks, % Joao. HTH 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) iD8DBQE961yIGb7uCXufRwARAggsAKCHfwucTYh/I/fhaa6oQUdK5B1bsACdG06d Vy9OJxoCf2RlnUR/xvXWckE= =Jgfg -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
RE: Advice for dataupload
* Hayan Al Mamoun I have two design-identical database, one on my intranet, the other on the internet, is there any procedure that Synchronizes the content of two databases? Depends on what you mean with 'Synchronizes'. 'Real' synchronization, where both servers are written to and both must update eachother in close-to-real-time, is rather difficult, but possible. The challenge is to avoid duplicate primary keys and other problems arising from the fact that the data will not be inserted in the same order on both servers. Your application must be written/re-written with this in mind. mysql supports replication, both two-way and one-way. Two-way replication is when multiple servers are written to, and the problems mentioned above apply. One-way replication is when one server (the master) is written to, and the other(s) (the slaves) are only read from. This is pretty straight forward, and will not require any changes in your application. URL: http://www.mysql.com/doc/R/e/Replication.html For a simple, one-time transferral of the database from one server to another server, use mysqldump: URL: http://www.mysql.com/doc/m/y/mysqldump.html If you just need to synchronize the two databases once, you could try to 'merge' the output of mysqldump from the two databases. If the amount of data is small, this could be done manually with a text editor, otherwise some scripting language could be used, reading both files simultaneously and changing the conflicting primary keys. -- Roger - 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: Advice
Tod Harter writes: Hi Guys I've been using MySQL for a few years now, but always on medium sized projects. I think the biggest thing I ever did has maybe 200k records in it. Now all of a sudden I have a client with a requirement for a database that we estimate will be in the range of 1 to 4 TERABYTES of data... Now this data is largely static (though there will be a few updates and some insertions) non-transactional, and needs to be searched on several indexes. Furthermore its image data, plus metadata (which the searching is done on). Anyone have experience with this kind of application? Is MySQL going to handle it? What are the upwards limits on scalability? Ideally from a system design perspective I'd like to have ALL the data in the database itself and in a single set of tables. Unfortunately the images themselves are up to 10 megs each. Is it even POSSI BLE to use blobs in that context? I know someone is going to suggest putting the image data outside the database, but for various reasons I consider that a last resort, not the least of which is security requirements are so high that running other protocols to access data thats outside tables is problematic. I'd love to hear about other's experience in this area. Thanks ahead of time :o). Hi! A number of our registered customers has database of that size. But in order to optimise things, I do not think it is wise to store such huge images in tables. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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
Re: Advice
On Tuesday 29 January 2002 12:40, Sinisa Milivojevic wrote: Tod Harter writes: Hi Guys I've been using MySQL for a few years now, but always on medium sized projects. I think the biggest thing I ever did has maybe 200k records in it. Now all of a sudden I have a client with a requirement for a database that we estimate will be in the range of 1 to 4 TERABYTES of data... Now this data is largely static (though there will be a few updates and some insertions) non-transactional, and needs to be searched on several indexes. Furthermore its image data, plus metadata (which the searching is done on). Anyone have experience with this kind of application? Is MySQL going to handle it? What are the upwards limits on scalability? Ideally from a system design perspective I'd like to have ALL the data in the database itself and in a single set of tables. Unfortunately the images themselves are up to 10 megs each. Is it even POSSI BLE to use blobs in that context? I know someone is going to suggest putting the image data outside the database, but for various reasons I consider that a last resort, not the least of which is security requirements are so high that running other protocols to access data thats outside tables is problematic. I'd love to hear about other's experience in this area. Thanks ahead of time :o). Hi! A number of our registered customers has database of that size. But in order to optimise things, I do not think it is wise to store such huge images in tables. What would be the performance considerations? Considering it theoretically if I (for instance) set things up so that I had an ftp server for the images and stored filenames in a column then as far as raw data transfer efficiency its tcp either way. I guess the question becomes one of how does the database deal with blobs internally? Given that the data is images its essentially opaque data to the RDBMS (ie there would never be any reason to search the blob columns themselves, just recover them based on searches of other indexes). I guess the other related question with indexes is how big a deal is it to have such huge indexes? In a practical sense its one data set, so I really would like to have indexes covering the whole thing. The number of records runs up into the low millions. Queries will need to be done on maybe up to 4 or 5 columns in that data set. Can I expect a query like that to complete in a time frame of under 1 minute? What kind of hardware would be recommended to get that sort of performance? We will be building out the entire system, so essentially its up to us to define that. I'd considered something in the order of a top of the line dual processor Pentium 4 class system with a couple of SAN boxes and fibre channel. I expect its no challenge to throw a few gigs of ram at it, but naturally there is NO way to put enough ram in a box to even come close to holding complete indexes... - 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: Advice
Tod Harter writes: What would be the performance considerations? Considering it theoretically if I (for instance) set things up so that I had an ftp server for the images and stored filenames in a column then as far as raw data transfer efficiency its tcp either way. I guess the question becomes one of how does the database deal with blobs internally? Given that the data is images its essentially opaque data to the RDBMS (ie there would never be any reason to search the blob columns themselves, just recover them based on searches of other indexes). I guess the other related question with indexes is how big a deal is it to have such huge indexes? In a practical sense its one data set, so I really would like to have indexes covering the whole thing. The number of records runs up into the low millions. Queries will need to be done on maybe up to 4 or 5 columns in that data set. Can I expect a query like that to complete in a time frame of under 1 minute? What kind of hardware would be recommended to get that sort of performance? We will be building out the entire system, so essentially its up to us to define that. I'd considered something in the order of a top of the line dual processor Pentium 4 class system with a couple of SAN boxes and fibre channel. I expect its no challenge to throw a few gigs of ram at it, but naturally there is NO way to put enough ram in a box to even come close to holding complete indexes... Hi! You should go by the book. Normalize schema, optimise queries, tune MySQL etc .. Choose right hardware, system software etc ... You can expect it to complete in a second if you have followed the above guidelines. -- Consider taking our support. Visit https://order.mysql.com Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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
Re: Advice on Random Numbers
*] I need to generate a RANDOM UNIQUE number for every new record. Is there an *] easy way to accomplish this inside of mySQL. *] What are the requirements for 'random' and 'unique'? Do you need some sort of cryptographic randomness? Does uniqueness be kept across tables? If you 'only' need a unque id you could you AUTO_INCREMENT. If you want uniqueness to carry over tables etc. you could take a look at the aproach made by the Apache module mod_unique_id. If you need a cryptographic randomness you should look into some litterature about the area so as to get a better understanding of what you truly need. -- Aigars - 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: Advice on Random Numbers
On Thu, 24 May 2001, Aigars Grins wrote: *] I need to generate a RANDOM UNIQUE number for every new record. Is there an *] easy way to accomplish this inside of mySQL. *] What are the requirements for 'random' and 'unique'? Do you need some sort of cryptographic randomness? Does uniqueness be kept across tables? If you 'only' need a unque id you could you AUTO_INCREMENT. If you want uniqueness to carry over tables etc. you could take a look at the aproach made by the Apache module mod_unique_id. If you need a cryptographic randomness you should look into some litterature about the area so as to get a better understanding of what you truly need. Would an MD5 encryption be another option? Use a auto incrementing ID to keep your records sorted, then the MD5 field for your unique randomness. -- === Kip Turkphone: 915.234.5678 Systems Administrator or 800.695.9016 Killer of Spam/Writer of Code/Penguin Proponent West Central Net fax: 915.656.0071 === - 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: Advice on Random Numbers
*] I need to generate a RANDOM UNIQUE number for every new record. Is there an *] easy way to accomplish this inside of mySQL. *] What are the requirements for 'random' and 'unique'? Do you need some sort of cryptographic randomness? Does uniqueness be kept across tables? If you 'only' need a unque id you could you AUTO_INCREMENT. If you want uniqueness to carry over tables etc. you could take a look at the aproach made by the Apache module mod_unique_id. If you need a cryptographic randomness you should look into some litterature about the area so as to get a better understanding of what you truly need. Would an MD5 encryption be another option? Use a auto incrementing ID to keep your records sorted, then the MD5 field for your unique randomness. Good idea. Practical and easy (there are a lot fast MD5 implementations easy accessable). That should take care of the cryptographic randomness. Uniqueness won't be true across tables but that might not be necessery. If it was a value similar to mod_unique_id could be MD5'ied. -- Aigars - 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: Advice on Random Numbers
On Wed, May 23, 2001 at 02:22:02PM -0400, Dave Carter spake thusly: *] I need to generate a RANDOM UNIQUE number for every new record. Is there an *] easy way to accomplish this inside of mySQL. *] The way I do this for session ids is through a perl function that I wrote a long time ago. Essentially I create a really long string of mixed case alpha characters and numeric digits. I suppose you could use the same method from perl for record numbers. -- -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ Peter L. Berghold[EMAIL PROTECTED] Schooner Technology Consulting http://www.berghold.net Unix Professional ServicesPerl Perl/CGI mod_perl - 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: Advice on Random Numbers
*] I need to generate a RANDOM UNIQUE number for every new record. Is there an
*] easy way to accomplish this inside of mySQL.
*]
The way I do this for session ids is through a perl function that I
wrote a long time ago. Essentially I create a really long string of
mixed case alpha characters and numeric digits.
I suppose you could use the same method from perl for record numbers.
Hmm. As a coincidence, I just rewrote a function kind of like this
about 15 minutes ago.
It basically does (mod_perl):
my $id;
while (1) {
$id = int(rand(40));
my $sth = $dbh-prepare(INSERT INTO tab (id) VALUES ($id));
if ($sth-execute) {
$sth-finish;
last;
}
$sth-finish;
# try again
}
return $sid;
This ONLY works if id is a UNIQUE (ie, PRIMARY) key. It relies on the query
failing to determine whether it's taken or not. This wouldn't scale very
well if you need to create a million records per minute, or plan to store
more than 100,000 records at a time, because at that point, it becomes easier
to brute force guess a valid one. At about 40,000,000, the chances become 1 in 100
that the INSERT will fail. At 400,000,000, they're 1 in 10.
Also, it can't tell the difference between real error and a duplicate key error.
A bail out scheme after say, 10 tries wouldn't be a bad idea.
--
Michael Bacarella [EMAIL PROTECTED]
Technical Staff / System Development,
New York Connect.Net, Ltd.
-
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: Advice needed....
Well I was hoping to be able to do it in SQL, however I do know it can be accomplished in the middleware. I am seriously considering it as my deadline is approaching quickly Dave Carter Chief Web Architect Accelerated Business Technologies, Inc. http://www.abti.cc 717.464.2970 -Original Message- From: clay bond [mailto:[EMAIL PROTECTED]] Sent: Saturday, May 19, 2001 6:51 PM To: [EMAIL PROTECTED] Subject: Re: Advice needed On Sat, 19 May 2001, Dave Carter wrote: I'm converting an Access db to mySQL, and Access allows users to Insert Records into a table AND call them as well in the FROM clause. This is illeagal in ANSI SQL however and therefore illeagal in mySQL, but I don't know really how to get around it. How can I insert only new records that aren't already in a table, without calling the table first? Can this be done in SQL? But you're not working only with SQL, are you? Like transactions or foreign keys, you can easily do this in your middleware, whatever that might be (PHP, Perl, Coldfusion, whatever). Select first, then check to see if it's there, and if it isn't, insert it. A simple if-statement will do the job, so what's the problem? -- /\ \ /ASCII RIBBON CAMPAIGN X AGAINST HTML EMAIL / \AND POSTINGS - 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: Advice needed....
On Sat, 19 May 2001, Dave Carter wrote: I'm converting an Access db to mySQL, and Access allows users to Insert Records into a table AND call them as well in the FROM clause. This is illeagal in ANSI SQL however and therefore illeagal in mySQL, but I don't know really how to get around it. How can I insert only new records that aren't already in a table, without calling the table first? Can this be done in SQL? But you're not working only with SQL, are you? Like transactions or foreign keys, you can easily do this in your middleware, whatever that might be (PHP, Perl, Coldfusion, whatever). Select first, then check to see if it's there, and if it isn't, insert it. A simple if-statement will do the job, so what's the problem? -- /\ \ /ASCII RIBBON CAMPAIGN X AGAINST HTML EMAIL / \AND POSTINGS - 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: Advice needed....
On 19-May-01 Dave Carter wrote: I'm converting an Access db to mySQL, and Access allows users to Insert Records into a table AND call them as well in the FROM clause. This is illeagal in ANSI SQL however and therefore illeagal in mySQL, but I don't know really how to get around it. How can I insert only new records that aren't already in a table, without calling the table first? Can this be done in SQL? INSERT IGNORE then SELECT ... Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. - 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: advice sought on using a select to extract binary data
On 3/27/01 4:54 PM, "WeAreUs" [EMAIL PROTECTED] wrote:
Hi!
I enter some .wav encoded (windows) data into a MySql table as follows:
$data = addslashes(fread(fopen($form_data, "r"),
filesize($form_data)));
$result=MYSQL_QUERY("INSERT INTO
b(audio,filename,filesize,filetype,audio_description) ".
"VALUES
('$data','$form_data_name','$form_data_size','$form_data_type',
'$audio_description')");
$uid= mysql_insert_id();
Later, I try to reconstruct the original audio file as follows:
$query = mysql_query("select audio from dupe_b where uid='87' into
outfile 'c:/windows/desktop/data.wav' ");
This does not yield the correct format. I take it that I require "fields
terminated by" or something like that to make this work. However, I
cannot get any further with this.
Any suggestions?
Thanks for the time!
Have you tried putting the returned value into a variable, opening up a file
for writing(as binary)
In php (which it looks like you are using)
$fh=fopen('newfile.wav','wb');
fputs($fh,$fielddata);
fclose($fh);
I think that selecting into an outfile does some textencoding and newline
conversion type stuff (just guessing).
The above is the way I would do it, if I had to put my files into a db.
I would rather store my files, as files, in a directory, and store the meta
information (file location, name,size,description,copyright whatever) in the
database.
It is so much easier to have the file path/name inserted into the html, than
to pull all the data out, create the file, insert the filename, server up
the html.
-
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: ADVICE: Best way to store multi values
Assuming you have table staff with an id and name, and title with id and title, You create a join table. Staff_id Title_id 1 1 1 2 2 1 2 3 Then: Select name,title FROM staff s , title t, jointable j WHERE s.id=j.staff_id AND j.title_id=t.id; Mark Worsdall wrote: Hi, I have a table called staff which currently has a column called subjectTitles_id which is the type INTEGER. I have another table called subjectTitles which contains 12 subject titles. So that is how a member of staff is assigned a subject. Trouble is I need to be able to assign a multiple number of subjects to anyone member of staff. The method I thought to use was change staff.subjectTitles_id from INTEGER type to CHAR type and store a string of CSV numbers which correspond to the id's of the table subjectTitles, e.g.:- 1;2;7;10 Is this the way? It seems quite ungraceful. M. -- - 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: ADVICE: Best way to store multi values
you need a join table between the two tables. It would contain the primary keys of both tables. This way for any given staffID, you could have 0-n records that contain staffId subjectTitles_id. To get all the subjectTitles for a given staff you would Select * from staff_subjectTitles where staffId = thePersonToLookFor; you can use where clauses to join in the Staff and SubjectTitles tables to that select as well to gather them all into one select. Cal http://www.calevans.com -Original Message- From: Mark Worsdall [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 20, 2001 2:22 PM To: [EMAIL PROTECTED] Subject: ADVICE: Best way to store multi values Hi, I have a table called staff which currently has a column called subjectTitles_id which is the type INTEGER. I have another table called subjectTitles which contains 12 subject titles. So that is how a member of staff is assigned a subject. Trouble is I need to be able to assign a multiple number of subjects to anyone member of staff. The method I thought to use was change staff.subjectTitles_id from INTEGER type to CHAR type and store a string of CSV numbers which correspond to the id's of the table subjectTitles, e.g.:- 1;2;7;10 Is this the way? It seems quite ungraceful. M. -- He came from Econet - Oh no, I've run out of underpants :( Home:- [EMAIL PROTECTED] http://www.wizdom.org.uk Shadow:- [EMAIL PROTECTED] http://www.shadow.org.uk Work:- [EMAIL PROTECTED] http://www.hinwick.demon.co.uk - 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: ADVICE: Best way to store multi values
You are building a "many to many" relationship. How to do it: * Each staff member should have an unique ID (eg StaffMember_ID) in the Staff table; * Each subject has an ID in the SubjectTitles table; * and you need a third table, with two fields: StaffMember_ID and SubjectTitle_ID, plus extra data if needed (eg: due date, assignment date, comments, priority, ...) When you assign a subject to a staff member, you add a record to that table. Hope this helps you. Regards, Jean-Claude - Original Message - From: "Mark Worsdall" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 20, 2001 9:22 PM Subject: ADVICE: Best way to store multi values Hi, I have a table called staff which currently has a column called subjectTitles_id which is the type INTEGER. I have another table called subjectTitles which contains 12 subject titles. So that is how a member of staff is assigned a subject. Trouble is I need to be able to assign a multiple number of subjects to anyone member of staff. The method I thought to use was change staff.subjectTitles_id from INTEGER type to CHAR type and store a string of CSV numbers which correspond to the id's of the table subjectTitles, e.g.:- 1;2;7;10 Is this the way? It seems quite ungraceful. M. -- He came from Econet - Oh no, I've run out of underpants :( Home:- [EMAIL PROTECTED] http://www.wizdom.org.uk Shadow:- [EMAIL PROTECTED] http://www.shadow.org.uk Work:- [EMAIL PROTECTED] http://www.hinwick.demon.co.uk - 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: ADVICE: Best way to store multi values
Hi, Thanks for all the advice, one question, the create a JOIN table, you mean just create a table, is the naming convention for join tables something like:- join_jobTitles In other words prefix all join tables with join_? M. In message [EMAIL PROTECTED], Gerald L. Clark [EMAIL PROTECTED] writes Assuming you have table staff with an id and name, and title with id and title, You create a join table. Staff_id Title_id 1 1 1 2 2 1 2 3 Then: Select name,title FROM staff s , title t, jointable j WHERE s.id=j.staff_id AND j.title_id=t.id; Mark Worsdall wrote: Hi, I have a table called staff which currently has a column called subjectTitles_id which is the type INTEGER. I have another table called subjectTitles which contains 12 subject titles. So that is how a member of staff is assigned a subject. Trouble is I need to be able to assign a multiple number of subjects to anyone member of staff. The method I thought to use was change staff.subjectTitles_id from INTEGER type to CHAR type and store a string of CSV numbers which correspond to the id's of the table subjectTitles, e.g.:- 1;2;7;10 Is this the way? It seems quite ungraceful. M. -- -- He came from Econet - Oh no, I've run out of underpants :( Home:- [EMAIL PROTECTED] http://www.wizdom.org.uk Shadow:- [EMAIL PROTECTED] http://www.shadow.org.uk Work:- [EMAIL PROTECTED] http://www.hinwick.demon.co.uk - 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
