| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13
Comments:
<0> we have databases that have more than 50 million trows to be searched
<0> s/trows/rows/
<1> i'm concerned about my auto_increment field growing larger than the allowed number and causing the table to lock
<1> danh_ do you use auto_incr?
<0> yes
<1> Darien please do not forget about me :)
<2> BucWheat: sorry, working on like three things at the moment
<1> danh_ have you ran into that problem?
<2> BucWheat: try querying the channel in general
<0> not yet
<2> more eyes are better than some eyes
<1> Darien k
<2> and it seems like you want something fairly simple
<0> but i have done queries that take more than 30 minutes
<0> not acceptible
<1> yea that bites
<1> i'm trying to figure out how to average a row based on timedate and return the result for the past 13 hours worth of data
<0> i have been using a xp 2700 with 1.5 gb hooked to a raid5 with 950mb
<1> that is not too shabby
<0> maybe more ram?
<1> more ram never hurts :)
<0> to help with the more comple3x queries?
<3> that sounds more like your query is highly suboptimal
<1> if you went to a server based board you could get up to 4gb of ram or more
<3> are you indexing properly?
<0> i work in a research lab, and i', trying to fugure out prroposals for a grant
<3> !m danh_ explain
<4> danh_: (EXPLAIN Syntax (Get Information About a SELECT)) : http://dev.mysql.com/doc/mysql/en/EXPLAIN.html
<3> danh: try EXPLAIN $querythattakes30minutestorun
<3> unless you're trying to decode protein chains using the database string functions, 30 minutes is ludicrous
<1> this is the query i'm using: SELECT AVG(data1),tstamp,id FROM tData where (tstamp >= date_format(date_sub(now(),interval 13 hour),"%Y-%m-%d %H:%M:%S")) AND (data1 >= 0) AND (tstamp > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 MINUTE)) group by tstamp desc; and it just returns the current hour and it does not average data1 for a min
<1> any ideas? here is the data in the table: http://mla1.dnsalias.com/dataSet1.txt
<5> !m alter table
<4> alter: (Table Optimization) : http://dev.mysql.com/doc/mysql/en/Optimization.html
<1> in the data on my site that is for the past 13 hours
<5> !m alter table syntax
<4> alter: (ALTER TABLE Syntax) : http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html
<1> i'm trying to get the data for the past 13 hours with the values for every minute in the hour averaged
<1> how do i do this?
<1> infi any ideas?
<3> jade: !man <item>. !m nick <item>
<1> can i nest date_sub commands?
<0> thx, inf
<0> infi: we're selecting datapoints from 16 mice over 14 days, with a resolution of 50 milliseconds
<0> so it's similar to protein channel work
<6> BucWheat: what is your last where clause for?
<5> !man alter table
<4> (ALTER TABLE Syntax) : http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html
<5> !man nick alter table
<3> danh, more ram, faster disks, faster processor, in roughly that order. also, look at some of the optimization tips in the manual
<4> Nothing found.
<3> !m danh optimization
<4> danh: (Table Optimization) : http://dev.mysql.com/doc/mysql/en/Optimization.html
<1> dkr to average all of the values for 1 minute
<3> danh: there are more on the site, for adjusting the in-memory cache, etc. I don't recall what to search for.
<6> BucWheat: you are filtering what you are averaging to the last minute though
<0> info: yeah, i was just looking for appropriate orders of magnitude for hardware reqs
<3> you will benefit highly from some reconfiguration of the mysqld for that
<7> danh_ eg preloading your indexes to ram
<0> i did some changes to the inf
<0> i think my limits were mainly hardware
<0> just wondering if i should be thinking 2 gigs or 32 gigs of ram
<1> dkr really?
<6> BucWheat: you query is saynig give me rows with tstamp in the last 13 hours AND tstamp in the last minute
<5> trying to alter table, what is my error ? http://hashmysql.org/paste/viewentry.php?id=1477
<0> already know i need to get faster disks, better raid
<1> dkr well that is definetly not what i meant
<6> BucWheat: obviousl yit's only going to returns values from the alst minute
<1> dkr i'm trying to say give me rows for with tstamp in last 13 hours and average data1 column for all of the seconds in the same minute
<0> thx to infi, archivist n all for input
<6> yeah, I understand; trying to think how to do it
<6> I guess you could do it by chopping out the seconds digits
<1> dkr if i did that it would give me an error because tstamp is my primary key
<6> I don't mean modify the data int he table, I mean just to amke the grouping
<1> oh. what would that look like?
<8> Hmm, I used yum to install mysql 4.1 on fedora fc4. I cannot locate the binary to start the server....
<1> just take %S out of my current query?
<6> BucWheat: I'm not sure, never done it myself. no, that %s doesn't affect how you group. first off you would remove that last clasue with interval1 minute
<6> then perhaps you could do group by substr(tstamp, uhm, forgot syntax to remove last two chars
<1> !man substr
<4> (String Functions) : http://dev.mysql.com/doc/mysql/en/String_functions.html
<1> dkr i get error when i use group by substr(tstamp);
<6> then read that url you just triggered. :) it takes more than one argyument, :)
<1> heh ok
<9> if it took argyuments it would, but I'm pretty sure it takes more than one argument
<6> I was using a russian accent
<1> dkr i've added substring(tstamp,0,16) and it only returns one result
<1> i need the minutes for the past 13 hours
<6> the thing is the avg has to be applied to each of those groups where the tstamp is the same minute...
<6> did you remove that interval 1 minue clause?
<1> yea i also changed the 0 to 1 in the substring clause
<1> and that gave me output
<1> now i need to pull out the calculator and make sure mysql did the math right
<5> how can i gracefully abort an operation that is taking too long without wrecking my data?
<9> what's the operation?
<5> update
<10> I installed (compiled under Debian) MySQL 5.0 and I have some problems. For example, when I try to connect in phpmyadmin, I have the following error: "#1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client". Any idea?
<5> shabbs, how bout select?
<9> jade: select is ok to kill, update might mess with your data if interrupted
<5> shabbs, ok thanks
<9> !m guilherme-jorge old client
<4> guilherme-jorge: (Client does not support authentication protocol) : http://dev.mysql.com/doc/mysql/en/Old_client.html
<11> guilherme-jorge: try setting the p***word to old_p***word('something');
<12> hi
<6> BucWheat: wow, it works, :)
<12> can anybody help me? http://hashmysql.org/paste/viewentry.php?id=1478
<1> dkr now i need to figure out why it works. i would have never thought of using substring
<1> dkr please explain
<13> Hi, mysql -uroot -h127.0.0.1 works but mysql -uroot -h192.168.0.110 gives a "Can't connect to MySQL server on '192.168.0.110' (111)" error, why?!!
<6> BucWheat: by chopping off the seconds part of the timestamp, all the enrties in the same minute look the same
<1> dkr so when you GROUP BY ... that does ?
<1> !m group by
<4> group: (Sorting Rows) : http://dev.mysql.com/doc/mysql/en/Sorting_rows.html
<12> can anybody help me? http://hashmysql.org/paste/viewentry.php?id=1478
<1> dkr is there a function to return rows with EVEN(id) where id is an auto_increment field?
<6> it displays your selection(which does the aervage) for each group(and the group is defined by all the fields that you specified thata re same)
<6> BucWheat: that would be a simple select query
<1> dkr ahh so the groups that have the same min are averaged because i said Select avg(colum),tstamp group'd by tstamp,1,16
<6> right
<1> dkr so EVEN() is a correct function? if so i guessed hehe
<6> I don't know, actually, i ***ume dit was since you were using it, heh
<1> !m even
<4> Incorrect usage. See 'help m'
<1> what is the best way to search for a function?
<1> !m math
<4> Incorrect usage. See 'help m'
<1> !m auto increment
<4> auto: (How to Get the Value of an AUTO_INCREMENT Column in ODBC) : http://dev.mysql.com/doc/mysql/en/ODBC_and_last_insert_id.html
<6> !m arithmetic
<4> Incorrect usage. See 'help m'
<9> !man math functions
<4> (Mathematical Functions) : http://dev.mysql.com/doc/mysql/en/Mathematical_functions.html
<9> !man even
<4> (Event Reports Generated in MySQL Cluster) : http://dev.mysql.com/doc/mysql/en/MySQL_Cluster_Event_Reports.html
<9> heh, that's not it :)
<6> yeah, you can get even numbers by using MOD()
<1> !man mod()
<4> (Mathematical Functions) : http://dev.mysql.com/doc/mysql/en/Mathematical_functions.html
<6> MOD(number,2)=0
<14> modulus = remainder after division
<6> remained of 0 when dividng by two means it is even
<14> if the divisor is an number bigger than 2, then 2 is a possible remainder
<6> eh? remained can only be 0 or 1
<6> remainder
<14> if you want an even or odd test, do modulus 2
<14> yep
<1> dkr how would that work with my query though would i need to include an if then else statement or something or will select mod(id,2) return all even ids?
<6> you only want to average entires with an even id?
<1> no now i think i want to display the averaged minutes with an even id :) this will make my graph look prettier with half of the data sets
<6> select id from table where mod(id,2)=0
<6> would return even ids
Return to
#mysql
or
Go to some related
logs:
shlemozzle
#fedora
gentoo xorg.conf alt_r
#perl
#osdev
Compaq nx6125 modem Ubuntu
#web
Unison hidden files
#ubuntu
ubuntu hda microphone
|
|