RE: index collate
Got it; Thanks! Zhigang -Original Message- From: Hartmut Holzgraefe [mailto:hart...@skysql.com] Sent: Thursday, September 11, 2014 1:44 PM To: mysql@lists.mysql.com Subject: Re: index collate On 09/11/2014 05:55 AM, Zhigang Zhang wrote: The “CREATE INDEX Syntax” can not assign a collation. What collation to use to create a index on a column? The collation used for an index on a textual column is the columns collation itself. You can't choose a different collation for the index, this would come down to having a functional index or index on expression as you'd need to apply CAST() or CONVERT() on the column first to change the collation. MySQL doesn't have support for expression based indexes though ... -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) SkySQL - The MariaDB Company | http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
index collate
I have a question. The “CREATE INDEX Syntax” can not assign a collation. What collation to use to create a index on a column? Thanks Zhigang.
Exec_Master_Log_Pos is smaller than it's correct value
Hi I have a simple replication service, “show slave status” shows that the Exec_Master_Log_Pos is smaller than it’s correct value occasionally, why? Mysql version: 5.1.53 Regards, Zhigang
Exec_Master_Log_Pos is smaller than it's correct value
Same as this bug report: http://bugs.mysql.com/bug.php?id=25713 Please tell me why? Regards Zhigang _ From: Zhigang Zhang [mailto:zzgang2...@gmail.com] Sent: Tuesday, March 18, 2014 4:04 PM To: 'mysql@lists.mysql.com' Subject: Exec_Master_Log_Pos is smaller than it's correct value Hi I have a simple replication service, “show slave status” shows that the Exec_Master_Log_Pos is smaller than it’s correct value occasionally, why? Mysql version: 5.1.53 Regards, Zhigang
How to make multiple master to single slave in mysql?
How to make multiple master replicate to single slave in mysql? Tell me some approaches? Thanks Zhigang
RE: How to make multiple master to single slave in mysql?
OK! Thank you very much! Zhigang. -Original Message- From: shawn l.green [mailto:shawn.l.gr...@oracle.com] Sent: Wednesday, February 26, 2014 11:57 AM To: mysql@lists.mysql.com Subject: Re: How to make multiple master to single slave in mysql? On 2/25/2014 9:55 PM, Zhigang Zhang wrote: How to make multiple master replicate to single slave in mysql? Tell me some approaches? Put your masters in a replication ring, hang a slave from one of them (as suggested by Mr. Van der Westhuizen) Use an external product, such as GoldenGate, that can collect data from multiple sources and perform the steps necessary to keep the MySQL instance you are calling the slave updated. (as mentioned by xiangdong...@gmail.com) Write your own daemon or script to poll each master, in turn, by adjusting the slave's replication configuration in some kind of round-robin technique (several of these scripts are on the web). Using the native replication process, no MySQL versions (5.7 or earlier) support one slave replicating data from multiple masters. It is possible to have multiple slaves replicating from a single master but you cannot have one slave repicating from multiple masters. Why are you not reading the responses you are getting? Regards, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Multi-master to one slave
Does someone have the best schema from multi-master to one slave excluding the circular replication. Please tell me. Thanks. Zhigang
Multi-master to one slave
Does someone have some schemas from multi-master to one slave excluding the circular replication. Please tell me. Thanks. Zhigang
Multi-master to one slave
Does someone have some schemas from multi-master to one slave excluding the circular replication. Please tell me. Thanks. Zhigang
Multi-master to one slave schema
Does someone have some schemas from multi-master to one slave excluding the circular replication. Please tell me. Thanks. Zhigang
RE: LIKE sql optimization
Done. Thand you very much! Zhigang _ From: Jesper Wisborg Krogh [mailto:my...@wisborg.dk] Sent: Wednesday, February 12, 2014 5:30 PM To: Morgan Tocker; Zhigang Zhang Cc: mysql@lists.mysql.com Subject: Re: LIKE sql optimization On 12/02/2014 13:16, Morgan Tocker wrote: Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang mailto:zzgang2...@gmail.com zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. I think I understand the question - you are asking why MySQL will not index scan, find matching records, and then look them up rather than table scan? I believe the answer is that there is no way of knowing if 1 row matches, or all rows match. In the worst case (where all rows match), it is much more expensive to traverse between index and data rows for-each-record. So a table scan is a “safe choice / has less variance. In addition to what Morgan writes, then with an index scan you will end up doing a lot of random I/O: even if the index scan itself is one sequential scan (which is not guaranteed) then for each match, it will be necessary to look up the actual row. On the other hand a table scan will generally be more of a sequential read as you already have all the data available for each match. Random I/O is more expensive than sequential I/O - particularly on spinning disks - so in general the optimizer will try to reduce the amount of random I/O. In some cases though, you may see the index scan be preferred. Assume you have a query like: SELECT val FROM table WHERE condition LIKE '%abcd'; and you have an index (condition, val) or (val, condition) then the whole query can be satisfied from the index (it's called a covering index). In that case the index scan is usually preferred over the table scan. For the purpose of using an index to do index lookups to find the matching rows rather than doing either a table or index scan for WHERE clauses like LIKE '%abcd' you can do a couple of things: * Duplicate the column used in the WHERE clause, but reverse the string. That way the above WHERE clause becomes: WHERE condition_revers LIKE 'dcba%' This can use an index as it is a left prefix. * If you always look for around the same number of characters at the end in your WHERE clause, you can create a column with just those last characters, e.g. so the WHERE clause becomes: WHERE condition_suffix = 'abcd' Do however be careful that you ensure you have enough selectivity that way. If for example 90% of the rows ends in 'abcd' an index will not do you much good (unless you are looking for the last 10% of the rows). Best regards, Jesper Krogh MySQL Support
LIKE sql optimization
For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang
RE: LIKE sql optimization
I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. zhigang _ From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang
RE: LIKE sql optimization
I checked a myisam table index, the index is a copy of the whole field. Zhigang -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Wednesday, February 12, 2014 10:02 AM To: mysql@lists.mysql.com Subject: Re: LIKE sql optimization because a index is not just a dumb copy of the whole field and you simply can't seek in the middle of it? http://en.wikipedia.org/wiki/B-tree http://mattfleming.com/node/192 Am 12.02.2014 02:48, schrieb Zhigang Zhang: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan.
RE: LIKE sql optimization
Thank you very much! Zhigang -Original Message- From: Morgan Tocker [mailto:morgan.toc...@oracle.com] Sent: Wednesday, February 12, 2014 10:16 AM To: Zhigang Zhang Cc: mysql@lists.mysql.com Subject: Re: LIKE sql optimization Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. I think I understand the question - you are asking why MySQL will not index scan, find matching records, and then look them up rather than table scan? I believe the answer is that there is no way of knowing if 1 row matches, or all rows match. In the worst case (where all rows match), it is much more expensive to traverse between index and data rows for-each-record. So a table scan is a “safe choice / has less variance.=
RE: Query Plan Analyzer
By experience! -Original Message- From: Cabbar Duzayak [mailto:cab...@gmail.com] Sent: Thursday, November 22, 2012 3:13 PM To: mysql@lists.mysql.com Subject: Query Plan Analyzer Hi All, Is there a way of looking at how mysql builds the query plan and executes it for a given query? EXPLAIN is definitely a useful tool, but it is not exact (shows approximations as far as I can see), furthermore I want something like how the predicates were applied (before or after JOINS), which indexes were used, etc. Basically, Oracle's EXPLAIN PLAN and SQL TRACE do this, and it is very very useful for optimizing queries. Don't get me wrong, I love mysql, but with mysql, you optimize queries more by trial and error, instead of understanding what exactly is going on. Thanks a ton. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: About character_set_*
I know this, But I think the variable character_set_connection is unnecessary. Because the server know the table's encoding, So it can convert the encoding from character_set_client to the table's encoding. -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Wednesday, August 08, 2012 2:55 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: RE: About character_set_* The answer is long and involved... http://mysql.rjweb.org/doc.php/charcoll -Original Message- From: Zhigang Zhang [mailto:zzgang2...@gmail.com] Sent: Tuesday, August 07, 2012 3:25 AM To: mysql@lists.mysql.com Subject: About character_set_* Hello. As I know, when client send data to server, the encoding converting process is: character_set_client -- character_set_connection -- db table encoding. I think it is a short path than above way: character_set_client -- db table encoding. Why it is converted to character_set_connection? Yours Zhigang. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
About character_set_*
Hello. As I know, when client send data to server, the encoding converting process is: character_set_client -- character_set_connection -- db table encoding. I think it is a short path than above way: character_set_client -- db table encoding. Why it is converted to character_set_connection? Yours Zhigang.
RE: How often the slave to pull data from master?
Hi If there are additional events beyond that, it retrieves those in sequence after it writes the current statement into the relay logs. I have a question about this: Whether the slave is sent a signal whenever the master generates each event? -Original Message- From: Shawn Green [mailto:shawn.l.gr...@oracle.com] Sent: Friday, July 27, 2012 2:51 AM To: mysql@lists.mysql.com Subject: Re: How often the slave to pull data from master? Hello Rick, You nearly got it all correct. On 7/26/2012 1:21 PM, Rick James wrote: My understanding is... * There is a permanent connection (TCP/IP socket) between the Master and each Slave. This is opened by the Slave when the slave starts (or restarts or recovers from a network glitch). * Master writes to binlog _and_ to that connection whenever there is a 'write' operation. (I do not know whether the binlog is written/flushed before or after or simultaneous with writing to the Slave(s).) The slave is sent a signal that more data is available. The slave then requests that data from the master's binary log at the 'next' position it expects it to be. If there are additional events beyond that, it retrieves those in sequence after it writes the current statement into the relay logs. * If the connection is interrupted, the Slave will reconnect and ask the Master for old info. This comes from the binlog. Otherwise, the Master's binlog file is not part of the flow of data from Master to Slave. Once the slave can reconnect, it asks for the 'next' statement in sequence (based on the end of the last statement it has received). Any statements beyond that already in the binary logs retrieved in sequence. If there is no 'next' statement, it waits for a signal that more data is available. If the binary log was shortened (which can occasionally happen during an InnoDB recovery processing) the slave throws an error and waits for the DBA to fix things. The removal of statements and the conditions necessary to remove some events from the end of the binary log is discussed here: http://dev.mysql.com/doc/refman/5.1/en/binary-log.html * Semi-sync (5.5) adds some chatter from Slave to Master so that the Master can wait for at least one Slave to have written to its relay log. * Percona Cluster, Galera, etc., add more complexity to the replication traffic. ... Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
How often the slave to pull data from master?
Hello: I haven't read the source code of mysql ,but as I know ,the slave get data from master server, rather than the master push data to slave. I think there is a timer to do this. Who can tell me the interval about the replicationhow often the slave to pull data from master? Thanks! Zhigang Zhang
how often the slave to pull data from master?
Hello: I haven't read the source code of mysql ,but as I know ,the slave get data from master server, rather than the master push data to slave. I think there is a timer to do this. Who can tell me the interval about the replicationhow often the slave to pull data from master? Thanks! Zhigang Zhang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
hi all
Dear: A open source development community for unix-like systems. http://dev.unix-like.net http://dev.unix-like.net/ Thanks.
