| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11
Comments:
<0> <1>are you just adding the total of a field on each table, then adding each of the total together again?
<1> no, i'm selecting a field calls for a certain date from each table
<0> <1> one row per table, three rows total?
<2> how do I show the cols of a tbl?
<1> say like bill make 5 sales calls on monday, dan make 7 and steve make 3
<3> describe tbl;
<1> each person has a table
<1> I want to grab 5, 7 3 and add them up to put in 15 into anopther table
<2> What's a quick way to convert unix timestamps in a column all to the datetime format?
<0> <1> how do you do the query to find out how many call bill made?
<1> so, it would be selecting one row from each table and adding them together
<4> why does each person have a table?
<1> so thateach person can havea prodution report
<1> well, I would do Select calls from Bill where date = monday
<2> Anyone? :)
<5> http://pastebin.com/528249
<0> <2> FreeOne3000 gave you the answer...
<2> jollygood: "GaryKing: What's a quick way to convert unix timestamps in a column all to the datetime format?"
<1> can I do something like Select sum(bil.calls,steve.calls,dan.calls) from stev,dan,bil Where date = monday?
<6> tunachips: That's a serious design mistake.
<1> why's is that?
<6> tunachips: Extremely serious.
<6> tunachips: There should be one table for all salesmen.
<0> <1> not without joining tables somehow, it sounds like you need bill, steve,and dan all in one "CALLS" table
<4> because you end up not being able to extract the data you need
<6> tunachips: You obtain a report for a single sales person by considering only records with the proper sales_person_id. If you want a full report of all sales, obtain that without regard to sales_person_id.
<0> <jay> since you have unique album name you don't need to sort... or limit..
<7> Is there a command to get me the everage... like SUM(stuff) / COUNT(*) ?
<5> Okay jollygood
<0> <jay> and running querys on a key with varchar(200) is going to need some resources...
<1> well, in my actual project, I have a table from each person that collect the type of call they receive and what they do with it
<6> tunachips: Lookup the term "normalization" within the context of relational database theory.
<5> Alright
<6> tunachips: Yes. You should fix that soon.
<5> so I should probably do a bit of fixing on the CPU usage.
<1> each table has check, redirect, info, billing, acct, and other fields
<5> Anything else I can do to optimize the table structure/index structure?
<2> What's a quick way to convert unix timestamps in a column all to the datetime format? :)
<1> the rows are entered per date
<8> GaryKing: from_unixtime()
<6> tunachips: Yes. I'm sure you have all sorts of design issues. That's what happens when the DB designer has no experience.
<8> !m GaryKing from_unixtime
<2> Darien: yeah but for entier table
<9> GaryKing: (Date and Time Functions) : http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
<2> I know that func..
<8> ok
<2> I'm unsure of how to do for entire table.
<8> well
<8> you can't do it in-place
<8> so create a separate datetime column
<8> then do UPDATE foo SET datetime=FROM_UNIXTIME(unixtime_column)
<2> Darien: nice. thxd!
<6> tunachips: For your case, if you need a report for all sales people, just UNION the tables together.
<8> np
<2> Darien: I was going to write a ph script and everything..
<2> *php
<8> haha
<8> SQL ftw
<6> tunachips: With the correct design, you wouldn't need this mess.
<2> Darien: yeah I knew there wa as way
<8> you were right :)
<8> I like you
<8> you trust in the SQL server
<1> well, you tell me how I'm supposed to record what ecah person does
<1> three people, seven task outcomes
<1> on a day
<6> tunachips: You already have your design. Just use a UNION and create a derived column to indicate which data comes from which sales person.
<6> tunachips: In a correct design, each data record would have a sales person id.
<1> I don't thinkyou understand what i'm doing
<5> Oh, is it incorrect to have 'albuminfo' in both primary key and a normal key?
<5> normal index*
<5> ?
<6> tunachips: I understand enough. I can't redesign your entire database based on the tiny bit of information you gave. But I have enough information to know you probably have a very weak design.
<8> _Jay: is one of them a composite index?
<5> What is a composite index?
<8> an index that consists of multiple columns
<1> i doubt it
<5> yes
<8> then no
<5> alright, thanks
<8> glad to help
<5> :o)
<6> tunachips: Different tables should normally represent different types of data, not different instances of the same data.
<6> tunachips: Sales info for peter and paul is not different types of data.
<6> tunachips: It's two instances of the exact same type of data.
<1> taken over what period
<6> tunachips: It belongs in one table (partitioning aside).
<6> tunachips: date is an attribute.
<0> tunachips: add a date field to keep track of time period
<6> tunachips: You obtain date range reports by quering for the correct date ranges.
<6> tunachips: You obtain aggregate reports by aggregating over the right groups.
<6> tunachips: All done from the same table or set of relations.
<6> tunachips: That doesn't mean your entire database is one table. You still need to store different types of related data in separate tables.
<6> tunachips: It's tough to teach relational design in 5 minutes.
<5> is there a difference between doing 5 indexes each containing a column and doing 1 index containing 5 columns?
<6> _Jay: Yes.
<4> yes
<5> Alright.
<5> I know there is no 'which is better' answer...
<5> But there might be one for my situation in particular
<1> well, I'll move along. You have completely misunderstood what I'm doing, but, thats ok, thanks for you ***istance.
<5> is there a place where I can read about which of the two choices I should make, and why?
<6> tunachips: Good luck.
<8> _Jay: MySQL will only use one index per query
<0> xgc: I thought your explanation was nice....
<6> jollygood: *nod*
<8> _Jay: so if you do queries like 'where user = 'foo' and date = "whatever"'
<8> _Jay: then you want to have a composite index representing that
<5> Ahhh.
<6> jollygood: Hard to do in 20 words or less.
<0> xgc: agree
<5> So if I have two composite indexes, one containing user and date, and one containing user, date, blah, blah2, it will be quicker/better to use the first one?
<5> and will mySQL work out which one is best, on its own?
<5> So many questions. sheesh. Just trying to learn a bit about optimizing my database, cause no matter what I will have limited resources on the machine I use
<8> _Jay: generally, yes
<0> <jay> you may want to create an index on your albumname of the first 10 characters
<8> _Jay: generally, a given table will have very few types of queries that get run, so you can make individual indexes for each style of query you make
<5> Yes I was about to ask about that Darien if that wouldnt make sense
<5> jollygood - Alright
<8> e.g. for a table that stores usernames and p***words, a (username,p***word) index would be ideal
<5> jollygood - How do I specify the first 10 characters for this index?
<5> ahg.
<5> Got it.
<0> <jay>ok
<8> first 10 characters?
<8> why not the whole thing?
<0> <jay> but again your sort looks like it is being done on disk, so it will also the whole thing down....
<0> s/also/slow/
<5> I think that by removing the sort and limits, that will remove the largest part of my issues.. Also, before I add an album, I do a SELECT query to check if it has already been added, would it not be faster to just add and then make my code recognize that if the add failed, most likely it was because the album is already in the database?
<8> it's faster to do the INSERT and check the error code if it fails
<10> Darien :O
<5> Alright thanks, in fact I will just do that, and then have the code do a SELECT if it fails to determine if that was the cause
<0> <jay>cause albumname is the primary key you will not be able to insert the same album name twice
<5> then it makes sense to drop that select statement every time, since that is the 'root of all my evil' so far
<5> thanks a ton guys.
<8> evil rocks
<5> perhaps for you but it has given me so much stress that I think I will have to retire before actually getting a job :P
<10> :o
<10> yeeeeeeeeeeeeeeeeeeees, let the hate flow thru you, Darien :P~
<11> hello
<12> naive question: is it safe to concurrently (without transactions, on MyISAM) increment a counter field by using "update tbl set x=x+1 where id = .."? is "update" atomic?
<8> yes
<8> UPDATE does a table lock
<12> thank you
<8> np
<13> )
<10> heh
<10> ack
<8> wtf
<14> shavedgoats!
Return to
#mysql
or
Go to some related
logs:
input irq status +71 +redhat
error: dependancy is not satisfiable: xchat
#suse
postfix overriding MX
#math
#php
#centos
+maildrop glibc detected free
#lisp
#javascript
|
|