Re: Query

[chris smith]

On 8/5/06, Karl Larsen <[EMAIL PROTECTED]> wrote:

I am using Linux called Fedora Core 4. I had no problem getting
mysql 4 working here and liked it a lot. But when I discovered 4 doesn't
have VIEW but 5 does I have tried several RPM sets of 5 that fail for
basic reasons. The reasons are the wrong libraries or they are missing
in total :-)

Found a 5 that looked like a tar ball but no joy. It is a set of
binary files and a bash file to load it all. This I have not read up on
but fear the binary files will not run on my linux version.

I know I can yum mysql 5 on Fedora Core 5 but for other reasons I
prefer to stay with Fedora Core 4. Has anyone got mysql version 5 to
work on my version of Linux?


It should work fine. What errors were you getting exactly? Those
library dependencies will be the problem but without knowing what they
are, no-one will be able to help.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: previous and next query

[Jay Pipes]

On Fri, 2006-08-04 at 17:14 -0700, Tanner Postert wrote:
> select text from table where user = 2 and id < 3 order by id ASC limit 1;
> select text from table where user = 2 and id > 3 order by id ASC limit 1;
> 
> is it possible to do this using 1 query?

select text from table 
where user = 2 and id < 3 order by id ASC limit 1
UNION ALL
select text from table 
where user = 2 and id > 3 order by id ASC limit 1;


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: FW: How do I find all the users that are new since my last login (repost)

[Daevid Vincent]

I've thought about this a bit more since last night, and it seems that I can
just use the built in 'CURRENT_DATE' (sp?) as it's a given that if they're
logged in, then they're logged in NOW() which is == CURRENT_DATE minus the
hh:mm:ss.

I guess I could also store in a session (this is in PHP) the 'login_date'
BEFORE I update it with the current info. So effectively, that would be the
LAST login date right?

Then I need to whip up some SQL to compare all users created_on date BETWEEN
that 'LAST' date and CURRENT_DATE?

DÆVID  

> -Original Message-
> From: Martin Jespersen [mailto:[EMAIL PROTECTED] 
> Sent: Friday, August 04, 2006 5:35 PM
> To: Daevid Vincent
> Cc: mysql@lists.mysql.com
> Subject: Re: FW: How do I find all the users that are new 
> since my last login (repost)
> 
> You could add an extra field called last_login_date which 
> you'd set only 
> once per session - at login time. At login time you'd set this to the 
> value that exists in login_date. Then use that for comparison against 
> created_on.
> 
> Daevid Vincent wrote:
> > I have a SQL challenge I'm not sure how to solve. But it's 
> so common, I feel
> > kind of stupid asking this...
> > 
> > I have a 'user' table with 'login_date' which is an auto 
> updated DATETIME
> > column and a 'created_on' which is a DATETIME (but not 
> updated after the
> > record is created the first time)
> > 
> > I want to show a list of users who are new since my last 
> login. But the
> > problem is that my last login changes for every page load 
> (i.e. it is
> > updated so that I can guestimate if a user is logged in 
> still or not... I
> > consider 10m to be the window, since rarely do users ever officially
> > 'logout').
> > 
> > So I think I need to have a SQL query that only deals with 
> the date, not the
> > mins/secs? I'm just not sure the optimum way to do this.
> > 
> > The other challenging part seems to me that as soon as 
> 'login_date' is
> > updated, then everyone that was 'new' is now 'old' because 
> the login_date
> > just got updated right? I kinda want the 'new' user status 
> to persist for
> > the whole session [or maybe even the whole day (that is, I 
> could logout/in
> > and those people would still show as new) -- but I can live 
> with just being
> > new for the session]
> > 
> > How is this sort of thing usually handled? Do I need 
> another column that
> > isn't auto-updated and that just get's set upon each new 
> 'login' session?
> > Unlike a "message board" or web based "email" system, I 
> CAN'T flag each user
> > as viewed or something like that. This feature is for a 
> "network" type
> > scenario where a user can see new users added to their 
> network of friends
> > since the last time they logged in. Or so the 
> Administrators can see all new
> > users. This type of thing.
> > 
> > 
> > ÐÆ5ÏÐ 
> > 
> > 
> 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: FW: How do I find all the users that are new since my last login (repost)

[Martin Jespersen]

You could add an extra field called last_login_date which you'd set only 
once per session - at login time. At login time you'd set this to the 
value that exists in login_date. Then use that for comparison against 
created_on.


Daevid Vincent wrote:

I have a SQL challenge I'm not sure how to solve. But it's so common, I feel
kind of stupid asking this...

I have a 'user' table with 'login_date' which is an auto updated DATETIME
column and a 'created_on' which is a DATETIME (but not updated after the
record is created the first time)

I want to show a list of users who are new since my last login. But the
problem is that my last login changes for every page load (i.e. it is
updated so that I can guestimate if a user is logged in still or not... I
consider 10m to be the window, since rarely do users ever officially
'logout').

So I think I need to have a SQL query that only deals with the date, not the
mins/secs? I'm just not sure the optimum way to do this.

The other challenging part seems to me that as soon as 'login_date' is
updated, then everyone that was 'new' is now 'old' because the login_date
just got updated right? I kinda want the 'new' user status to persist for
the whole session [or maybe even the whole day (that is, I could logout/in
and those people would still show as new) -- but I can live with just being
new for the session]

How is this sort of thing usually handled? Do I need another column that
isn't auto-updated and that just get's set upon each new 'login' session?
Unlike a "message board" or web based "email" system, I CAN'T flag each user
as viewed or something like that. This feature is for a "network" type
scenario where a user can see new users added to their network of friends
since the last time they logged in. Or so the Administrators can see all new
users. This type of thing.


ÐÆ5ÏÐ 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

previous and next query

[Tanner Postert]

so i have a record set:

id user text
1  2  ...
2  6  ...
3  2  ...
4  4  ...
5  2  ...
6  8  ...
7  1  ...
8  8  ...
9  2  ...


so lets say I am looking at record 3. i need to know the previous record in
the table that has that user, as well as the next record in the table that
has that user.

right now i am using 2 queries.

select text from table where user = 2 and id < 3 order by id ASC limit 1;
select text from table where user = 2 and id > 3 order by id ASC limit 1;

is it possible to do this using 1 query?

FW: How do I find all the users that are new since my last login (repost)

[Daevid Vincent]

I have a SQL challenge I'm not sure how to solve. But it's so common, I feel
kind of stupid asking this...

I have a 'user' table with 'login_date' which is an auto updated DATETIME
column and a 'created_on' which is a DATETIME (but not updated after the
record is created the first time)

I want to show a list of users who are new since my last login. But the
problem is that my last login changes for every page load (i.e. it is
updated so that I can guestimate if a user is logged in still or not... I
consider 10m to be the window, since rarely do users ever officially
'logout').

So I think I need to have a SQL query that only deals with the date, not the
mins/secs? I'm just not sure the optimum way to do this.

The other challenging part seems to me that as soon as 'login_date' is
updated, then everyone that was 'new' is now 'old' because the login_date
just got updated right? I kinda want the 'new' user status to persist for
the whole session [or maybe even the whole day (that is, I could logout/in
and those people would still show as new) -- but I can live with just being
new for the session]

How is this sort of thing usually handled? Do I need another column that
isn't auto-updated and that just get's set upon each new 'login' session?
Unlike a "message board" or web based "email" system, I CAN'T flag each user
as viewed or something like that. This feature is for a "network" type
scenario where a user can see new users added to their network of friends
since the last time they logged in. Or so the Administrators can see all new
users. This type of thing.


ÐÆ5ÏÐ 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query Help for Loosely Couple Properties

[Robert DiFalco]

I think this only works if I "know" that "Orange" is the default value.
For example if "Orange" is the default and I am searching for "Blue" I
will have to use a different query. Since I don't know the default (its
in the type table) I would have to do two queries. How about something
like this?

SELECT *
  FROM T
LEFT JOIN StringVal SV ON T.ID = SV.REF_ID
INNER JOIN StringType ST
  ON SV.TYPE_ID = ST.ID AND ST.ID = COLOR
WHERE SV.VAL = "Blue" OR (ST.VAL = "Blue" AND SV.REF_ID IS NULL);

 

