RE: mod_perl and mySql
We don't cache connections, either, and the connection overhead appears to be trivial, connecting to a remote MySQL server over the local Ethernet. 50,000 to 100,000 Apache::ASP pages served per day, some with many rather complex queries. Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Michael Bacarella [mailto:[EMAIL PROTECTED]] Sent: Sunday, 05 January, 2003 11:40 To: Chris Faust Cc: MySql Mailing List Subject: Re: mod_perl and mySql Are you sure it would help? MySQL is damn fast at taking connections. When we converted to mod_perl I made a mental note to switch to persistent connections. It turns out that it still ran fast enough even though every hit results in an RDBMS connection. And at this point we're doing 5M of them per day. It has never been a bottleneck for us. -M Due to ever increasing traffic I'm converting my site into mod_perl and quite honestly I'm confused about persistent DB connection and DB connection pooling.. I know there is tons of info out there on the goggle groups and on perl.apache.com, I also picked up Mod_Perl Developers cookbook and I also have the awesome Mysql and MySql and Perl for the web. (BTW so far the little mod_perl section in MySql and Perl for the web has proven more useful then anything I've found in the whole Mod_Perl dev cookbook). - 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: PHP bias (Way OT) -- and ASP on UNIX
Try Apache::ASP -- ASP syntax with mod_perl on Apache. http://www.nodeworks.com/asp/ We run a pretty busy database-driven web site with Apache::ASP and MySQL. I'm quite happy with it, as it gives us access to the massive collection of Perl modules, and enables us to use a single language for scripting, whether for web pages or for non-web utilities. Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Van [mailto:vanboers;dedserius.com] Sent: Sunday 10 November 2002 16:01 To: MySQL Subject: Re: PHP bias (Way OT) Alan: ASP only runs on Windows. [...] - 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
Query cache size
I just upgraded our web server from 3.23.47 to 4.02 -- and I must say that 4.02 performance rocks! :-) I was planning to wait for the beta builds, but the thing that triggered my decision was the discovery of the query cache. Our database server (1.4 GHz Athlon on FreeBSD) typically runs between 20% and 40% load, and complex pages (like our front page!) with many queries were getting the page processing time (including the web server, a 533 MHz Alpha on NetBSD) up to 4-5 seconds. With my initial 4.02 configuration, my front page processing times are between 0.5 and 1.5 seconds -- and the database server load is hovering around 10-20%. Excellent! The query cache, though ... the documentation doesn't make many suggestions about sizing it. I started with this: - 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
Query cache size
[Sorry about the early send on the previous one ...] I just upgraded our web server from 3.23.47 to 4.02 -- and I must say that 4.02 performance rocks! :-) I was planning to wait for the beta builds, but the thing that triggered my decision was the discovery of the query cache. Our database server (1.4 GHz Athlon on FreeBSD) typically runs between 20% and 40% load, and complex pages (like our front page!) with many queries were getting the page processing time (including the web server, a 533 MHz Alpha on NetBSD) up to 4-5 seconds. With my initial 4.02 configuration, my front page processing times are between 0.5 and 1.5 seconds -- and the database server load is hovering around 10-20%. Excellent! The query cache, though ... the documentation doesn't make many suggestions about sizing it. I started with this: set-variable= query_cache_limit=2M set-variable= query_cache_size=64M set-variable= query_cache_startup_type=1 Is this reasonable? This is what my status shows (after 30 minutes or so): | Qcache_queries_in_cache | 789 | | Qcache_inserts | 1512 | | Qcache_hits | 2847 | | Qcache_not_cached| 0| | Qcache_free_memory | 65710008 | Is that Qcache_free_memory figure really right? And, more importantly, should I allocate more memory for this? This seems like my biggest win, and I do have 1 GB available. Tom Haapanen [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Count() does not count correctly?
Jens, You need to select on the games on the left, to ensure that you don't end up with years on the left with no corresponding game records. Try this: SELECT years.year AS yearlist, count(years.year) AS gamecount FROM games LEFT JOIN years ON games.release_year = years.year GROUP BY years.year ORDER BY gamecount DESC, yearlist Or just simply SELECT release_year AS yearlist, count(release_year) AS gamecount FROM games GROUP BY release_year ORDER BY gamecount DESC, yearlist (Not tested here, sorry ...) Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Jens Mildner [mailto:[EMAIL PROTECTED]] Sent: Monday, 07 January, 2002 15:08 To: [EMAIL PROTECTED] Subject: Count() does not count correctly? Hello MySQL-users ! Houston, I've got a problem here. I'm running the following query from a PHP-Script: SELECT years.year AS yearlist, count(years.year) AS gamecount FROM years LEFT JOIN games ON years.year = games.release_year GROUP BY years.year ORDER BY gamecount DESC, yearlist This query should do the following: Every year from years.year should be returned along with the number of games that have been released in that year sorted by the gamecount. years.year and games.release_year both do NOT contain NULL. The problem is the following: Years in which no games were released are returned with a gamecount of 1. Why that??? All other years with games released in it are getting the correct gamecount. Please help me. Greetings from Germany, Jens Mildner. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql daemon monitoring
Fred, You could use something like daemontools (http://cr.yp.to/daemontools.html) or safetynet (http://www.unixpimps.org/software/safetynet/). All assuming you are using a UNIX-type platform, of course. Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Fred Taurus [mailto:[EMAIL PROTECTED]] Sent: 05 January 2002 15:04 To: [EMAIL PROTECTED] Subject: Mysql daemon monitoring Hello , I am looking for a solution in order to monitoring mysql daemon to restart automatically the daemon when is down. I was thinking maybe there is a possibility like services watching in the cobalt interface thanks in advance @+ Fred mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Full Search
And, alas, by the time MySQL finishes building my 500 MB fulltext index (more than 12 hours) the table is throroughly corrupted, and myisamchk can't even recover it with -o. I'm waiting for 4.01 to go beta, and will then give it a try to see if things are happier there. Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Steve Rapaport [mailto:[EMAIL PROTECTED]] Sent: 03 January 2002 04:39 To: Neil Tompkins Cc: [EMAIL PROTECTED] Subject: Re: Full Search Yes, you can add a FULLTEXT index to that table including all 15 fields, e.g. CREATE FULLTEXT INDEX findtext ON mytable (field1, field2, field3,...field15); Caveats: The fields must be of type VARCHAR or TEXT to work, (CHAR can be changed to VARCHAR easily, and often is silently changed.) Depending on the table size and other things, creating this index can be very slow, taking hours or even days. Try in advance to limit the size and number of fields in the index to what you really want, and don't plan on re-indexing very often. Steve. Neil Tompkins wrote: I have a mySQL table which has about 15 fields. Is it possible using a standard SQL statement to do a full text search on all fields ?? -- Steve Rapaport World Citizen - 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: Importing large text file
Greg, Your best bet is to feed the data into Perl -- relatively simple Perl script can deal with variances you indicate, and feed the data into MySQL using DBI. Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Greg Peretti [mailto:[EMAIL PROTECTED]] Sent: Thursday, 03 January, 2002 16:33 To: [EMAIL PROTECTED] Subject: Importing large text file This is my first post on this list, so bear with me. I have a text file with 10,000 stories in it (about 130 MB). I would like to break it up into 10,000 separate files and am trying to figure out a way to feed it into a MYSQL database. I have set up the database with the pertinent fields, except the individual stories do not necessarily follow the same pattern. For instance, almost all of them have: Header Publication section edition date page number headline byline story all separated by two returns, with each overall story separated by four returns. Now, I can load the file using fields terminated by \n\n and lines terminated by \n\n\n\n, but if, for instance, a story does not have a byline, I'm in trouble. It will happily put the story data in the byline field. Am I out of luck, or can I specify text to look for in specific fields as the file is being loaded, i.e. the date field is always preceded by Date: and if no Date: is found, skip the field? I'm using 3.23.44 -- Greg Peretti web developer www.abqjournal.com (505) 823-3888 --- The web of our life is of a mingled yarn, good and ill together. - William Shakespeare - 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: SubSelect Workaround help
Is this too simple? select distinct p.symbol, i.name from portfolio p, stockinfo i where p.symbol = i.symbol and p.type = '401k' and p.owner='jim' order by p.symbol - Tom Haapanen -- Software Metrics/Equitrac Corp. Advanced Printing Solutions -- http://www.metrics.com/ -Original Message- From: Mr. psm996 [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 02 January, 2002 09:30 To: [EMAIL PROTECTED] Subject: SubSelect Workaround help How would one load the results of the following query into a one line comma separated list so that it could be then included with an IN expr to get around the lack of subselect in MySQL select distinct symbol from portfolio where type='401k' AND owner='jim' select distinct p.symbol, i.name from portfolio p, stockinfo i where p.symbol = i.symbol and p.symbol in (string name) order by symbol Thanks. Happy New Year Paul _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: NetBSD versus RedHat versus Solaris x86
Hank, I suspect some of the NetBSD threading comments you found in the archives may be mine. We run NetBSD/Alpha for our site (motorsport.com) on the web server, our work server, plus several other boxes. We originally did the same for the database server, but, at the time (this being early 2000) MySQL appeared not to be happy on a 64-bit machine, at least on NetBSD. (I think this is now much better ...) So we switched to generic Intel hardware. And things ran OK, except that we had increasing numbers of MySQL restarts (5-20 times/day). Automatic, true, but they would always cause a delay in service, and we have a reasonably busy site -- we serve a few million pages per month, all database-driven. (I suspect threading issues, but I have no absolute proof of that.) So this past summer I switched to FreeBSD 4.3, on the same hardware, and moved the MySQL server over. I installed FreeBSD on the new server over FTP, added a preconfigured MySQL binary from http://www.freebsd.org/, and got it running in less than two hours. As a result, MySQL is now rock-solid. We run on a P3/750 on an ASUS motherboard with 384 MB of memory and IBM's 7200 rpm IDE disks. Right now the server uptime is 69+ days (I rebooted in October to change the hardware configuration), and the mysqld process (version 3.23.36) has since then accumulated some 250 hours of CPU time without a glitch. MySQL on NetBSD may have improved since last summer, but I can definitely vouch for FreeBSD at this point. The only question mark is the effectiveness of SMP -- I can't say anything about this, really, since we run on a single CPU. Configuring a dedicated MySQL server is straight-forward (much more so than a web server with integrated Apache and PHP or Apache::ASP). Once you have the OS and MySQL installed and tested, you should be able to just move your databases over, and point your web server at the remote database. Good luck with your choice ... Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Henry Hank [mailto:[EMAIL PROTECTED]] Sent: 29 December 2001 01:18 To: [EMAIL PROTECTED] Subject: NetBSD versus RedHat versus Solaris x86 Hello all, I just ordered a new server to act as a database-only server for my website (wheresgeorge.com). I'm currently running mysql 3.22.29 (I'm stuck there due to my web hosting provider). I plan to upgrade to the latest stable version of mysql, but have the following questions about the choice of Operating System. The hardware is a Dell PowerEdge 2550 Dual 1GHZ Pentium/1GB Memory, 3x18GB SCSI disks and will be set up as RAID 5 via a PERC3/Di card. I also require large file size support (2GB). My current database has over 16 million records and processes about 80 queries per second (on average). Peak usage could be around 300 queries/second (estimate). It is running on Linux on a dual PIII-800. Performance is very good, but I'm going to hit the 2GB limit soon, and need to expand beyond what my ISP can provide with their managed solutions. So I am going to co-locate this new DB server as a backend to the current webserver. On to the questions: I was considering Net/FreeBSD, but read about threading problems in the archives, so I don't know if they have been resolved. I was then thinking about RedHat7.2 with the 2.4 kernel (large file size support, I think). I then read about Solaris x86.. which is where I'm leaning, but I was told Solaris x86 might be slower than Linux of Free/NetBSD. I'm also thinking of installing Oracle 8i as a side-by-side comparison to mySQL running on the same HW/OS. Anyway, I'm not a UNIX whiz/admin by any stretch... I know enough to *NIX to manipulate files, jobs, processes, etc. I've even compiled a few packages in the past and got PHP/mysql installed and running on a linux box last year. I can write rudimentary shell scripts. I'm looking for general comments regarding the current status of any of these choices of OS on this hardware in relation to running a fast and stable mySQL installation. Are there any pitfalls I need to watch out for on any one OS? Is any one significantly more favorable for mysql that the other? As I've said, I have searched the archives, but can not find any real-current comments on OS selection. Thanks in advance, -Hank Eskin ([EMAIL PROTECTED]) __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual)
RE: mySQL Issues
Matthew, Without any more information, I would put money on this being either a database design issue or a query design issue. I really expect that database optimization would buy you a lot of performance. But without knowing what kind of database structure you have, what kind of queries you run, what kind of optimization you have done, what you consider to be a LOT of traffic, what kind of server settings you are using and what kind of hardware you run on -- I really can't say much more than that. Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Matthew Hale [mailto:[EMAIL PROTECTED]] Sent: 29 December 2001 00:15 To: [EMAIL PROTECTED] Subject: mySQL Issues I run a site that gets a LOT of traffic and uses mySQL. But for some reason, mySQL is making MAJOR load on my server. I have tried it on several servers, and they all end up with load averages like 10+! We have optimized our code as much as possible and STILL are getting super high loads! Is there ANYWAY to optimize mySQL so it doesn't run s much load?? Sincerely, Matthew Hale Get paid cash every time you receive email! Sign up FREE at: http://www.MintMail.com/?m=1825549 - 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: Very untidy
select hour(ts), count(ts) from dundeefw
where date=$date and action=1
group by hour(ts)
Assuming ts is your timestamp column ...
Tom Haapanen
[EMAIL PROTECTED]
-Original Message-
From: Leon Noble [mailto:[EMAIL PROTECTED]]
Sent: 23 October 2001 05:45
To: [EMAIL PROTECTED]
Subject: Very untidy
Hi all,
can someone look at my very untidy code and maybe give me some pointers.
Searching for values by the hour.
etc
for ($i = 0; $i = 23; $i++)
{
$query = select count(num) as mycount from dundeefw where date=$date
and action=1 and time=hour($i);
// Execute query
$result = mysql_query($query) or die (Query Failed);
while ($row = mysql_fetch_array ($result))
{
$connection[$i] = $row[mycount];
}
}
I am wondering if there is anyway using SQL to avoid doing this the very
messy way.
many thanks
Leon.
-
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: selecting rows where all rows meet a criterion
This might work ... but I don't have a suitable database to test with ... select * from students,grades where students.id=grades.id and min(grades.grade - students.average_grade) 0 group by students.id Tom Haapanen [EMAIL PROTECTED] -Original Message- From: David Hugh-Jones [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 23 October, 2001 13:10 To: [EMAIL PROTECTED] Subject: selecting rows where all rows meet a criterion OK, I have a tricky SQL syntax problem I would like to share. For simplicity's sake, I'll use an analogy. I have two tables, of students and grades, each with a ID column for use in joins, corresponding to a student's ID. The student table has an average grade column which shows that student's average grade last year. The grades table has a column for subject (e.g. English, Math) and a column for the grade achieved. Suppose I want to select all my students who have performed very well this year. I want to select all the students who have beaten last year's average in ALL of this years marks! SELECT * FROM students,grades WHERE students.ID=grades.ID AND grades.grade students.average_grade This doesn't work. It gets me all the occasions where a student has beaten his average. But I only want students who have never gone below their average! In other words, I want to select from the students table, based on information on multiple rows in the grades table, where ALL the grades rows need to fulfil a certain criterion. Help would be gratefully received! -- Dave - 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: Database Structures
25M tables in one database -- wow. How big were the tables? Was this with MySQL? Which OS and filesystem did you run this on? Given that MySQL stores each table in three separate files in the database directory, that would imply 75,000, files in the directory containing the database -- and non-trivial directory search times every time MySQL had to open a file (i.e. database table or index), right? Or did you manage to configure MySQL to cache all these file handles so that it did not need to reopen the tables (and search the directory for the appropriate entries)? Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Steve Meyers [mailto:[EMAIL PROTECTED]] Sent: 22 October 2001 03:27 To: Joe; [EMAIL PROTECTED] Subject: RE: Database Structures Creating a new table per image isn't unheard of -- at a company I used to work for, we estimated that we had around 25 million tables at one point in time. There were lots of factors that went into why our database was structured like that, but it worked for us. - 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: Database Structures
Joe, It's possible to create a table for each image. However, as others have suggested, in most cases, it makes more sense to have an images table, with all the images, and with a unique image ID for each image (you can create your own or use autoincrement). You can then have tables like reviews, comments and ratings, which all refer to the images table using an image ID field. This means you don't have to do a create table each time there is a new image, but, really, the key advantage to doing this is that it's easy to do queries to locate ... - all the images that match certain criteria - all the reviews for one image - all ratings made by one user - the 100 top-rated images and so on. What it really does is enables you to use SQL to locate and select data in the database, rather than having to write code to locate and loop through tables to find what you are looking for. Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Joe [mailto:[EMAIL PROTECTED]] Sent: 22 October 2001 02:04 To: [EMAIL PROTECTED] Subject: Database Structures Hello, Hi, I need some help on something here. I need to know the best way to set up this database. I have a site with a bunch of images/reviews. What i want is for registered users to rate and leave reviews themselves. I'm not sure how I should set up this database though. Now, I have a table that holds the user info. Should I create a new table for each image, which holds the user, and his comments/rating? (this seems rather bloated, and inefficient) or should I create a table somehow for all images and keep track of the user comments/ratings another way? Thanks. I've been looking for sites about database structuring and stuff, but no good luck. If anyone could give me sites or their ideas it would be appreciated - 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: faster inserts updates
Priya, Need more information ... how long does it take to insert 28,000 records? On what kind of platform? How much data do the records contain? What kind of primary keys and indices do you have? Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Priya Ramkumar [mailto:[EMAIL PROTECTED]] Sent: 22 October 2001 06:01 To: [EMAIL PROTECTED] Subject: faster inserts updates I have a PHP program which adds records to a MySQL table. The number of records in the table after the execution of the program is around 28,000. I also have select update queries in the program on the same table. The process takes a very long time to complete all insertions to the table. I already tried Insert Delayed on a sample segment of data but found that it does not speed up things. Can anyone suggest a method so that the insertions updates to the table can be faster? - 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: faster inserts updates
Priya, I suspect that the primary key and index are causing your problems -- each time you insert, MySQL must create the indices and validate for uniqueness. Can you remove the indices and try it that way? If that is very fast (as I suspect it will be), then you can focus on coming up with a more efficient way of indexing the table. In general, if your database is 99% reads (as ours is, and so are most other web sites), it makes sense to do fairly heavy indexing, as the savings on queries far outweighs the cost on updates. But if that doesn't describe your database, you should reduce the amount of indexing in order to speed up your inserts... Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Priya Ramkumar [mailto:[EMAIL PROTECTED]] Sent: 22 October 2001 06:26 To: Haapanen, Tom Cc: [EMAIL PROTECTED] Subject: Re: faster inserts updates Hi Tom, Thanks for your response. It takes around 1 hour 40 minutes to insert 28000+ records. Also, I find that the first few thousand transactions are quite fast but slows down later. I have MySQL on Linux. I have a concatenated primary key of multiple fields. And I also have a unique index on each of these fields. There are 1,734,291 bytes of data in the table with an average row length of 61. Thanks Priya - 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: Non Ascii characters
Jamie, This is likely a client software issue. We use Perl and DBI, and we have no trouble storing or retrieving non-USASCII characters. What are you using to access the MySQL database? Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Jamie Smith [mailto:[EMAIL PROTECTED]] Sent: 03 October 2001 06:23 To: Mysql@Lists. Mysql. Com Subject: Non Ascii characters I'm having a problem with the sending of the new euro € symbol into the mysql database through a standard SQL stament. I have tried everything cut and paste or ALT 0128 on the command line but all I seem to get is a ? character stored. I know it can be done because I have managed to enter the symbol using a program 'ArtAdmin' by artronic to enter the data but this will not be usable because I am trying to write and web based from system to submit information. Can anyone tell me how to get this symbol in and are there any others that might casue trouble. I have not been able to find anything on this in the manual or previous posts. TIA Jamie - 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: Counting Sequences Clarified
Bruce, Why not just determine this number when you do a query? Why do you need to have it be stored in the database? It's easy to create a Perl (or probably PHP, but I really don't know PHP) script to fill in such a column, too, but you would need to manually maintain that. And right now I don't understand the problem well enough to convince me that such maintenance would be worthwhile ... Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Bruce Collins [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 02 October, 2001 07:04 To: [EMAIL PROTECTED] Subject: Counting Sequences Clarified Hello, Thank's for your interest Paul. I did a poor job of explaining my problem. Here is another go: For an athlete's first entry in the database I need a column value of 1. The second performance entry of the same athlete would have a value of 2. And so on. I need to apply this retrospectively to ten years of performance records. Ongoing updates are not an issue at present. Thanks Bruce - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is right DB for our purpose
A piece of cake for MySQL. Our news/photo database is growing by nearly 10,000 records per month, with no noticeable performance impact. As long as you design your database structure and queries right ... Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Paul Murphy [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 02 October, 2001 09:21 To: [EMAIL PROTECTED] Subject: Is right DB for our purpose Hi, We are planning to create an web database(6 - 7 tables with the average of 6 columns), where members search for the right person by entering the proper search criteria. We expect that this database will grow in the rate of 1000 to 2000 records in a month. Could you please let us know whether the MySQL database is the right choice for our purpose as soon as possible. Because we are nearly ready to sign an agreement with a webhosting company. Please reply to the following email address [EMAIL PROTECTED] Thank you Ruthira S Steinhart Net esolutions - 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: MS-Access
This helps you convert the data to a MySQL database (a much faster and more robust datastore than JET). If you need to access the data from both platforms, including existing Access applications, install MyODBC on NT, create a suitable ODBC DSN and, then, in Access, use File Get External Data Link Tables to get access to the data residing on the MySQL server. Tom Haapanen [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, 01 October, 2001 10:17 To: Andreas Schmidt Cc: [EMAIL PROTECTED] Subject: Re: MS-Access On Mon, 1 Oct 2001, Andreas Schmidt wrote: Hi List I want to migrate from a NT-Server to a Linux Server. Therefore I'm looking for a solution to run my existing MS-Access-Databases under MySQL. Is there a chance to point from a MySQL-DB to a MS-Access-DB in order that my data can stay within the MS-Access-DB? Hi. I find this: Exportsql/Importsql. Convert Mircrosoft Access databases to MySQL and vice versa. These tools are Access functions that can be used to export Access tables in a format readable by MySql. They can also convert SQL output from MySQL and mSql into a form readable by access. I am also interesting in the same question/topic as you. That's why I post this... Perhaps somebody known this tool or have somethings like a perl module for this situation?? Karl :-) - 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: why would LOWER
MySQL is not Oracle ... and that's a good thing, too! :-) Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Joe Kaiping [mailto:[EMAIL PROTECTED]] Sent: 30 September 2001 19:37 To: [EMAIL PROTECTED] Subject: RE: why would LOWER Thanks to all for your responses. I had missed this important piece of information in the documentation that Benjamin pointed to and: http://www.mysql.com/doc/C/H/CHAR.html Values in CHAR and VARCHAR columns are sorted and compared in case-insensitive fashion, unless the BINARY attribute was specified when the table was created. The BINARY attribute means that column values are sorted and compared in case-sensitive fashion according to the ASCII order of the machine where the MySQL server is running. BINARY doesn't affect how the column is stored or retrieved. -Joe - 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: Powered by MySQL?
This is what the MySQL web site says: D. Use of Powered by MySQL. If you are a user of the MySQL database server, (whether under the GPL licence or a MySQL commercial licence) and have not modified the source code for the application or use in question, you may request permission to display the Powered by MySQL logo, as shown in the MySQL AB Mark Table in Section 4(A), in the user interface (including your website) of the application that runs on the MySQL server. To request permission, simply send an email to [EMAIL PROTECTED] in which you identify your name (and company name, if applicable) and the application, and you agree to adhere strictly to this Trademark Policy. MySQL AB reserves the right not to grant such permission, or to withdraw any such permission, in its sole discretion. Use of the Powered by MySQL logo must comply with applicable provisions of this Trademark Policy, including (without limitation) Sections 3(D) and 4. But, what the heck, I'll try to email [EMAIL PROTECTED] Maybe someone actually reads that mailbox ... - Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Daniel Ouellet [mailto:[EMAIL PROTECTED]] Sent: 29 September 2001 21:31 To: [EMAIL PROTECTED] Cc: Haapanen, Tom Subject: RE: Powered by MySQL? True you did email, but look at the email address you send your request; Quote I have sent email to [EMAIL PROTECTED], but I have received no response. So, you send to [EMAIL PROTECTED] Instead of the one point it out in the doc: [EMAIL PROTECTED] May be if you try thisone you may get the answer you are looking for. Daniel - 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: mysqld under FreeBSD
What hostname are you providing JSP? If you want to replicate the behaviour of your mysql command, it needs to be localhost or 127.0.0.1, not the network name of your server. If you are using the server name, then you need to use mysql command the same way, too: ./mysql -u root -h servername -p Tom Haapanen [EMAIL PROTECTED] -Original Message- From: gokeeper [mailto:[EMAIL PROTECTED]] Sent: Sunday, 29 July, 2001 10:31 To: [EMAIL PROTECTED] Subject: mysqld under FreeBSD hi all i am running mysql 3.23.40 and tomcat 3.2.3 on FreeBSD when i try to connect to the local mysql server in jsp using mm driver it says: Server configuration denies access to data source the userpass i provide is right i can connect to local mysqld use ./mysql -u root -p also php+mysql works well. BTW:the same script can connect to another Mysql server running under windows2000,so i think this is mysqld's problem - 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: FreeBSD issues
Michael, MySQL worked beatifully for us on a vanilla FreeBSD system -- I simply installed the binary (precompiled) package from the FreeBSD ports collection. I recommend you get the binary rather than playing with the configuration yourself ... - Tom Haapanen -- Software Metrics/Equitrac Corp. Advanced Printing Solutions -- http://www.metrics.com/ -Original Message- From: Institute For Social Ecology [mailto:[EMAIL PROTECTED]] Sent: 18 July 2001 18:33 To: Ken Menzel; [EMAIL PROTECTED] Subject: RE: FreeBSD issues Ken, Thank you for your very helpful notes. We took your advice and updated to FreeBSD 4.3 and the last version of MYSQL. We are still experiencing problems with MYSQL, though. We installed it fresh, with no freeBSD tweaks. I was not able to locate the email you referred to regarding FreeBSD installs. If you have any pointers on tweaking a freeBSD install, it would be very helpful. Regards, Michael Caplan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL loves FreeBSD (but not NetBSD) ...
Ken, I have absolutely no complaints at all about the support I got on this mailing list from the MySQL team -- they were very helpful, and provided many good suggestions. Unfortunately none of them worked, but such is life. I am happy that MySQL is stable on FreeBSD -- I was ready to go all the way to Red Hat, but I'm glad I was able to get FreeBSD running with the binary MySQL package with very little pain. And FreeBSD is close enough to NetBSD that administration really isn't a big issue. (Linux would have been far more painful for us, as we don't run any Linux boxes right now.) Oh, and I'm quite aware of the difficulty of debugging threading, from my other life. It's painful at best of times (with NT and MSVC), and excruciatingly difficult when you have to work with debuggers that have minimal threading support -- which, alas, includes most free UNIXes. (Incidentally, we are in the process of OEM licensing MySQL for that company as well, so I do hope that provides some support for the project as well.) Anyway, kudos to MySQL team for creating a great product, and for making it work on as many platforms as it does ... Tom Haapanen [EMAIL PROTECTED] (nights) [EMAIL PROTECTED] (days) -Original Message- From: Ken Menzel [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 18 July, 2001 10:51 To: Haapanen, Tom; Subject: Re: MySQL loves FreeBSD (but not NetBSD) ... Hi Tom, As another user of MySQL on FreeBSD, I can tell you from my own experience that the MySQL Team is dedicated to having MySQL run on as many platforms as possible. I say this as an almost direct quote from e-mail Monty sent me. Things were not always smooth with FreeBSD threads, it is only through quite a bit of work by some dedicated people that this has smoothed out. There are still issues that remain, but they are not with MySQL. They MySQL Team has gone out of thier way to help debug OS threads problems. I imagine more emphasis will be placed on reliable pthreads libraries when the new threads based apache web server moves out of alpha test phase and onto production systems. It is not easy to debug these these type of things, the main issue is getting some who understands threads debugging on the OS involved to look at the code and/or writing a test program that duplicates the problem. Neither of these is a trivial task, and of course the MySQL team needs to feed thier families, and continue to move the product forward. I and my company support MySQL AB (to make it clear) in both words and financialy (I have a login support contract). They are a dedicated hard working group, and I am sure they are as interested as you in the problems on NetBSD, however I think it is amazing that they run on as many platforms as they do now without problems! But there is a limited amount of resources to go around, and as there are more Linux (mysql) users than FreeBSD, problems tend to get noticed and fixed there first. I think it might be similer with NetBSD and OpenBSD, there just less users, therefor less testing and since MySQL is very USER supported there exists less support. I am not saying anything bad about OpenBSD or NetBSD, just that it seems there are less users on this list. The more users looking at an issue, the more likely someone will find the answer/problem. So in a nutshell what I am saying is you don't have to give up if you really feel you want to run on NetBSD. Go rally the people together to find the problem. If you can support financialy do it. If you support bu collecting the right people, do that. But if the users don't get involved at some level the problems may not go away, or a different user group may solve the problem for a different reason (IE Apache). But the users have to get involved, it's part of what open source is all about! Best of luck to you what ever your decison! Ken - 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
MySQL loves FreeBSD (but not NetBSD) ...
... or should that be FreeBSD loves MySQL? I finally caved in after a year of increasing (and unexplained) MySQL crashes, despite numerous updates to more recent versions of NetBSD and MySQL, and dozens of experiments with different configuration options. MySQL would die (and restart automatically) every 5-15 minutes on the database server acting as the back end for our web site, motorsport.com. (This on NetBSD/i386, as when we started, MySQL was distinctly unhappy with NetBSD/Alpha) So I picked up a clean disk, installed FreeBSD 4.3 over the Internet, booted to multiuser, and did pkg_add -r mysql-server to installe the prebuilt MySQL binaries. Stopped the server, ftp'd the databases over -- and it worked! Wonderfully, in fact ... in 48 hours now, not a single crash, and the CPU utilization has been quite reasonable, too. In the end, I suspect that MySQL couldn't deal with the threading libraries available on NetBSD, although I have no proof of this. No other application has given us this much trouble, but nothing else we use uses such heavy threading, either. (We still continue to use NetBSD/Alpha for all our other servers.) If anyone on the list is running MySQL on NetBSD in a reasonably busy production environment, I would still like to hear about it, though. Tom Haapanen [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqld keeps falling over on NetBSD
Our normally reliable back-end server for our web site has now gone completely crazy ... it's falling over every few seconds. Automatic restarts are nice, but it really makes the web site unusable. mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. 010611 10:06:28 mysqld restarted /usr/local/libexec/mysqld: ready for connections mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. 010611 10:06:30 mysqld restarted /usr/local/libexec/mysqld: ready for connections mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. 010611 10:06:32 mysqld restarted /usr/local/libexec/mysqld: ready for connections There is very little information available here. I have tried check table, and myisamchk with -r, -o and -e options ... and no help. This is running MySQL 3.23.36 on NetBSD/i386 1.4.1. Are there any significant bug fixes in 3.23.38 to make it worthwhile downloading, building and installing it? Tom Haapanen [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
