| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Comments:
<0> There is also an issue: if there is only one comment, it must aslo be selected ( thats the reason of the UNION and the second query)
<1> Hello
<2> arjenAU: One way I used is MPTT and select all the chilren from the categories table, then join it to the data table
<0> arjenAU, do you understand my issue ?
<1> Is this query right? UPDATE pc SET pc_id = CONCAT('0',pc_id)
<2> arjenAU: this would be quick for nodes with lots of children in the data, because it can just run through the insert_time index and find the first x matches (with LIMIT x)
<2> arjenAU: but it's slow when you join the data where there are a small number of data rows, because either a filesort will have to be done, or a LOT of the insert_time column index will have to be searched
<3> Jivedue: you probably can't do that indexed order by anyway.
<2> arjenAU: so I created a categories2data table which contains every parent there is, to every data row
<3> Jivedue: unless you were joining on the same field you want to order by on.
<2> arjenAU: that's what I'm getting to
<2> arjenAU: so I make a table where it maps out all the parent to data combinations
<2> arjenAU: so then I'll have a column: INDEX ( parent , close_time )
<1> Is this query right? UPDATE pc SET pc_id = CONCAT('0',pc_id)
<4> ugh, i cannot set up ssl :(
<2> arjenAU: the data table can then be joined directly to this summary table, and is extremely fast, only the summary table contains a ton of rows, and huge indexes
<3> Jivedue: it's nonsense, because if you then ORDER BY close_time, it's still a complete sort. sort_time in that index only has ordering WITHIN each parent.
<2> arjenAU: the number of `category2data` rows would be approximately (average number of parents ~= 4) * number of rows in the data table
<3> fromvega: looks like a bad table design to me ;-)
<2> arjenAU: when I'm selecting from the data table, it's not just the immediate children, it's ALL the children
<3> jbrimble: depending on version the server may not have it enabled.
<3> Jivedue: so?
<1> arjenAU: but, is it right? because mysql is telling that the function CONCAT does not exist
<0> arjenAU, do you understand my issue ? I want to build something like a changelog, selecting the changes that occured within the past month, for example
<3> fromvega: I doubt.
<5> Hey - stupid question - I've just been sent a mysql database, and its the actual mysql database as its stored on the filesystem.. thats not a good way to do it, is it?
<6> Leithal: send a kiss to tim, would ya
<2> arjenAU: so it ISN'T "nonsense"
<3> fromvega: that is, CONCAT() definitely exists and has done for years and year
<3> Jivedue: yes it is.
<1> arjenAU: so why it says it does not exist?
<2> arjenAU: the whole point is that I'm then able to select a single ancestor in the `categories2data` table and it's ordered already
<4> orlock: do you mean as a text file?
<5> jbrimble: as in.. they tarred up /var/lib/mysql/* and burnt it to a cd and gave it to me
<3> Jivedue: right. ok. you should be able to tell by the explain
<0> arjenAU, but a "change" is not contained in one row, but in 2 rows.
<2> arjenAU: I just join the data table constant ancester/parent (close_time, id) to the categories2data table (parent, close_time, id)
<7> <3> daniel`js: as I noted earlier... this query needs to do a table scan. all 53724 rows need to be looked at for that flag.
<7> <3> daniel`js: you doing that kind of lookup often?
<7> Sorry for the late reply
<7> but yes
<3> orlock: depends. I hope they shut down their server or did some other locking/flushing to make sure the disk data was consistent.
<3> orlock: it can be ok.
<3> daniel`js: then do read what else I wrote to you, I gave you a complete and fast solution ;-)
<5> hmm, will be interesting then :)
<7> Oh..sorry didn't read further (obviously) :S
<3> orlock: if it's myisam tables, it's generally ok. but you can't just do that with innodb.
<3> daniel`js: you really wanna read that.
<4> meanwhile, i can't access the mysql server on my network - i was told that by default it'd be binded to 127.0.0.1 - how do i change this?
<2> arjenAU: do you get what I'm saying? because then I'm able to make a join from a categories table to the data table on the column I want to order by
<3> jbrimble: did you read the blog entry?
<3> !tell us about debian
<8> arjenAU asked me to tell you this: To access a MySQL server remotely on Debian, see http://www.livejournal.com/users/arjen_lentz/11410.html
<2> arjenAU: only because the categories2data table contains the parent of every single close_time and data.id
<4> ah, i see - that never came up before
<3> yes it did.
<4> thankyou arkjenAU
<4> i must have missed it
<3> ok all - I've got other stuff to do. laters.
<2> bourns.
<4> cya
<2> but thanks, none the less
<0> arjenAU, meh :(
<7> Hrmm it kind of makes sense to me..
<7> Have the id,date sent, and message stored in house_news
<7> than a seperate table house_news_sent with the coumns as id,sent_to,news_id?
<7> (the extra id as a prim key..wouldn't that be faster?)
<3> daniel`js: in that separate table, the id is the pk of course. I already said that.
<3> daniel`js: it's also the only field in the table.
<7> So than where is sent_to placed?
<7> stays in house_news?
<3> for a select like yours, wha tyou'd get is a tablescan on that table which actually only uses that index, then an eq_ref join using the primary key.
<3> what is sent_to... it's just a flag, right? true/false?
<3> since your query checked it for =1 ?
<7> Well no
<7> It's the ID of the account.
<7> (so ONLY he can view it and not others when they're logged in)
<3> euh so a particular house can only be sent to one account??
<3> that makes no sense. but that'll be your choices ;-)
<7> well a particular house news..yes
<7> Well you wouldnt want anyone else reading your mail.
<3> ok in that case, don't normalise, just index that column and any time your dataset changes significantly, do ANALYZE TABLE ...
<7> unless you're that kinky ;)
<3> and run the analyze straight when you create that index
<3> I thought it was just a flag.
<3> because of the 1.
<7> Eh? Re-index the column? And than analyze it..k
<7> What if this table is already created? Can I just do alter table house_news add index(id(10)) ?
<3> daniel`js: re-index? I'm presuming you hadn't got it indexed, since the index wasn't being used by the query.
<3> daniel`js: the (10) is nonsense
<3> but yes you can add indexes
<7> I only have the ID column as prim key and auto_inc
<9> I recommend trying mysql administrator, it will really ease your basic adminning problems, daniel`js
<7> heh maybe I should do that for all of my tables with <id> columns than yah? ;)
<4> arjenAU: I did what you suggested, it's possible to connect now, but it says that i'm not allowed
<7> Nathalas: Bah, I really like the command line though. :( Degrading to mysql admin is just...degrading heh
<9> You can use mysql admin to learn the commands that you dont know :P
<7> I know commands :P
<7> Just don't want to screw 50,000 rows heh
<7> well..actually..I do ;)
<3> jbrimble: and that, my friend, is what we'll talk about next Tuesday.
<7> But others wouldn't like it
<9> Hey I'm just trying to help you out, but I cant make you help yourself :P
<4> hehe, i see. Well you can expect to see me there :)
<3> I just don't have time for it today.
<7> Empty set (0.31 sec) - Still slow..bah maybe it's time to get two servers? ;)
<10> is this syntax correct? select * from table where name like %$tmp1% and id like %tmp2% etc ?
<11> Psycho-MaN: syntax error?
<10> i ask if it is a valid syntax to use like with and
<10> like ... and like ... and like ...
<11> Psycho-MaN: yes but you do have a syntax error elsewhere
<10> what syntax error?
<11> Psycho-MaN: try it and you shuold get 1
<10> oh i forgot a $ before tmp2 :-P
<3> oh you forgot way more, dude.
<10> oh thnx mine were sold out :-)
<12> I have table users and table fields.. fields has a subId column.. users has Id, status column... how can I delete all rows from fields where the (subid=Id) status=0?
<12> i tried this but no work: DELETE FROM fields USINGS users WHERE fields.SubId=users.Id AND users.Status=0
<13> hy all is possible to make a double order like order by name and order by cathegory
<4> Ammo: yes
<13> jbrimble: you know wher I can find reference to create the query?
<11> Kaitlyn: USING users,fields WHERE
<11> !m Ammo select
<8> Ammo: (SELECT Syntax) : http://dev.mysql.com/doc/mysql/en/SELECT.html
<13> Thank you..
<4> can someone give me some basic port forwarding details so i can set my database up for net access?
<9> www.portforwarding.com
<4> cheers
<9> probably even has info for your router in particular :)
<4> the site appears to be down atm
<9> ah
<9> www.portforward.com
<14> what query should I use for the following; I have a table with three columns, A, B, C; I want to group by A, B; and show the average for the value of C for each a\in A, and b\in B
<15> can anyone think of the simplest possible table scheme/data scheme that will produce horrlbly slow queries? I'm trying to write some query-caching stuff but don't want to bog down my laptop by putting the gigabytes of data that our production servers have on it.
<12> DELETE FROM fields USING fields AS f, users AS u WHERE f.SubId=u.Id AND u.Id=25;
<12> that gives me a syntax error
<12> error near 'using...'
<7> is "fields" a valid field name?
<16> anyone know how to make trying to drop a database that doesn't exist not be an error? kind of like rm -f
<12> daniel`js: ya, its all "right"
<12> using mysql 3.23
<7> XaXXon: You using PHP?
<16> no, just mysql command line
<7> Not sure than, sorry :(
<16> nod
<16> I'm trying to make a script to init mysql to run my app.. first thing is to delete the database if it exists and then re-create it.
<17> XaXXon: you can with mysql -e
<16> thanks
Return to
#mysql
or
Go to some related
logs:
bridge-util debian
nforce2 powernow clock drift
#css
#gentoo
#debian
restart the auth service
naughty neibors
#php
perldoc string comparision
#php
|
|