Re: storing images in the database
I've stored millions of images/files in mysql with minimal performance hit, performance loss is mostly based on your storage implementation. Checkout this article: http://www.dreamwerx.net/phpforum/?id=1 On 10/11/06, Greg Donald [EMAIL PROTECTED] wrote: On 10/10/06, Rodney Courtis [EMAIL PROTECTED] wrote: I want to store jpeg images in a mysql database from php in the form of 'blob' types. Is this possible and can you detail for me the steps to achieve this? For a nice size performance hit, yup: http://www.zend.com/zend/trick/tricks-sept-2001.php?article=tricks-sept-2001kind=trid=2033open=1anc=0view=1 -- Greg Donald http://destiney.com/ -- 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: please help optimize this query
I'd start by looking at your schema.. Quite a lot of use of
varchars... I'd suggest using chars (takes more space but processes
faster).. Or even better, normalize the data so that you have a table
for symbols that is linked to this table via an integer based foreign
key.
Also it seems wierd for 'shares' and 'value' to be varchar? I'd think
them to be integer and float types..
Also using date as the PK seems odd..
A lot of times queries perform poorly because of poor database/table design.
On 5/31/06, Joseph Alotta [EMAIL PROTECTED] wrote:
Greetings,
I generate this report of all the holdings by symbol, summing up over
accounts. It is taking much too long. I was wondering if I can add
an index on something to make it more efficient. The first query
gets the most recent date, the next query sums it up by symbol.
Thank you for you help,
Joe.
# mdate = $m.query(select max(date) from positions where symbol = '#
{sym}'
# and date = '#{last_date}' ).fetch_row.at(0)
# this query was taking too long to process. It has been replace
with the
# following:
line = $m.query(select * from positions where symbol = '#
{sym}'
order by date desc limit 1).fetch_row
next if line.nil?
mdate = line.at(4)# fourth position is the date
next if last_date mdate
total = $m.query(select sum(value) from positions where
symbol = '#{sym}'
and date = '#{mdate}' ).fetch_row.at(0).to_f
mysql describe positions;
+-+-+--+-++---+
| Field | Type| Null | Key | Default| Extra |
+-+-+--+-++---+
| account | varchar(12) | | PRI || |
| symbol | varchar(12) | | PRI || |
| shares | varchar(12) | YES | | NULL | |
| value | varchar(20) | YES | | NULL | |
| date| date| | PRI | -00-00 | |
+-+-+--+-++---+
5 rows in set (0.00 sec)
--
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]
Stored procedure issue.
I'm having some issues creating a stored procedure to optimize tables
in the database. I'm pulling the table names from the information
schema. The tablename doesn't seem to be correctly being replaced in
the optimize command..
I've tried used prepared statements which seem to correctly replace
the tableName, but I get an error that prepared statements do not
support that kind of query.
With this current version the error is: table queue.tableName doesn't exist..
Any thoughts from some SP gurus? Thanks.
--
DELIMITER $$;
DROP PROCEDURE IF EXISTS `queue`.`sp_OptimizeDatabase`$$
CREATE PROCEDURE `queue`.`sp_OptimizeDatabase` ()
BEGIN
DECLARE exitValue INT;
DECLARE tableName CHAR(120);
DECLARE cursorList CURSOR FOR
SELECT TABLE_NAME FROM information_schema.tables where
TABLE_SCHEMA = 'queue' AND TABLE_TYPE = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET exitValue = 1;
OPEN cursorList;
REPEAT
FETCH cursorList INTO tableName;
OPTIMIZE TABLE tableName;
-- SET @optSQL := concat('OPTIMIZE TABLE ', tableName);
-- PREPARE pOptimize FROM @optSQL;
-- EXECUTE pOptimize;
-- DEALLOCATE PREPARE pOptimize;
UNTIL exitValue = 1
END REPEAT;
CLOSE cursorList;
END$$
DELIMITER ;$$
-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upload images / mp3 more Than 1 MB capacity ---- please help
For an PHP example of how to import data getting around max_packet_size, performance, etc. issues.. Be sure to read this article. Also linked from: http://dev.mysql.com/doc/refman/4.1/en/blob.html Article @ http://php.dreamwerx.net/forums/viewtopic.php?t=6 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to log some queries and not others
Cron hack? have a console running: tail -f hostname.log | grep tablename filter.log -or- tail -f hostname.log | grep -v insert into blobtablename filter.log then have a cronjob every X minutes running: echohostname.log To truncate the full querylog from getting too big? Not pretty.. On Wed, 09 Feb 2005 19:52:33 +0300, George Chelidze [EMAIL PROTECTED] wrote: Hello, I have the following problem: I have a table with a blob field and turned on query logging. I'd like to log every query except inserts into table with a blob field because my log files grow very fast and I don't like to see binary data in my log files, while other logs are very informative and important. How can I do that? Best Regards, -- George Chelidze -- 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: Log data transfer amount?
No ideas internally to mysql, you can always fire up a sniffer (sniffit, tcpdump, etc).. something like that would tell you. On Mon, 31 Jan 2005 20:32:49 -0500, John May [EMAIL PROTECTED] wrote: Anyone have any ideas on this one? : I've scoured the MySQL manuals... does anyone know if there's any way to log the amount of data that individual queries produce? Eg: like bytes transferred in a web server log? - John -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] Professional Lasso / PHP / MySQL / FileMaker Pro Hosting -- 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: SELECT question
ORDER BY host DESC LIMIT 1 On Wed, 29 Dec 2004 14:18:02 -0800 (PST), Richard Reina [EMAIL PROTECTED] wrote: I know to most of you this will seem like a mundane question, but I was hoping someone can tell me how to select the last record in a table that meets certain criteria. Like to see who hosted the last party in CHicago. SELECT host FROM PARTY WHERE city=chicago; PARTY ID |host | city | st | 237|1256 | Chicago | IL | 244|945 | Chicago | IL | 355|2987 | Boston | MA | I need a query that would give me the one with highest ID i.e. host no. 945? Thanks for any help. Richard -- 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: Upgrade?
Read thru the changelog online and see if there's any fixes that affect you or new features you want.. otherwise just leave it..I have some terribly old mysql versions running, but they are rock solid doing their job.. If it aint broke don't fix it? might apply... On Mon, 22 Nov 2004 21:31:01 -0700, Steve Grosz [EMAIL PROTECTED] wrote: I'm referring to the MySql server v.4.1.5 that I'm using now, as compared to the newer release of v.4.1.7 Paul DuBois wrote: At 20:09 -0700 11/22/04, Steve Grosz wrote: I'm using 4.15 currently, and it seems to be very stable. I noticed that a v4.17 or something has come out recently. Versions 4.15 and 4.17 of what? You're asking us whether it's worth upgrading without specifying what it is you're asking about? Do you mean MySQL 4.1.5 and 4.1.7? Is it worth upgrading? Can I just install the new version without damaging my current databases or are there specified steps? Where would I find them if there are? Thanks, Steve -- 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: Images
It's an argument that can go on forever... We have 10's of thousands of images in mysql databases.. very fast/reliable.. easy to replicate, stream, etc.. On Tue, 28 Sep 2004 08:42:13 +0200, Jigal van Hemert [EMAIL PROTECTED] wrote: From: GH [EMAIL PROTECTED] I was wondering how to get images into and out of a Mysql database was told it was possible. I have mysql 4.0.18 The general opinion is that files should be stored in a file system and not a database. There are circumstances that you might want to store binary data in a database. Take a look at the BLOB column types (http://dev.mysql.com/doc/mysql/en/BLOB.html). Regards, Jigal. -- 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: Images
Separate/dedicated servers for web/database. All the data was chunked to allow faster streaming/lower overhead (large images/files).. If you have more specific questions I can answer them.. On Tue, 28 Sep 2004 11:49:27 -0400, GH [EMAIL PROTECTED] wrote: Is there anything special in your setup that you did to have such good performance? On Tue, 28 Sep 2004 11:47:26 -0400, DreamWerx [EMAIL PROTECTED] wrote: It's an argument that can go on forever... We have 10's of thousands of images in mysql databases.. very fast/reliable.. easy to replicate, stream, etc.. On Tue, 28 Sep 2004 08:42:13 +0200, Jigal van Hemert [EMAIL PROTECTED] wrote: From: GH [EMAIL PROTECTED] I was wondering how to get images into and out of a Mysql database was told it was possible. I have mysql 4.0.18 The general opinion is that files should be stored in a file system and not a database. There are circumstances that you might want to store binary data in a database. Take a look at the BLOB column types (http://dev.mysql.com/doc/mysql/en/BLOB.html). Regards, Jigal. -- 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: Images
Most people make the mistake of using the biggest blob size to store files.. That blob size is capable of storing just HUGE files..What we do is store files in 64K (medium blob) chunks.. So if the image was say 200K in size, the metadata for the image would be 1 row in a table, and the image data would be 4 rows in the data table. 3 full 64K rows + 1 partially used rows. There is a good article/sample code here on the kind of technique we started with: http://php.dreamwerx.net/forums/viewtopic.php?t=6 Using chunked data, apache/php only needs to pull row by row(64k) and deliver to the client, keeping the resultset size low = memory overhead low. The storage servers (mysql storage) I have tested on the LAN; them storing and retreiving data from mysql (using FTP gateway) at rates of 4600K/sec.. which is I think the fastest speed my laptop network card could deliver. That's pretty fast.. Rare day when most internet users can talk to servers at those speeds. On Tue, 28 Sep 2004 09:19:47 -0700, Ed Lazor [EMAIL PROTECTED] wrote: What do you mean chunked? I figured it would be easier to track about 32,000 images in MySQL than in files, so I setup a test to see what the performance difference is and if storing in MySQL would actually work. Everything is working and it's a lot easier to keep track of the images in MySQL. I ran some performance tests using Apache's ab though and there's a huge performance difference - 38 times faster grabbing the file. It could be my test system here at home. I've asked my ISP to upgrade my production server to PHP5 so that I can run tests from there. There could also be performance hits in the script that grabs the images from MySQL, because I tried using OOP. I was told OOP would be a faster approach, but I'm pretty new to it and may not have done something correctly. I'm going to create some non-OOP scripts to cross-reference the tests. I only have one production server, so I won't be able to separate / dedicate servers. Your mention of chunking sounds promising though. Actually, I'd appreciate any ideas or recommendations you have. Thanks, Ed -Original Message- From: DreamWerx [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 28, 2004 8:54 AM To: GH Cc: Jigal van Hemert; [EMAIL PROTECTED] Subject: Re: Images Separate/dedicated servers for web/database. All the data was chunked to allow faster streaming/lower overhead (large images/files).. If you have more specific questions I can answer them.. On Tue, 28 Sep 2004 11:49:27 -0400, GH [EMAIL PROTECTED] wrote: Is there anything special in your setup that you did to have such good performance? On Tue, 28 Sep 2004 11:47:26 -0400, DreamWerx [EMAIL PROTECTED] wrote: It's an argument that can go on forever... We have 10's of thousands of images in mysql databases.. very fast/reliable.. easy to replicate, stream, etc.. On Tue, 28 Sep 2004 08:42:13 +0200, Jigal van Hemert [EMAIL PROTECTED] wrote: From: GH [EMAIL PROTECTED] I was wondering how to get images into and out of a Mysql database was told it was possible. I have mysql 4.0.18 The general opinion is that files should be stored in a file system and not a database. There are circumstances that you might want to store binary data in a database. Take a look at the BLOB column types (http://dev.mysql.com/doc/mysql/en/BLOB.html). Regards, Jigal. -- 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] -- 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: Images
It would be quite easy to drop a squid or similar proxy infront to cache the db images in memory and deliver them for a set cache-time or something.. That would be 1 way to boost performance.. On Tue, 28 Sep 2004 09:55:54 -0700, Ed Lazor [EMAIL PROTECTED] wrote: -Original Message- I have heard also that it helps to keep the table with the blobs having a low number of fields. Like just a primary key and the blob field. Have all your other metadata in a seperate table. Especially if you are going to be occasionally doing queries of just the metadata and don't want to always retrieve the image. Comments? I agree. That's the approach I used. Data on products are stored in a products table, while images are stored in a table called images. The images table has the following fields: ID, ProductID, SizeID, Image. There are 4 images of different resolutions for each product. The SizeID is used to tell which resolution is being requested. For example: Select Image from images where ProductID='8443' AND SizeID='1' ProductID is an int. SizeID is a small int. The Image field is largeblob, because some images are as large as 300k. However, images are generally 15k, 45k, 90k, and 180k, so perhaps largeblob is causing a performance loss? I also plan on using caching tools, so maybe performance tuning on the backend isn't as significant? What do you think? -Ed -- 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: Images
Great .. I don't have any numbers.. but basically there is quite little latency using the FTP interface, data is streamed in and out of mysql on the fly, inserting and selecting blocks of rows.. The FTP server is written in java using JDBC to talk the database, so it's quite fast. On Tue, 28 Sep 2004 10:57:09 -0700, Ed Lazor [EMAIL PROTECTED] wrote: Thanks for the article. I'll check it out. Throughput of 4600K/s is great. How's latency? -Ed -Original Message- So if the image was say 200K in size, the metadata for the image would be 1 row in a table, and the image data would be 4 rows in the data table. 3 full 64K rows + 1 partially used rows. There is a good article/sample code here on the kind of technique we started with: http://php.dreamwerx.net/forums/viewtopic.php?t=6 Using chunked data, apache/php only needs to pull row by row(64k) and deliver to the client, keeping the resultset size low = memory overhead low. The storage servers (mysql storage) I have tested on the LAN; them storing and retreiving data from mysql (using FTP gateway) at rates of 4600K/sec.. which is I think the fastest speed my laptop network card could deliver. That's pretty fast.. Rare day when most internet users can talk to servers at those speeds. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Images
Getting images/files into blob is quite easy.. examples could be provided based on what lanaguge/interface way you want to use... Mysql site has a great list of data types and capacities, etc... do a search for it.. On Tue, 28 Sep 2004 13:58:16 -0400, GH [EMAIL PROTECTED] wrote: I thank you all for this discussion... and for the great information that everyone has provideded. Next question (which is part of my original) how do I actually get the images into the Blobs... Additionally, the what are the names and capacities of the datatypes that I could use? I have been hearing Blob but are there others? On Tue, 28 Sep 2004 13:39:20 -0400, DreamWerx [EMAIL PROTECTED] wrote: It would be quite easy to drop a squid or similar proxy infront to cache the db images in memory and deliver them for a set cache-time or something.. That would be 1 way to boost performance.. On Tue, 28 Sep 2004 09:55:54 -0700, Ed Lazor [EMAIL PROTECTED] wrote: -Original Message- I have heard also that it helps to keep the table with the blobs having a low number of fields. Like just a primary key and the blob field. Have all your other metadata in a seperate table. Especially if you are going to be occasionally doing queries of just the metadata and don't want to always retrieve the image. Comments? I agree. That's the approach I used. Data on products are stored in a products table, while images are stored in a table called images. The images table has the following fields: ID, ProductID, SizeID, Image. There are 4 images of different resolutions for each product. The SizeID is used to tell which resolution is being requested. For example: Select Image from images where ProductID='8443' AND SizeID='1' ProductID is an int. SizeID is a small int. The Image field is largeblob, because some images are as large as 300k. However, images are generally 15k, 45k, 90k, and 180k, so perhaps largeblob is causing a performance loss? I also plan on using caching tools, so maybe performance tuning on the backend isn't as significant? What do you think? -Ed -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MIGRATION OF DATABASE FROM ONE BOX TO ANOTHER BOX
Might try using windows program called SQLYOG.. makes this dead easy.. On Fri, 24 Sep 2004 09:52:10 -0700 (PDT), Seena Blace [EMAIL PROTECTED] wrote: Hi, How to migrate mysql database from one box to another box ? thanks -Seena - Do you Yahoo!? vote.yahoo.com - Register online to vote today! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question about blob data types ie. Storing .xls in blob
Good binary storage article w/ sample code: http://php.dreamwerx.net/forums/viewtopic.php?t=6 On Mon, 27 Sep 2004 16:47:37 -0500, Jack Taffar [EMAIL PROTECTED] wrote: Is it possible to store an excel file into a blob field in mysql? If so how do I go about importing the file into the blob field, and get it back out into an .xls file? Thanks in advnace Jack Taffar AOG -- 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: MySQL data files
Shutdown mysql, move data files, create symlink to datafile(s) on other partition/disk/whatever.. - Original Message - From: VijayKumar Dogra [EMAIL PROTECTED] Date: Wed, 22 Sep 2004 11:22:55 +0530 Subject: MySQL data files To: [EMAIL PROTECTED] Hello All, Is there any way by which I can store data files of mysql in other partition of my system Regards, VijayKumar Dogra -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
