Re: Mysql, Need extra Cash? - Get Paid in 48hrs - Home Reps Needed.Com home representatives are required!

[John Klein]

On Tue, 18 Feb 2003, Virdhagriswaran SHIBATA wrote:

 Mysql, your immediate help is needed.  We are a .com
 corporation that is growing fast (over 1000% per year). We simply cannot
 keep up with demand.

Remind me again why we bother with a spam filter?

--
John Klein
Database Applications Developer
Network Applications Services - Harvard Law School
Omnia Mutantur, Nihil Interit

-
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: Any way to make a top ten query?

[John Klein]

[EMAIL PROTECTED] wrote:
 
 As there is no TOP operator nor nested selects in MySQL, I wonder if there
 still exists some clever way to maketop ten type of a query , i.e. to
 select ten best selling products or ten highest mountains for example.

SELECT name, height FROM mountain ORDER BY height desc LIMIT 10;

-- 
John Klein, Database Applications Developer |  Omnia Mutantur,
Systems Group - Harvard Law School  |  Nihil Interit

-
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 there a MySQL 'field contains' function

[John Klein]

[EMAIL PROTECTED] wrote:
 
 I am trying to search a text field within a table that contains
 several keywords; for example the column might be called
 computer_description and contain the values i-mac blue 256MB
 500MHz  Is there any way of putting together a query that says
 something like:
 SELECT * from table1 WHERE computer_description CONTAINS 'blue' ?

Yes.

SELECT * from table1 WHERE computer_description LIKE %blue%;

 or should I separate this column up into several others  type, colour,
 ram, processor etc...?

Yes. You should do this anyway if you think you're going to be searching
by those a lot, since it'll be more efficient that way.

(In fact, you should make another table for computer types, colors,
processor types, etc, and join the two together.)

-- 
John Klein, Database Applications Developer |  Omnia Mutantur,
Systems Group - Harvard Law School  |  Nihil Interit

-
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: A small SQL query problem

[John Klein]

[EMAIL PROTECTED] wrote:
 
 Thanks for the suggestion! I modified your query to something that works for
 me (I was inaccurate with the column names before, sorry... :)
 
 SELECT a.article_id,a.title,a.date,a.summary,COUNT(c.id) as comments
 FROM articles as a,article_comments as c
 WHERE a.article_id = c.article_id
 GROUP BY a.article_id,a.title,a.date,a.summary
 ORDER BY a.date DESC
 LIMIT 3
 
 This works PERFECTLY
 Except:
 If an article has NO comments, it is not returned at all!
 
 So: When my articles table contains only 3 articles, and I make a query that
 has LIMIT 3, you would think that this query would return all articles, but
 it does not: Only the ones that actually has one or more comments.
 
 I tried this with 3 articles where 2 of them had comments: Only 2 rows
 returned. When I gave one comment on the last article: 3 rows returned.
 
 Seems like if COUNT(c.id) returns 0, then the row is not returned at all.

That's because there's no row in the comment table to join on. You want to
use a LEFT JOIN here. So the revised query might look like:

SELECT a.article_id,a.title,a.date,a.summary,COUNT(c.id) AS comments
FROM articles AS a
LEFT JOIN article_comments AS c
  ON a.article_id = c.article_id
GROUP BY a.article_id,a.title,a.date,a.summary
ORDER BY a.date DESC
LIMIT 3

-- 
John Klein, Database Applications Developer |  Omnia Mutantur,
Systems Group - Harvard Law School  |  Nihil Interit

-
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: time zones?

[John Klein]

[EMAIL PROTECTED] wrote:
 
 As I understand it, the mysql TIMESTAMP type represents the time in your
 local timezone (the one your computer is using).
 
 This makes it problematic to use in timezones that have a summer/daylight
 savings time.  All of the date arithmetic functions will yield inaccurate
 results if one of the dates is in summer time and the other is in standard
 time.
 
 For these reasons, I resort to using an INT field that stores Unix time
 (seconds since the start of the Unix epoch, 00:00:00 UTC, January 1, 1970).
 This makes the data independent of the database's time zone. I still use the
 TIMESTAMP data type as a convenient way to keep tabs on when a row was modified,
 if doing date arithmetic on the column is a concern.