-Original Message-
From: Jay Pipes [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 02, 2006 9:37 AM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Query Help for Loosely Couple Properties

On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote:
> The question is, how do I query this? Say I want all records from 
> table T whose COLOR property value is ORANGE.
> 
> The only thing I can come up with (and I'm no SQL expert and this 
> looks wrong to me) is the following:
> 
> SELECT *
> FROM T
> WHERE
> (
> T.ID NOT IN 
> ( 
> SELECT StringVal.REF_ID 
> FROM StringVal 
> WHERE StringValue.TYPE_ID = COLOR 
> )
> AND
> EXISTS
> ( 
> SELECT * 
> FROM StringType 
> WHERE StringType.DEF_VAL LIKE "Orange" AND StringType.ID = 
> COLOR
> 
> )
> )
> OR
> (
> T.ID IN 
> (
> SELECT StringVal.REF_ID 
> FROM StringVal 
> WHERE StringVal.VAL LIKE "Orange" AND StringVal.TYPE_ID =
COLOR
> )
> )

SELECT * FROM T
LEFT JOIN StringVal V
ON T.ID = V.REF_ID
INNER JOIN StringType ST
ON V.TYPE_ID = ST.ID
AND ST.ID = COLOR
WHERE V.REF_ID IS NULL
OR V.VAL = "Orange";




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Running Totals?

[Barry Newton]

Well, I said earlier that if I found a solution to this, I'd post it.  Here 
it is, with many thanks to Nicholas Bernstein's timely July 7 post to the 
doc on user variables:


It's not particularly elegant, it just gets the job done.   If there is a 
cleaner way to do this, I'm not ashamed to be educated.


Barry



*  Compquery.sql -- Compare Current Year Reg Numbers and Money to Prior Year */
/* 
*/
/* 
*/



/* ACCUMULATE DATA BY MONTH FOR BOTH 
YEARS*/


Drop Table If Exists Montable, Montable2;

Create Temporary Table Montable engine=memory
Select  Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as 
Registrations,

Extract(Year_Month from DatePaid) Monindex,
Sum(Amount) as Paid
From capclave2005reg
where ( amount > 0)
Group by Monindex;

Create Temporary Table Montable2 engine=memory
Select  Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as 
Registrations,

Extract(Year_Month from DatePaid) Monindex,
Sum(Amount) as Paid
From Capclavepresent
where ( amount > 0)
Group by Monindex;


/*  REPORT FOR BOTH YEARS WITH RUNNING 
TOTALS*/


Set @cumreg=0, @cumreg2=0, @cumpd=0, @cumpd2=0;

Select   Month,  Year,  Registrations, Paid RegIncom,
 Monindex, @cumreg:[EMAIL PROTECTED] + Registrations  RegYearToDate, @cumpd:= 
@cumpd+Paid RegIncomeYTD

From Montable

Union

Select   Month,  Year,  Registrations, Paid RegIncome,
 Monindex, @cumreg2:[EMAIL PROTECTED] + Registrations  RegYearToDate, @cumpd2:= 
@cumpd2+Paid RegIncomeYTD

From Montable2  ;



Barry Newton



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Query

[Karl Larsen]

   I am using Linux called Fedora Core 4. I had no problem getting 
mysql 4 working here and liked it a lot. But when I discovered 4 doesn't 
have VIEW but 5 does I have tried several RPM sets of 5 that fail for 
basic reasons. The reasons are the wrong libraries or they are missing 
in total :-)


   Found a 5 that looked like a tar ball but no joy. It is a set of 
binary files and a bash file to load it all. This I have not read up on 
but fear the binary files will not run on my linux version.


   I know I can yum mysql 5 on Fedora Core 5 but for other reasons I 
prefer to stay with Fedora Core 4. Has anyone got mysql version 5 to 
work on my version of Linux?


Karl Larsen


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Load Balancing

[Peter Zaitsev]

On Fri, 2006-08-04 at 15:54 -0400, Ed Pauley II wrote:
> I am looking into a scale-out solution for MySQL. I have read white 
> papers and searched the web but I can't find a load balancer that claims 
> to work well for MySQL.  MySQL's white paper shows NetScaler in the 
> scale-out stack but nothing on Citrix.com mentions MySQL. I also read 
> that friendster wrote a custom script for NetScaler to work in a MySQL 
> environment. I would rather not have to do that. Is there an out-of-box 
> solution for load balancing MySQL. My understanding is that MySQL is a 
> little more complicated than HTTP load balancing, which we already do 
> with Coyote Point Equalizers. I have thought about LVS. Has anyone had 
> any experience with load balancing MySQL? Any recommendations? Thanks in 
> advance.

As some people mentioned there is "Continuent" solution, this is what
was Emic networks previously.  

If you're building solution on your own such as master and number of
slaves there are plenty of things to think, regarding load balancing,
for example if replication breaks for any reason of falls behind on one
of the slaves you might want to kick it up.   

For very basic setup you even can use DNS for load balancing, which does
not solve many of the problems describe. 

The same kind of simple load balancing is build in MySQL JDBC Driver. 

In general everyone seems to implement something on their own, working
well for their application. 




-- 
Peter Zaitsev,  MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Load Balancing

[Atle Veka]

I should mention that the below concerns read-only daemons, Dan's post
reminded me of that. Having multiple masters in a load balanced
environment is extremely difficult to do right.

I would wager that for most applications, at least internet related,
you'll have a much higher read-to-write ratio where you can get by having
only one master while using multiple replicated slaves (even for really
high traffic sites).


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Fri, 4 Aug 2006, Atle Veka wrote:

