Handling Large Amounts Of Text
I am desiging a web store to hold and manage up to 150,000 to 200,000 items and have run into a problem which I believe can be fixed but I am not sure about the way to go about it.The problem is that every product has a rich text description that contains a lot of extra HTML code that goes into the database with the product description. I would estimate every product has an average of 10k of description and code.
Now instead of putting all of this into the database would it be possible to write this to a .txt file and keep it in a products_desc folder? Would this keep the database at a respectible size and optmize everything?Does anyone have any good examples or tutorials on going about this with PHP?
View Complete Forum Thread with Replies
Related Forum Messages:
Import Large Amounts
My client has a large database in MS Access. I need to put a section of this (approximately 900 rows) into MySQL to be displayed on a website. The thing is, every month I will be resupplied with updated data, which will need to replace the data that is currently in the SQL database. I am operating on a Mac and they've given it to me as an EXCEL spreadsheet, which I am trying to import into MYSQL using phpMyADmin with no luck. I've tried exporting from EXCEL to various formats, such as CSV, but I can't seem to get it to import into the MySQL database Does anyone know what I can do? I need it to be relatively simple so that every month I can simply reimport the new data. Is there some trick to importing the data from EXCEL, or should I ask the client to supply the data from MS Access in a different format.
View Replies !
Inserting Large Amounts Of Data Into Mysql
I have a whole bunch of numbers and state, county, city, etc. data that i'm trying to load into a database for a demographics website. The data is all in excel currently so I could easily copy and paste it to a text file or another medium if necessary but I'm wondering what the quickest and most efficient way to get it all entered into mysql is.
View Replies !
Handling Very Large Databases In MySQL
Currently I have a database of about 3.5 GB which contains about 5 text fields and to enable fast accesses to search this I have to use FULL TEXT INDEXES. My server has a RAM of 3.5 GB and I need to find a way so that I can have fast search process on this database.
View Replies !
Handling Large Database, Spreading The Load
For speed and performance issues, I am thinking about using two servers to handle a single mySQL database. Is this possible? If so, how would I go about doing it? Lets say hypothetically that one of my tables has millions of rows, what would I do to be able to spread the load on my two servers?
View Replies !
Outputting A New Line To Text File + Handling Dates
1) I'm trying to output stuff to an ascii text file. Right now i'm using: select date, open, high, low, close, volume into outfile 'c:/pf/data/myfile.txt' fields terminated by ',' lines terminated by ' ' from nox order by id DESC; (basically it's a table that has id (primary key) date, open, high, low, close, volume) Visually the ascii file (myfile.txt) looks fine and opens up fine in programs like Excel. However one program I need it to work in (Tradestation) absolutely refuses to see the newline character - it basically treats it like some high ascii character and reads it as date, open, high, low, close, gibberish high ascii character, date, open, high, etc.... ..... so basically 1 long string. Are there any other ways to order it to write on a new line other than using "lines terminated by ' '? 2) I'm reading my dates in taken from a yahoo csv file. So for example, it will write 2-Jan-2006. Is there any way you can think of that SQL can read it in as a string and reformat it so it writes it like 2/1/2006 instead.
View Replies !
Display Large Text Fields
I have created a table that is filled in by an online form. most of the fields are text fields or selects. But , I have one field that is a textarea on the form. When a user enters a large amount of text, the table listing is distorted due to the extra text. How can I limit the input, or better yet display a marker in the text field listing that will be linked to a larger display window ?
View Replies !
Importing Data Containing Large Text Fields
I'm in the middle of importing a very large database in MDB format into MySQL. The data consists of a fairly disgusting list of redundant columns, and one of those columns contains plain text, including line feeds and carriage returns. I've been bashing my brains out trying to import this data into MySQL WITHOUT resorting to using a BLOB (binary or otherwise) field to stick all that text data into. Varchar fields are fine, but there's a problem importing that data. The problem is that since the textual data in that field contains linefeeds and carriage returns, exporting it as quote-delimited text is fine, except that there's no way for MySQL's LOAD DATA statement to see the linefeeds in that text as anything except newlines - and since most data export tools I have only provide simple CR/LF (linefeeds and carriage returns) as row delimiters, MySQL kept complaining about missing columns when it got to a field containing that raw text. The fix was to append a text column to the raw table, containing an identical string in every row - I used XYZ, but almost anything goes. Then I re-exported the whole table using " string delimiters and CR/LF line terminators as usual. Code:
View Replies !
Can't Load Large Text File Into MySQL
I have a file with over 11,000 records (of woody species worldwide) that I am trying to load into MySQL. Usually, I use a session of PHPMyAdmin to do this and this resides inside an installation of XAMPP. I am running XP. NO matter what setting I try (using the CSV or SQL choice, No SQL compatibility or MYSQL40, I get the following error after a long delay: Fatal error: Maximum execution time of 300 seconds exceeded in D:XAMPPphpmyadminlibrariessqlparser.lib.php on line 544 Do I have to lengthen the timeout parameter in sqlparser.lib.php or is there some other mistake I am making. I have tried to use a straight text file, a text file that is tab delimited and even imported this into Excel to make a CSV file to see. The fields in the file are: GenusEpithet Authorities
View Replies !
MqSQL + ColdFusion Q - Large Text Fields?
So I'm trying to program a searchable "market notes" database for the marketing department at work, using mySQL and ColdFusion MX. I'm having trouble with storing the actual text of the note itself. Originally I had the datatype in mySQL for the "Text" field set to "blob", but CF didn't seem to like that... so then I switched the type to "longtext". After I did this, I was able to store the text, but when I go to retrieve it, I get funny characters tacked onto the beginning of the text...the "Trim" function in CF doesn't seem to elimate these. So my question would be, what's the best way to store the text in these market notes? The notes themselves are usually a paragraph up to a page or so long at the most. I'd like to store them in a way so that the body of the note is searchable.
View Replies !
Large Text File Import/input With Some Twists
I have a large text file, delimited by '|', with over 2million records (yes 2 million). I need to import/input this file in to a new table. There a three date fields in the text file, but the date has been stored in the format: '2008/07/20'. The table I am trying to bring the data into has the fields defined as 'date' and when I try to load the data using 'load data local infile ... etc', the dates just come in as '0000-00-00'. As I'm a bit of a newbie to this - what am I doing wrong? Is there a better way to import the data? (I can't use the bigdump utility as it is on a db where the admin's have even less skill than myself and they told me they don't know how to install/configure the utility).
View Replies !
Mammoth Amounts Of Self Joins
I have a table that contains keywords (Varchars) each one mapped to a product. so the database schema is just an id column, product column and keyword column. I want to select the products that contain x,y,z keywords. Now if this query involves many keywords I end up with a massive amount of self joins on the keywords table, is there a better way to do this than self joins? What would work perhaps is multiple unions where I could discard non duplicated rows but you can't seem to do that. Any ideas... ?
View Replies !
Adding Amounts Of Time
I am looking for a way, as the title suggests, to add a length of time to a time-type field.I needed to type something, and this is what I logically typed (of course, incorrect): SET playtime=playtime + '$length' Let's say playtime = 00:10:30, and $length = 00:04:30. What do I need to change the above statement to, so that the new value of playtime = 00:15:00 ?
View Replies !
Getting The Sum Of Amounts For Each User - How To Join?
I have a simple table for transactions that will store information like a bank account. To find the balance for a given user I want to sum all the amounts and display the result How can I do this for more than one user at a time? CREATE TABLE `transactions` ( `id` int(5) NOT NULL auto_increment, `user_id` int(10) NOT NULL default Ɔ', `created` datetime default NULL, `modified` datetime default NULL, `description` text character set latin1 collate latin1_general_cs NOT NULL, `amount` decimal(10,0) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I'm hoping to get output like: user_id | balance(sum of amounts) ------------------------------------------ 1 | 70 2 | -70 3 | 10 4 | 5
View Replies !
How To Save Accounts Based On Variable Amounts Of Time
I'm currently building a website where users can signup for various accounts. An account may last 30 days, 12 months or 24 months. If a user's account is about to expire, an email notification is send, via a CRON Job. I want to save the different account types in the database, but I'm not sure how to save the duration of an account. If it was all months, I could simply save ཈' or པ' as an INT, but it also has an account for 30 days, which is more flexible. Edit: It would also be nice if I could ORDER BY duration..
View Replies !
Handling
i have exported a large database - that big i cant import it in one piece, so i have to import it via mysql_query and first explode the whole string. but, i have problems with ';' at the end of each clause. how can i tell mysql to eat them? INSERT INTO mytable a,b VALUES ('c','d'); __________________________________^this.
View Replies !
Handling Checkboxes
I have the following db structure: user(userId,.....) userOption(userId,optionId) option(optionId, optionName, ....) I pull all the optionNames from the db and present them as a form of checkboxes (I mark them as selected if there exists a corresponding entry in userOption). What's the most effective way to process the submitted query?
View Replies !
IN Operator Value Handling
I have a small question on IN operator. What is the maximum count of values an IN operator can handle properly in a query. Code: SELECT * FROM table WHERE id IN (1,2,3,5.....) The values that i get are close to a million and i want to backup them up using mysqldump using the IN operator.
View Replies !
Handling Dates
Hi I have a table that I’m going to use for assigning equipment to a task since its going to be used for a simulating a company I need to filter out Sundays and non workable hours. I calculate the time the equipment will be used by multiplying the number of units to be produced by the time it takes mo make one, but I also need to add Sundays and the time that the factory isn’t producing, work hours are from 8am to 5pm. I’m really lost and don’t know to this, any help will be greatly appreciated. #---------------------------- # Table structure for orden_produccion #---------------------------- CREATE TABLE `orden_produccion` ( `Id` int(12) NOT NULL default Ɔ', `ProductID` int(12) NOT NULL default Ɔ', `TransaccionID` int(12) NOT NULL default Ɔ', `EquipmentID` int(12) NOT NULL default Ɔ', `StartDate` date NOT NULL default ��-00-00', `EndDate` date NOT NULL default ��-00-00', `Quantity` int(8) NOT NULL default Ɔ', PRIMARY KEY (`Id`,`IdProducto`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; The end date is what i want to calculate
View Replies !
On Handling Left()
i have a table. which has a column named 'pattern' and the values are look this pattern ------- 205332 212203 106466 203465 106465 203111 * data in varchar format Now i want to list all the raws thats left 3 digit = 203 i use this query and it doest not work it seems. Code: select * from tbl where left(pattern,3) = '203'
View Replies !
Deadlock Handling In InnoDB
I was hoping someone could confirm my understanding of how InnoDB handles deadlocks (error 1213) and timeouts (error 1206). The way I understand it, with AUTOCOMMIT=0, if I issue 3 SQL statements (updates), A, B, and C (in that order), and get one of the errors above while issuing statement C, InnoDB will have rolled back statements A and B. To recover from this error condition, I need to re-issue statements A and B, followed by C. Then, if all three go through, I can call COMMIT and have A, B, and C committed. Is this correct?
View Replies !
Handling Null DB Values
if a DB value is null, how do i handle it when it comes back from the database. I am assuming i need to convert it to an empty string or something of that nature? How can i do this? For example, the following code has a lot of null values:Code: SELECT CONCAT("<img src='thumbs/",s.Thumbnail,"' alt='' class='teaserImage' /><h3><a href="javascript:NewWindow('free.aspx?vod=",s.StoredVideoID,"','mypopup','1024','768','center','front');"><u>",s.Title,"</u></a><h3><p>",LEFT(s.Description, 100),"...</p>") As Event from StoredVideo s;
View Replies !
Handling Null Values
I have a tab-delimited text file containing null values (back to back tabs). When I use mysqlimport to import the data I get the following error: mysqlimport: Error: Incorrect date value: '' for column 'field_name' at row 1, when using table: table_name I would like the import tool to ignore these values and insert nothing into the table for those particular fields. Is this possible?
View Replies !
Huge Database Handling
I would like to have some hints about very large database handling with MySQL. I have a big database (10M+ users and their data) and I don't know if it's better to have just one single database or several smaller dbs (partitioned by user) that in any case will be running on the same machine. What is the advised maximun size (if there is) of a single database ?
View Replies !
Handling TREE Structure
I need some advice on how to handle SELECT query for TREE like structured object, whereby each node on the tree can be having unlimited child nodes. What is the best way to query a node with all of it's subordinate nodes ? The simplified table structure will look like as follows: nodeID | node_name | parentnodeID Is using Recursive tehcnique recommended for this case ? (I'm afradi this will consume a lot of server resources by using this technique)
View Replies !
Handling Huge Records In DB
I have two tables in my Database. Table1: Master Table Table2: Child Table [Transaction Table] Actually values inserted to child table is from front end. They will get the values from Flash. The Values which get inserted to the child table can be nearly 700 - 850 rows at a time of insertion. These values change when user do some modifications in the page then the new values from flash to PHP and corresponding old values are deleted in Database and again we are inserting the new values. But we think it will cause some issues in feature since the autoincrement value of the table will increase quite soon and reach higher value as we are deleting the old values and inserting the new values. we cannot update that since, the value we receive may be in addition as to add and update for existing one.
View Replies !
Recordset Handling In MySQL/PHP
I am using PHP / mySQL to write a Quiz. I currently load all the questions into a recordset. I then, iteratively, call this page, displaying the first question and possible answers, then the second question and possible answers and so on. While this works fine it is obviously dead ineffecient - I load the recordset with the same set of questions each time. Also, the questions come out in the same order each time. I know you can use the LIMIT clause to get a record / n records at a time and you can also use RAND() to mix the records. Ideally, what I would like to do is one select statement with a RAND() which returns all the questions in a different order time the quiz is started. However, once the recordset is loaded, I want to use it and get the next question on each subsequent call of the file.
View Replies !
MySQL Handling Heavy Loads
Has anyone used MySQL in a public web site environment that receives 1 - 1.5 million sessions/day? Sessions are mostly content and light commerce. Site spikes to 3 million sessions/day about 4 times/year. Trying to find a good example of MySQL holding up under this kind of load.
View Replies !
MYSQL Insert Error Handling
I tried to insert a row into a table in MySQL and it gave me the following error? WHY and WHAT should I do? ERROR 1205: Lock wait timeout exceeded; Try restarting transaction
View Replies !
Handling Image Submissions With MySQL/PHP
I've got an idea I'd like to try out which will harvest a lot of user-generated content. Well, some, at least :-) Quite simple - a page that asks people to talk about their love of their life. I'm referring, of course, about their vehicle :-D Ideally, I'd like to have the data captured and saved in the DB as unapproved. I would then read and approve (or edit) and bingo, job is done as it gets poured into the given template. Howeber, I've never handled images in MySQL before so need some advice. 1) I'd rather have them images stored on my server than hotlinked (too unreliable) but don't want the images to clog up the DB. My thinking was that you'd probably have something like this: a) user submits image b) image saved in DB in blob c) I review page and image d) if approved, entry in DB moves to 'approved' and the blob is removed and then created on the file system. Location of said file is then updated in DB Does this logic seem right? 2) I want to be sure that images are not above a certain height and width. I'm not concerned so much about file size. How straightforward is this in PHP? So, I really have zero experience in this matter, so I'd appreciate anything you can add here, be that: * Telling me I've got it all wrong, and that's a dreaful way of doing things :-D * Best practices * Things not to do * Any useful code samples you could share I am going to Google around for some tutorials right now, but I do like to get a recommendation or two from people who have already picked up a tip or two :-)
View Replies !
Help On Error Handling In Mysql ,in Primary Key
i have a question.I have column which is a primary key.i have inserted a value also to it.Now again if i insert the same value it shouls not give a error.but instead continue.So how to go about it.like vb onerror is there any way that we can write the script in mysql or shell programming do reply quickly.
View Replies !
Error Handling When Adding Duplicate Entry
I have an application with MS Access 2000 as the front end and MySQL as the back end. All was well until I upgraded the MySQL (Linux) server. The Problem: I insert data into a cumulative table. Before when I did this, and there were duplicate entries, the duplicate entries were rejected and I got a return code with the number of affected rows (number added). Now, I get a MyODBC error and the application stops when trying to add duplicate entries. 3.22.27.1 - previous ver MySQL that did not return error 4.0.16.0 - current ver MySQL that returns error. The MyODBC was not updated on the client (Windows) computer. However, I tried updating MyODBC from ver 2.50.39 to ver 3.51.03 with no change in results. So I am guessing that MySQL has changed what it passes to MyODBC when it encounters this condition. How can I set MySQL (or MyODBC or whatever) so that the insert query does not halt the insert when it encounters a duplicate entry?
View Replies !
Binary Data Handling With Mysql Client
I am wondering what I miss to be able to handle binary data from the mysql client. I have ensured that the user has file_priv set to 'Y' and that max_allowed_packet is larger that the binary lumps handled. Here is my version: Ver 4.1.5-gamma-log for pc-linux on i686 (Source distribution) Right now I'm trying this to insert binary data: mysql> update table set col=load_file("/path/file") where id=1; ....and this to fetch the data again: mysql> select col from table into outfile "/path/file"; As for insertion I am actually not quite sure whether or not it goes well, since I get garbled data out from the fetching. Any way to check the data when inside the table (data length, for instance)? I have two problems with the fetching of data: 1) I cannot specify a path. I am only allowed to specify a filename, and the that file is written to the table's directory in the server's data directory. 2) The resulting file is garbled data. I am not sure if this garbling happened at the time of insertion of data or when I fetched the data out again.
View Replies !
Sort Not Handling Negative Numbers Correctly
In the table in my database I have a column of numbers ranging from -3.3 to 7.1. When I sort the column using (SELECT * FROM mytable ORDER BY mytable;) the column gets sorted as follows: -1.9, -3.3, 1.7, 2.2, 7.1 I streamlined the list as there are actually over 500 numbers between -3.3 and 7.1. The problem I am having is that when I SORT mysql is recognizing -1.9 as a smaller number than -3.3. When I try to find the minimum number in the column using (SELECT MIN(mycolumn) FROM mytable;) mysql also displays -1.9 as the minimum number. Is this a known problem with mysql or am I doing something wrong?
View Replies !
Error Handling And Raising Application Errors...
im still not very familiar using mysql so im having a little bit of trouble here...how can i call a error handler in mysql? what i need to do is something like this in a stored procedure SELECT field1 INTO var1 FROM tbl1 if the result of the above query is nothing then an application error should be raised.
View Replies !
Automatically Generate Text Excerpt For Text Near Keyword Hits?
I would like to generate an automatic text excerpt showing the text close to the first occurance of a keyword needle in a text haystack. For example: searching for keyword = "foo" in a table called Webpages in the column Content = "Yadda, yadda, yadda. Bla, bla, bla foo yadda yadda yaddda bla bla bla" would return a column called Excerpt = "...a, bla foo yadda yadd..." Is this best done with PHP after fetching the entire column Content, or will it be more efficient to do directly with MySQL? What would that SQL query look like?
View Replies !
Exporting Blob / Text Column With Hyphen Delimited Text
I have a table that contains a column (blob, text type, formatted as MEDIUMTEXT) with text hyphen (-) delimited, listing rooms by numbers, and I need to export or insert that data into another table, where all other info on the other columns remains the same, but I need the data on the blob field to be single listed another words, i have a table like this (example): School Rooms Your Hometown High School 1034-1035-1037-1039 My Hometown High School 208-178-1432-1728 I need it like this: School Rooms Your Hometown High School 1034 Your Hometown High School 1035 Your Hometown High School 1037 Your Hometown High School 1039 My Hometown High School 208 My Hometown High School 178 My Hometown High School 1432 My Hometown High School 1728
View Replies !
Full Text Search - Whats Best Text Or Varchar Or Both
I'm creating a search script in perl using the FullText Engine of MySQL. The table has one field with one kind of description thats a type VARCHAR and another field with a longer description of type TEXT. Whats better for performance to search over the two fields or or merge the value in the VARCHAR field with the value in TEXT field and just search over one field the TEXT one.
View Replies !
Insert Text File In A Text Field
2 questions: 1) How can I insert a text file into a TEXT field (of a table) by a mysql instruction ?; 2) How can I insert a microsoft word file into a BLOB field (of a table) by a mysql instruction?
View Replies !
Help: Replacing Same Text With New Text In Hundred Rows
I have about a couple hundred email addresses in a table... every email address includes @domain.com (the same domain). However, that domain is changing to a new domain... and I need to replace only the @domain.com to @newdomain.com. Any ideas how I do that? Ie.. a table might look like this: ID -- email 1 -- aaron@domain.com 2 -- brian@domain.com 3 -- chase@domain.com needs to change to 1 -- aaron@newdomain.com 2 -- brian@newdomain.com 3 -- chase@newdomain.com
View Replies !
Adding Text To Existing Text
I am trying to add a string of text to the end of text that is already stored in the DB. For example likes = "I like television" is already stored in the DB The user then wants to add "-I like music". So I want the end result to be: likes = "I like television-I like music" I tried: UPDATE table SET likes = likes + "-I like music" but that gives me an error and only works for numbers What is the text equivilent of adding to an existing field?
View Replies !
Large Db
i have some problem when trying make insert a lot of row in a large db. I have to pharse a 10 file of 1 million of record and insert it into a table ... when arrive at around 1 million of record, every 1000 / 2000 records, the query take 1 or 2 second to make 1 insert, then restart with a normal fast inserting ... in first i think was an hardware problem (like hd with bad sector or similar) but i changed pc and the problem persist the so is slackware 10.1 with recompiled mysql 5.1 the client program that made the insert is a vb application and the odbc is 3.51
View Replies !
Large SQL
Just wondering if there is a better way to view SQL code. Reason I ask this is because I have downloaded a backup of my database onto my local machine (desktop) and the only way I can view the .sql file is in Note Pad but it takes ages to load. Is there any software that allows me to view my msSQL backup?
View Replies !
Large Databases
We are in the process of building an application to front end a database of about 2 million records. I would like to know if MYSQL can handle this and if not, what would be a good database to go with. We would like to use opensource as much as possible, but if MYSQL cannot handle this kind of volume then we will have to go in for commercial software.
View Replies !
Precaution For A Large Db
I have to handle a very big (~50 gigabyte) database (quite all in a table), and I am going to use mysql. Can mysql handle all that ammount of data? What problems I am going to have? Should I take any precaution? Is some other database-engine more suitable for this work? Posgresql maybe?
View Replies !
Large Tables
So i've got a table with 15 million rows. id is the primary key SELECT COUNT(id) FROM listings WHERE id != 0; Takes about a minute to load... which is insanely slow.. anyone know what could be causing this?
View Replies !
A Large *.sql Import
I am trying to import a very large .sql file of about 165mb. When i try to import the file i use "source C:DB.sql" and it runs for a bit and then stops. When i try to query on this table it crashes. But when i do "show columns from DB;" is show me the information correctly. I did update my my.ini config file with: innodb_data_file_path = cseibdata1:10M:autoextend still not working.
View Replies !
Importing A Very Large DB
I have a rather large .sql dump that I was given to work with thats about 3 gig. I cannot get a smaller .sql dump unfortunately. I am using wamp server on my local machine and importing this database will take forever. I have two questions...... Can I optimize the amount of memory or processor dedicated to php or mysql to speed this up? Is there a way to add some sql parameters to the import to only import parts of the database I am working with? One of the columns is a unix timestamp which would also solve my problem.
View Replies !
|
TRACKER
