How to find the top most member in a hierarchy of subcategories
Hi, I have a table structure like : ID , NAME, PARENT_ID 1 , Top , 0 2 , Level 1 , 1 3 , Level 2 ,2 4 , Another Top , 0 and so on. I wanted to know the topmost cat. if i have the lowest category id ie. 3 in this case. I wanted to get like 3-2-1 Pl. help me , cn i do this in one query, also i do not know how many sublevels are there, Thanks, Abhishek jain
Re: Understanding Query-Cache math...
Hi The query cache uses variable-length blocks and the Qcache_total_blocks and Qcache_free_blocks may indicate query cache memory fragmentation. After FLUSH QUERY CACHE, only a single free block remains. So the variables query_cache_min_res_unit, query_cache_limit, query_prealloc_size, query_alloc_block_size determines the free and available number of blocks. Every cached query requires a minimum of two blocks - for query text and for the query results. Also, every table that is used by a query requires one block. However, if two or more queries use the same table, only one block needs to be allocated. The information provided by the Qcache_lowmem_prunes status variable can help you tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache. If you have query with the size larger than the query_cache_size then the query is not cached. If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, you should decrease the value of query_cache_min_res_unit. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables. Thanks ViSolve DB Team - Original Message - From: Christian Hammers [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 01, 2006 1:52 PM Subject: Understanding Query-Cache math... Hello I don't understand why query_cache_size / query_cache_min_res_unit != Qcache_total_blocks and Qcache_free_memory / query_cache_min_res_unit != Qcache_free_blocks Can anybody enlight me so that I know if I have to increase the Query-Cache or not? mysql SHOW status LIKE Qcache_%; +-+---+ | Variable_name | Value | +-+---+ | Qcache_free_blocks | 10382 | | Qcache_free_memory | 247491776 | | Qcache_hits | 119254865 | | Qcache_inserts | 5412923 | | Qcache_lowmem_prunes| 0 | | Qcache_not_cached | 58724815 | | Qcache_queries_in_cache | 16002 | | Qcache_total_blocks | 42464 | +-+---+ mysql SHOW variables LIKE query_cache_%; +--++ | Variable_name| Value | +--++ | query_cache_limit| 8388608| | query_cache_min_res_unit | 4096 | | query_cache_size | 268435456 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF| +--++ thanks, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 [EMAIL PROTECTED]D-52064 Aachen Fax 0241/911879 -- 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]
changing datadir
Has anyone good advice on changing the datadir on a Linux box. I have version 5.0.16 and my databases amount to about 5Gb. I'd like to move them over to one of the other logical disks on the Linux file system. Without any advice, I'd dump, reconfigure the datadir line in the cfg, and restore... but if anyone has a better way, please let me know! Helen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN ANY subqueries
Hi
The ANY keyword, which must follow a comparison operator, means return TRUE if
the comparison is TRUE for ANY of the values in the column that the subquery
returns
In has 2 forms:
1. IN (subquery). [The word IN is an alias for = ANY (subquery)].
2. IN (list of values seperated by comma)
Hence the exact syntax to use is:
Select fields from table where fieldname = ANY ( select fieldname from
table);
Ref: http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html
Thanks
ViSolve DB Team
- Original Message -
From: Ben Lachman [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, September 03, 2006 10:33 AM
Subject: IN ANY subqueries
I have a string comparison that I would like to do against a short
list of constant strings.
at the moment I am using the syntax
SELECT id FROM t1 WHERE name IN('s1', 's2', 's3', ...);
However, this limits me to exact matches and I'd like to move to a
caparison expersion that lets me match names that contain any of the
list. The MySQL docs state that 'IN()' is an alias to '= ANY()'
however when I substitute' = ANY' for IN I get a parse error. What
I'd like to do is write something like (although I figure there may
be a better way to do the comparison that I am not thinking of):
SELECT id FROM t1 WHERE name LIKE ANY('%s1%', '%s2%', '%s3%', ...);
Does anyone know a way to do this?
Thanks,
-Ben
Re: CentOS 4.3 - MySQL - NIS user issue
On 8/31/06, Tom Brown [EMAIL PROTECTED] wrote: Hi I have what i think is a strange issue - snip now when a NIS user tries to use the db [EMAIL PROTECTED] su - bugzilla [EMAIL PROTECTED] mysql -u bugs -p bugs ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13) any ideas why this is or what i can do to make it work? Do your NIS users mount only their HOME directories from the NIS/MySQL server? If so, obviously the /var partition on the MySQL server wouldn't be available to them, right? To confirm: 1. Log in as a NIS user 2. su to root 3. Check out ls -l /var/lib/mysql/ snip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find the top most member in a hierarchy of subcategories
I´m not quite sure if it could help you, because it´s whole in portuguese,
but i´ll send you.
My table is called categoria and has the follow structure:
CREATE TABLE `categoria` (
`id` int(20) NOT NULL auto_increment,
`cat_id` int(20) NOT NULL default '0',
`nome` varchar(50) NOT NULL default '',
`icone` varchar(255) NOT NULL default '',
`cod_shop` varchar(5) NOT NULL default '',
`topo` char(1) NOT NULL default '',
PRIMARY KEY (`id`)
)
Where the relations each register of the tree are like this:
ID , CATEGORIA, CAT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0
And then i use the follow routine to get the tree:
$sql = SELECT
*
FROM
categoria
ORDER BY
cat_id, nome;
$con-Query($sql);
$counter = $con-count;
$categorias=array();
for($z=0;$z$con-count;$z++){
$con-Seek($z);
list($id_cat, $cat_id, $nome_cat, $icone_cat) = $con-result;
$categorias[$id_cat]=array(id_cat = $id_cat, cat_id = $cat_id,
nome_cat = $nome_cat, icone_cat = $icone_cat, familia = $id_cat, );
$continua=$cat_id!=null $cat_id0;
if ($continua) {
$qual=$cat_id;
$categorias[$id_cat][indice]=;
while ($continua) {
$categorias[$id_cat][indice]=$categorias[$qual][nome_cat].$categorias[$id_cat][indice];
$continua=$categorias[$qual][cat_id]!=null
$categorias[$qual][cat_id]0;
$tem_pai=$cat_id!=null $cat_id0;
if ($tem_pai) $pai=$cat_id;
while ($tem_pai) {
if (!strpos($categorias[$pai][familia],,
.$categorias[$id_cat][id_cat]))
$categorias[$pai][familia].=, .$categorias[$id_cat][id_cat];
$tem_pai=$categorias[$pai][cat_id]!=null
$categorias[$pai][cat_id]0;
$pai=$categorias[$pai][cat_id];
}
if ($continua) {
$qual=$categorias[$qual][cat_id];
}
}
}
}
reset($categorias);
$linhas=array();
foreach ($categorias as $categoria) {
$linhas[$categoria[indice].$categoria[nome_cat]]=array(id_cat =
$categoria[id_cat], nome_cat = $categoria[nome_cat], icone_cat =
$categoria[icone_cat], indice = $categoria[indice], familia =
$categoria[familia]);
}
ksort($linhas);
reset($linhas);
Hope help you.
--
João Cândido de Souza Neto
Curitiba Online
[EMAIL PROTECTED]
(41) 3324-2294 (41) 9985-6894
http://www.curitibaonline.com.br
abhishek jain [EMAIL PROTECTED] escreveu na mensagem
news:[EMAIL PROTECTED]
Hi,
I have a table structure like :
ID , NAME, PARENT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0
and so on.
I wanted to know the topmost cat. if i have the lowest category id ie. 3
in
this case.
I wanted to get like 3-2-1
Pl. help me , cn i do this in one query, also i do not know how many
sublevels are there,
Thanks,
Abhishek jain
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to find the top most member in a hierarchy of subcategories
MySQL is not recursive. This might help you: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html /Peter www.lauri.se - personal web site www.dwsasia.com - corporate web site -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of abhishek jain Sent: Monday, September 04, 2006 4:29 PM To: mysql@lists.mysql.com Subject: How to find the top most member in a hierarchy of subcategories Hi, I have a table structure like : ID , NAME, PARENT_ID 1 , Top , 0 2 , Level 1 , 1 3 , Level 2 ,2 4 , Another Top , 0 and so on. I wanted to know the topmost cat. if i have the lowest category id ie. 3 in this case. I wanted to get like 3-2-1 Pl. help me , cn i do this in one query, also i do not know how many sublevels are there, Thanks, Abhishek jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to find the top most member in a hierarchy of subcategories
Yes, and this shows that you can not do it will MySQL purely :) But a
scripting language like php can do it for you with a recursive function as
the best option.
/Peter
-Original Message-
From: João Cândido de Souza Neto [mailto:[EMAIL PROTECTED]
Sent: Monday, September 04, 2006 7:55 PM
To: mysql@lists.mysql.com
Subject: Re: How to find the top most member in a hierarchy of subcategories
I´m not quite sure if it could help you, because it´s whole in portuguese,
but i´ll send you.
My table is called categoria and has the follow structure:
CREATE TABLE `categoria` (
`id` int(20) NOT NULL auto_increment,
`cat_id` int(20) NOT NULL default '0',
`nome` varchar(50) NOT NULL default '',
`icone` varchar(255) NOT NULL default '',
`cod_shop` varchar(5) NOT NULL default '',
`topo` char(1) NOT NULL default '',
PRIMARY KEY (`id`)
)
Where the relations each register of the tree are like this:
ID , CATEGORIA, CAT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0
And then i use the follow routine to get the tree:
$sql = SELECT
*
FROM
categoria
ORDER BY
cat_id, nome;
$con-Query($sql);
$counter = $con-count;
$categorias=array();
for($z=0;$z$con-count;$z++){
$con-Seek($z);
list($id_cat, $cat_id, $nome_cat, $icone_cat) = $con-result;
$categorias[$id_cat]=array(id_cat = $id_cat, cat_id = $cat_id,
nome_cat = $nome_cat, icone_cat = $icone_cat, familia = $id_cat, );
$continua=$cat_id!=null $cat_id0;
if ($continua) {
$qual=$cat_id;
$categorias[$id_cat][indice]=;
while ($continua) {
$categorias[$id_cat][indice]=$categorias[$qual][nome_cat].$categorias[$i
d_cat][indice];
$continua=$categorias[$qual][cat_id]!=null
$categorias[$qual][cat_id]0;
$tem_pai=$cat_id!=null $cat_id0;
if ($tem_pai) $pai=$cat_id;
while ($tem_pai) {
if (!strpos($categorias[$pai][familia],,
.$categorias[$id_cat][id_cat]))
$categorias[$pai][familia].=, .$categorias[$id_cat][id_cat];
$tem_pai=$categorias[$pai][cat_id]!=null
$categorias[$pai][cat_id]0;
$pai=$categorias[$pai][cat_id];
}
if ($continua) {
$qual=$categorias[$qual][cat_id];
}
}
}
}
reset($categorias);
$linhas=array();
foreach ($categorias as $categoria) {
$linhas[$categoria[indice].$categoria[nome_cat]]=array(id_cat =
$categoria[id_cat], nome_cat = $categoria[nome_cat], icone_cat =
$categoria[icone_cat], indice = $categoria[indice], familia =
$categoria[familia]);
}
ksort($linhas);
reset($linhas);
Hope help you.
--
João Cândido de Souza Neto
Curitiba Online
[EMAIL PROTECTED]
(41) 3324-2294 (41) 9985-6894
http://www.curitibaonline.com.br
abhishek jain [EMAIL PROTECTED] escreveu na mensagem
news:[EMAIL PROTECTED]
Hi,
I have a table structure like :
ID , NAME, PARENT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0
and so on.
I wanted to know the topmost cat. if i have the lowest category id ie. 3
in
this case.
I wanted to get like 3-2-1
Pl. help me , cn i do this in one query, also i do not know how many
sublevels are there,
Thanks,
Abhishek jain
--
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: How to find the top most member in a hierarchy of subcategories
Peter Lauri wrote: Yes, and this shows that you can not do it will MySQL purely :) But a scripting language like php can do it for you with a recursive function as the best option. IMHO, the best option would do it with a procedure as you don't get out of the database and don't have any overhead from outside. --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL subqueries and JOIN conditions
Hello all, I need to perform an SQL statement over 5 different tables, with complex request. To do so, i'm using subqueries. SELECT ... FROM (SELECT .WHERE) as T1 LEFT JOIN (SELECT .WHERE) as T2 WHERE . The problem is that only JOIN seems working with subqueries (LEFT JOIN raise an error) Here is an example of results T1 and T2 (T1 and T2, as shown in the request before are result of subqueries) : T1 : id col1col2 1 a b 2 c d 3 e f T2: id col3col4 2 aa bb 3 cc dd 6 ee ff And i'd like to obtain the final resultset : id col1col2col3col4 1 a b NULLNULL 2 c d aa bb 3 e f cc dd 6 NULLNULLee ff Vincent Badier
Combo of 2 fields needs to be unique
I'm tracking site visitors with a database - one field is the IP address, the other is the page they've visited. I don't want any duplicates of this combination. Is there a MySQL statement similar to INSERT IGNORE but will ignore only if the combination is duplicated? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Combo of 2 fields needs to be unique
Hello Brian, IMHO if you put this combinaison as a uniq key, any insert with this combinaison already existing will fail Vincent Brian Dunning [EMAIL PROTECTED] 04/09/2006 16:49 To: mysql@lists.mysql.com cc: Subject:Combo of 2 fields needs to be unique I'm tracking site visitors with a database - one field is the IP address, the other is the page they've visited. I don't want any duplicates of this combination. Is there a MySQL statement similar to INSERT IGNORE but will ignore only if the combination is duplicated? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN ANY subqueries
So possibly a better solution would be to create a temporary table
and then do a subquery on that table?
-Ben
On Sep 4, 2006, at 7:37 AM, Visolve DB Team wrote:
Hi
The ANY keyword, which must follow a comparison operator, means
return TRUE if the comparison is TRUE for ANY of the values in the
column that the subquery returns
In has 2 forms:
1. IN (subquery). [The word IN is an alias for = ANY (subquery)].
2. IN (list of values seperated by comma)
Hence the exact syntax to use is:
Select fields from table where fieldname = ANY ( select
fieldname from table);
Ref: http://dev.mysql.com/doc/refman/5.0/en/any-in-some-
subqueries.html
Thanks
ViSolve DB Team
- Original Message -
From: Ben Lachman [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, September 03, 2006 10:33 AM
Subject: IN ANY subqueries
I have a string comparison that I would like to do against a short
list of constant strings.
at the moment I am using the syntax
SELECT id FROM t1 WHERE name IN('s1', 's2', 's3', ...);
However, this limits me to exact matches and I'd like to move to a
caparison expersion that lets me match names that contain any of the
list. The MySQL docs state that 'IN()' is an alias to '= ANY()'
however when I substitute' = ANY' for IN I get a parse error. What
I'd like to do is write something like (although I figure there may
be a better way to do the comparison that I am not thinking of):
SELECT id FROM t1 WHERE name LIKE ANY('%s1%', '%s2%', '%s3%', ...);
Does anyone know a way to do this?
Thanks,
-Ben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what this error was
Hi, I was trying to import some of the data into the 'datavalue' table of my database i was getting th following error Error Code : 1452 Cannot add or update a child row: a foreign key constraint fails (`gtest1/datavalue`, CONSTRAINT `FKEB92DC077A7A2FFA` FOREIGN KEY (`dataElement`) REFERENCES `dataelement` (`id`)) (5428 ms taken) My table structures: dataelement CREATE TABLE `dataelement` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, `shortName` varchar(255) default NULL, `description` varchar(255) default NULL, `active` bit(1) default NULL, `type` varchar(255) default NULL, `aggregationOperator` int(11) default NULL, `parent` int(11) default NULL, `alternativeName` varchar(255) default NULL, `code` varchar(255) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), UNIQUE KEY `shortName` (`shortName`), UNIQUE KEY `alternativeName` (`alternativeName`), UNIQUE KEY `code` (`code`), KEY `FKE1611C125DC41C92` (`parent`), CONSTRAINT `FKE1611C125DC41C92` FOREIGN KEY (`parent`) REFERENCES `dataelement` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 -- datavalue CREATE TABLE `datavalue` ( `dataElement` int(11) NOT NULL, `period` int(11) NOT NULL, `source` varchar(255) NOT NULL, `flag` varchar(255) default NULL, `value` varchar(255) default NULL, `storedBy` varchar(255) default NULL, `timestamp` datetime default NULL, `comment` varchar(255) default NULL, PRIMARY KEY (`dataElement`,`period`,`source`), KEY `FKEB92DC077F9CE9CC` (`period`), KEY `FKEB92DC077A7A2FFA` (`dataElement`), CONSTRAINT `FKEB92DC077A7A2FFA` FOREIGN KEY (`dataElement`) REFERENCES `dataelement` (`id`), CONSTRAINT `FKEB92DC077F9CE9CC` FOREIGN KEY (`period`) REFERENCES `period` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Re: what this error was
- Original Message - From: VenuGopal Papasani [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, September 05, 2006 12:28 AM Subject: what this error was Hi, I was trying to import some of the data into the 'datavalue' table of my database i was getting th following error Error Code : 1452 Cannot add or update a child row: a foreign key constraint fails (`gtest1/datavalue`, CONSTRAINT `FKEB92DC077A7A2FFA` FOREIGN KEY (`dataElement`) REFERENCES `dataelement` (`id`)) (5428 ms taken) My table structures: dataelement CREATE TABLE `dataelement` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, `shortName` varchar(255) default NULL, `description` varchar(255) default NULL, `active` bit(1) default NULL, `type` varchar(255) default NULL, `aggregationOperator` int(11) default NULL, `parent` int(11) default NULL, `alternativeName` varchar(255) default NULL, `code` varchar(255) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), UNIQUE KEY `shortName` (`shortName`), UNIQUE KEY `alternativeName` (`alternativeName`), UNIQUE KEY `code` (`code`), KEY `FKE1611C125DC41C92` (`parent`), CONSTRAINT `FKE1611C125DC41C92` FOREIGN KEY (`parent`) REFERENCES `dataelement` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 -- datavalue CREATE TABLE `datavalue` ( `dataElement` int(11) NOT NULL, `period` int(11) NOT NULL, `source` varchar(255) NOT NULL, `flag` varchar(255) default NULL, `value` varchar(255) default NULL, `storedBy` varchar(255) default NULL, `timestamp` datetime default NULL, `comment` varchar(255) default NULL, PRIMARY KEY (`dataElement`,`period`,`source`), KEY `FKEB92DC077F9CE9CC` (`period`), KEY `FKEB92DC077A7A2FFA` (`dataElement`), CONSTRAINT `FKEB92DC077A7A2FFA` FOREIGN KEY (`dataElement`) REFERENCES `dataelement` (`id`), CONSTRAINT `FKEB92DC077F9CE9CC` FOREIGN KEY (`period`) REFERENCES `period` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 The message is pretty clear; a dataelement value on a row failed to satisfy the foreign key constraint during either an insert or an update. Let's say that the dataelement value had a foreign constraint that limited the values to 'M' or 'F'. If you added a new row to the table and the value in the new dataelement column was anything but 'M' or 'F', you would have violated the foreign constraint and gotten that error. Or, if you had updated an existing row and tried to change the dataelement value to anything but 'M' or 'F', you would get that same message. You need to look at the values that are allowable in this dataelement column and then see what value is actually being inserted or what the existing value is being changed to. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.7/437 - Release Date: 2006-09-04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