> You can have a simple LVS setup running with a plugin from Nagios,
> check_mysql, which will connect to the mysql daemon and run a status
> query. If you want anything more than that you most likely will have to
> write a custom check plugin (shouldn't be that hard). LVS works nicely as
> a mysql loadbalancer in my experience.
>
>
> Atle
> -
> Flying Crocodile Inc, Unix Systems Administrator
>
> On Fri, 4 Aug 2006, Ed Pauley II wrote:
>
> > I am looking into a scale-out solution for MySQL. I have read white
> > papers and searched the web but I can't find a load balancer that claims
> > to work well for MySQL.  MySQL's white paper shows NetScaler in the
> > scale-out stack but nothing on Citrix.com mentions MySQL. I also read
> > that friendster wrote a custom script for NetScaler to work in a MySQL
> > environment. I would rather not have to do that. Is there an out-of-box
> > solution for load balancing MySQL. My understanding is that MySQL is a
> > little more complicated than HTTP load balancing, which we already do
> > with Coyote Point Equalizers. I have thought about LVS. Has anyone had
> > any experience with load balancing MySQL? Any recommendations? Thanks in
> > advance.
> > -Ed
> >
> > --
> > Ed Pauley II
> > [EMAIL PROTECTED]
> > http://www.brisnet.com
> > http://www.brisbet.com
> >
> >
> >
> >
> >
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Backup SQL

[Chris White]

On Friday 04 August 2006 11:26 am, Daniel da Veiga wrote:
> Think better before you hit "send".

Dude

> --
-- 
Chris White
PHP Programmer/DBarn
Interfuel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Backup SQL

[John Meyer]

If you're using Myphpadmin, you can turn this option off when generating the
dump file.

-Original Message-
From: Chris White [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 04, 2006 12:14 PM
To: mysql@lists.mysql.com
Subject: Re: Backup SQL

On Friday 04 August 2006 10:35 am, Daniel da Veiga wrote:
> What if each .sql contains a "DROP TABLE IF EXISTS" statement at the 
> start? Something to be carefull if its the program that generated the 
> backup likes to add this tags.

What if my website code breaks?  This train of "what if" type questions can
easily be answered by a quick skim through the sql before running it.  If
you believe that to be tiresome, think of how long it's going to take you to
get order from chaos when things go down.
--
Chris White
PHP Programmer/DBirth
Interfuel

-- 
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]

unexplained sigsegv, aborted backtrace

[Danny Rathjens]

I have an unexplained crash I am trying to figure out.
This is a pretty static system, that has been running fine for a couple years.
It crashed at 7:45am which is after any nightly bakups have all been done, so
not even under any load.  All other aspects of the system such as i/o,ram,cpu,
number of mysql threads, requests per second look nominal at the time(based on
sar and custom stats graphed in mrtg).  My maintenance scripts that check all
dbs and repair if necessary run weekly, which was a few days ago.

Unfortunately, I do not have --debug compiled in so I did not get a stacktrace.
It has been running fine since the crash(safe_mysqld restarted it fine), the 
only
other crash in my log was my own fault when I ran the system out of memory and 
the
kernel killed off some mysqld procs a few weeks ago.

Thanks for any ideas you can give me.
Here is the error log message:

mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=536870912
read_buffer_size=3141632
max_used_connections=52
max_connections=512
threads_connected=18
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3667964 K

bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x70b06c18
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Bogus stack limit or frame pointer, fp=0xbfebda48, stack_bottom=0x2, 
thread_stack=126976, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x7110e120  is invalid pointer
thd->thread_id=301520
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
060804 07:45:47  mysqld restarted
/usr/sbin/mysqld: ready for connections.
Version: '4.0.20-standard-log'  socket: '/tmp/mysql.sock'  port: 3306


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

unexpected trigger behavior on BEFORE UPDATE trigger using NEW. and OLD. column values

[Ferindo Middleton]

I have two databases that effect each other when triggers get excecuted.
There is a schedules database that updates registration database. The
problem I have is with the enrolled, attended, waitlisted, completed,
cancelled, etc. booleans values. The registration db has triggers on it that
enforce certain logical rules to be enforced to ensure that logical
registration statuses get correctly (Ex. the user of the database should be
enrolled=true if cancelled=true)

The problem I have is that, ON schedule UPDATEs, the registration data gets
defaulted back to enrolled = true even if pre-existing regitration data in
the db is already set to say attend, or completed status. I will paste the
two triggers below if you might just notic something wrong with the logic
causing this unexpected behavior when the trigger defaults pre-existing
registration data to match the enrolled state:

CREATE TRIGGER trigger_on_schedule_updates
AFTER UPDATE
ON schedules
FOR EACH ROW
BEGIN

UPDATE registration_and_attendance SET
class_id = new.class_id,
start_date = new.start_date,
end_date = new.end_date
WHERE schedule_id = new.id;


END;


CREATE TRIGGER trigger_registration_and_attendance_before_update
BEFORE UPDATE
ON registration_and_attendance
FOR EACH ROW
BEGIN


IF (new.enrolled = true) THEN
SET new.attended = false;
SET new.completed = false;
SET new.waitlisted = false;
SET new.cancelled = false;
END IF;

IF (new.attended = true) THEN
   SET new.enrolled = true;
SET new.waitlisted = false;
SET new.completed = false;
SET new.cancelled = false;
END IF;

IF (new.completed = true) THEN
   SET new.enrolled = true;
SET new.attended = true;
SET new.waitlisted = false;
SET new.cancelled = false;
END IF;

IF (new.waitlisted = true) THEN
  SET new.enrolled = false;
SET new.attended = false;
SET new.completed = false;
SET new.cancelled = false;
END IF;

IF (new.cancelled = true) THEN
  SET new.enrolled = false;
SET new.attended = false;
SET new.completed = false;
SET new.waitlisted = false;
SET new.overflow_registrant = false;
END IF;


END;

Ferindo
--
justferindo

Re: MySQL Load Balancing

[Dan Buettner]

Ed, in Jeremy Zawodny's (excellent) book "High Performance MySQL",
there is a chapter on load balancing - though it's a bit more of a
theoretical discussion than a how-to.

There are a couple of commercial products mentioned briefly - Veritas
and EMIC Networks.

One idea he presents might work for you, since you're already doing
HTTP load balancing: have each HTTP server talk to a mysql instance on
localhost, with the mysql instance configured as a replication slave.

Depends on your situation but that could be elegantly simple -
assuming you have a farm of servers, you just add mysql to your
standard build image, rebuild them and start replicating.  Roll out
your app talking to localhost and you've got it.

Of course rarely are things read-only; for something that writes as
well as reads, you'll likely have to do some re-coding so that db
writes go to the master and reads go to localhost.  Then you have
possible problems if you need reads to show the results of writes
right away.  It gets fun!

Sound like this might work for you?

Dan



On 8/4/06, Ed Pauley II <[EMAIL PROTECTED]> wrote:

I am looking into a scale-out solution for MySQL. I have read white
papers and searched the web but I can't find a load balancer that claims
to work well for MySQL.  MySQL's white paper shows NetScaler in the
scale-out stack but nothing on Citrix.com mentions MySQL. I also read
that friendster wrote a custom script for NetScaler to work in a MySQL
environment. I would rather not have to do that. Is there an out-of-box
solution for load balancing MySQL. My understanding is that MySQL is a
little more complicated than HTTP load balancing, which we already do
with Coyote Point Equalizers. I have thought about LVS. Has anyone had
any experience with load balancing MySQL? Any recommendations? Thanks in
advance.
-Ed

--
Ed Pauley II
[EMAIL PROTECTED]
http://www.brisnet.com
http://www.brisbet.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: MySQL Load Balancing

[Atle Veka]

You can have a simple LVS setup running with a plugin from Nagios,
check_mysql, which will connect to the mysql daemon and run a status
query. If you want anything more than that you most likely will have to
write a custom check plugin (shouldn't be that hard). LVS works nicely as
a mysql loadbalancer in my experience.


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Fri, 4 Aug 2006, Ed Pauley II wrote:

> I am looking into a scale-out solution for MySQL. I have read white
> papers and searched the web but I can't find a load balancer that claims
> to work well for MySQL.  MySQL's white paper shows NetScaler in the
> scale-out stack but nothing on Citrix.com mentions MySQL. I also read
> that friendster wrote a custom script for NetScaler to work in a MySQL
> environment. I would rather not have to do that. Is there an out-of-box
> solution for load balancing MySQL. My understanding is that MySQL is a
> little more complicated than HTTP load balancing, which we already do
> with Coyote Point Equalizers. I have thought about LVS. Has anyone had
> any experience with load balancing MySQL? Any recommendations? Thanks in
> advance.
> -Ed
>
> --
> Ed Pauley II
> [EMAIL PROTECTED]
> http://www.brisnet.com
> http://www.brisbet.com
>
>
>
>
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Aggregate Function Help (first and last records)

[Michael Caplan]

Thanks Nestor!

I think I am almost there.  However, how can I limit the result of a 
JOIN in a query, and not the entire result set?  For example:


SELECT
   *
FROM
   a
JOIN
   b
ON
   a.id = b.id

If I wanted all records from "a" and only the first record from "b", how 
would I integrate a LIMIT statement in this?  Appending it to the end of 
the query will limit the entire result set.


Any ideas?

Thanks again,

Michael


Néstor wrote:

Michael,

Take a look at this link, it talks about limits.
http://72.14.203.104/search?q=cache:8uOO2iKVffAJ:www.oreilly.com/catalog/mysqlckbk/chapter/ch03.pdf+mysql+query+obtain+last+record&hl=en&gl=us&ct=clnk&cd=7&client=firefox-a 



Néstor :-)

On 8/4/06, *Michael Caplan* <[EMAIL PROTECTED] 
> wrote:


Thanks Nestor,

I thought about that, but limit 1 doesn't work in my scenario as I
want
to access both the FIRST() and LAST() column for a result set
simultaneously.  By telling MySQL to limit to 1, I could get the
first,
but not the last.  I want mysql to give me the first, drop
everything in
between, and the last.

Any ideas?

Thanks,

Michael

Néstor wrote:
> I beleive that when you do your query you can add 'limit 1' and the
> query will return the
> first record only.
>
> Néstor :-)
>
> On 8/4/06, *Michael Caplan* <[EMAIL PROTECTED]

