Re: Order Problem
That is fine. On Thu, May 8, 2008 at 4:51 PM, Neil Tompkins [EMAIL PROTECTED] wrote: Perfect. It worked just how I wanted. Thanks for your help. Neil Date: Wed, 7 May 2008 19:54:39 +0200 To: [EMAIL PROTECTED] Subject: Re: Order Problem From: [EMAIL PROTECTED] Hi, You should look at the `FIND_IN_SET` function here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set Your query could look like: SELECT ProductID FROM Products WHERE Enabled= ' Yes' AND ProductID IN(varProductID) ORDER BY FIND_IN_SET(ProductID, varProductID); Haven't tested it, though... Take care, Aleksandar _ Discover and Win with Live Search http://clk.atdmt.com/UKM/go/msnnkmgl001007ukm/direct/01/ -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: index creation taking too much time
Change the following parameter: myisam_sort_buffer_size=300MB Larger than here. myisam_max_sort_file_size=10GB Reduce this value to 30% of your real memory. On Tue, May 13, 2008 at 7:10 PM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Krishna, how do i make my index to get more key blocks On 5/13/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi anand, PRIMARY KEY (`id`), KEY `KI_IDX_0805090456` (`words`,`id`), KEY `CI_IDX_0805090456` (`lf_id`) Since id is a primary key. Then why again indexing on id is being created (`words`,`id`). It will be a duplicate index on id. words is a varchar type. So instead of creating fulltext index restrict word(15)). Try. Key buffer seems to be OK. No free key blocks are there. This can be the reason. Key_blocks_unused 0 On Mon, May 12, 2008 at 6:48 PM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, We have a table which is around 100 Million rows. Its a myisam table, but the db default is innodb. CREATE TABLE `dc_data` ( `id` decimal(22,0) NOT NULL, `words` varchar(255) NOT NULL, `lf_id` decimal(22,0) NOT NULL, `occurence` bigint(20) NOT NULL, `date_modified` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP) ENGINE=MyIsam DEFAULT CHARSET=utf8 indexs are as below PRIMARY KEY (`id`), KEY `KI_IDX_0805090456` (`words`,`id`), KEY `CI_IDX_0805090456` (`lf_id`) we have 8 cpu, 8 gb ram. We use set below parameters at session level myisam_sort_buffer_size=300MB myisam_max_sort_file_size=10GB Each index creation is taking 10hrs, is there any way i can speed up index creation. regards anandkl -- Krishna Chandra Prajapati -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
very simple but slow query
Hi, can someone could explain where are problems in this query: EXPLAIN SELECT ID FROM ven_tes WHERE ID IN (SELECT ID FROM ven_tes WHERE ID_ven=6573) +++-+-++-+-+--+--+--+ | id | select_type| table | type| possible_keys | key | key_len | ref | rows | Extra| +++-+-++-+-+--+--+--+ | 1 | PRIMARY| ven_tes | index | NULL | PRIMARY | 4 | NULL | 6573 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | ven_tes | unique_subquery | PRIMARY,ID_ven | PRIMARY | 4 | func |1 | Using index; Using where | +++-+-++-+-+--+--+--+ as you can see, it doesn't use absolutely indexes on ven_tes (ID is the primary key, ID_ven is index) Thanks in advance Carlo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Authors wanted for German Linux Magazin
Hello List, As an editor for the german Linux Magazine I am looking for Authors who would want to write an article of about 5-7 pages (=15-2 characters) on MySQL Troubleshooting and a second on Clustering and Replication issues. The Article is planned for our forthcoming Linux Technical Review 09 - Datenbanken, which aims at IT-Managers, Technicians and Administrators at a very high skill level. The deadline is in four weeks. I would feel very happy if one or two of you would like to write for us, and if you have good ideas about relevant topics, please let me know. Please let me know, I'd be very happy if you could contribute... (Don't worry about language!) -- Best Regards - Mit freundlichen Gruessen Markus Feilner - Feilner IT Linux GIS Linux Solutions, Training, Seminare und Workshops - auch Inhouse Koetztingerstr 6c93057 Regensburg Telefon:+49 941 8 10 79 89 Mobil: +49 170 3 02 70 92 WWW: www.feilner-it.net mail: [EMAIL PROTECTED] -- My OpenVPN book - http://www.packtpub.com/openvpn/book OPENVPN : Building and Integrating Virtual Private Networks My new book - Out now: http://www.packtpub.com/scalix/book SCALIX Linux Administrator's Guide -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple but slow query
in mysql sub queries dont perform well. You can could try this SELECT a.ID FROM ven_tes a, ven_tes b where a.id=b.id and b.id_ven=6573 . On 5/20/08, Wakan [EMAIL PROTECTED] wrote: Hi, can someone could explain where are problems in this query: EXPLAIN SELECT ID FROM ven_tes WHERE ID IN (SELECT ID FROM ven_tes WHERE ID_ven=6573) +++-+-++-+-+--+--+--+ | id | select_type| table | type| possible_keys | key | key_len | ref | rows | Extra| +++-+-++-+-+--+--+--+ | 1 | PRIMARY| ven_tes | index | NULL | PRIMARY | 4 | NULL | 6573 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | ven_tes | unique_subquery | PRIMARY,ID_ven | PRIMARY | 4 | func |1 | Using index; Using where | +++-+-++-+-+--+--+--+ as you can see, it doesn't use absolutely indexes on ven_tes (ID is the primary key, ID_ven is index) Thanks in advance Carlo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple but slow query
We use a sub select on a 8M+ row table because it takes better advantage of indexes. SELECT startip,endip FROM geodb a WHERE a.startip = (SELECT max(startip) FROM geodb WHERE b.startip = 3250648033) AND a.endip = 3250648033; startip and endip are INT(10) unsigned and unique keys. This returns, on a fairly crappy old system in milliseconds after the table is loaded. Carlo, What do your tables look like exactly, and what are you considering to be poor performance? Look up the profiling flag, if you set that, you can get a detailed breakdown on the time spent in each query. mysql set profiling=1; Query OK, 0 rows affected (0.00 sec) mysql select count(*) from visitor; +--+ | count(*) | +--+ | 152 | +--+ 1 row in set (0.00 sec) mysql show profile; ++--+ | Status | Duration | ++--+ | (initialization) | 0.08 | | checking query cache for query | 0.000232 | | Opening tables | 0.48 | | System lock| 0.25 | | Table lock | 0.000125 | | init | 0.62 | | optimizing | 0.34 | | executing | 0.000314 | | end| 0.19 | | query end | 0.12 | | storing result in query cache | 0.000245 | | freeing items | 0.3 | | closing tables | 0.23 | | logging slow query | 0.11 | ++--+ 14 rows in set (0.01 sec) http://www.futhark.ch/mysql/122.html is a good tut on joining a table on itself which might be where you are going. Don't use cross joins. Just do some googling as to why. Thanks, Eric Ananda Kumar wrote: in mysql sub queries dont perform well. You can could try this SELECT a.ID FROM ven_tes a, ven_tes b where a.id=b.id and b.id_ven=6573 . On 5/20/08, Wakan [EMAIL PROTECTED] wrote: Hi, can someone could explain where are problems in this query: EXPLAIN SELECT ID FROM ven_tes WHERE ID IN (SELECT ID FROM ven_tes WHERE ID_ven=6573) +++-+-++-+-+--+--+--+ | id | select_type| table | type| possible_keys | key | key_len | ref | rows | Extra| +++-+-++-+-+--+--+--+ | 1 | PRIMARY| ven_tes | index | NULL | PRIMARY | 4 | NULL | 6573 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | ven_tes | unique_subquery | PRIMARY,ID_ven | PRIMARY | 4 | func |1 | Using index; Using where | +++-+-++-+-+--+--+--+ as you can see, it doesn't use absolutely indexes on ven_tes (ID is the primary key, ID_ven is index) Thanks in advance Carlo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maximum insert records per query?
Hi, Thanks for the reply. Now I also manipulate it using PHP script to loop every 1000 of records. Regards, Willy On Tue, 2008-05-20 at 11:48 +0800, Moon's Father wrote: To make the parameter max_allowed_packet larger. On Mon, May 19, 2008 at 12:17 PM, sangprabv [EMAIL PROTECTED] wrote: Hi, Currently in my production environment, mysql only allow max 50.000 records per insert. Is it possible to increase the number of records inserted during insert into query? How to do it? TIA Regards, Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- [sangprabv] http://www.sangprabv.web.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple but slow query
On Tue, May 20, 2008 at 7:05 AM, Wakan [EMAIL PROTECTED] wrote: can someone could explain where are problems in this query: EXPLAIN SELECT ID FROM ven_tes WHERE ID IN (SELECT ID FROM ven_tes WHERE ID_ven=6573) If that subselect only returns a single result, try using = instead of IN. MySQL performed very poorly on OR queries before version 5. It would avoid using indexes. The new index merge stuff in version 5 fixed that. Also, this query is probably better written as a JOIN. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple but slow query
On Tue, May 20, 2008 at 11:33 AM, Perrin Harkins [EMAIL PROTECTED] wrote: If that subselect only returns a single result, try using = instead of IN. MySQL performed very poorly on OR queries before version 5. It would avoid using indexes. The new index merge stuff in version 5 fixed that. Also, this query is probably better written as a JOIN. Agreed on the join, disagree about OR queries in general. Index merge is good queries like WHERE index_col1 ='foo' OR index_col2 = 'bar' If mysql didn't suck dealing with subqueries this fellow would have a result set transformed into something like: WHERE index_col1 ='foo' OR index_col1 = 'bar' which can definitely use an index on 3.23 (though for a multi column index index_col1 would need to be the left most column... but that is another longer explanation) http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and auto_increment
Thanks. That sounds pretty labor intensive, since I'd have to do the copy for each table. That makes the process much harder to automate. And I'm not sure the end result would be what I wanted anyway. I would like to be able to do this without copying data around. Does anybody have another suggestion? On Mon, May 19, 2008 at 11:00 PM, Moon's Father [EMAIL PROTECTED] wrote: You can import your data into a test database,then export the data using statement select ... into ... then You can complare the two. On Tue, May 20, 2008 at 9:14 AM, Dan Lipsitt [EMAIL PROTECTED] wrote: Is there a set of flags for mysqldump that will include the auto_increment specifier for columns, but leave out the AUTO_INCREMENT=x saved values? I want to compare the schema of two versions of a database, without considering the data. In my opinion, the saved auto increment counter is part of the data, not part of the schema, but the --no-data flag doesn't seem to share my opinion. If I run mysqldump a -d a.sql mysqldump b -d b.sql diff a.sql b.sql I want to see no output if the schemas are the same, regardless of how many rows of data each database has. Is there a way to do this with just mysqldump or mysql? Thanks, Dan -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Odd error
Customer has tested his site in Mysql 5.1 and we are running Mysql 4.1 . His test works but over here we get: Warning: DOMDocument::__construct() [function.DOMDocument---construct]: Malloc(88) in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 5 Warning: DOMDocument::__construct() [function.DOMDocument---construct]: Malloc(88) Ok in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 5 Warning: DOMDocument::__construct() [function.DOMDocument---construct]: Malloc(4) in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 5 Warning: DOMDocument::__construct() [function.DOMDocument---construct]: Malloc(4) Ok in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 5 Warning: DOMDocument::__construct() [function.DOMDocument---construct]: Malloc(6) in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 5 Warning: DOMDocument::__construct() [function.DOMDocument---construct]: Malloc(6) Ok in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 5 Warning: DOMDocument::createElement() [function.DOMDocument-createElement]: Malloc(60) in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 7 Warning: DOMDocument::createElement() [function.DOMDocument-createElement]: Malloc(60) Ok in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 7 Warning: DOMDocument::createElement() [function.DOMDocument-createElement]: Malloc(8) in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 7 Warning: DOMDocument::createElement() [function.DOMDocument-createElement]: Malloc(8) Ok in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 7 Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'vesna'@'localhost' (using password: YES) in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 13 Not connected : Access denied for user 'vesna'@'localhost' (using password: YES) Warning: Unknown: Freed(8) Ok in Unknown on line 0 Warning: Unknown: Freed(60) Ok in Unknown on line 0 Warning: Unknown: Freed(4) Ok in Unknown on line 0 Warning: Unknown: Freed(6) Ok in Unknown on line 0 Warning: Unknown: Freed(88) Ok in Unknown on line 0 The username and password are correct. What could be the problem? -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd error
You should post this question to php section. On Wed, May 21, 2008 at 6:51 AM, Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem [EMAIL PROTECTED] wrote: Customer has tested his site in Mysql 5.1 and we are running Mysql 4.1 . His test works but over here we get: Warning: DOMDocument::__construct() [function.DOMDocument---construct]: Malloc(88) in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 5 Warning: DOMDocument::__construct() [function.DOMDocument---construct]: Malloc(88) Ok in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 5 Warning: DOMDocument::__construct() [function.DOMDocument---construct]: Malloc(4) in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 5 Warning: DOMDocument::__construct() [function.DOMDocument---construct]: Malloc(4) Ok in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 5 Warning: DOMDocument::__construct() [function.DOMDocument---construct]: Malloc(6) in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 5 Warning: DOMDocument::__construct() [function.DOMDocument---construct]: Malloc(6) Ok in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 5 Warning: DOMDocument::createElement() [function.DOMDocument-createElement]: Malloc(60) in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 7 Warning: DOMDocument::createElement() [function.DOMDocument-createElement]: Malloc(60) Ok in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 7 Warning: DOMDocument::createElement() [function.DOMDocument-createElement]: Malloc(8) in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 7 Warning: DOMDocument::createElement() [function.DOMDocument-createElement]: Malloc(8) Ok in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 7 Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'vesna'@'localhost' (using password: YES) in /usr/home/vesna/html/pryvit/church_maps/data_genxml.php on line 13 Not connected : Access denied for user 'vesna'@'localhost' (using password: YES) Warning: Unknown: Freed(8) Ok in Unknown on line 0 Warning: Unknown: Freed(60) Ok in Unknown on line 0 Warning: Unknown: Freed(4) Ok in Unknown on line 0 Warning: Unknown: Freed(6) Ok in Unknown on line 0 Warning: Unknown: Freed(88) Ok in Unknown on line 0 The username and password are correct. What could be the problem? -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Usefulness of mysql logs when using innodb?
You should keep it on in my opinion. On Tue, May 13, 2008 at 6:04 PM, Nico Sabbi [EMAIL PROTECTED] wrote: Hi, I guess that when I'm using only Innodb and no replication I can safely disable mysql's (bin-) log files (that grow to no end) because Innodb has its own log files. Is it correct? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MySQL performance on LVM2
What is LVM? 2008/5/12 MarisRuskulis [EMAIL PROTECTED]: Hello! I'm wondering about MySQL LVM2 preformance, but cant found any comparisions. I know that there is some speed decrease with LVM, something about 30%. But how this decrease impacts overal MySQL performance? Now we are backuping replication slave server with mysqldump w full table locks, this takes some time. I think better solution is to use LVM snapshots, but this performance decrease really scares me. Has anyone some advices on this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Percentage growth query.
Don't you use the following statements instead of yours? ...month between 10 and 12 On Wed, May 7, 2008 at 1:46 AM, kabel [EMAIL PROTECTED] wrote: I have a table from which I need to calculate the percentage growth of downloads from specific countries over two distinct periods of time. Calculating the total downloads from a country over one period of time is not difficult: SELECT country, SUM(total_dl) AS q3_total FROM show_history WHERE year = 2007 AND ( month = 12 OR month = 11 OR month = 10 ) GROUP BY (country); I need to be able to compare those numbers with the result of: SELECT country, SUM(total_dl) AS q4_total FROM show_history WHERE year = 2007 AND ( month = 10 OR month = 11 OR month = 12 ) GROUP BY (country); I'm currently using temporary tables to store the results of each query, however, I think there's a way to accomplish this using subqueries. Any thoughts? TIA, kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: [solved] Re: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5)
The general version can not be replaced *frequently * On Fri, May 9, 2008 at 4:23 PM, Dominik Klein [EMAIL PROTECTED] wrote: Upgrade to 5.0.51b seems to have solved the problem. On wednesday, I saw the problem about a dozen times during a peak time. Upgraded wednesday night and have not seen the error since. Thanks Dominik Juan Eduardo Moreno wrote: Hi, This error occur when slave servers could incorrectly interpret an out-of-memory error from the master and reconnect using the wrong binary log position. This was fix in 5.0.48 version. Please, try to update your version ( from 5.0.45) of mysql and try again. regards, Juan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: ANN: Data Wizard for MySQL 8.4 released
Just try to get it. On Mon, May 5, 2008 at 10:12 PM, SQL Maestro Group [EMAIL PROTECTED] wrote: Hi! SQL Maestro Group announces the release of Data Wizard for MySQL 8.4, a powerful Windows GUI solution for MySQL data management. Data Wizard for MySQL provides you with a number of easy-to-use wizards to generate PHP and ASP.NET scripts for the selected tables, views and queries, convert any ADO-compatible database to the MySQL database, export data from MySQL tables, views and queries to most popular formats, and import data into the tables. http://www.sqlmaestro.com/products/mysql/datawizard/ New features = 1. Now it is possible to create a system scheduled task or convert a task created in Data wizard to system one. Such tasks can be executed by the Windows scheduler service even without user logon. 2. Command line options have been implemented. 3. Since this version each task is displayed as a separated icon in the system tray when executing. 4. A possibility of connecting to remote MySQL databases via HTTP tunneling has been implemented. 5. PHP Generator: a lot of new features. See full press-release for details. 6. Data Pump: now you can execute custom SQL scripts after connecting, before data importing and after pumping. 7. ASP.NET generator: since this version it is possible to specify a custom connection string. There are also some other useful things. Full press release is available at http://www.sqlmaestro.com/news/company/5148/ Background information: --- SQL Maestro Group is engaged in developing complete database admin and management tools for MySQL, Oracle, MS SQL Server, DB2, PostgreSQL, SQLite, Firebird and MaxDB providing the highest performance, scalability and reliability to meet the requirements of today's database applications. Sincerely yours, The SQL Maestro Group Team http://www.sqlmaestro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
