Re: [OT] Suggestion of query manager
http://dev.mysql.com/downloads/gui-tools/5.0.html On Mon, Oct 12, 2009 at 7:23 PM, Marcelo de Assis saloma...@gmail.com wrote: Hi people! Can anyone suggest a query manager on linux environment - like Heidisql? I using MySQL Navigator: http://www.bookofjesus.org/images/fl8ze90wpgyt87bkp5.png Thanks! -- Marcelo de Assis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [OT] Suggestion of query manager
The GUI tools are horrible, and I probably wouldn't recommend them to my worst enemy :) Take a look at workbench. It is getting better with every release, especially now that they added SSH tunneling into it. It is still beta-status though, but it might work for you: http://dev.mysql.com/downloads/workbench/5.1.html Walter On Tue, Oct 13, 2009 at 12:59, Michael Dykman mdyk...@gmail.com wrote: http://dev.mysql.com/downloads/gui-tools/5.0.html On Mon, Oct 12, 2009 at 7:23 PM, Marcelo de Assis saloma...@gmail.com wrote: Hi people! Can anyone suggest a query manager on linux environment - like Heidisql? I using MySQL Navigator: http://www.bookofjesus.org/images/fl8ze90wpgyt87bkp5.png Thanks! -- Marcelo de Assis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
OR vs UNION
Hi List, i have a problem with an OR STATEMENT. Maybe someone can explain to me why the mysql optimizer doesn't work like expected. Please have a look at following similar queries. mysql explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id = 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) or (kunde_id= 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1); ++-+---+-++- ---+-+--+--+ --+ | id | select_type | table | type| possible_keys | key| key_len | ref | rows | Extra | ++-+---+-++- ---+-+--+--+ --+ | 1 | SIMPLE | KTEMP | index_merge | buchungs_kunde_id,kunde_id | buchungs_kunde_id,kunde_id | 71,71 | NULL |2 | Using union(buchungs_kunde_id,kunde_id); Using where | ++-+---+-++- ---+-+--+--+ --+ 1 row in set (0.00 sec) All seems fine here . Optimizer choose to use an union! This is the same as following union query. mysql explain extended (SELECT * FROM KTEMP WHERE buchungs_kunde_id = 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) UNION (SELECT * FROM KTEMP WHERE kunde_id = 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1); ++--++--+---+--- +-+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--++--+---+--- +-+---+--+-+ | 1 | PRIMARY | KTEMP | ref | buchungs_kunde_id | buchungs_kunde_id | 71 | const,const,const |1 | Using where | | 2 | UNION| KTEMP | ref | kunde_id | kunde_id | 71 | const,const,const |1 | Using where | |NULL | UNION RESULT | union1,2 | ALL | NULL | NULL | NULL| NULL | NULL | | But the following query is handled in a strange way mysql explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id = 'dfb49c8c0b441e9f' or kunde_id= 'dfb49c8c0b441e9f') and veranst_id = 16058 and status = 1; ++-+---+--++--+- +--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--++--+- +--+-+-+ | 1 | SIMPLE | KTEMP | ALL | buchungs_kunde_id,kunde_id | NULL | NULL| NULL | 1040700 | Using where | ++-+---+--++--+- +--+-+-+ I don't get it! Maybe someone has a clue or a hint for me. TABLEDEF. | KTEMP | CREATE TABLE `KTEMP` ( `tid` bigint(20) NOT NULL auto_increment, `kunde_id` varchar(20) collate utf8_bin NOT NULL, `buchung_id` varchar(20) collate utf8_bin NOT NULL default '0', `buchungs_kunde_id` varchar(20) collate utf8_bin NOT NULL, `veranst_id` bigint(20) NOT NULL, `rolle_nummer` int(11) default '0', `status` tinyint(1) unsigned NOT NULL, `tstamp_insert` bigint(20) NOT NULL, `tstamp_update` bigint(20) NOT NULL, `KategorienWechsel` tinyint(4) NOT NULL default '0', PRIMARY KEY (`tid`), KEY `buchungs_kunde_id` (`buchungs_kunde_id`,`veranst_id`,`status`), KEY `kunde_id` (`kunde_id`,`veranst_id`,`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin | mysql SELECT VERSION(); +-+ | VERSION() | +-+ | 5.0.27-standard-log | +-+ 1 row in set (0.00 sec) Regards, Majk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: OR vs UNION
Majk, all, I'm no optimizer expert, but your result doesn't really surprise me. I'll reorder your post because that makes reasoning simpler: majk.sko...@eventim.de wrote: Hi List, First, your table: TABLEDEF. | KTEMP | CREATE TABLE `KTEMP` ( `tid` bigint(20) NOT NULL auto_increment, `kunde_id` varchar(20) collate utf8_bin NOT NULL, `buchung_id` varchar(20) collate utf8_bin NOT NULL default '0', `buchungs_kunde_id` varchar(20) collate utf8_bin NOT NULL, `veranst_id` bigint(20) NOT NULL, `rolle_nummer` int(11) default '0', `status` tinyint(1) unsigned NOT NULL, `tstamp_insert` bigint(20) NOT NULL, `tstamp_update` bigint(20) NOT NULL, `KategorienWechsel` tinyint(4) NOT NULL default '0', PRIMARY KEY (`tid`), KEY `buchungs_kunde_id` (`buchungs_kunde_id`,`veranst_id`,`status`), KEY `kunde_id` (`kunde_id`,`veranst_id`,`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin | So you have two indexes which consist of three fields each, and the least significant two fields are the same for both indexes. You do a SELECT that fully specifies values for these two indexes, combining them with OR: mysql explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id = 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) or (kunde_id= 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1); ++-+---+-++- ---+-+--+--+ --+ | id | select_type | table | type| possible_keys | key| key_len | ref | rows | Extra | ++-+---+-++- ---+-+--+--+ --+ | 1 | SIMPLE | KTEMP | index_merge | buchungs_kunde_id,kunde_id | buchungs_kunde_id,kunde_id | 71,71 | NULL |2 | Using union(buchungs_kunde_id,kunde_id); Using where | ++-+---+-++- ---+-+--+--+ --+ 1 row in set (0.00 sec) All seems fine here . Optimizer choose to use an union! This is the same as following union query. As an alternative, you replace the OR by a UNION. No real change: mysql explain extended (SELECT * FROM KTEMP WHERE buchungs_kunde_id = 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) UNION (SELECT * FROM KTEMP WHERE kunde_id = 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1); ++--++--+---+--- +-+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--++--+---+--- +-+---+--+-+ | 1 | PRIMARY | KTEMP | ref | buchungs_kunde_id | buchungs_kunde_id | 71 | const,const,const |1 | Using where | | 2 | UNION| KTEMP | ref | kunde_id | kunde_id | 71 | const,const,const |1 | Using where | |NULL | UNION RESULT | union1,2 | ALL | NULL | NULL | NULL| NULL | NULL | | Note that both queries fully specify the index values. Then, you apply Boolean logic to factor out the two identical predicates from both the OR alternatives: But the following query is handled in a strange way mysql explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id = 'dfb49c8c0b441e9f' or kunde_id= 'dfb49c8c0b441e9f') and veranst_id = 16058 and status = 1; ++-+---+--++--+- +--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--++--+- +--+-+-+ | 1 | SIMPLE | KTEMP | ALL | buchungs_kunde_id,kunde_id | NULL | NULL| NULL | 1040700 | Using where | ++-+---+--++--+- +--+-+-+ I don't get it! Maybe someone has a clue or a hint for me. While that is equivalent from a logic point of view, it is different for the optimizer: You do not specify the values for any of the indexes completely, because the first term contains an OR over two different fields, and the other terms don't specify the most significant fields of the index. The only way for the system to use an index would be to revert your change and to go back to the first statement, where the OR is on the outermost level. mysql SELECT VERSION(); +-+ | VERSION() |
AW: OR vs UNION
-Ursprüngliche Nachricht- Von: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com] Gesendet: Dienstag, 13. Oktober 2009 15:26 An: Skoric, Majk Cc: mysql@lists.mysql.com Betreff: Re: OR vs UNION Majk, all, I'm no optimizer expert, but your result doesn't really surprise me. I'll reorder your post because that makes reasoning simpler: majk.sko...@eventim.de wrote: Hi List, First, your table: TABLEDEF. | KTEMP | CREATE TABLE `KTEMP` ( `tid` bigint(20) NOT NULL auto_increment, `kunde_id` varchar(20) collate utf8_bin NOT NULL, `buchung_id` varchar(20) collate utf8_bin NOT NULL default '0', `buchungs_kunde_id` varchar(20) collate utf8_bin NOT NULL, `veranst_id` bigint(20) NOT NULL, `rolle_nummer` int(11) default '0', `status` tinyint(1) unsigned NOT NULL, `tstamp_insert` bigint(20) NOT NULL, `tstamp_update` bigint(20) NOT NULL, `KategorienWechsel` tinyint(4) NOT NULL default '0', PRIMARY KEY (`tid`), KEY `buchungs_kunde_id` (`buchungs_kunde_id`,`veranst_id`,`status`), KEY `kunde_id` (`kunde_id`,`veranst_id`,`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin | So you have two indexes which consist of three fields each, and the least significant two fields are the same for both indexes. You do a SELECT that fully specifies values for these two indexes, combining them with OR: mysql explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id = 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) or (kunde_id= 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1); ++-+---+-+--- -+- ---+-+--+--+- --- --+ | id | select_type | table | type| possible_keys | key| key_len | ref | rows | Extra | ++-+---+-+--- -+- ---+-+--+--+- --- --+ | 1 | SIMPLE | KTEMP | index_merge | buchungs_kunde_id,kunde_id | buchungs_kunde_id,kunde_id | 71,71 | NULL |2 | Using union(buchungs_kunde_id,kunde_id); Using where | ++-+---+-+--- -+- ---+-+--+--+- --- --+ 1 row in set (0.00 sec) All seems fine here . Optimizer choose to use an union! This is the same as following union query. As an alternative, you replace the OR by a UNION. No real change: mysql explain extended (SELECT * FROM KTEMP WHERE buchungs_kunde_id = 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) UNION (SELECT * FROM KTEMP WHERE kunde_id = 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1); ++--++--+---+ --- +-+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--++--+---+ --- +-+---+--+-+ | 1 | PRIMARY | KTEMP | ref | buchungs_kunde_id | buchungs_kunde_id | 71 | const,const,const |1 | Using where | | 2 | UNION| KTEMP | ref | kunde_id | kunde_id | 71 | const,const,const |1 | Using where | |NULL | UNION RESULT | union1,2 | ALL | NULL | NULL | NULL| NULL | NULL | | Note that both queries fully specify the index values. Then, you apply Boolean logic to factor out the two identical predicates from both the OR alternatives: But the following query is handled in a strange way mysql explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id = 'dfb49c8c0b441e9f' or kunde_id= 'dfb49c8c0b441e9f') and veranst_id = 16058 and status = 1; ++-+---+--++- -+- +--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--++- -+- +--+-+-+ | 1 | SIMPLE | KTEMP | ALL | buchungs_kunde_id,kunde_id | NULL | NULL| NULL | 1040700 | Using where | ++-+---+--++- -+- +--+-+-+ I don't get it! Maybe someone has a clue or a hint for me. While that is equivalent from a logic point of view, it is different for the optimizer: You do not specify the values for any of the indexes completely, because the first term contains an OR over two
escape quotes
Hi, I'm trying to replace this: h2 style=margin: 0px 0px 5px; padding: 0px; font-family: 'Trebuchet MS',Arial,Helvetica,sans-serif; font-weight: bold; line-height: 30px; font-size: 30px; color: #b4b4be; but UPDATE `jos_content` SET `introtext` = replace(`introtext`,h2 style=\margin: 0px 0px 5px; padding: 0px; font-family: 'Trebuchet MS',Arial,Helvetica,sans-serif; font-weight: bold; line-height: 30px; font-size: 30px; color: #b4b4be;\,h2) just gives me 0 results... How do I correctly replace and unescape the quote? Thanks! -- Lex Thoonen Pêng Smart Web Design - http://www.peng.nl Gran Canaria Info - http://www.gran-canaria-info.com Hollandse Nieuwe - http://www.hollandsenieuwe.com tel. +34 928 88.61.77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