> mailto:[EMAIL PROTECTED]>>> wrote:
>
> I just noticed that MSAccess and SQL server support FIRST() and
> LAST()
> functions.  Is there an equivalent in MySQL?  My research
has come up
> with nil so far.
>
> Thanks,
>
> Michael
>
> Michael Caplan wrote:
> > Hi there,
> >
> > I am trying to figure out how to "flatten" the result set
of a join
> > query using aggregate functions.  For several fields
> (b.refering_url,
> > c.string, b.first_page, b.last_page) I need to pull out the
> _first_ or
> > _last_ item as ordered from the records returned from the
join.
> > However, I'm just lost for how this would be
accomplished.  I'd like
> > to say FIRST(col), or LAST(col).
> >
> > Any ideas?
> >
> > Below is a sample query I have been plugging away at.
> >
> > Thanks,
> >
> > Michael
> >
> >
> >
> > SELECT
> >a.visitor_id as cookie,
> > a.ip_address as ipaddress,
> >b.refering_url as referingdomain,
> >a.browser_type as browsertype,
> >a.os as operatingsystem,
> >MIN(b.first_visit_time) as firsttime,
> >MAX(b.last_visit_time) as lasttime,
> >DATE_FORMAT(MIN(b.first_visit_time), \'%H\') as hour,
> >(DATE_FORMAT(MIN( b.first_visit_time), \'%w\') + 1) as day,
> >DATE_FORMAT(MIN( b.remote_time), \'%H\') as localhour,
> >(DATE_FORMAT(MIN(b.remote_time), \'%w\') + 1) as localday,
> >a.browser_language as language,
> >c.string as keyword,
> > a.color_depth as colordepth,
> >a.res_height_pix as screenheight,
> >a.res_width_pix as screenwidth,
> >COUNT(b.visit_id) as visit,
> >b.first_page as firstpage,
> >b.last_page as lastpage
> > FROM
> >lps_visitor as a
> > JOIN
> >lps_visits as b
> > ON
> >a.visitor_id = b.visitor_id
> > LEFT JOIN
> >lps_keywords as c
> > ON
> >c.keyword_id = b.keyword_id
> > GROUP BY
> >a.visitor_id,
> >a.ip_address ,
> >a.browser_type,
> >a.os,
> >a.browser_language,
> >a.color_depth,
> >a.res_height_pix
> > ORDER BY
> > a.visitor_id,
> >b.visit_id
> >
>
>
> --
> 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 Load Balancing

[Ed Pauley II]

I am looking into a scale-out solution for MySQL. I have read white 
papers and searched the web but I can't find a load balancer that claims 
to work well for MySQL.  MySQL's white paper shows NetScaler in the 
scale-out stack but nothing on Citrix.com mentions MySQL. I also read 
that friendster wrote a custom script for NetScaler to work in a MySQL 
environment. I would rather not have to do that. Is there an out-of-box 
solution for load balancing MySQL. My understanding is that MySQL is a 
little more complicated than HTTP load balancing, which we already do 
with Coyote Point Equalizers. I have thought about LVS. Has anyone had 
any experience with load balancing MySQL? Any recommendations? Thanks in 
advance.

-Ed

--
Ed Pauley II
[EMAIL PROTECTED]
http://www.brisnet.com
http://www.brisbet.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tricky query

[Morten]

How about:
SELECT DISTINCT `key`, COUNT(`key`) AS c
FROM `table` WHERE `value` in (8,9,10)
HAVING c=3;



Clever! Thanks :-)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Aggregate Function Help (first and last records)

[Michael Caplan]

Thanks Nestor,

I thought about that, but limit 1 doesn't work in my scenario as I want 
to access both the FIRST() and LAST() column for a result set 
simultaneously.  By telling MySQL to limit to 1, I could get the first, 
but not the last.  I want mysql to give me the first, drop everything in 
between, and the last.


Any ideas?

Thanks,

Michael

Néstor wrote:
I beleive that when you do your query you can add 'limit 1' and the 
query will return the

first record only.

Néstor :-)

On 8/4/06, *Michael Caplan* <[EMAIL PROTECTED] 
> wrote:


I just noticed that MSAccess and SQL server support FIRST() and
LAST()
functions.  Is there an equivalent in MySQL?  My research has come up
with nil so far.

Thanks,

Michael

Michael Caplan wrote:
> Hi there,
>
> I am trying to figure out how to "flatten" the result set of a join
> query using aggregate functions.  For several fields
(b.refering_url,
> c.string, b.first_page, b.last_page) I need to pull out the
_first_ or
> _last_ item as ordered from the records returned from the join.
> However, I'm just lost for how this would be accomplished.  I'd like
> to say FIRST(col), or LAST(col).
>
> Any ideas?
>
> Below is a sample query I have been plugging away at.
>
> Thanks,
>
> Michael
>
>
>
> SELECT
>a.visitor_id as cookie,
>a.ip_address as ipaddress,
>b.refering_url as referingdomain,
>a.browser_type as browsertype,
>a.os as operatingsystem,
>MIN(b.first_visit_time) as firsttime,
>MAX(b.last_visit_time) as lasttime,
>DATE_FORMAT(MIN(b.first_visit_time), \'%H\') as hour,
>(DATE_FORMAT(MIN( b.first_visit_time), \'%w\') + 1) as day,
>DATE_FORMAT(MIN(b.remote_time), \'%H\') as localhour,
>(DATE_FORMAT(MIN(b.remote_time), \'%w\') + 1) as localday,
>a.browser_language as language,
>c.string as keyword,
>a.color_depth as colordepth,
>a.res_height_pix as screenheight,
>a.res_width_pix as screenwidth,
>COUNT(b.visit_id) as visit,
>b.first_page as firstpage,
>b.last_page as lastpage
> FROM
>lps_visitor as a
> JOIN
>lps_visits as b
> ON
>a.visitor_id = b.visitor_id
> LEFT JOIN
>lps_keywords as c
> ON
>c.keyword_id = b.keyword_id
> GROUP BY
>a.visitor_id,
>a.ip_address,
>a.browser_type,
>a.os,
>a.browser_language,
>a.color_depth,
>a.res_height_pix
> ORDER BY
>a.visitor_id,
>b.visit_id
>


--
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: Tricky query

[Gerald L. Clark]

Morten wrote:



Hi,

Given the table keyval(key int(11), val int(11)), I would like to be 
able to retrieve the keys for which a row exist for given X values.


Example:

key  value
18
19
110
28
38
310
48
49
410
411

Given values 8, 9, 10 the query should thus return 1 and 4. The possible 
number of values is variable.


Can this be expressed somewhat more elegantly than multiple EXISTS 
subqueries?


SELECT DISTINCT key FROM keyval outer
WHERE EXISTS (SELECT * FROM keyval inner
WHERE outer.key   = inner.key
AND inner.val = 8)
AND EXISTS (SELECT * FROM keyval inner
WHERE outer.key   = inner.key
AND inner.val = 9)
...

Br,

Morten




I forgot the GROUP BY

How about:
SELECT DISTINCT `key`, COUNT(`key`) AS c
FROM `table` WHERE `value` in (8,9,10)
GROUP BY `key`
HAVING c=3;

--
Gerald L. Clark
Supplier Systems Corporation


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tricky query

[Gerald L. Clark]

Morten wrote:



Hi,

Given the table keyval(key int(11), val int(11)), I would like to be 
able to retrieve the keys for which a row exist for given X values.


Example:

key  value
18
19
110
28
38
310
48
49
410
411

Given values 8, 9, 10 the query should thus return 1 and 4. The possible 
number of values is variable.


Can this be expressed somewhat more elegantly than multiple EXISTS 
subqueries?


SELECT DISTINCT key FROM keyval outer
WHERE EXISTS (SELECT * FROM keyval inner
WHERE outer.key   = inner.key
AND inner.val = 8)
AND EXISTS (SELECT * FROM keyval inner
WHERE outer.key   = inner.key
AND inner.val = 9)
...

Br,

Morten




How about:
SELECT DISTINCT `key`, COUNT(`key`) AS c
FROM `table` WHERE `value` in (8,9,10)
HAVING c=3;

--
Gerald L. Clark
Supplier Systems Corporation

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tricky query

[Morten]

Peter Brawley wrote:
 >Can this be expressed somewhat more elegantly than multiple EXISTS 
subqueries?

 >SELECT DISTINCT key FROM keyval outer
 >WHERE EXISTS (SELECT * FROM keyval inner
 >WHERE outer.key   = inner.key
 >AND inner.val = 8)
 >AND EXISTS (SELECT * FROM keyval inner
 >WHERE outer.key   = inner.key
 >AND inner.val = 9)

Would you be looking for ...

SELECT DISTINCT key
FROM keyval k1
JOIN keyval k2 USING (key)
WHERE k1.val IN(8,9,10);


The tricky part is that there must be a record for EACH of the values 
(8, 9, 10) and not just any one value (as IN requires). Your proposal 
will return 1,2,3,4 and not just 1,4.


Br,

Morten



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Aggregate Function Help (first and last records)

[Michael Caplan]

I just noticed that MSAccess and SQL server support FIRST() and LAST() 
functions.  Is there an equivalent in MySQL?  My research has come up 
with nil so far.


Thanks,

Michael

Michael Caplan wrote:

Hi there,

I am trying to figure out how to "flatten" the result set of a join 
query using aggregate functions.  For several fields (b.refering_url, 
c.string, b.first_page, b.last_page) I need to pull out the _first_ or 
_last_ item as ordered from the records returned from the join.  
However, I'm just lost for how this would be accomplished.  I'd like 
to say FIRST(col), or LAST(col).


Any ideas?

Below is a sample query I have been plugging away at.

Thanks,

Michael



SELECT
   a.visitor_id as cookie,
   a.ip_address as ipaddress,
   b.refering_url as referingdomain,
   a.browser_type as browsertype,
   a.os as operatingsystem,
   MIN(b.first_visit_time) as firsttime,
   MAX(b.last_visit_time) as lasttime,
   DATE_FORMAT(MIN(b.first_visit_time), \'%H\') as hour,
   (DATE_FORMAT(MIN(b.first_visit_time), \'%w\') + 1) as day,
   DATE_FORMAT(MIN(b.remote_time), \'%H\') as localhour,
   (DATE_FORMAT(MIN(b.remote_time), \'%w\') + 1) as localday,
   a.browser_language as language,
   c.string as keyword,
   a.color_depth as colordepth,
   a.res_height_pix as screenheight,
   a.res_width_pix as screenwidth,
   COUNT(b.visit_id) as visit,
   b.first_page as firstpage,
   b.last_page as lastpage
FROM
   lps_visitor as a
JOIN
   lps_visits as b
ON
   a.visitor_id = b.visitor_id
LEFT JOIN
   lps_keywords as c
ON
   c.keyword_id = b.keyword_id
GROUP BY
   a.visitor_id,
   a.ip_address,
   a.browser_type,
   a.os,
   a.browser_language,
   a.color_depth,
   a.res_height_pix
ORDER BY
   a.visitor_id,
   b.visit_id




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Aggregate Function Help (first and last records)

[Michael Caplan]

Hi there,

I am trying to figure out how to "flatten" the result set of a join 
query using aggregate functions.  For several fields (b.refering_url, 
c.string, b.first_page, b.last_page) I need to pull out the _first_ or 
_last_ item as ordered from the records returned from the join.  
However, I'm just lost for how this would be accomplished.  I'd like to 
say FIRST(col), or LAST(col).


Any ideas?

Below is a sample query I have been plugging away at.

Thanks,

Michael



SELECT
   a.visitor_id as cookie,
   a.ip_address as ipaddress,
   b.refering_url as referingdomain,
   a.browser_type as browsertype,
   a.os as operatingsystem,
   MIN(b.first_visit_time) as firsttime,
   MAX(b.last_visit_time) as lasttime,
   DATE_FORMAT(MIN(b.first_visit_time), \'%H\') as hour,
   (DATE_FORMAT(MIN(b.first_visit_time), \'%w\') + 1) as day,
   DATE_FORMAT(MIN(b.remote_time), \'%H\') as localhour,
   (DATE_FORMAT(MIN(b.remote_time), \'%w\') + 1) as localday,
   a.browser_language as language,
   c.string as keyword,
   a.color_depth as colordepth,
   a.res_height_pix as screenheight,
   a.res_width_pix as screenwidth,
   COUNT(b.visit_id) as visit,
   b.first_page as firstpage,
   b.last_page as lastpage
FROM
   lps_visitor as a
JOIN
   lps_visits as b
ON
   a.visitor_id = b.visitor_id
LEFT JOIN
   lps_keywords as c
ON
   c.keyword_id = b.keyword_id
GROUP BY
   a.visitor_id,
   a.ip_address,
   a.browser_type,
   a.os,
   a.browser_language,
   a.color_depth,
   a.res_height_pix
ORDER BY
   a.visitor_id,
   b.visit_id

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqldump large file (>2GBs) : some tests

[Ken Menzel]

Hi,
I just saw your e-mail.

 We use mysqldump on solaris 9 with large files all the time.  Is the 
partition you are writng the file to mounted with the largefiles 
option in /etc/mnttab?


Our mysql dump files are 25 gig.

Ken



- Original Message - 
From: "Duhaime Johanne" <[EMAIL PROTECTED]>

To: 
Cc: "Duhaime Johanne" <[EMAIL PROTECTED]>
Sent: Monday, July 24, 2006 1:50 PM
Subject: mysqldump large file (>2GBs) : some tests


Hello

This is kind of a follow up of the previous thread "Got errno 27 on
write. file too large" and kind of a survey. The problem was :  on a
solaris 9, 64 bits and mysql 4.1.7, I could not mysqldump in a file 
more

than 2G .  Thank you to the people that help me.

I make a new thread since my question is different: is there anybody
using that version of mysql (more or less) and that version of Solaris
and be able to mysqldump more than 2GBs. If so I would appreciate to
have more details on revision etc. ?

Here is why I am asking this question:

Since it appears it was the revision on my server that might be the
problem, but because the upgrade could not be done in short term and
because Sun Support did not find any problem of that sort on the 
system,

I decide to forget about the mysqldump for now and copy files for
backup.

But then I wondered if, later on, as my datafiles increase in size, I
will have the problem with the 2 gbs limitation for the datafiles
themselves . Which would be much worse. But I  succeded in dumping 
4gbs

in one table. Good news! Then I wonder if my OS allows to do that, how
come it does not work for mysqldump.


Thank you in advance

Johanne Duhaime
[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tricky query

[Peter Brawley]

>Can this be expressed somewhat more elegantly than multiple EXISTS 
subqueries?

>SELECT DISTINCT key FROM keyval outer
>WHERE EXISTS (SELECT * FROM keyval inner
>WHERE outer.key   = inner.key
>AND inner.val = 8)
>AND EXISTS (SELECT * FROM keyval inner
>WHERE outer.key   = inner.key
>AND inner.val = 9)

Would you be looking for ...

SELECT DISTINCT key
FROM keyval k1
JOIN keyval k2 USING (key)
WHERE k1.val IN(8,9,10);

?

PB

-

Morten wrote:



Hi,

Given the table keyval(key int(11), val int(11)), I would like to be 
able to retrieve the keys for which a row exist for given X values.


Example:

key  value
18
19
110
28
38
310
48
49
410
411

Given values 8, 9, 10 the query should thus return 1 and 4. The 
possible number of values is variable.


Can this be expressed somewhat more elegantly than multiple EXISTS 
subqueries?


SELECT DISTINCT key FROM keyval outer
WHERE EXISTS (SELECT * FROM keyval inner
WHERE outer.key   = inner.key
AND inner.val = 8)
AND EXISTS (SELECT * FROM keyval inner
WHERE outer.key   = inner.key
AND inner.val = 9)
...

Br,

Morten






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: determine safest value for max_connections

[Kishore Jalleda]

This might help you
http://kjalleda.googlepages.com/maxconnbymysql

Kishore Jalleda

On 8/2/06, Rithish Saralaya <[EMAIL PROTECTED]> wrote:


Hello folks.

How do I determine what is the safest value that I can set for
max_connections in my.cnf? The default value of 100 is proving to be a
shortfall during some cases of peak hour traffic.

Could you point me to a resource available if any?

1.  Our server is RHEL 3, 2*3.00 GHz, 4GB ram. This acts both as our
web
and db server.
2.  The tables are of INNODB type.
3.  mytop shows qps as 66; Hits/s as 12.3

Regards,
Rithish.

Tricky query

[Morten]

Hi,

Given the table keyval(key int(11), val int(11)), I would like to be 
able to retrieve the keys for which a row exist for given X values.


Example:

key  value
18
19
110
28
38
310
48
49
410
411

Given values 8, 9, 10 the query should thus return 1 and 4. The possible 
number of values is variable.


Can this be expressed somewhat more elegantly than multiple EXISTS 
subqueries?


SELECT DISTINCT key FROM keyval outer
WHERE EXISTS (SELECT * FROM keyval inner
WHERE outer.key   = inner.key
AND inner.val = 8)
AND EXISTS (SELECT * FROM keyval inner
WHERE outer.key   = inner.key
AND inner.val = 9)
...

Br,

Morten



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Backup SQL

[Daniel da Veiga]

On 8/4/06, Chris White <[EMAIL PROTECTED]> wrote:

On Friday 04 August 2006 10:35 am, Daniel da Veiga wrote:
> What if each .sql contains a "DROP TABLE IF EXISTS" statement at the
> start? Something to be carefull if its the program that generated the
> backup likes to add this tags.

What if my website code breaks?  This train of "what if" type questions can
easily be answered by a quick skim through the sql before running it.  If you
believe that to be tiresome, think of how long it's going to take you to get
order from chaos when things go down.


I dont know exactly what's the purpose of your message. I was just
warning the OP that he should take a look at the SQL before running
it, not taking your advice blindly:


To answer what I think was your original question, no, m-s.sql

wouldn't interfere with a-l.sql's >insertions because they run in
sequence, first a-l, then m-s.

You're wrong, they may run in sequence and still, m-s can interfer in
what a-l has done, it all depends on the program used to generate the
sql and the defaults it put (create tables, drop tables if exist or a
simple sequence of the sql generated in a-l).

Think better before you hit "send".

--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Backup SQL

[Chris White]

On Friday 04 August 2006 10:35 am, Daniel da Veiga wrote:
> What if each .sql contains a "DROP TABLE IF EXISTS" statement at the
> start? Something to be carefull if its the program that generated the
> backup likes to add this tags.

What if my website code breaks?  This train of "what if" type questions can 
easily be answered by a quick skim through the sql before running it.  If you 
believe that to be tiresome, think of how long it's going to take you to get 
order from chaos when things go down.
-- 
Chris White
PHP Programmer/DBirth
Interfuel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Another question on Cardinality??

[Brent Baisley]

I didn't say consider a table with a thousand rows, must have been someone elses response. But anyway, it comes down to knowing your 
data. If you know your data, then you can create the best set of indexes.


I would almost never create an index on a field with a cardinality of 2. However, I would most likely create a compound index that 
would include that field and others. Knowing which fields to includes under a single index requires knowing the data.


Just index everything is probably and ok policy for many of the databases out there. But when you are adding/updating millions of 
records a day, performance is a concern. Indexing everything with have a noticeable slow down on inserts, updates and deletes 
because all the indexes also need to be updated.


- Original Message - 
From: "Philip Mather" <[EMAIL PROTECTED]>

To: "Brent Baisley" <[EMAIL PROTECTED]>
Cc: "Ratheesh K J" <[EMAIL PROTECTED]>; 
Sent: Friday, August 04, 2006 1:31 PM
Subject: Re: Another question on Cardinality??



Brent,
   Given that...

You really have to match cardinality with distribution of values.

...sounds like hard work (well you actually have to think about it) and...

considering all the above cases, what should I conclude? should I have indexes 
on these three fields?
Looking for a specific answer than a "depend on situation" kind of an answer.
Frankly I'd ask "Why shouldn't I be indexing these fields?" not the other way around, you said to consider a table with only a 
thousand rows right? Unless I'm missing something an index would cost a trivial amount of disk space. If the real case is 10's of 
millions rows then compared to the data set it's still going to trivial anyway. Just index everything and throw some more hard 
drives at it.


As someone else pointed out however there's plenty of comparative info all over the web let alone MySQL's own site, you could 
probably find some approximate numbers to play with out there.


Regards,
   Phil 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Backup SQL

[Daniel da Veiga]

On 8/4/06, Chris White <[EMAIL PROTECTED]> wrote:

On Friday 04 August 2006 04:04 am, Kaushal Shriyan wrote:
> mysql -u kaushal -h example.com -p drupal <
> /home/kaushal/drupal/new/a-l.sql and then do
> mysql -u kaushal -h example.com -p drupal <
> /home/kaushal/drupal/new/m-s.sql

Better would be:

mysql -u kaushal -h example.com -p drupal < ~/drupal/new/a-l.sql &&
mysql -u kaushal -h example.com -p drupal < ~/drupal/new/m-s.sql

Couple of things, first off ~/ expands to /home/kaushal assuming you're
running this as the kaushal user.  If root, you can do ~kaushal/ to achieve
the same effect.  Next is && which means "run the next command only if the
first command finishes successfully".  To answer what I think was your
original question, no, m-s.sql wouldn't interfere with a-l.sql's insertions
because they run in sequence, first a-l, then m-s.  The only time I'd really
see this as an issue is if you were trying to run both at once, which in my
opinion is a Bad Idea(tm).



What if each .sql contains a "DROP TABLE IF EXISTS" statement at the
start? Something to be carefull if its the program that generated the
backup likes to add this tags.

--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Another question on Cardinality??

[Philip Mather]

Brent,
   Given that...

You really have to match cardinality with distribution of values.

...sounds like hard work (well you actually have to think about it) and...
considering all the above cases, what should I conclude? should I have 
indexes on these three fields?
Looking for a specific answer than a "depend on situation" kind of an 
answer. 
Frankly I'd ask "Why shouldn't I be indexing these fields?" not the 
other way around, you said to consider a table with only a thousand rows 
right? Unless I'm missing something an index would cost a trivial amount 
of disk space. If the real case is 10's of millions rows then compared 
to the data set it's still going to trivial anyway. Just index 
everything and throw some more hard drives at it.


As someone else pointed out however there's plenty of comparative info 
all over the web let alone MySQL's own site, you could probably find 
some approximate numbers to play with out there.


Regards,
   Phil

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Order by

[Chris Sansom]

At 1:00 +0200 4/8/06, Johan Höök wrote:

what you can do is:
SELECT [fields]
FROM [table]
 WHERE id IN (id1,id2,id3...)
ORDER BY FIELD([field],value1,value2,value3,...)


Ooh - so I can. I didn't know that wrinkle for 
order by - though I did wonder if something like 
that should be possible.


Thanks very much! Problem solved.

--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/

Consider for a moment any beauty in the name Ralph.
   -- Frank Zappa

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Another question on Cardinality??

[Brent Baisley]

It depends on the data, not the situation. How's that?

FLD_4 is doubtful that you would want/need and index on it. This assumes an even distribution of both values (ie. male/female). 
Since you would be scanning half the table anyway, an index won't really help. Now if it's not an even distribution, like people 
under/over 70, and you will always be searching on people above 70, then an index may help since it will narrow the records down 
considerably.


You really have to match cardinality with distribution of values. An index should allow you to quickly narrow the set of records 
that need to be analyzed. Cutting out half the records isn't going to help much, it's quicker just to read through the entire file 
sequentially than jump around to 50% of the records individually.


- Original Message - 
From: "Ratheesh K J" <[EMAIL PROTECTED]>

To: 
Sent: Friday, August 04, 2006 6:15 AM
Subject: Another question on Cardinality??


Hello all,

Another question on cardinality.

Consider a table with 1000 rows and  columnns. Details of the columns are as 
below:

FLD_1 - int - cardinality 1000 - PRIMARY KEY

FLD_2 - tinyint- cardinality 400

FLD_3 - varchar - cardinality 10

FLD_4 - varchar - cardinality 2

FLD_5 - varchar - cardinality 5

Assuming that cardinality exactly is the number of distinct values for that column, Which are the fields that is best for indexing 
for the table.


Is it meaningful for me to index FLD_3, FLD_4 and FLD_5 knowing that their 
cardinality is always going to be the same?

There are certain scenarios wherein I have queries on the tables as below:

1) Select * from table where FLD_4 = 1;

2) Select * from table where FLD_5 = 3;

3) Select * from table where FLD_3 >1 AND FLD_5 < 6;

considering all the above cases, what should I conclude? should I have indexes 
on these three fields?


Looking for a specific answer than a "depend on situation" kind of an answer.

Thanks

Ratheesh Bhat K J


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SELECT duplicate set of rows

[James Neff]

Greetings,

I need help with a select statement that finds duplicate sets of rows.

I have a table like so:

batch_data
---
ID - int (auto inc)
Record ID - int
DataValue - VarChar(99)
DataType - int


With a sample of data like this:

1   100   1122A   1
2   100   1350G   1
3   100   1001  2
4   101   1122A   1
5   101   1350G   1
6   101   1002  2
7   102   8493P1
8   102   1003  1


What I want to do is return the DataValue where theDataType = "2" of the
duplicate set of matching datavalue records where data type = "1".

In this case I want the value of "1002" returned (from row 6) because
there are 2 matching DataValue rows, 1 & 2, which match rows 4 & 5. 

A set of DataValue's is defined by the RecordID.  So set 1 consists of
rows 1 to 3, set 2 is 4 to 6, and set 3 is rows 7 & 8. 

Is this possible to do in one SELECT statement, or should I just iterate
through a resultset in my application?

Thanks in advance,

-- 

James Neff


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Backup SQL

[Chris White]

On Friday 04 August 2006 04:04 am, Kaushal Shriyan wrote:
> mysql -u kaushal -h example.com -p drupal <
> /home/kaushal/drupal/new/a-l.sql and then do
> mysql -u kaushal -h example.com -p drupal <
> /home/kaushal/drupal/new/m-s.sql

Better would be:

mysql -u kaushal -h example.com -p drupal < ~/drupal/new/a-l.sql &&
mysql -u kaushal -h example.com -p drupal < ~/drupal/new/m-s.sql

Couple of things, first off ~/ expands to /home/kaushal assuming you're 
running this as the kaushal user.  If root, you can do ~kaushal/ to achieve 
the same effect.  Next is && which means "run the next command only if the 
first command finishes successfully".  To answer what I think was your 
original question, no, m-s.sql wouldn't interfere with a-l.sql's insertions 
because they run in sequence, first a-l, then m-s.  The only time I'd really 
see this as an issue is if you were trying to run both at once, which in my 
opinion is a Bad Idea(tm).

-- 
Chris White
PHP Programmer/DBeer
Interfuel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: db logic questions...

[Philip Mather]

Bruce,

i had initially thought that i could have the following tbl structure:

UniversityTBL (
  name
  ID auto_increment,
)
  
In a generic kinda "Best Practice", things I've picked up from various 
places way I'd recommend the following things: -

1) The first field always be the Primary Key and always call it 'ID'.
2) The second field onwards should contain your Foreign Key fields 
usually postfixed with FK, although ID is fine.
3) In an Object Orientated approach the attributes of the object come 
next so Name, post code whatever...
4) Lastly put a Soft_deleted enum (Y/N), Date_modified 
(CURRENT_TIMESTAMP default, and that auto update thingy) and 
Date_created timestamp on the end for auditing purposes

5) Index anything with ID or FK in it.
6) Don't bother with the TBL in the table name.
7) Choose and stick to a capitalization, pluralization and underscores 
for spaces policy, whatever floats your boat most is fine.

my question though, is, is this approach the best. i'm also considering the
  

Long and the short of it yes.

the single tbl would be:
tbl
  name
  parentID
  ID
Don't for the love of all that's good! I've seen this carried to the 
extremes and it makes your nose bleed after a while, there are perfectly 
legitimate and good reasons for adopting this approach but it's probably 
a bad idea when the rows are going to represent different types of object.
You won't be able to determine what type of object each record is, 
you'll end up with a gigantic blob of a table and it'll have a million 
different fields that don't apply to the 75% of the individual records.
Given the complexity of your problem and the fact that your all most 
certainly using an OO language stick with representing each object with 
it's own table.

this could work.
That's what they said about the chocolate teapot. Don't, trust me it's 
not a good idea.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

db logic questions...

[bruce]

hi peter.

i've got a couple of logic/layout questions that i'm grappling with. hope
you don't mind my posting.

i've had some responses from a few people on the icr/mysql but i'm not sure
as to their level of mysql usage/skill.

my test app is used to build a system of college class schedules. i want to
give the user the ability to select a college, and from there, iterate
through/across the app to get to the classes, and finally the resulting
class information.

to support this, i need a series of tbls. the goal is to be relatively fast,
as well as relatively easy to maintain, and to write/create the queries
required to handle the system. keep in mind, i'm not a db guru.

i had initially thought that i could have the following tbl structure:

UniversityTBL (
  name
  ID auto_increment,
)

SemesterTBL (
  SemesterYear
  universityID
  ID auto_increment,
)

deptTBL (
  name
  universityID
  ID
)

courseTBL (
  name
  semesterID
  deptID
  section
  instructorID
  ID
  UNIQUE KEY name (name,section,deptID)
)



the idea is :

universityTBL
  |
||
|   DeptTBL
  ||
  ||
 CollegeSemesterTBL|
   |   |
   |---|
  |
  CourseTBL

of course the above is somewhat simplified.

with this approach, the app can easily modify/manage each tbl, and the
underlying information in each tbl. in fact, this is what some of the
comments that i got suggested.

my question though, is, is this approach the best. i'm also considering the
approach were i maintain a single tbl which would encompass the university,
semester, and dept information. this gets into the issue of trees/graphs
within a db representation. the course would still be in it's own tbl.

the single tbl would be:
tbl
  name
  parentID
  ID

this would allow me to have a parent/child relationship between every item
in the tbls. give my app, this could work. every university has a series of
semesters, where each semester has depts, where each dept, has a list of
classes, etc...

so what are the pros/cons of each approach? which approach is 'better', and
why?.. is it really just a preference? are there scaling issues with  agiven
approach that i'm not aware of...

thanks for your time/thoughts on this!!!

-bruce


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: delimiter in mysql client

[Asif Lodhi]

Hi,

I am replying back to my own post.  Sorry, I forgot to mention that I
am using mysql client on WinXP Pro SP2 and MySQL-5.0.22 is installed
on the same machine.

Any hints as to why mysql client is showing this behavior?

Thanks in advance,

--
Asif

On 8/4/06, Asif Lodhi <[EMAIL PROTECTED]> wrote:

Hi,

When I create scripts, I change the delimiter at the beginning of the
create_table script from ";" (without quotes) to "//".  At the end of
the script, I change the delimiter back to the original ";".  This is
according to what is described in doc at www.mysql.com.

However, when I run these scripts from mysql client, I don't get the
original delimiter back - all I get is a prompt "->" no matter how
many times I press Enter - like this:

   ->
   ->
   ->
   ->

The mysql client doesn't show the above behavior when I change the
delimiter manually by actually typing it and then copying and pasting
the script contents (without the delimiter change statements lines)
and then again change back to the normal delimiter ";" manually by
typing the "DELimiter ;"  in the mysql client.

Is there any way I can hope to run my scripts - without having to copy
and paste them into mysql client?

--
Thanks in advance,

Asif



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't get v5.0.22 to work;alternatives?

[Asif Lodhi]

On 8/1/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

I filed bug #20941 (mysqld seg faults during instance configuration on
XP pro, http://bugs.mysql.com/?id=20941) some time ago and have seen no
real movement on fixing it.  I'm dead in the water.  I can't get MySQL
v5.0 to work on my system.  I'd  like to try installing an older build
as a stop gap (even if it has other, non-critical bugs).  Is there
somewhere I can find an older build?


--
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]

ROLLBACK/COMMIT in Stored Procedures

[Asif Lodhi]

Hi,

In my stored procedures, i want to ROLLBACK when I encounter any
invalid values.  However, as it happens, I cannot because MySQL does
not support COMMIT/ROLLBACK functionality right now (as of ver. 5.0.22
on WinXP Pro).  I am setting session variables (Set @XX="Error
Message') according ot each anomally I find in the IN args of the
procedures and inserting duplicate values in a temporary table to make
MySQL throw me a duplicate-key error that I can then check from VB6
using another procedure giving me the @XX value.

Is this the correct way?  Is there any other better way of doing the same thing?

--
Thanks in advance,

Asif

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

delimiter in mysql client

[Asif Lodhi]

Hi,

When I create scripts, I change the delimiter at the beginning of the
create_table script from ";" (without quotes) to "//".  At the end of
the script, I change the delimiter back to the original ";".  This is
according to what is described in doc at www.mysql.com.

However, when I run these scripts from mysql client, I don't get the
original delimiter back - all I get is a prompt "->" no matter how
many times I press Enter - like this:

  ->
  ->
  ->
  ->

The mysql client doesn't show the above behavior when I change the
delimiter manually by actually typing it and then copying and pasting
the script contents (without the delimiter change statements lines)
and then again change back to the normal delimiter ";" manually by
typing the "DELimiter ;"  in the mysql client.

Is there any way I can hope to run my scripts - without having to copy
and paste them into mysql client?

--
Thanks in advance,

Asif

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Checking List

[Mike Blezien]

Is this list still working ?

Mickalo

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Backup SQL

[Kaushal Shriyan]

Hi

I have a query is i have taken backup from druapl due to timeout error
in three backup files

a-l.sql
m-s.sql
t-z.sql

Now my understanding is if i do

mysql -u kaushal -h example.com -p drupal < /home/kaushal/drupal/new/a-l.sql
and then do
mysql -u kaushal -h example.com -p drupal < /home/kaushal/drupal/new/m-s.sql

will it overwrite a-l.sql command insertion in the database

Please Let me know if you need more information on this

Thanks and Regards

Kaushal

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Order by

[Johan Höök]

Hi Chris,
what you can do is:
SELECT [fields]
FROM [table]
 WHERE id IN (id1,id2,id3...)
ORDER BY FIELD([field],value1,value2,value3,...)

/Johan

Chris Sansom skrev:

Yes, I have looked at the docs and can't find what I'm looking for.

I'm doing a very simple query:

SELECT [fields]
FROM [table]
WHERE id IN (id1,id2,id3...)

Is there a way to return the results in the order they appear in the IN 
list?


I'm sure there's something obvious and simple, but as a relative 
novice... you know how it is. :-)





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Order by

[Chris Sansom]

Yes, I have looked at the docs and can't find what I'm looking for.

I'm doing a very simple query:

SELECT [fields]
FROM [table]
WHERE id IN (id1,id2,id3...)

Is there a way to return the results in the order they appear in the IN list?

I'm sure there's something obvious and simple, but as a relative 
novice... you know how it is. :-)


--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/

Justice is incidental to law and order.
   -- J. Edgar Hoover

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Doubt on Index Merge??

[Ratheesh K J]

Hello all,

I have a doubt on Index Merge. 

We are currently using MySQL server 4.1.11. As a part of DB management I am 
currently doing an analysis on all the Tables.

While looking into the table structures, their columns and Indexes I found that 
most of the tables have an Index on fields that have a very low cardinality.

For an estimate let me say that there were indexes on fields with cardinality 
17 for a table with 13 lac rows. So i decided to remove the Index on such 
fields.

I made this decision because I assume that the probability of MySQL optimizer 
choosing such indexes is very low. MySQL would always choose a better index 
than this.

Now i doubt my assumption when I move to MySQL server 5.0.X. In MySQL 5 there 
is a concept of Index Merge. So was it right for me to remove these indexes if 
we were to use MySQL 5?

How much of a difference in terms of performance would removal of Index make in 
MySQL 4.1.11?

How much of a difference in terms of performance would retaining of Index make 
in MySQL 5?


Expecting a specific answer than a "depends on situation" kind of an answer...


Thanks,

Ratheesh Bhat K J

Re: Another question on Cardinality??

[Martin Jespersen]

Have you considered reading up on basic database management?

There are plenty of good material on the web for you to read where you 
can actually learn how to manage databases, so you don't have to ask 
others about every single detail.



Ratheesh K J wrote:

Hello all,

Another question on cardinality.

Consider a table with 1000 rows and  columnns. Details of the columns are as 
below:

FLD_1 - int - cardinality 1000 - PRIMARY KEY

FLD_2 - tinyint- cardinality 400

FLD_3 - varchar - cardinality 10

FLD_4 - varchar - cardinality 2

FLD_5 - varchar - cardinality 5

Assuming that cardinality exactly is the number of distinct values for that 
column, Which are the fields that is best for indexing for the table.

Is it meaningful for me to index FLD_3, FLD_4 and FLD_5 knowing that their 
cardinality is always going to be the same?

There are certain scenarios wherein I have queries on the tables as below:

1) Select * from table where FLD_4 = 1;

2) Select * from table where FLD_5 = 3;

3) Select * from table where FLD_3 >1 AND FLD_5 < 6;

considering all the above cases, what should I conclude? should I have indexes 
on these three fields?


Looking for a specific answer than a "depend on situation" kind of an answer.

Thanks

Ratheesh Bhat K J



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Another question on Cardinality??

[Ratheesh K J]

Hello all,

Another question on cardinality.

Consider a table with 1000 rows and  columnns. Details of the columns are as 
below:

FLD_1 - int - cardinality 1000 - PRIMARY KEY

FLD_2 - tinyint- cardinality 400

FLD_3 - varchar - cardinality 10

FLD_4 - varchar - cardinality 2

FLD_5 - varchar - cardinality 5

Assuming that cardinality exactly is the number of distinct values for that 
column, Which are the fields that is best for indexing for the table.

Is it meaningful for me to index FLD_3, FLD_4 and FLD_5 knowing that their 
cardinality is always going to be the same?

There are certain scenarios wherein I have queries on the tables as below:

1) Select * from table where FLD_4 = 1;

2) Select * from table where FLD_5 = 3;

3) Select * from table where FLD_3 >1 AND FLD_5 < 6;

considering all the above cases, what should I conclude? should I have indexes 
on these three fields?


Looking for a specific answer than a "depend on situation" kind of an answer.

Thanks

Ratheesh Bhat K J

Table size??

[Ratheesh K J]

Helo all,

Just wanted to know when should a Table be considered for partitioning ( or 
should it be archiving ).

Almost all of our tables are of Innodb type. I am looking for an estimate 
rather than a "Depends on situation" kind of an answer.

We have few of our table swhich are very huge ( in terms of number of rows ), > 
70 lac rows. Should this be a factor for table partitioning or should the 
actual data size be a factor. 

1) In that case for a system with 80 GB reserved for MySQL when should we worry 
about Table sizes?

2) We have 3 specific tables which are of size 5GB, 3GB and 1.7GB respectively. 
The first two of them have more than 70 lakh rows. As a preventive measure what 
could be the best way to optimize these tables?


Thanks,

Ratheesh Bhat K J

Re: Calculating birthdays and distances... Is there a bug?

[Chris]

Daevid Vincent wrote:

I posted this as a comment on the page too, but I’m curious as to why the
top solution is off by a day or so... Is this a bug or a rounding issue or
what? Is there a way to "fix" the top one to work the way I expect/want it
to work? I suspect it's because (as Jack Palance said in 'City Slickers')
"the day ain't over yet" that I get the rounding error.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

SET @DOYNOW = DAYOFYEAR(CURDATE());

SELECT (DAYOFYEAR(birthdate) - @DOYNOW) AS birthdays, birthdate, @DOYNOW,
CURDATE() 
FROM users

WHERE birthdate IS NOT NULL;

then if birthdays == 0, it's that persons birthday, otherwise you know if
the birthday is in the future by how many days, or if you missed it and how
many beers you owe them...

(although the missed/negative days seems to be off)

+---++-++
| birthdays | birthdate  | @DOYNOW | CURDATE()  |
+---++-++
|83 | 1969-10-26 | 216 | 2006-08-04 | 
| 3 | 1981-08-07 | 216 | 2006-08-04 | 
|-1 | 1972-08-02 | 216 | 2006-08-04 | 
| 0 | 1946-08-04 | 216 | 2006-08-04 | 
|  -151 | 1976-03-05 | 216 | 2006-08-04 | 
+---++-++


Shouldn't that -1 be -2 ?
Am I missing something obvious?


Was 1972 a leap year? In which case the day of year will be higher for 
that year than it is for this year because of the extra day in Feb.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Calculating birthdays and distances... Is there a bug?

[Daevid Vincent]

I posted this as a comment on the page too, but I’m curious as to why the
top solution is off by a day or so... Is this a bug or a rounding issue or
what? Is there a way to "fix" the top one to work the way I expect/want it
to work? I suspect it's because (as Jack Palance said in 'City Slickers')
"the day ain't over yet" that I get the rounding error.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

SET @DOYNOW = DAYOFYEAR(CURDATE());

SELECT (DAYOFYEAR(birthdate) - @DOYNOW) AS birthdays, birthdate, @DOYNOW,
CURDATE() 
FROM users
WHERE birthdate IS NOT NULL;

then if birthdays == 0, it's that persons birthday, otherwise you know if
the birthday is in the future by how many days, or if you missed it and how
many beers you owe them...

(although the missed/negative days seems to be off)

+---++-++
| birthdays | birthdate  | @DOYNOW | CURDATE()  |
+---++-++
|83 | 1969-10-26 | 216 | 2006-08-04 | 
| 3 | 1981-08-07 | 216 | 2006-08-04 | 
|-1 | 1972-08-02 | 216 | 2006-08-04 | 
| 0 | 1946-08-04 | 216 | 2006-08-04 | 
|  -151 | 1976-03-05 | 216 | 2006-08-04 | 
+---++-++

Shouldn't that -1 be -2 ?
Am I missing something obvious?

If I do "SELECT DATEDIFF('2006-08-01', CURDATE());" I get -2 as I expect.

So, I guess the real solution is to use this:

SET @YEAR = CONCAT(EXTRACT(YEAR FROM CURDATE()),'-');

SELECT DATEDIFF(CONCAT(@YEAR, DATE_FORMAT(birthdate, '%m-%d')), CURDATE())
AS birthdays, birthdate, CURDATE() 
FROM users
WHERE birthdate IS NOT NULL;

+---+++
| birthdays | birthdate  | CURDATE()  |
+---+++
|83 | 1969-10-26 | 2006-08-04 | 
| 3 | 1981-08-07 | 2006-08-04 | 
|-2 | 1972-08-02 | 2006-08-04 | 
| 0 | 1946-08-04 | 2006-08-04 | 
|  -152 | 1976-03-05 | 2006-08-04 | 
+---+++

By the way, if you're using PHP or some other scripting language, you can
get rid of the @YEAR stuff and just do:

DATEDIFF(DATE_FORMAT(birthdate, '".date('Y')."-%m-%d'), CURDATE()) AS
birthdays

ÐÆ5ÏÐ 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Cardinality

[Ratheesh K J]

Hello all,

Need an explanation for this:

I did the following - 

SELECT DISTINCT COLUMN1 FROM TBL_XXX ;

I got the foll result

1
2
3
4
5
7
8
10
11
12
13
14
16
17
18
19
20
21
23
24
25
26
27
28
29
30
--
Totally 26 rows

Now when I saw the Cardinality of this col ( COLUMN1 ) by doing a SHOW INDEX on 
TBL_XXX, It shows 93.

How can this be possible, as Cardinality should be the number of distinct 
values ( 26 in this case ) for that column right?

I also did an ANALYZE TABLE on TBL_XXX. The result is still the same.

Thanks,

Ratheesh Bhat K J