| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
Comments:
<0> inviso: is you here?
<0> :)
<0> inviso, did you remember , you spoke me
<0> how can i repalce the subselect
<0> with join?
<0> i have a question for this technique:)
<0> how can i sort/order/WHERE with this?
<0> with join, and aliasing...
<0> so, has you an idea?:)
<1> How do I reset a query back to the start, in PHP ?
<2> err .. what?
<1> I've looked on php.net
<3> he means the result pointer
<2> mysql_result_seek or something like that
<4> MetaMorfoziS: have to see the query you have now. Sorting ought to be rather straightforward though
<1> mysql_data_seek($query,0); - thanks.
<0> inviso the main query is bad on my forum
<0> it isn't lists the newest post
<0> i has the topic id in the query
<0> and i need the newest post with that topic_id
<0> but with join and alias i can't order by time
<4> MetaMorfoziS: Like I said, I would need to see the query. Let me know when you have it available online somewhere
<0> oh, i started it from 0
<0> okay i produce sg
<0> moment
<5> I'D LIKE TO MAKE AN ANNOUNCEMENT
<6> hi, i was wondering if it's possible to have null rows appear first when ordering by a column descending.. i tried "order by col is null, col desc" but that doesn't do it.
<5> I'm drinking coffee out of my brand new stainless steel MySQL mug.
<7> you could try something like ORDER BY isnull(col, -1) (or is that ifnull?)
<7> and whatever value you want the null to be
<6> hm, i guess i could select coalesce(col, LARGE_CONSTANT) and then just order by col desc.
<6> ah that might work too, thx.
<8> jdolan: or try ORDER BY col IS NULL DESC, col DESC;
<6> snoyes, that's what i did, see? :)
<8> not quite
<8> missing a DESC
<6> ooooh..
<6> interesting.
<0> inviso: http://sql.pastebin.com/655972
<6> good call, is null will return 1 or 0, so true would come after false.
<6> 8)
<0> so i need list TOPICS in forum GROUPS
<0> and for all topic, the last post, the last poster name/nick_id (if not registered or registered)
<0> with join and alias i can't order by forum_lines.ido
<0> ido = time
<8> jdolan: I wonder if there's a performance difference between 'is null desc' and 'is not null'
<9> hey all
<9> question: how can I select a range of daily sums (from first to last of the month, say) grouped by day without creating a reference table containing all days, but contaning zeros for the days that have no entries?
<10> you can't.
<9> ie, normally, if I do "SELECT DAYOFMONTH(hittime),SUM(hits) FROM daily_hits WHERE MONTH(hittime) = 03 GROUP BY DAYOFMONTH(hittime)", I get entries (1,20),(2,30),(4,10) - no entries in daily hits for day 3, no entry in my results.
<9> and i want (1,20),(2,30),(3,0),(4,10)
<9> really?
<9> so i have to create a reference table for each month?
<10> really really.
<9> and have two reference tables for february, in case of a leap year?
<9> I can't have them created dynamically somehow?
<7> you could have a table for the months and the number of days in each
<7> and use a stored procedure
<4> MetaMorfoziS: You don't have detail data from morf_lines to order by anyway. You're grouping by morf_groups/morf_topics which will collapse up the data in morf_lines for summary purposes
<10> either that, or have your app do something sensible when no rows are returned for a given day
<9> :|
<4> MetaMorfoziS: if you want full detail, remove the group by
<9> yeah... just was hoping I could do it in sql, not at application level
<9> extra complexity
<0> but how can i order the forum lines?
<0> morf_lines ?
<9> shabbs: hmm. thank you.
<4> if the group by is removed, it will happen automatically
<8> on the contrary, it allows the database to do its job (store data) and the application to do its job (analyze data)
<0> i not need auto
<0> i need orderby manually by time, name and other
<4> MetaMorfoziS: well, with the ORDER BY morf_lines.ido, it will sort. You might want to add morf_lines.ido to your columns selected so you can see that data.
<4> MetaMorfoziS: ORDER BY morf_lines.ido, morf_groups.name, other
<0> okay but i need orderby in the topics
<0> for see the last post
<0> and need order the end results
<4> MetaMorfoziS: then you want the max(ido) rather than just ido. Take a look at the following doc for group-wise maximums
<4> !m MetaMorfoziS group-wise max
<11> MetaMorfoziS: (The Rows Holding the Group-wise Maximum of a Certain Field) : http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html
<0> it's too heavy for me
<7> hard
<0> please don't be teaser
<12> shabbs: i am desperating to know when the insertion of the file will end up, don't you have any idea how could i know the advancement of the process?
<7> loiic: I'm afraid there is no way to know when it will end. you can check to see if it's indeed still doing the operation with SHOW PROCESSLIST; but that's about it
<12> is it possible to grep directly in the db files or something ?
<7> it's possible you could do a select count(*) from mytable; to see how it's doing
<12> doesnt work
<12> it just hang on ...
<7> does the select get blocked
<7> yeah, probably
<7> hrm
<7> don't know of any other tricks
<12> hehe thanks anyway
<12> are the db in a binary format?
<7> yeah
<7> you could supposedly look at the filesystem and watch the size grow, but it doesn't really tell you how far along it is, just that it's still inserting
<12> is the text plain even if it s binary ?
<4> MetaMorfoziS: eh? I'm concerned that you don't really understand what group by does. You may want to take a look at the information in the tutorial on group by and aggregation
<7> loiic: if you look at the table.MYD on disk you can actually see entries, but since it also contains binary data it won't look very pretty :D
<7> and I'm not sure if it's a good idea to be doing anything with the .myd file while it's being inserted into
<12> then grep should be able to understand it!
<0> i understand groupping but i want too big for my knowledge, my problem is too heavy or hard
<0> i has forum, forum groups, forum topics, forum lines (in f.topics) and forum_users
<12> actually the file size give me good indication already ; )
<13> HI can anyone help me, I want to backup both MySQL and Postgres databases with the same methodology(same application) can anyone recommend a way or an application to do this efficently?
<0> i want to list it in groups, all group has some topics, and all topics has some informations about the last post (nick or nick_id,last post time and all posts number)
<7> MetaMorfoziS: and why can't you just do the last part in a 2nd query?
<12> is it possible to calculate the size of a row knowing i have 2 varchar of 100 with 1 index on each?
<0> i want all in one query
<0> because it's faster
<7> uhm, not necessarily. if your indexes aren't correct your joins would be a lot slower than running a 2nd query
<0> and if it starts, and some peoples create some forums and some gorups,topics blaa...
<0> so the queryes powed
<0> 1 forum * 10 group * 10 topics * 1000 posts
<0> if i **** up the query it causes a brutal cpu and memory load.
<0> and ^^ not a big number
<7> no, because you're not reading all of the posts
<7> you're only doing the query on the 10 topics
<0> yes but it counts the posts
<0> and get info about the last post
<7> if counting 1000 posts, or even 1000000 was a problem for mysql, it wouldn't be as popular as it is
<14> !m string functions
<11> string: (Functions and Operators) : http://dev.mysql.com/doc/mysql/en/Functions.html
<7> and you're still not looping through all posts
<14> doh!
<3> loiic - no. varchar == variable length. FYI, indexes aren't in .MYD; they're in .MYI, and are fixed width. You might be able to calculate by the size of .MYI, though
<14> how to get the man page for string functions
<0> shabbs: it's not one forum
<7> MetaMorfoziS: yes, but you're still restricting your query to the particular forum, group, and topic when looking at posts
<0> somebody who register can make forum
<14> !man string functions
<11> (String Functions) : http://dev.mysql.com/doc/mysql/en/String_functions.html
<0> so you said i need explode it 2 query?
<14> woohoo!
<12> the thing is the .MYD is already a bit bigger than the file i am inserting
<3> so?
<0> like the groups topics
<12> so how is it possible hehe
<0> and the second with topics id gets the number of posts and other?
<0> it isn't a bad track?
<3> lots of ways
<0> not simpatic for me:(
<0> i dunno why
<0> but it is the easiest.
<14> i wonder if REPLACE() can be used instead of CONCAT() to dynamically construct a query inside an SP?
<7> no, it makes more sense than trying to understand a 3 table join with group bys
<7> in fact if it was me, I'd just do for loops per level of indirection, ie forum, group, topic
<12> ho actually the size of the file doesnt move any more only the size of MYI is i guess it s mean it s now building the index, is that correct?
Return to
#mysql
or
Go to some related
logs:
#sed
Gentoo EmergePackageError, fatal, emerge, Could not emerge + pkg + !
#math
#lisp
partial match mysql
atftpd cant bind port 69
#perl
#debian
#math
gentoo alsasound' not found; aborting
|
|