I find myself doing this with a lot of databases as well, simply because
it's more useful in Perl-land to have an epoch time value. It might be
worthwile to have some way of retreiving a timestamp as an epoch value in
addition to other fifty ways you can currently retrieve it. (If there
already is a way, I'd be thrilled to hear about it.)

-- 
John Klein, Database Applications Developer |  Omnia Mutantur,
Systems Group - Harvard Law School  |  Nihil Interit

-
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: Char vs Varchar field types

[John Klein]

[EMAIL PROTECTED] wrote:
 
 When is it appropriate to use Char instead of Varchar and vice versa?

Use char when you expect the length of the field to always be the same.
Use varchar when you're not sure.

Basically, when you're using varchar you're assigning a limit to the size
of the string that can be put into the field, whereas with char you're
saying that the string will always be exactly X characters long.

So, for instance, it would be appropriate to store serial numbers as char,
since most likely they will include alphabetical characters and will
always be exactly the same length. And it would be more appropriate to
store the name of the hardware component as a varchar, since that would be
a different length for each record.

Also: query, query, sql. Blasted filter.

-- 
John Klein, Database Applications Developer |  Omnia Mutantur,
Systems Group - Harvard Law School  |  Nihil Interit

-
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 Power ?

[John Klein]

[EMAIL PROTECTED] wrote:
 
 I don't mean to start an opinion war, but ...
 
 Can mySQL handle many processors, many servers (clustering), load
 ballancing, etc as well as Oracle.  Or should one use Oracle (some other
 database) for large volume high response requirements.  Is mySQL too basic
 for these capabilities?
 
 Pros and Cons, please.  This should help settle an internal debate that is
 raging!

Something from the MySQL front page:

http://www.eweek.com/article/0,3658,s=708a=23115,00.asp

-- 
John Klein, Database Applications Developer |  Omnia Mutantur,
Systems Group - Harvard Law School  |  Nihil Interit

-
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: Tricky Query...

[John Klein]

[EMAIL PROTECTED] wrote:
 
 Here is a FABRICATED table I have TYPED OUT to demonstrate the information
 that I'm looking for.
 I'm looking for a single query that can produce:
 
 +--+--++---+
 | clientid | revision | name   | address   |
 +--+--++---+
 |1 |1 | Fred Flintstone| 145 Stonewall Terrace |
 |2 |0 | Barney Rubble  | 57 Bedrock Way|
 |3 |2 | Pebbles Flintstone | 25 Mammoth Road   |
 |4 |1 | Bam Bam Rubble | 25 Mammoth Road   |
 +--+--++---+
 
 if it were legal and it worked, I could do a
 
 SELECT clientid, revision, name, address FROM foobar WHERE revision =
 max(revision) GROUP BY clientid;
 
 I dunno - I'm thinking I have to do something with the indexes that I'm
 missing.
 
 Any help would be appreciated.

Just thinking through this...

One possibility would be to run multiple selects. You could do the SELECT
max(revision), clientid FROM foobar GROUP BY clientid first, then
programmatically either cycle through all of them doing mini-selects or
glop together a giant or statement (probably that will fail when you have
large numbers of clients).

You could create a temporary table, use an INSERT ... SELECT statement to
populate it with the above data, then join that table with your original
table.

You could programmatically maintain a 'most recent' flag which would be
update whenever a new record was inserted.

I'm going to set up a fake DB like yours and experiment with it.

...

...

Here we go, this seems to work:

SELECT t1.clientid, max(t1.revision) as maxrev, t2.revision, t2.name,
t2.address FROM foobar t1, foobar t2 WHERE t1.clientid=t2.clientid GROUP
BY clientid, revision HAVING maxrev=revision;

See if that helps any.

-- 
John Klein, Database Applications Developer |  Omnia Mutantur,
Systems Group - Harvard Law School  |  Nihil Interit

-
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: Keeping MySQL Databases Heathly

[John Klein]

[EMAIL PROTECTED] wrote:
 
 Do MySQL server administrators recommend running a cron script daily
 to isamchk?
 
 Is there any other things that should be run daily to keep MySQL
 running top-notch? If so, does anyone have any pointers to a script
 that has already been developed?

I actually find it helpful to periodically run the 'SHOW STATUS' command
on the database and dump the results to a file. Examining this can give
all sorts of information on how much use the database is getting, whether
queries are optimized, etc. Documentation on the command is here:
http://www.mysql.com/doc/S/H/SHOW_STATUS.html. I'm actually running this
every half-hour, because I'm a freak.

The script I use isn't exactly production quality (one of those ten-minute
jobs). I keep intending to fix it someday or just use some internal MySQL
statement that does the same thing, but I'm sure you know how it is. The
script takes one parameter (a filename to dump results to). If called
without one, it prints to STDOUT. Here's my code:

