| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14
Comments:
<0> haris, enable slow query logging and EXPLAIN <query>
<1> most people just start with the slow query log
<1> a db is not a a web server
<0> supersmack and another one ... Peter Zaitsev recommends .. check planetmysql.org
<1> a db is not a a web server, so talking about response time is kind of retarded
<1> either your queries are optimized, or they aren't
<1> you need to run explain on each and every query
<2> I see.
<1> one bad sql query will destroy any performance tuning you did
<3> re
<1> databases generally aren't cpu bound
<1> they're optimizer bound or io bound
<1> so comparing 2 cpu's is not that helpful on a system with bad queries
<0> Haris, he's right about that
<4> depends on which queries there is
<4> are
<5> actually I personally think that optimizing queries is *all* about monitoring response times and wait times
<5> we just have not instrumented a proper way to do that yet
<6> can i group things if 2 fields are equal?
<5> but if we are talking about "databases" then yes, it is still about response times
<6> but both must be exactly the same!
<5> you can have a query running against an index that is slower than doing a full table scan
<5> just looking at an EXPLAIN may not tell you that.
<5> peter's recommended benchmarking tool is sysbench, btw
<7> hello, im trying to get a couple of my friends to use linux on their desktops, they use mysq front on their windows machines, so i suggested they should use mysql query browser in linux, but apparently they find mysql front better, especially when they want to add records to a database by hand, any suggestions for a good substiture for mysqlfront, or is this the wrong place to ask?
<8> Hi, have heard that there is a config option that enables the logging of all queries that doesn't use an index but can't find the option in the mysql handbook - does anybody know how to enable this feature?
<0> perl_cont, you think i was brown nosing ? lol ... there's got to be a position of power for that to exist.. I was being nice. I've corrected my interactions with you tho... :)
<0> Leithal, ya that's the one i was thinking about .. :)
<1> lakez: :)
<1> qwertz: I think you mean the slow query log
<5> with the fantastically named --log-queries-not-using-indexes variable
<1> oo
<0> isn't that only in 5.1 ?
<5> no
<9> hi all
<5> 5.1 allows you to turn it on and off dynaically
<5> dynamically*
<1> linuxboyfriend: nice nick.
<9> how can i backup my database to a flash drive so i can transfer it to my other system?
<10> mysqldump?
<1> shut mysql down and copy it?
<9> wobbles, can you explain a bit
<9> perl_cont, where can i locate the database?
<11> when i do fulltext search, how can i select places where the matches have been found, or somehting like that? I want to get a quite short column value of content_content
<10> linuxboyfriend: I could repeat what is at http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html ...
<8> perl_cont, no I asked the question in a mysql online presentation cause the slow query log isn't enough for my purposes but thanks for mentioning
<9> wobbles, i tried to follow it but its giving someking of p***word error!
<9> wobbles, although i am also root
<9> wobbles, can you tell me the command to transfer the database to my flash drive
<3> re -.-
<6> err what is to join fields again something with 'concat()'
<10> linuxboyfriend: system user root <> mysql user root
<0> !man mysqldump
<12> (The mysqldump Database Backup Program) : http://dev.mysql.com/doc/mysql/en/mysqldump.html
<9> wobbles, sorrry?
<6> err what is to join fields again something with 'concat()'?
<10> linuxboyfriend: No, I can't tell you the command, other than mysqldump
<5> Drakas: use SUBSTRING() AND LOCATE()
<11> thanks
<9> wobbles, ok
<5> SUBSTRING(col, LOCATE(col, 'word'), 50) or something like that off the top of my head
<10> linuxboyfriend: Beecause I have no idea what values you need to give mysqldump
<5> HS^: yes concat
<5> !m HS^ concat()
<12> HS^: (String Functions) : http://dev.mysql.com/doc/mysql/en/String_functions.html
<6> Leithal what if i want to concat all fields in a GROUP, that differ
<5> GROUP_CONCAT?
<9> wobbles, hmm, ok lemme try .....
<6> !m HS^ group_concat()
<12> HS^: (GROUP BY (Aggregate) Functions) : http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html
<11> LOCATE(content_content, 'Hydrocarbons') always returns zero although there are such words. is Locate() case sensitive?:/
<11> oh case sensitive. anything like that that is NOT case sensitive?
<10> Force them both to upper or lower case for the comparison?
<11> ok
<6> Leithal yes but with group_concat it does what i want....except it also 'merges' the 2 fields if they are identical
<6> or row
<6> whatever the name
<5> only if you use DISTINCT, if I understand you correctly
<6> i do this query
<6> SELECT rubriek,naam, straat, GROUP_CONCAT(levert) as levert, GROUP_CONCAT(rubriek) as rubriek FROM bedrijvenBU.foo group by naam,straat
<6> i ge tthis as result for example as 'rubriek' -> Zeilbotenverhuur, Zeilbotenverhuur
<6> it merges the same
<6> i want only GROUP_CONCAT if DISTINCT
<5> if you only want a distinct result set, use DISTINCT, if you only want rows where there are no more than one in the column, use HAVING COUNT(levert)
<5> =1
<6> i want result -> Zeilbotenverhuur as row 'levert' but if different names are grouped, i want for example ->Zeilbotenverhuur, somethingelse
<6> but never 2 times the same
<6> maybe distinct
<6> yes looks like that works;)
<6> :)
<6> now a last question.. if it merges something it always put a , on the end....
<6> but if it merges only one field i get a result like this 'city,'
<6> it puts a ',' even when theres no second
<6> or i get result like 'city,,city2'
<6> so i also want a field to not be empty
<6> that possible?
<6> ROUP_CONCAT(DISTINCT levert) <-- but only if a 'grouped' field is not null?
<6> if field a = 'test' and field b = '' .. it will still group it because they are distinct
<0> damn i love how fast innodb is
<13> anything i need to watch out for when convertin from myisam to innodb? shoulnd't be or?
<14> is it possible to reset the value of "auto_increment" for example so it will start at 1 insted of 6 if it is truncated?
<0> jax... honestly I prefer to .. select * from myisamtable order by primarykey into outfile '/var/tmp/table.data'; ...
<0> set foreign_key_checks=0
<0> set autocommit=0
<0> set unique_checks=0
<0> and then create the table, and commit after it's done
<0> load data infile '...' ..
<13> then turn on auto commit again?
<0> might be overkill but that's what i do for huge chunks of data
<13> when would you want to turn autocommit off?
<0> Jax, yeah ... the autocommit=1 in innodb generates a flush per row i believe.. the begin/commit causes 1 flush after
<0> when you're dealing with innodb
<0> !man innodb tips
<12> (InnoDB Performance Tuning Tips) : http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html
<0> When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET AUTOCOMMIT and COMMIT statements:
<0> Jax, these tips are for huge tables... if you're dealing with small tables just alter table .. engine=innodb
<0> myisam kicks *** until a certain point and then innodb, although the data files are bigger.. kills in performance.. I went from myisam to innodb on one of my tables... no deadlocks, and increased the speeds of everything 1000%
<0> also jax, innodb is missing a few features that myisam has.. one being the fulltext
<0> !man fulltext
<12> (Full-Text Search Functions) : http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
<13> yeah i don't use it
<6> i have a problem with one of those formats..... i tried o backup the data folder and it wont read it
<6> because of innodb or..myisam.. i forgot
<6> is it possible to convert a data folder?
<0> myisam you can, innodb you can't
<0> what do you mean convert a folder?
<6> yes
<6> i have a folder that contains a innodb
<6> (so i cant open it)
<6> ?
<6> anyone knows?
<0> HS, what ?
<6> if you can convert a folder to myisam
<0> those folders are just the database names
<6> i cant open it..
<0> you can't conver those.. mysql works on 'tables'
<6> because theres a innodb in it
<0> where you can ***ign engines to a table
<6> ye
<6> s
<6> so i can i change the tables in the files?
<0> so like... /var/lib/mysql/hs/yahoo.ibd
<0> that's an Innodb table in your "hs" database... select * from hs.yahoo limit 10;
<0> but you can't convert "hs" to innodb
<0> HS, you just type : alter table yahoo engine=myisam;
<0> it'll take the innodb, recreate the table as myisam and get rid of the old innodb files...
<0> !man alter table
<12> (ALTER TABLE Syntax) : http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html
<6> ok thanks, and the other question, aboiut GROUP_CONCAT(DISTINCT levert) <-- this will work, but of course some fields are empty
Return to
#mysql
or
Go to some related
logs:
gentoo cdrecord: No such file or directory. Cannot open '/dev/pg*'. Cannot open
gnomebaker mp3 ubuntu
install soya
libdvdcs of ubuntu
checkbox inline
#linuxhelp
#css
ubuntu hda buffer IO error
#bash
entrance_edit
|
|