Fast Input Of Binary Data
We are considering MySQL as an alternative to the Sybase databases that we have now. One thing we need to be able to do is insert tens of millions of rows of data quickly. Each row may have 10 to 20 columns. The source of these data are binary data files. Is there a way to do this quickly? Currently we are using Sybase's bulk copy utility bcp. Is there something comparable in MySQL?
We also need to be able to extract large amounts of data from the database and put them into binary data files. Again we are currently doing this with Sybase's bcp utility. Can we do this in MySQL?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
How To Save Binary Data Into A Binary Field
I want to save some binary data into MYSQL. dim s as string s=chr(0)&chr(1) chr(3) & chr(6) & chr(7) conn.execute "INSERT INTO test values ('" & s & "')" The field in the table test is binary. The odbc returns an error but using the query browser the query ends good. Any idea?
View Replies !
View Related
Fast Data Retieval
I want your suggestion about how I can retrieve data very fast from a mysql table.The table size is 1.2 GB. Whenever I am trying to run a query which includes group by it is taking too much time.
View Replies !
View Related
Handle Replacing ' In Data Already Input
I have recently been given a project that belonged to another programmer. The issue I'm having is that no data validation was put in place of the program and so now I have data that has characters that are not allowed by MySQL, specifically apostrophes. I now how to do it in PHP to replace the character, but what I need to do now is to replace the data already in the table. I'm specifically talking about a user's login name. Can I escape it inside the database and not have the user's login name change? For example I have a username that is O'LearyDM. I haven't been able to comb through all the code to figure out just how the original programmer was able to even allow insertion of apostrophes, but now I need to fix it.
View Replies !
View Related
Formatting Data From Input Form
I have a form on my website and it's imperative that regardless of the way users enter their information into the form, I need the data to be stored in the database in a certain way. For example: john smith needs to be displayed as John Smith JOHN SMITH needs to be displayed as John Smith Can I do this at the database level after the fact or do I have to do it via PHP at the form level?
View Replies !
View Related
Counting Columns That Match Input Data
Is it possible to count the number of columns that match certain conditions and return a single value? For example in the database we have a record where: Column 1 = Male Column 2 = A Column 3 = B Column 4 = D Column 5 = M And the data to be used for the query is: Column 1 = Male Column 2 = D Column 3 = B Column 4 = Z Column 5 = M I would like to be able to return all records where Column 1 = Male and also return how many columns (from Column 2 through 5) match the data input. In this case the number would be 2 since Column 3 and Column 5 in the database match the data used for the query. If no columns match the input data, the number returned would be 0.
View Replies !
View Related
How Do I Extract All My Text Data Input From A Php Forum?
I manage a small website using phpbb on a Solaris platform, using mysql 4.x. After being repeatedly hacked, I'd like to migrate to a less singled out forum bb, but without losing my data--most of which is in Chinese characters (so doing string searches is definitely out). I have direct file access via ftp to the server, and i've also downloaded the corrupted files, only two of which have been modified by the hacker, namely, phpbb_config.ISD phpbb_forums.ISD Unfortunately, the last previous backup the hosting company performed was some 8 months before...
View Replies !
View Related
LOAD DATA Gives Problem When Last Column In Input Is Empty!!
I had created a table with 20 fields, setting most of them to DEFAULT to NULL. (partially shown bellow). I populated the table with LOAD DATA. Last column in most of my input rows are empty, yet separated by a tab from the previous column. So most of the rows typically ends with a tab (and a newline, ofcourse). Load data works fine with out any warning. But, when query the database, i do NOT see NULL value in last column(lab here). This column just looks empty. Where as I expected NULL to be there. Is there something i miss here. NOTE: If a column does not have the data in the middle of the row (say for 4th field), then I get the NULL value set to that filed. Which is perfect. Code:
View Replies !
View Related
Passing Search Data To D/b Via A Hyperlink And Without Input Fields
How can I pass hard-coded search data to a MySQL results.php page with a hyperlink and without input fields? These are the details: I have a simple online database which can be searched by classification (business type) and/or company name (it does an 'or' search). There is an index.php (search) page and a results.php page. (The results page also has a search, which leads back to the same results page.) What my client wants to do is this: If some records are returned, it gives a list on the results page of companies found to match the criteria (this works just fine). But if no records are found, he wants a list of classifications, each of which is clickable, and will do a search for all companies with that classification. It doesn't matter whether this would be on the same results.php page or another page.
View Replies !
View Related
Loading Binary Data
I have a large data set of binary data files and I'd like to create a database so that I can search on the data contained in these files. I'm new to database development but from what I've seen it is only possible to store & search variables in the database in text format. Is it possible to load the files as binary variables and still use MYSQL commands to search and manipulate the database? For example, assume a file contains a variable X which is a 32-bit floating point number ranging between 0 and 10. Does the variable X have to be converted to text first (possibly using a C program), stored into another text file, which is then loaded into the database/table? Or is it possible to load the 32-bit floating point number directly from the original binary file and perform a MYSQL query looking for instances of variable X with values 1.2 and 4.8?
View Replies !
View Related
Add A BINARY Data Table
Is it possible to add a data table in BINARY FORMAT to an existing database which runs on a linux server with apache webserver and MySQL database? Our hosting provider keeps telling me that they need a plain ASCII format in order to add the table but the programmers who did the database programming keep telling me that it should be no problem to add a new table in binary format to our existing database. I am no programmer at all and kind of sitting between the chairs now. Our programmer tells me I should switch to a different hostign service which is able to use the binary format of the table and our hosting service keept telling me to get the ASCII version of the table in order to get it uploaded and inserted in our database. I hope someone on here can give me some instructions which I can forward to our hosting service so that they will be able to get our binary data table into our database.
View Replies !
View Related
Storing Binary Data
I am just starting to get into MySQL and PHP. I am using Apache as well. I have read 2 things: You can store binary data in your MySQL database in a BLOB or text type OR you can store binary data on the file system and then store pointers to files in My SQL. I am not all the savvy on the tech talk but I want to know how to do the latter (specifically PDF documents). I have figured out the first way (as there seems to be much more information about this) but I want to know how to do the second version.
View Replies !
View Related
Compare Longblob With Binary Data
checking the data of a longblob for equality. assume = the `data` field is of type longblob. the following statement returns = the image records with equal data. SELECT * FROM `image` WHERE `data`=3D? how does mysql performe the test. does it check the length of the blob = before comparing the data, or makes it sense to add the length test as = follows SELECT * FROM `image` WHERE LENGTH(`data`) =3D ? AND `data`=3D?
View Replies !
View Related
Converting Encrypted Binary Data To HEX
I currently do the following to encrypt strings of text that are less than 20 characters - aes_encrypt('mystring', 'mypassword'). This gives me a binary string, which i'll have to store in a blob type field. I'd like to be able to use text or tiny text to store them. So how can I convert this to hex or any other text format (but not like the original string)? Will using hex(aes_encrypt('mystring', 'mypassword')) be OK? Do you see any drawbacks in doing this? What will be getting encrypted is only text strings. Will there be any problems by dong this?
View Replies !
View Related
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 !
View Related
BLOB Data To Store Binary Files
I am sold on the idea of using BLOB data to store binary files. I have created an application that accepts and HTML form HTTP header upload of an image file and stores it as BLOB data in my database. I am now stuck because I can't get the data out of the BLOB field and use it as an image in an HTML file.
View Replies !
View Related
Reading Binary Data With Prepared Statements
is there any example of getting binary data from a mysql database?. I tried the example in http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-fetch.html which reads strings, numbers and datetimes, and it works fine. But how should I do the same with blob fields?. What is the buffer_length for blob data type? (in the example it is STRING_SIZE=50 for string, but it doesn't work for blobs (FIELD_TYPE_BLOB). I tried calling mysql_fetch_lengths but it doesn't work for prepared statements.
View Replies !
View Related
Perform Case Insensitive Select On Binary Data
I have a table which has a column that store text data in *binary* format. I understand that mysql is case insensitive for if a field is *text* format, but case sensitive for *binary* data. I d like to ask how can I perform a query to select any tuples that satisfy a condition such as that the binary field data has a string "the value" but ignoring case? For example, I have a table which as a field "content" defined as "binary" type. I d like to select any tuples whose "content" contains the word "mysql", and ignoring case. So I would expect to get tuples whose "content" is "mysql", or "MySQL" or "mySQL" etc. I have tried SELECT content FROM mytable where LCASE(content) LIKE LCASE('%MySQL%'); However this only gives me the tuples that contains string "MYSQL"... but not "mysql" or "MySQL" or "MySql" etc...
View Replies !
View Related
Writing Large Chunks Of Binary Data To MySQL With ODBC
I'm having a lot of trouble writing large chunks of binary data (tests are in the range of 16-512K, but we need support for large longblobs) to MySQL using ODBC. Database is local on a W2K system, but I have to support all modern Windows systems, and a variety of ODBC configurations. (I'll be testing against multiple ODBC databases soon - but development is against MySQL) I've been able to adapt some example code that executes a statement (SQLExecute) and uses SQLBindParameter to mark the longblob field as SQL_DATA_AT_EXEC. That works. However I really need to use a result set (opened for update), and to add new rows using SQLBulkOperation( hStmt, SQL_ADD) This works for the normally bound fields, but fails if I bind a variable using something like: Code:
View Replies !
View Related
Exporting Binary Data From Access 2000 Into MySQL 4.1.5 Gamma
I'm trying to export a pictures table, my mySQL database table already exists so I have linked both tables in Access and I tried to run a simple append query in Access to export the data but I either : - lose the connection to mySQL - have packets that exceed max_allow_packet I have tried different things to set max_allow_packet to a higher value which seems to be 1M by default. I want it to be 1G like setting the value at run-time from the prompt but it seems that no matter what I tried, I either get the syntax wrong or the command is simply ignored. I have also added in my.cnf, the following lines :
View Replies !
View Related
Facing Error While Restoring Binary Log Data From A Text File
I took the backup of my binary log file "dev02-bin.000003" as "binlog000003.txt" at the location "/backup" directory with the help of the command shell> mysqlbinlog "/var/lib/mysql/dev02-bin.000003" > /backup/binlog000003.txt Also now i deleted the original log file i.e "dev02-bin.000003" from the "/var/lib/mysql" directory" Now i tried to restore the same binary log file from the text file "binlog000003.txt" with the help of the command shell> mysql -p < /backup/binlog000003.txt after running this command i am getting the error ERROR 1062 (23000) at line 21:Duplicate entry 'v9jfud8bkhjm8v19kqu3dhujq0' for key 1
View Replies !
View Related
Converting MS Access Dates To MySQL Dates During LOAD DATA INPUT ?
My insert code is below. The MySQL server is on my local machine and is version 4.1.22. I am exporting from an access table with 6 fields to a mysql table with those 6 plus 5 more fields. The main problem I am having SO FAR, is converting dates. The data file dates are formated like: 2/2/2006 0:00:00, 12/20/2006 9:22:05 Any ideas how to format those into mysql friendly dates? Also, how do I convert currency fields in the load data process? I know you use SET by what kind of formula? =========================================== LOAD DATA INFILE 'C:Documents and Settingspath_to_filedata.txt' INTO TABLE auto (field2, field3, field4, field5, currency_field6, field7, date_field8, date_field9) SET id = MD5(UUID()) FIELDS TERMINATED BY ',' LINES TERMINATED BY ' '
View Replies !
View Related
Key1 Without Binary And Key2 With Binary
I have two search keyes, key1 and key2. The following is where clause. where say like '%#key1#%' and say like binary '%#key2#%' I have to use binary for key2 like the above. Now I like to make the following where clause. say like '%#key1#%#key2#%' The where clause above will retrieve all records which has #key1# and #key2# in that order. My problem is in the use of binary, i.e I have to use binary for key2. I can make it like the following for using binary. say like binary '%#key1#%#key2#%' But the code above makes not only key2 but also key1 bianry. Key1 doesn't need bianry but key2 needs bianry. How can I make the where clause key1 without binary and key2 with binary? The following would-be code doesn't work correctly, but it'll show what I want. woudl-be code say like '%#key1(non-binary)#%#key2(binary)#%'
View Replies !
View Related
How Fast Is A Lookup?
I have 100K records in a mysql db and wondered how long - approximately - it takes to look up a matching ip address. My code looks something like this: PHP $ipaddress=$_SERVER['REMOTE_ADDR'];$offer=$_GET['offer'];$affiliate=$_GET['affiliate'];$db1=mysql_connect("mysite.com",'user','pw');mysql_select_db("mydb",$db1);$q=mysql_query("SELECT * FROM `offers` WHERE `offer`='$offer' AND `affiliate`='$affiliate'");if (mysql_num_rows($q)==1) $r=mysql_fetch_assoc($q) AND mysql_query("UPDATE visitor_log SET `sale` = Ƈ', `oid` = '{$r['offer']}', `comm`='{$r['comm']}' WHERE ipaddress='$ipaddress'");mysql_close($db1); Does this sort of thing take under a second? Ten seconds? I've no idea.
View Replies !
View Related
What Is The Best Way To Get Fast Query?
I've uploaded big database with millions of rows (different places with geographical coordinates) to my server (MySQL). I wonder how can I get faster queries? I have cheap server. It's Celeron 2.8 with 1 Gb of RAM. For example, this query takes 10 seconds: Quote: SELECT * FROM table WHERE latitude > ཬ.832633619561896' AND latitude < ཬ.90083790234088' AND longitude > ƈ.1649932861328125' AND longitude < ƈ.5014495849609375' ORDER BY 'population' DESC LIMIT 0, 20 I need to get fast queries, like 0.1-0.5 seconds instead of 10 seconds. What is the best way to get fast queries: 1. Better, more expensive server. Will this solve the problem? Or it's not worth of the money? 2. Optimizing queries. Still I don't think this will give me 0.1 seconds query instead of 10 seconds. Am I wrong? 3. Splitting database to 500-5000 tables by coordinates (splitting map virtually to small quares). Is this a good idea?
View Replies !
View Related
Fast Keyword Search
I´m asked to "speed up" a keyword search based on MySQL. The material i´m given to work with is a quite large MySQL table with 1.5 mio rows, defined something like: CREATE TABLE datarecords ( id BIGINT(20) NOT NULL auto_increment, [3-4 other INT type columns], keywords TEXT NOT NULL, updated TIMESTAMP(14) NOT NULL, PRIMARY KEY (id)) TYPE=MyISAM; The search requires the id-fields of the datarecords table to be returned, based on keywords within the keywords-field, like so: SELECT id FROM datarecords WHERE ( (keywords LIKE '%someword%' AND keywords LIKE '%otherword%') AND (keywords NOT LIKE '%notthisword%' AND keywords NOT LIKE '%thisneither%') ) The table has grown unexpectedly large and will keep growing, so a different method for keyword searching appears to be required, since search-speed has decreased significantly (now at about 12 secs, depending an the number of searched keywords) Using FULLTEXT indexing has brought no performance increase... Question: Does anybody have any suggestion on how to tackle this type of problem, to get search-speed down to about 2-3 secs? One way i´m looking at this is to create a seperate keyword-table something like this: CREATE TABLE testkwdlist ( keyword varchar(32) NOT NULL default '', ids text NOT NULL, PRIMARY KEY (keyword) ) TYPE=MyISAM; and filling this table up with the keywords and listing the IDs in the ids-field, doing seperate queries for the positive and negative searches and compiling the actual resultset in code... Does this sound it has potential for a performance boost or is it competely insane?
View Replies !
View Related
Innodb Fast, Then Very Slow
I have a massive table that I converted to innodb. It made a huge speed difference in my site because tables locks basically stopped happening. However, after about 10 minutes mysql slows to a crawl. All queries seem to take forever to run, like 20-30 seconds each. If I reset mysql its all fast again for another 10-15 mins. This only happens when I have that one table set to innodb. When everything is MyISAM the database speeds are consistent, I just get a lot of table locks (which I'm trying to reduce).
View Replies !
View Related
Looking For Fast MySQL Components For Delphi 6/7
I'm on a trek looking for fast reliable (bug free) MySQL 4.1 components for Delphi 6/7 for accessing large MyISAM and Innodb tables. Can anyone make any recommendations? I've tried MySQLDAC from MicroOlap and unfortunately support is terrible. (No reply to any of my e-mails in the past 2 weeks). I'm also looking at Zeos (where are the support newsgroups?), SciBit, and CoreLab. Does anyone have any recommendations? Horror stories? Ones to avoid?
View Replies !
View Related
How To Make Database Access Fast
In my linux server (LOCAL), *accessing data through the PHP scripts from MySQL (3.23.41)tables, it is taking more time. * Earlier it was very fast. * Now a days some reports it is taking more than five minuits. Earlier it use to come with in a minuit. *What may the problem and what I have to do spead up the process.
View Replies !
View Related
Fast MySQL Backup With Rsync
wouldn't it be possible to do a fast backup of the MySQL data in this way: 1.) rsync the MySQL data (with running mysqld) 2.) Stop mysqld 3.) rsync the MySQL data again Please answer. I have found a similiar post in the year 2001, but no replies. I think, if this is possible, it would be very useful for a whole bunch of tasks. (For example changing hard drives in servers: You could rsync everything, including the mysql data. The only downtime would be from the point of shutting down all daemons to get a consistent snapshot to the point of rebooting the server.)
View Replies !
View Related
Slow Join On Otherwise Fast Views?
select * from vw_fielddata takes 16ms to return just four rows, why does select * from vw_fielddata firstname join vw_fielddata secondname on secondname.AccountID = firstname.AccountID take over an hour? The views themselves contain joins on tables and further views, and although AccountID isn't a primary key from the tables it's gathered from it is a foreign key in them (I assume this makes it indexed?).
View Replies !
View Related
Slow Insert On My PC But Fast On My Laptop?
I'm just wondering what can be the cause of slow inserts on my PC but super fast on my Laptop? I'm using MySql x64 on both PC. Operating System should be also identical as I mirrored it between these two PCs but PC would have more programs running.. Settings on my MySql should be identical unless there's more than just Server Instance Config Wizard. My PC is definately faster in terms of CPU and Hard drive compared to my Laptop. But insertion on laptop was like 10x faster.
View Replies !
View Related
Fast In INTERNET, Slow In Wifi
- Internet connected to mysql: all ok and fast - Wifi or lan connected to mysql: all fast, except UPDATE, INSERT, DELETE, that works in about 5-10 seconds. How is possible that UPDATE, INSERT or DELETE registres is slow in network connection?
View Replies !
View Related
Trying To Write A Two-part Fast Query
The situation is this: there are two tables, one called "objects" (fields: object_id[int] and object_name) and properties (fields: object_id[int] and property_name and score[int]). Objects have one or more properties. In short, what these 2 queries need to ask are "what objects could an unknown object be, given what properties I know?" and "what property should I measure next to most narrow down the possible objects?" The objective of the first query is to return an object who has all of the given properties scored above or below the given value. For example, I might look for the object_id of an object who has the property "green" scored above 3, the property "hot" scored below 2, and the property "bright" scored above 0. It might have other properties, too, but it must have these 3. And then, for the second part, I need a query that will select properties which are NOT in this set of given properties but are relevant to the returned set of objects. In this part, I'm not concerned with the property's score - just that it is relevant to the objects. In other words, this query might tell me that the property "hard" is relevant to 3/4 objects (even though one of them had a very low score for the hard property, another had a very high one, etc). Basically I want to know which new property, once I can determine the score of this new property, will help narrow down the object-set the most, so that I can collect data for the most relevant property to narrow down the object set.
View Replies !
View Related
Searching Rows Containig A Substring Fast
I am creating a server indexing files in my local area network, in order to provide a searching feature. So i want to make it possible to searchsuch rows where the 'name' (VARCHAR) column contains a substring submitted by user. an i use a full-text index to make it faster? from my first sight i noticed that this index can be used to search for words in a string. So the example below would not go: searching 'cool' in 'uncoolness' Am i right? Are there any other solutions? Maby some other databases provide this use case?
View Replies !
View Related
Simple Fast Way To Grab The Highest ID From A MySQL Table?
I have a MySQL table on my web server (external web host) that has an auto-increment integer as it's primary key field. Is there a quick PHP function that will get me the highest ID in the table, that is, the ID that belongs to the last record added. Note, I need it to work across and between connection session, not from within the same connection session.
View Replies !
View Related
Fast SELECTs Versus Slow INSERTs With Indexes
I run a MySQL database using phpMyAdmin interfaced with an application written in C++, and most of my nine database tables have 500,000 - 2,000,000 records in them. These tables also have about five-to-six indexes each. This makes working with the C++ application extremely fast, which is nice. Any SELECT statements can be run in less than a second, and I'm very happy. The problem comes when I have to insert new records into the tables. Every morning, I am received a pipe-separated .txt file of the previous day's new records (about 500 - 1,000) that I have to insert into the tables using my C++ application. Well, this takes an excruciatingly long time (almost an hour, actually) and I need to find some way to make it run faster. I am thinking of several options here:
View Replies !
View Related
How Do I Rewrite A Slow Subquery Into A Fast Join? (Prolly Real Easy For A Non-noob To Answer)
I used subquerys because they made more sense to me, until the table got "a lot" of data in it (not really... just 1000 entries) - then all querys including subquerys slowed down to 4-5 secs EACH!! :) This is insanely slow. What I am doing here below is looking into what messages a user has already read in the subquery, so that none of the ones he already has read will EVER again show up for him. So, how do I rewrite this subquery: NOT IN (SELECT reffen FROM $readt WHERE sender = $nr) From this entire SELECT: SELECT halfref, brokensms, DATE_FORMAT(arrived, '%y'), DATE_FORMAT(arrived, '%m'), DATE_FORMAT(arrived, '%d'), DATE_FORMAT(arrived, '%H'), DATE_FORMAT(arrived, '%i'), priv FROM $halft WHERE sender = $nr and halfref NOT IN (SELECT reffen FROM $readt WHERE sender = $nr) order by id desc limit 1 Into a faster join of some sort? I'd aprichiate if you told me what each part of the rewrite actually does, because I been reading about joins for a day now and still don't get them at all!
View Replies !
View Related
|
TRACKER
