#mysql
01 November 2007
Total 22 pages. You are browsing page 1/22.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Thu Nov 01 00:00:05 2007
00:00 <****> nop
00:00 <****> localy
00:00 <****> or both
00:01 <****> if I knew how to do this in PHP i would. i suppose I could do a preg_replace on all of the results?
00:03 <****> E-bola??
00:05 <****> timelf123: well if you just want to drop anything but ASCII chars... you could probably do something with the hex value of characters
00:05 <****> hey... I need some help. I'm trying to connect to a database on 1 server from another ... I did: grant all on *.* to '*'@'ipaddr';
00:05 <****> timelf123: all standard ascii characters are in a contiguous range. Just trim any characters outside of this range
00:05 <****> is there any command to get the databases from a server??
00:05 <****> show databases;
00:06 <****> fitoria: ^^
00:06 <****> ok!
00:06 <****> thanks
00:06 <****> thanks ToeBee
00:06 <****> The server running mysql just has a root user and a non privileged user for management....the server users are connecting from are logging in with system accounts created for them
00:06 <****> sensei / flupps: i didn't use any old files, it's just normal confixx-usage that's crashing the table
00:06 <****> timelf123: not sure if it can be accomplished in SQL or not... might have to take a good look at that string functions page I linked to. Some implemenations of regex support matching on character hex values
00:08 <****> there has to be an easier way. maybe i can just run a PHP function to clean up all the text and add a new field to mysql so it doesn't have to be run dynamically
00:08 <****> I'm trying to connect with mysql -h ipaddr with no love
00:09 <****> !man remote
00:09 <****> (Establishing a Remote Connection to System A from System B) : http://dev.mysql.com/doc/mysql/en/remote-connection.html
00:09 <****> thumbs thanks I'll revie wnow
00:10 <****>c ?
00:10 <****> i.e, I'm looking for a join - if at all possible, that will single out a values with a missing b value - b values being select distinct(b) from the same table.
00:10 <****> timelf123: hex values aren't THAT hard :)
00:10 <****> thumbs, if I want all user to have access without a password is that possible?
00:10 <****> heh ok
00:11 <****> http://www.asciitable.com/
00:11 <****> 41-5A is capitals, 61-7A is lower case
00:12 <****> might want to keep numbers too I guess. 30-39
00:12 <****> pnof, do you have tables with the possible a and b values ?
00:13 <****> pnof, specially the b values
00:13 <****> bbiab, thanks ToeBee
00:15 <****> If I have an index which involves two column in a table, and I rename one of the columns, do I need to remove the old index, and re-add it with the new column name?
00:16 <****> smtlaissezfaire, should be trivially easy to test
00:16 <****> Okay - will do
00:17 <****> gnari: yes. that same table ;)
00:17 <****> smtlaissezfaire, but i guess the answer is no
00:17 <****> gnari: (and, another one, but that would yield _WAY_ too many results)
00:17 <****> gnari: it's a language table, with system constant and language id and a corresponding value.
00:18 <****> gnari: seeing as our "supported langauges" table includes the entire iso list, and then some, and we currenly have only translated 5 languages, that'd suck ;)
00:19 <****> pnof, ok.
00:20 <****> I must admit - I have no idea how to view which indexes are present.
00:20 <****> gnari: which is why I was thinking left outer join, but it yields no nulls because it's on the same table.
00:20 <****> smtlaissezfaire, show create table foo;
00:20 <****> smtlaissezfaire: show create table
00:20 <****> (or, I'm doing it -very- wrong)
00:20 <****> ;)
00:20 <****> ah, gotcha.
00:21 <****> pnof, (distinct a) cross join (distinct b) left join (a,b) where outer side is null
00:21 <****> gnari: I've been staring myself blind the last couple of hours, a bit more verbose example would be -very- appreciated.
00:22 <****> pnof, ok. what is the name of your table ?
00:22 <****> gnari: if you can fit it to the current foo table (a,b,c) example I gave you, that'd work nicely :)
00:22 <****> if my datetime column is 0000-00-00 00:00:00 will that return null?
00:23 <****> a record in that column
00:23 <****> I'm pretty sure that will return 0000-00-00 00:00:00
00:23 <****> You should try it though
00:24 <****> hey guys, having some scaling issues
00:24 <****> i think zero is supported now
00:24 <****> select a1.a,b1.b from (select distinct a from foo) as a1 CROSS JOIN (select distinct b from foo) as b1 LEFT JOIN foo ON foo.a=a1.a and foo.b=b1.b WHERE foo.b IS NULL
00:24 <****> look at the date stuff inthe manual
00:24 <****> if you had this query, what would you do to optimize it:
00:24 <****> http://dpaste.com/23853/
00:24 <****> gnari: ah!
00:24 <****> (explain included)
00:24 <****> the key its referencing is on related_table_id + object_id
00:24 <****> err related_object not object_id
00:24 <****> gnari: oow, trying to fit it :) - thanks
00:25 <****> gnari: lovely. works wonders.
00:25 <****> now to actually put it to the test on the real data.
00:26 <****> gnari: I didn't realize you could subselect cross joins.
00:26 <****> pnof, you mean cross join subselects ?
00:28 <****> gnari: yep. works wonders - _muchly_ appreciated.
00:29 <****> zeeg, index on post_date
00:31 <****> gnari, a seperate index on it?
00:31 <****> its myisam btw
00:32 <****> zeeg, any views involved?
00:32 <****> ya the "relationships" table is a view
00:32 <****> but the order by post_date is slow in general
00:32 <****> 10ms
00:32 <****> +/- 5
00:33 <****> that query i pasted is taking 40ms
00:33 <****> changing it to a subqyer, which the explain shows it should be slower, was still 20-25ms
00:33 <****> zeeg, there is no way anyone can give you intelligent advice withoout seeing the view definitions
00:33 <****> alright one sec
00:34 <****> http://dpaste.com/23854/
00:34 <****> pretty basic
00:36 <****> zeeg, i still think an index on post_date should help
00:36 <****>= and <= right?
00:36 <****> gnari, my local tests are showing it might be
00:36 <****> letm e do some more
00:37 <****> zeeg, looks like you do not have really many rows in those tables
00:40 <****> zeeg, an index could possibly help on images_image.related_table_id, but i am not sure if the optimizer can use it in the face of that UNION
00:40 <****> images_image has 50k
00:40 <****> oh sorry, the images_image queries took far longer
00:40 <****> im testing it on a different table now, which has 5k rows
00:40 <****> same query syntax, same indexes
00:41 <****> is there a way to flush the query cache (so i know how long the query really is taking each time)
00:41 <****> why don't you just put SQL_NO_CACHE in your query
Total 22 pages. You are browsing page 1/22.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last