Re: calculate the total amount of revenue each month-year
SELECT CONCAT(b.month, '-', b.year) AS Date, SUM(b.quantity * a.price) AS Total FROM items AS a, orders AS b WHERE a.code = b.code GROUP BY month, year; where items is Table A and orders is Table B Thanks Aveek On Jun 20, 2011, at 9:27 AM, HaidarPesebe wrote: Please help us calculate the total amount of revenue each month-year from the two databases below. The first database is the name of the item and price. The second database is the goods sold. I Will Make a recapitulation of every month to my total income (total only). I've always tried but failed. TABLE A (item name and price) NO : CODE : NAME : PRICE(USD) : -- 1 : A01 : NAME A : 20 2 : A02 : NAME B : 15 - TABLE B (items sold) - : NO : CODE : CITY : QTY : MONTH : YEAR : --- : 1: A01: PARIS : 20 : 1 : 2011 : : 2: A01: LONDON : 11 : 1 : 2011 : : 3: A02: PARIS : 15 : 1 : 2011 : : 4: A02: PARIS : 10 : 1 : 2011 : : 5: A01: PARIS : 7 : 2 : 2011 : : 6: A01: LONDON : 8 : 2 : 2011 : : 7: A02: LONDON : 10 : 2 : 2011 : the result will be like this NO : DATE (month year): Total (USD) --- 1 : 1 - 2011: 995 2 : 2 - 2011: 450 We have tried but does not match the sum qyt. After I check the price turns out to be called just only the price of A even for multiplication NAME A02 CODE. Over its support I thank you. Haidapesebe __ Information from ESET NOD32 Antivirus, version of virus signature database 6221 (20110619) __ 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?unsub=arch...@jab.org
MySQL server has gone away
Hi, We are seeing intermittent errors of the type: failed to execute SELECT * FROM cluster_info WHERE cluster = ?: MySQL server has gone away The error MySQL server has gone away is the error from the db handle. Can anyone give me any pointers on why that happens? I looked up the documentation in MySQL docs and the most common reason seems to be that it happens if the query size is very large or if there is a timeout. None of them seems to be a probable cause. The max_allowed_packet on the server is 16 MB and as can be seen in the query above, the query is very small and nowhere near the size limit. We also have a timeout setting (wait_timeout) of 10 minutes and the above query for us cannot possibly take that amount of time. In any case, given the same query, it executes correctly 99% of time (so to speak). It fails intermittently with the above error. What possibly could be the reason? I also looked at the max connections on the server at that time (around ~80) and it is much less than the limit we have (limit is 1000). How can I extract more information when this happens? This error message sucks since it does not tell me what exactly happened. The server version is 5.1.45. Thanks Aveek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL server has gone away
Nothing in the error log or the slow query log that suggests that the query size is too large or us taking too much time to execute. Thanks Aveek On May 25, 2011, at 3:53 PM, Rik Wasmus wrote: failed to execute SELECT * FROM cluster_info WHERE cluster = ?: MySQL server has gone away The error MySQL server has gone away is the error from the db handle. Can anyone give me any pointers on why that happens? I looked up the documentation in MySQL docs and the most common reason seems to be that it happens if the query size is very large or if there is a timeout. None of them seems to be a probable cause. The max_allowed_packet on the server is 16 MB and as can be seen in the query above, the query is very small and nowhere near the size limit. We also have a timeout setting (wait_timeout) of 10 minutes and the above query for us cannot possibly take that amount of time. In any case, given the same query, it executes correctly 99% of time (so to speak). It fails intermittently with the above error. What possibly could be the reason? I also looked at the max connections on the server at that time (around ~80) and it is much less than the limit we have (limit is 1000). How can I extract more information when this happens? This error message sucks since it does not tell me what exactly happened. The server version is 5.1.45. Can you access the error log of the server? That can probably shed more light on the issue... -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with a query
I eventually came up with a solution myself although the query is a bit different SELECT C.file, C.digest, (a.cnt_A + b.cnt_B) AS total_count, C.refcount FROM C, (SELECT file, digest, COUNT(file) AS cnt_A FROM A GROUP BY file, digest) as a, (SELECT file, digest, COUNT(file) AS cnt_B FROM B GROUP BY file, digest) as b WHERE C.file = a.file and C.digest = a.digest and C.file = b.file and C.digest = b.digest and C.refcount (a.cnt_A + b.cnt_B); Thanks Aveek On May 20, 2011, at 1:52 PM, Anupam Karmarkar wrote: Hi Aveek, You need to use something like union all and having to get desire result Follow example below select file, digest from ( SELECT file, digest,Count(*) as Cnt FROM A GROUP BY file, digest union all SELECT file, digest,Count(*) as Cnt FROM B GROUP BY file, digest ) tmp group by file, digest HAVING Sum(Cnt) (Select sum(refcount) from C WHERE tmp.file = C.file and tmp.digest = C.digest); --Anupam --- On Tue, 17/5/11, Aveek Misra ave...@yahoo-inc.commailto:ave...@yahoo-inc.com wrote: From: Aveek Misra ave...@yahoo-inc.commailto:ave...@yahoo-inc.com Subject: Help with a query To: mysql@lists.mysql.commailto:mysql@lists.mysql.com mysql@lists.mysql.commailto:mysql@lists.mysql.com Date: Tuesday, 17 May, 2011, 1:23 PM I have a table A and table B of the same type as CREATE TABLE A ( `file` varbinary(255) not null, `digest` binary(40) not null ) CREATE TABLE B ( `file` varbinary(255) not null, `digest` binary(40) not null ) I have another table C of the following type CREATE TABLE C ( `file` varbinary(255) not null, `digest` binary(40) not null, `refcount` bigint(20) not null ) I need to write a query where for the same file and digest in table A and table B, the refcount is table C is not the same. So: SELECT COUNT(*) as count 1 FROM A WHERE file='file1' AND digest='digest1'; SELECT COUNT(*) as count 2 FROM B WHERE file='file1' AND digest='digest1'; and then adding up the two counts from these queries and comparing it with the result of the following query: SELECT refcount FROM C WHERE file='file1' AND digest='digest1'; basically (refcount == (count1 + count2)) should be true and I am interested in finding out all such records in table C where this is not the case. Thanks Aveek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sb_akarmar...@yahoo.com
Help with a query
I have a table A and table B of the same type as CREATE TABLE A ( `file` varbinary(255) not null, `digest` binary(40) not null ) CREATE TABLE B ( `file` varbinary(255) not null, `digest` binary(40) not null ) I have another table C of the following type CREATE TABLE C ( `file` varbinary(255) not null, `digest` binary(40) not null, `refcount` bigint(20) not null ) I need to write a query where for the same file and digest in table A and table B, the refcount is table C is not the same. So: SELECT COUNT(*) as count 1 FROM A WHERE file='file1' AND digest='digest1'; SELECT COUNT(*) as count 2 FROM B WHERE file='file1' AND digest='digest1'; and then adding up the two counts from these queries and comparing it with the result of the following query: SELECT refcount FROM C WHERE file='file1' AND digest='digest1'; basically (refcount == (count1 + count2)) should be true and I am interested in finding out all such records in table C where this is not the case. Thanks Aveek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Deleting the duplicate values in a column
SELECT * from table group by id having count = 1; On May 9, 2011, at 5:45 PM, abhishek jain wrote: hi, If we have a following mysql table: Name - ids A 1 B 1 C 2 D 3 I want to remove all duplicate occurances and have a result like Name - ids C 2 D 3 how can i do that with a query in mysql Pl. help asap -- 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?unsub=arch...@jab.org
Re: Deleting the duplicate values in a column
Sorry I jumped the gun, didn't realize it was a delete. The problem is that GROUP BY does not work with DELETE. You can try this: DELETE FROM mytable where id IN (SELECT id from my table GROUP BY id HAVING COUNT(*) 1); Not sure whether this will work though. You can always use a temp table for this purpose although that is not so appealing as doing it in one query CREATE TABLE tmp LIKE mytable; INSERT INTO tmp SELECT * from my table GROUP BY id HAVING COUNT(*) = 1; DROP TABLE mytable; RENAME TABLE tmp TO mytable; Thanks Aveek On May 9, 2011, at 7:54 PM, Aveek Misra wrote: SELECT * from table group by id having count = 1; On May 9, 2011, at 5:45 PM, abhishek jain wrote: hi, If we have a following mysql table: Name - ids A 1 B 1 C 2 D 3 I want to remove all duplicate occurances and have a result like Name - ids C 2 D 3 how can i do that with a query in mysql Pl. help asap -- 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?unsub=ave...@yahoo-inc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I can't have group as a column name in a table?
Use a quote around the column name or explicitly specify the column as table.column (as for e.g. mytable.group) in the query. For more details refer to http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html Thanks Aveek On Feb 24, 2011, at 4:36 PM, Dave M G wrote: MySQL users, Simple question: In one table in my database, the column was named group. I kept getting failed query errors until I renamed the column. I've never before encountered a situation where MySQL mistook a column name for part of the query syntax. Should I never use the word group for column names? Seems a little silly. Is there a way to protect column names to that there is no confusion? -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com
Re: select data from two tables and SUM qty of the same ID
SELECT product.ID, product.ProductName, product.ISBN, SUM(salesreport.QTY) as Total_Quantity FROM salesreport, product WHERE product.ID = salesreport.ID GROUP BY salesreport.ID ORDER BY Total_Quantity DESC; On Nov 10, 2010, at 3:53 PM, HaidarPesebe wrote: please help us look for the data select from two tables with the following details: Primary table: product ID | ProductName | ISBN | --- 1 | Product A | 123 | 2 | Product B | 456 | 3 | product C | 444 | --- second table: salesreport IDS | ID | CITY | QTY | 1| 1 | New York | 3 | 2| 1 | Alabama | 5 | 3| 1 | London| 1 | 4| 1 | Jakarta| 5 | 5| 2 | New York | 8 | 6| 2 | Alabama | 4 | 7| 2 | London| 9 | 8| 2 | Jakarta| 3 | -- ID in table product same with ID table salesreport How we will show SALES REPORT product is sold only at the table salesreport SUM qty to the top sold and unsold product C will not be displayed. The result will be like this: No. Product Name ISBN QTY 1. product B 45624 2. product A 12314 I'm using PHP. Thanks a lot for information. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
I don't see how BETWEEN is not equivalent to (startNum = and endNum =). Of course please try and let us know if that resolves the issue. But if it doesn't, I suspect it is because the indexes are created on columns which are floating point data type. That's because floating point numbers are approximate and not stored as exact values. Attempts to treat double values as exact in comparison may lead to the kind of issues that you are getting. I could be wrong though; but if Johan's trick does not work, you might try and change the data type to DECIMAL to see if it helps (or BIGINT if your numbers are not using any digits after the decimal since BIGINT and DOUBLE both use 8 bytes for storage). Thanks Aveek On Nov 9, 2010, at 1:21 PM, Johan De Meersman wrote: Indexes typically only work on the left-hand-side. Rewrite as select * from ip_test where startNum = 3061579775 and endNum = 3061579775; Magic will happen. 2010/11/9 wroxdb wro...@gmail.com Hello, I have a query below: mysql select * from ip_test where 3061579775 between startNum and endNum; +++-+--+--++ | startNum | endNum | country | province | city | isp| +++-+--+--++ | 3061514240 | 3061579775 | 中国| 河南 | | 联通 | +++-+--+--++ the desc shows it isn't using the index: mysql desc select * from ip_test where 3061579775 between startNum and endNum; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ 1 row in set (0.01 sec) the table structure is: CREATE TABLE `ip_test` ( `startNum` double(20,0) default NULL, `endNum` double(20,0) default NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 please help, thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
Probably indexes need to be rebuilt using myisamchk after you changed the data type of the index columns. Apart from that I can't see why your query is not using the indexes. Is it possible that the cardinality of the column values is so low that indexes are not being used? You could try and run a ANALYZE TABLE (or myismachk -a for MyISAM tables) and then a SHOW INDEX to see the cardinality information for these key columns. Thanks Aveek On Nov 9, 2010, at 3:43 PM, wroxdb wrote: Thanks for the idea. I have changed the datatype to bigint, the result is not changed. mysql desc select * from ip_test where startNum = 3061579775 and endNum = 3061579775; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ CREATE TABLE `ip_test` ( `startNum` bigint(20) NOT NULL, `endNum` bigint(20) NOT NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 在 2010年11月9日 下午5:20，Aveek Misra ave...@yahoo-inc.com 写道： I don't see how BETWEEN is not equivalent to (startNum = and endNum =). Of course please try and let us know if that resolves the issue. But if it doesn't, I suspect it is because the indexes are created on columns which are floating point data type. That's because floating point numbers are approximate and not stored as exact values. Attempts to treat double values as exact in comparison may lead to the kind of issues that you are getting. I could be wrong though; but if Johan's trick does not work, you might try and change the data type to DECIMAL to see if it helps (or BIGINT if your numbers are not using any digits after the decimal since BIGINT and DOUBLE both use 8 bytes for storage). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SHA1 returns binary value
Use UNHEX(SHA1('abc')) to get the string value
On Sep 17, 2010, at 5:38 PM, Tompkins Neil wrote:
Hi
Why when I run the command (MySQL 5.1) SELECT SHA1('abc'); is it returned as
a binary value and not a string value ?
Cheers
Neil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: query results group/summed by interval
try this ... select 5 * floor(seconds/5) as start, 5 * floor(seconds/5) + 5 as end, sum(calls) from calls group by 5 * floor(seconds/5); This should give you an output of the type +---+--++ | start | end | sum(calls) | +---+--++ | 0 |5 |387 | | 5 | 10 |225 | |10 | 15 | 74 | +---+--++ Thanks Aveek From: Ghulam Mustafa [mustafa...@gmail.com] Sent: Tuesday, July 27, 2010 3:53 PM To: mysql@lists.mysql.com Subject: query results group/summed by interval Hi everyone, i have two columns (seconds, number of calls), i need to produce a report which will show total number of calls in intervals (let'say 10 seconds interval), i know i can do this programmability in my script but i was wondering if it's possible to accomplish this behavior within mysql. for example i have following data. +--+---+ | calls | queue_seconds | +--+---+ | 250 | 0.00 | | 28 | 1.00 | | 30 | 2.00 | | 56 | 3.00 | | 23 | 4.00 | | 31 | 5.00 | | 33 | 6.00 | | 50 | 7.00 | | 49 | 8.00 | | 62 | 9.00 | | 74 | 10.00 | ... ... and so on... ... +--+---+ now result should look like this with a 5 seconds interval. +--+---+ | count(*) | queue_seconds | +--+---+ | 250 | 0.00 | | 168 | 5.00 | | 268 | 10.00 | ... ... and so on... ... +--+---+ i would really appreciate your help. Best Regards. -- Ghulam Mustafa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to get last record for each product
SELECT ProductID, MAX(OrderDate) FROM table GROUP BY ProductID; or if you want all the columns SELECT * FROM table a, (SELECT ProductID, MAX(OrderDate) as MaxDate FROM table GROUP BY ProductID) as b WHERE a.ProductID = b.ProductID AND a.OrderDate = b.MaxDate; Tompkins Neil wrote: Hi, I have a list of product orders in a table with the following structure : OrderID ProductID OrderDate OrderCost What query would I need to get the last order for each productID ? Cheers, Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Auto Increment in InnoDB
I have a InnoDB table which contains columns named 'cluster' and 'file'
('cluster' + 'file' is a primary key). I want to add a new column that
tracks the revision number of a file for a given cluster and a file. The
situation is tailor made for a MyIsam table where I can add a new Auto
Increment column as a secondary column in a multiple column index. How
can I get the same behavior in an InnoDB table? Given below is a view of
how the records will look like
| Cluster |File| Rev |
| clusterA | fileA | 1|
| clusterA | fileA | 2|
-
| clusterB | fileA | 1|
| clusterB | fileB | 1|
Thanks
Aveek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto Increment in InnoDB
I am not sure I understand. If I make the autoincrement column as part
of the primary key as (rev + cluster + file), how do I ensure that a
reset of the revision number is done as soon as (cluster + file)
combination changes? It looks like I need to do the following to mimic
the same behavior as that of an autoincrement column in MyISAM
SELECT @id := IFNULL(MAX(rev), 0) FROM table WHERE cluster='clusterA'
AND file='fileA' ;
SET @id := @id + 1;
INSERT INTO table (cluster, file, rev) VALUES ('clusterA', 'fileA', @id);
Additionally I guess the above needs to be encapsulated in a transaction
to ensure atomic updates to the 'rev' number for a given cluster and
file combination. Any thoughts?
Thanks
Aveek
Johan De Meersman wrote:
You can't, iirc - if you add an autoincrement to InnoDB it MUST be the
primary key.
You *can*, however, add that, set it as PK and stick a unique index on
(cluster, file) instead. Behaviour will be identical, but be aware
that there will be some performance implications - you will now have
to do an extra primary key lookup every time you select based on the
(cluster,file) key.
On Thu, Apr 22, 2010 at 7:03 AM, Aveek Misra ave...@yahoo-inc.com
mailto:ave...@yahoo-inc.com wrote:
I have a InnoDB table which contains columns named 'cluster' and
'file' ('cluster' + 'file' is a primary key). I want to add a new
column that tracks the revision number of a file for a given
cluster and a file. The situation is tailor made for a MyIsam
table where I can add a new Auto Increment column as a secondary
column in a multiple column index. How can I get the same behavior
in an InnoDB table? Given below is a view of how the records will
look like
| Cluster |File| Rev |
| clusterA | fileA | 1|
| clusterA | fileA | 2|
-
| clusterB | fileA | 1|
| clusterB | fileB | 1|
Thanks
Aveek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto Increment in InnoDB
MyISAM has this really cool feature where you can specify autoincrement on a secondary column in a multiple column index. In such a case the generated value for the autoincrement column is calculated as MAX(autoincrement column) + 1 WHERE prefix='given-prefix'. For more refer to http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html. This is exactly what I want, however I have an InnoDB table so this will not work (in an InnoDB table, you cannot specify autoincrement on a secondary column). So what I wanted to know was if there is some easy way to mimic that behavior. In my last mail that I sent, in order to mimic that functionality on InnoDB, I had to write several statements that possibly need to be a part of a transaction. Of course that also meant that I cannot specify that column as an autoincrement but instead specify it something as INT NOT NULL. Thanks Aveek Johan De Meersman wrote: On Thu, Apr 22, 2010 at 12:09 PM, Aveek Misra ave...@yahoo-inc.com wrote: I am not sure I understand. If I make the autoincrement column as part of the primary key as (rev + cluster + file), how do I ensure that a reset of the revision number is done as soon as (cluster + file) combination changes? You want the autoincrement to go up every time you change the values in your primary key ? Aside from it not being a recommended practice, to put it mildly, that you update primary key values (possible referential inconsistency), I'm not aware of this behaviour in MyISAM, either. An autoincrement is assigned if, and only if you assign NULL or (zero) to an autoincrement column during an insert. If your application behaves differently, it is probably already done either in the application, or possibly through the use of triggers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto Increment in InnoDB
How can count(*) in an InnoDB table be faster than MAX() considering that the former needs to do a table scan and the latter can use an index if correctly used? My code starts the sequence from 1. Thanks Aveek Johan De Meersman wrote: Kudos for managing to drag up such an obscure piece of functionality :-) I can see where it would be useful, though. As to your question, though: given that that page indicates that it will reuse deleted sequence numbers, I think your best bet would be select @id := count(*)+1 from table where cluster='clusterA' AND file='fileA' ; - should be slightly faster than a max(), I think. That in a trigger on your table should emulate the behaviour pretty closely. Am I mistaken, or does your code try to start from sequence 0 ? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
DELETE CASCADE
I have the following two tables CREATE TABLE `cfg_tags` ( `cluster` varbinary(128) NOT NULL, `tag` varbinary(128) NOT NULL, `user` varchar(40) NOT NULL, PRIMARY KEY (`cluster`,`tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `cfg_cluster_info` ( `cluster` varbinary(128) NOT NULL, `admin` varbinary(128) NOT NULL, PRIMARY KEY (`cluster`), CONSTRAINT `cfg_cluster_info_ibfk_1` FOREIGN KEY (`cluster`) REFERENCES `cfg_tags` (`cluster`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql select * from cfg_tags; +---+--++ | cluster | tag | user | +---+--++ | mycluster | tag1 | aveekm | | mycluster | tag2 | aveekm | +---+--++ Now when I delete one row from this table for the cluster 'mycluster', all the matching rows in the table cfg_cluster_info are deleted. However this is not what I intended. I want that the delete cascade should take effect when all the rows in cfg_tags with 'mycluster' are deleted. Should I then remove the delete cascade condition and take care of this myself? Thanks Aveek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Innodb Buffer Pool vs Query Cache
I am using the innodb storage engine for a table that is used for a lot of SELECT's on columns that are defined as indexes. I have not enabled the query cache as of now since the innodb buffer pool already caches data and index information for InnoDB tables. So my question is - is the query cache used primarily for MyISAM tables or is it also useful for InnoDB tables (considering that the InnoDB has a buffer pool in place)? If I do enable the query cache, is there an additional overhead of maintaining two buffers that essentially contain the same data? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