#!/usr/local/bin/perl

use strict;
use DBI;

my $filename = shift;
my @time = localtime();
my $dbtype = 'mysql';
my $database = '';  # Insert your database's name here
my $port = '';  # Insert your port here, or use a socket below
my $dbuser = '';# Some correctly set-up username
my $dbpassword = '';# The password for said name

# Alternately, you could just hardcode everything into the connect
# statement here. I have it broken up to make it easier for non-Perl
# users to reconfigure the script.

my $dbh =
DBI-connect(DBI:$dbtype:database=$database;host=127.0.0.1;port=$port, 
$dbuser, $dbpassword);

if (! $filename) {
  open (STATFILE, 'STDOUT');
  print STATFILE makeheader();
} elsif (-f $filename) {
  open (STATFILE, $filename);
} else {
  open (STATFILE, $filename);
  print STATFILE makeheader();
}

printf STATFILE ('%04d,%02d,%02d,%02d,%02d', $time[5] + 1900,
$time[4] + 1, $time[3], $time[2], $time[1]);
my $sth = $dbh-prepare('show status');
$sth-execute;
while (my $row = $sth-fetchrow_arrayref) {
  print STATFILE ,\$row-[1]\;
}
print STATFILE \n;

close (STATFILE);
$dbh-disconnect;

## END

sub makeheader {
  my $outstring = 'Year,Month,Day,Hour,Minute';
  my $sth = $dbh-prepare('show status');
  $sth-execute;
  while (my $row = $sth-fetchrow_arrayref) {
$outstring .= ,\$row-[0]\;
  }
  $outstring .= \n;
  return ($outstring);
}

-- 
John Klein, Database Applications Developer |  Omnia Mutantur,
Systems Group - Harvard Law School  |  Nihil Interit

-
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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query help

[John Klein]

[EMAIL PROTECTED] wrote:
 
 This will not work for my example.  I have a select box that was populated
 from the db with firstname lastname.  The option tag has the value of
 firstname+lastname then.  Here lies the problem, when I build the query, I
 have no way of distinguishing firstname and lastname.

There are two ways to solve this problem. One is to build your select box
differently, so that it passes the data as firstname/lastname or
firstname|lastname or with some other delimiter, then split the data
before entering/examining it.

If you're stuck with the current behavior, though, try:
select [fields] from [table] where concat([firstname],[lastname]) is
[value];

-- 
John Klein, Database Applications Developer |  Omnia Mutantur,
Systems Group - Harvard Law School  |  Nihil Interit

-
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

Redefining data area

[John Klein]

Sorry if this is an obvious question. I swear I RTFMed first.

Is there any compile-time option to redefine MySQL's data area (normally
mysql-path/var with compiled versions)? I've seen many possible ways of
doing it at run-time, but really I'd prefer this to be compiled in so I
don't have to worry about someone sneakily starting MySQL without
safe_mysqld (with terrifying and sexy results).

-- 
John Klein, Database Applications Developer |  Omnia Mutantur,
Systems Group - Harvard Law School  |  Nihil Interit

-
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