Re: HELP WITH A DATE QUERY USING 4.0.17
I'm not sure if there is a built-in, but what I usually do for similar things is to use general purpose table to force iteration. E.g. create a table called ITERATE with one column, x, and populate with values 0,1,2,3,4,... -- in your case up to 10 -- and index the column. Then it's easy - I only show the date field here: select DATE_ADD(B.Booking_Start_Date,interval it.x day) from Bookings B,iterate it where B.User_ID = '610' and it.x = 1 and it.x = 10 group by DATE_ADD(B.Booking_Start_Date,interval it.x day) Regards, John -Original Message- From: shaun thornburgh Hi, I have a table called Bookings which holds start times and end times for appointments, these are held in Booking_Start_Date and Booking_End_Date. I have a page on my site that runs a query to produce a grid to show availiability per day for the next ten days for each user of the system. Users work 8.5 hours a day and the query shows how many hours available the user has on that day: SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS Available_Hours FROM Bookings B WHERE B.User_ID = '610' AND NOT ( '2005-04-08' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2005-04-08' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) The problem here is I have to do this query to produce a result for each cell(date) and then for each user so 100 users = 1000 queries to load the page! Is there a way to produce the result so that I only need one query per user so it groups the result by day for the next ten days? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Mysqld Stability and maintanability
We have been running two 3.23.47 and one 4.0.4 (innodb) on a single machine the past 2 years. We haven't had stability or any ongoing problems. If something happens to one instance, it has not affected any other. The main issue is resource allocation. Also be sure that the different instances do not share the same directories, tmp space as well. John Winner H Manurung wrote: Dear All, I was an Oracle dba, now my new company want to use Mysql 4.0.18. Does anybody here has experience of running multiple mysqld (i.e. multiple instance on one machine). Is it stable and totally independent to each other? Thank you for your answer Winner Mau dapat hadiah jutaan rupiah, ikuti game dan quiz-nya di http://www.m-stars.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Record lag functionality in MySQL?
If I'm reading this correctly, a self-join might work if you have a primary key defined: select t1.a as a1 ,t1.b as b1 ,t1.c as c1 ,t2.a as d1 from thetable as t1 ,thetable as t2 where t2.primaryid = t1.primaryid+1 John David L. Van Brunt, Ph.D. wrote: I have a table of data... A1 B1 C1 A2 B2 C2 And I¹d like to make a query where I ³lag² a value, grabbing a value from the next row of the table. Most stat packages have a lag function, but I can¹t find this in MySQL. The end result would look like: A1 B1 C1 D1 (where ³D1² would equal A2 from above) Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Drop all keys / indexes on a table?
I've been using comand line piping through awk to handle
mass tables modifications and listings. E.g. in your case
something like the following would hit every index, except
the primary keys, in table tablename in the test database.
echo show index from tablename |
| mysql -uuser -ppswd test
| awk '{if($3 !~ /Key_name/ $3 !~ /PRIMARY/) print $1 $3}'
| awk '{print alter table $1 drop index $2;}'
| mysql -uuser -ppswd test
To hit every table in the test database:
mysql -uuser -ppswd -e show tables from test
| awk '{if(NR1) print show index from $1;}'
| mysql -uuser -ppswd test
| awk '{if($3 !~ /Key_name/ $3 !~ /PRIMARY/) print $1 $3}'
| awk '{print alter table $1 drop index $2;}'
| mysql -uuser -ppswd test
John
Daevid Vincent wrote:
Thanks for the reply, however looking at all those options and none seems to
do what I need.
-Original Message-
From: PeterWR [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 30, 2004 11:55 AM
To: Daevid Vincent; [EMAIL PROTECTED]
Subject: Re: Drop all keys / indexes on a table?
Hi,
Take at look at CHECK TABEL - as far as I remember, the CHECK
TABLE EXTENDED
will do a re-index (check index), otherwise some of the other
OPTIMIZE, etc.
can help on this.
Take a look in the exellent manual.
Best regards
Peter
- Original Message -
From: Daevid Vincent [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, March 30, 2004 9:44 PM
Subject: RE: Drop all keys / indexes on a table?
Ugh. I was afraid you were going to say that...
Seriously, there's no way to just 'wildcard' ALL indexes,
someone should
add
that as a feature request. We're using 4.0.17 BTW.
What happens if I list out all the indexes that there
_could_ be in one
ALTER line like that, and one of the indexes doesn't
actually exist? Will
the whole ALTER fail?
Here's the situation, I wrote a script that runs
recursively through a
directory and applies all the .sql files it finds (in alpha
order). This
script runs as part of a client update, and doesn't
necessarily run the
same
number of times for everyone. So, some clients may have
extra indexes:
foo_1, foo_2, foo_3, ... foo_15 etc. (the problem at hand),
and other
clients may just have: foo_1, foo_2, foo_3.
-Original Message-
From: Victoria Reznichenko
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 30, 2004 12:48 AM
To: [EMAIL PROTECTED]
Subject: Re: Drop all keys / indexes on a table?
Daevid Vincent [EMAIL PROTECTED] wrote:
It has come to my attention that we have maxed out our keys
due to a stupid
update script bug. It seemst that we've not been explicitly
naming our keys
and therefore mysql tried to be helpful and adds a new key
each time!
*sigh*.
Is there a SQL command to DROP ALL keys on a table, so I
can just ALTER it
and add them specifically again?
Specify several DROP INDEX clause in the single ALTER
TABLE statement:
ALTER TABLE table_name DROP INDEX index_name1, DROP
INDEX index_name2, .. , DROP INDEX index_nameN;
--
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb logfiles timestamp question
Yes that makes sense, thanks Heikki. I monitored the ib_logfiles some more and see the cycling between pairs logfile0+logfile1 and logfile0+logfile2. Heikki Tuuri wrote: John, - Original Message - From: John Thorpe [EMAIL PROTECTED] I am running 4.0.4 using innodb tables on a linux box. My innodb config is set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=500M set-variable = innodb_log_buffer_size=30M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 My question has to do with innodb's usage of the redo log files. Currently they are: 524288000 Mar 11 11:19 ib_logfile0 524288000 Mar 3 08:59 ib_logfile1 524288000 Mar 11 11:19 ib_logfile2 I always see them timestamped like this, with two having identical times, or the same within a minute or two. The particular pair of the three having the same timestamp varies. the checkpoint stamp fields are in the first ib_logfile. Does that explain the observed phenomenon? I was under the impression that mysql would cycle through these logfiles - e.g. write to logfile0 until it is full, then switch to logfile1 until full, then logfile2.., then logfile0, etc. Thanks, John Best regards, Heikki Tuuri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb logfiles timestamp question
Hi, I am running 4.0.4 using innodb tables on a linux box. My innodb config is innodb_data_file_path = ibdata1:1800M;ibdata2:1800M;...ibdata10:1800M set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=500M set-variable = innodb_log_buffer_size=30M set-variable = innodb_buffer_pool_size=800M set-variable = innodb_additional_mem_pool_size=150M My question has to do with innodb's usage of the redo log files. Currently they are: 524288000 Mar 11 11:19 ib_logfile0 524288000 Mar 3 08:59 ib_logfile1 524288000 Mar 11 11:19 ib_logfile2 I always see them timestamped like this, with two having identical times, or the same within a minute or two. The particular pair of the three having the same timestamp varies. I was under the impression that mysql would cycle through these logfiles - e.g. write to logfile0 until it is full, then switch to logfile1 until full, then logfile2.., then logfile0, etc. This doesn't appear to be the case. Has anyone run across this before? (My goal is to reduce the size of the logfiles so that the time between switching is on the order of a couple hours rather than days.) Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interesting Challenge
I know this is not elegant, but have have you tried using a temporary table? It adds up your function column correctly. There was an example of this earlier today from Oyekanmi - Re: getting around a subselect, http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:123911:200211:onbajmklkgifeckohcpa Try create temporary table temp your query here; select sum(cell),sum(sector),sum(att), sum(etc) from temp; John Black, Kelly W [PCS] wrote: Hi sql query wizards! I need some help. Is there a way I can take this query here = mysql SELECT cell, sector, - sum(att) as att, - sum(lc) as lc, - sum(csh) as csh, - ROUND((SUM( lc + csh ) * 100 ) / (SUM(att) - SUM(tccf + bpp + bpc + suf)),2) AS drops, - sum(tccf) as tccf, - sum(bpp) as bpp, - sum(bpc) as bpc, - sum(suf) as suf, - ROUND(SUM( tccf + bpp + bpc + suf) *100 / SUM(att),2) AS blocks, - sum(mou) as mou - FROM ss - WHERE release=CURDATE() - GROUP BY cell - HAVING sector=1 AND (cell=148 or cell=3); +--++--+--+--+---+--+--+--+--+-- --+--+ | cell | sector | att | lc | csh | drops | tccf | bpp | bpc | suf | blocks | mou | +--++--+--+--+---+--+--+--+--+-- --+--+ |3 | 1 | 734 | 12 |6 | 2.52 | 21 |0 |0 |0 | 2.86 | 1501 | | 148 | 1 | 2746 | 93 | 30 | 4.59 | 63 |0 |0 |1 | 2.33 | 4672 | +--++--+--+--+---+--+--+--+--+-- --+--+ And have it display the two rows as a total sum together in one row? I have been struggling with this and could really use some help. Thanks in advance for any ideas. Regards, Kelly Black Linux was very clearly the answer, but what was the question again? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
