Re: distinct count operation with the use of where count $num
Hi, you have to use `HAVING' instead of `WHERE' like this. SELECT DISTINCT `term`, COUNT(*) AS count FROM blp_sql_distinct_temp_table GROUP BY `term` HAVING count = 5 ORDER BY count DESC; put `HAVING' next of `GROUP BY'. `WHERE' behaves at before aggregate of `GROUP BY'. your SQL means like SELECT .. FROM (SELECT * FROM .. WHERE count = 5) AS dummy GROUP BY .. because of that, mysqld says `Unknown column .. in where clause' regards, 2012/6/17 Haluk Karamete halukkaram...@gmail.com: Hi, I'm trying to get this work; SELECT distinct `term`,count(*) as count FROM blp_sql_distinct_temp_table where count = 5 group by `term` order by count DESC But I get this error; Unknown column 'count' in 'where clause' How do I get only those records whose group by count is above 5? -- 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
Re: Which Database when lot of insert / update queries to execute
Hello, I am designing a solution which will need me to import from CSV, i am using my JAVA code to parse. CSV file has 500K rows, and i need to do it thrice an hour, for 10 hours a day. try to use `LOAD DATA INFILE' to import from CSV file. http://dev.mysql.com/doc/refman/5.5/en/load-data.html Also , if i need to do 'group by', on a column on a large table what should i keep in mind, is it advisable, create index on columns used by 'group by' and columns used by aggregate functions. regards, 2012/6/15 abhishek jain abhishek.netj...@gmail.com: hi, I am biased on mysql, and hence i am asking this on mysql forum first. I am designing a solution which will need me to import from CSV, i am using my JAVA code to parse. CSV file has 500K rows, and i need to do it thrice an hour, for 10 hours a day. The Queries will mainly be update but select and insert also at times, The database size will be estimated to be about 5GB. I need to know is this a classic case for a NOSQL database or mysql is a good option. Also , if i need to do 'group by', on a column on a large table what should i keep in mind, is it advisable, Please advice, -- Thanks and kind Regards, Abhishek jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: category with parentid
Pesebe-san,
I'm sorry to i'm not clear what do you want to do.
do you mean like this? (exampled by Perl)
#---
use DBI;
$cn = DBI-connect(DBI:mysql:test,root,);
$st = $cn-prepare(SELECT t1.name AS cat,GROUP_CONCAT(t2.name) AS
subcat FROM `table` AS t1 LEFT JOIN `table` AS t2 ON t1.id =
t2.parentid WHERE t1.parentid = 0 GROUP BY cat;);
$st-execute;
while ($rs = $st-fetchrow_hashref) {
print $rs-{cat},br\n;
@buff = split(/,/,$rs-{subcat});
foreach $i (@buff) {
print -,$i,br\n;
}
}
#---
regards,
ts. tanaka//
2012/5/30 HaidarPesebe haidarpes...@gmail.com:
and also id (either cat or subcat) can be called
- Original Message - From: HaidarPesebe haidarpes...@gmail.com
To: MySQL Lists mysql@lists.mysql.com
Sent: Wednesday, May 30, 2012 2:31 PM
Subject: Re: category with parentid
Hello ts. Tanaka,
I've tried your way and succeed. Thank you.
But I want to ask your help again what if the result will be like this:
A. Cat A
- Subcat A
- Subcat A
2. CatB
- Subcat B
possible by using br or div
which the previous result is
Cat A - SubcatA, SubcatA
Cat B - SubcatB
thank you
__ Information from ESET NOD32 Antivirus, version of virus
signature database 7179 (20120529) __
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
__ Information from ESET NOD32 Antivirus, version of virus signature
database 7179 (20120529) __
The message was checked by ESET NOD32 Antivirus.
http://www.eset.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
Re: category with parentid
Hello Pesebe-san, how about this? SELECT t1.name AS cat, GROUP_CONCAT(t2.name) AS subcat FROM table AS t1 LEFT JOIN table AS t2 ON t1.id = t2.parentid WHERE t1.parentid = 0 GROUP BY cat; +---+---+ | cat | subcat| +---+---+ | cat A | subcat A,subcat A | | cat B | subcat B | +---+---+ 2 rows in set (0.00 sec) regards, ts. tanaka// 2012/5/28 HaidarPesebe haidarpes...@gmail.com: Thanks for the information; I tried to call the database with something like this; select id,name from TABLE WHERE parentid='0' and a second call to the same table as this; select id,name from TABLE WHERE parentid='$id' (this $id is the result of calling the first call TABLE) and successfully. or are there other examples that simple? Thanks __ Information from ESET NOD32 Antivirus, version of virus signature database 7173 (20120527) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.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
Re: Need help for performance tuning with Mysql
Hello, I seem your mysqld doesn't use enough memory. Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 if your mysqld uses InnoDB oftenly, edit innodb_buffer_pool_size in you my.cnf. http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. It is solution for only sql's large result,i think. if you doesn't recognize that problem causes large result, you should approach other way,too. regards, ts. tanaka// 2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com: Also following is the free command result. total used free sharedbuffers cached Mem: 81623807843676 318704 0 956325970892 -/+ buffers/cache:17771526385228 Swap: 8032492 235608008932 Thanks, Yu Yu Watanabe さんは書きました: Hello all. I would like to ask for advice with performance tuning with MySQL. Following are some data for my server. CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total) Memory : 8GB OS : RHEL 4.4 x86_64 MySQL : MySQL 5.0.50sp1-enterprise Attached file # my.cnf.txt : my.cnf information # mysqlext_20120522131034.log : variable and status information from mysqladmin I have 2 database working with high load. I wanted to speed up my select and update queries not by optimizing the query itself but tuning the my.cnf. I have referred to following site, http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html and read Hiperformance Mysql vol.2 , and increased the following values, table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. According to the ps and sar result *1 PS result Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 *2 SAR Average CPU user 25% sys 5% io 3% I assume that MySQL can work more but currently not. I am considersing to off load 1 high load database to seperate process and make MySQL work in multiple process. It would be a great help if people in this forum can give us an adivice for the tuning. Best Regards, Yu Watanabe __ -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Need help for performance tuning with Mysql
Hello, Yu-san, (へろへろな英語で申し訳ないです) Can I think that you already tweaked Index on the tables? if you yet,please create apt indexes. MyISAM caches only Index without data. i take way for decreasing disk seek, 1) create more indexes on the tables,if the tables doesn't update quite often. including data into index forcibly. this makes slow for insert and update,and this is dirty idea,i think. (よくSELECTされるカラムをINDEXに含めてしまいます。 ただし、SELECT * FROMで呼ばれることが多い場合には使えない上に かなり美しくない策です。。) 2) tune filesystem and disk drive parameter for datadir. MyISAM table's data caches only in the filesystem cache. But i regret that i don't have knowledge around filesystem. あまり力になれなくて申し訳ないです。 regards, ts. tanaka// 2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com: Hello Tsubasa. Thank you for the reply. (返信ありがとうございます。) Our high loaded DB are both INNODB and MyISAM. Espicially , on MyISAM. I will consider the tuning of innodb_buffer_pool_size as well. Do you know the tips for how to tune the disk access for MyISAM? Thanks, Yu Tsubasa Tanaka さんは書きました: Hello, I seem your mysqld doesn't use enough memory. Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 if your mysqld uses InnoDB oftenly, edit innodb_buffer_pool_size in you my.cnf. http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. It is solution for only sql's large result,i think. if you doesn't recognize that problem causes large result, you should approach other way,too. regards, ts. tanaka// 2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com: Also following is the free command result. total used free sharedbuffers cached Mem: 81623807843676 318704 0 956325970892 -/+ buffers/cache:17771526385228 Swap: 8032492 235608008932 Thanks, Yu Yu Watanabe さんは書きました: Hello all. I would like to ask for advice with performance tuning with MySQL. Following are some data for my server. CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total) Memory : 8GB OS : RHEL 4.4 x86_64 MySQL : MySQL 5.0.50sp1-enterprise Attached file # my.cnf.txt : my.cnf information # mysqlext_20120522131034.log : variable and status information from mysqladmin I have 2 database working with high load. I wanted to speed up my select and update queries not by optimizing the query itself but tuning the my.cnf. I have referred to following site, http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html and read Hiperformance Mysql vol.2 , and increased the following values, table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. According to the ps and sar result *1 PS result Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 *2 SAR Average CPU user 25% sys 5% io 3% I assume that MySQL can work more but currently not. I am considersing to off load 1 high load database to seperate process and make MySQL work in multiple process. It would be a great help if people in this forum can give us an adivice for the tuning. Best Regards, Yu Watanabe __ -- 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 -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
