Fulltext problem
Hi,
today I've experienced a problem that I don't understand and I can't solve!
I've created a table as follows:
---
CREATE TABLE `ft_test` (
`id` int(11) NOT NULL auto_increment,
`field1` varchar(255) NOT NULL ,
`field2` varchar(255) NOT NULL ,
`field3` text NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `field1` (`field1`),
FULLTEXT KEY `field2` (`field2`),
FULLTEXT KEY `field3` (`field3`),
FULLTEXT KEY `f1d2` (`field1`,`field2`)
) ENGINE=MyISAM CHARSET=utf8;
-
and then I've filled this table with the following statement:
-
insert into ft_test (field1,field2,field3)
VALUES('mysql full text', 'this is a test', 'mysql fulltext');
-
the problem is that when I execute this query I always get 0 as relevance:
select *, match(field1) against('mysql') as relevancy from ft_test;
id field1 field2 field3 relevancy
-- --- -- -- -
1 mysql full text this is a test mysql fulltext 0
I expected a value for relevancy! shouldn't I?
I tested with all other words and combination of fulltext index:
---
select *, match(field2) against('mysql') as relevancy from ft_test;
select *, match(field3) against('mysql') as relevancy from ft_test;
select *, match(field3) against('fulltext') as relevancy from ft_test;
---
but I always get 0!
This is a show variables of my mysql server
Variable_name Value
--
version 5.0.18-nt
ft_boolean_syntax + -()~*:|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
Any advice?
Thanks
Global Unique Identifiers
Hi All, I'm designing a database where it is required for each row in a table to have a unique identifier accross a bunch of running MySQL servers on a network. I'm considering two options: 1. Have a two-column primary key, where the first column is a normal auto incremented ID field, the second identifies the database and contains a constant ID per server. Something like this: CREATE TABLE MyTable ( ROWID int unsigned not null auto_increment, DBID int unsigned not null, AnyData varchar(10) not null, PRIMARY KEY (ROWID, DBID) ); INSERT INTO MyTable (DBID, AnyData) VALUES (8, 'Any text'); 2. I would use a traditional one-column binary primary key populated by the built-in uuid() fuction of MySQL. Like this: CREATE TABLE MyTable ( ID binary(36) not null, AnyData varchar(10) not null, PRIMARY KEY (ID) ); INSERT INTO MyTable (ID, AnyData) VALUES (uuid(), 'Any text'); In my view both solutions have their adventages and disadvantages. The first is more optimal in storage space and speed, the second is easier to maintain, administer and query. And there is another aspect, which is needed to be tested, I guess... How fast is the second solution when I execute complex queries based on primary key relations? Does it pay to use the ease of the second solution? Anyone has any experience in similar problems? What is your opinion? Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Foreign Key
Mungbeans, I do not understand how you get error 152 from the ALTER. ./include/my_base.h:355:#define HA_ERR_ROW_IS_REFERENCED 152 Please print SHOW INNODB STATUS\G after you get that error. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . From: Mungbeans Date: January 2 2007 12:56am Subject: Deleting Foreign Key Get Plain Text I have a foreign key in a table which I need to drop. I have successfully dropped other foreign keys from this table using phpMyAdmin. However when I use these commands I get similar error messages: ALTER TABLE `mytable` DROP FOREIGN KEY `keyname` #1025 - Error on rename of '.\database\mytable' to '.\database\#sql2-6ec-11' (errno: 152) ALTER TABLE `mytable` DROP INDEX `keyname` #1025 - Error on rename of '.\database\#sql-6ec_13' to '.\database\mytable' (errno: 150) Any clues as to what I need to do to get rid of this constraint? I want to delete the entire table structure and replace it with a different one and this is the only thing stopping me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext problem
devy wrote:
---cut---
-
insert into ft_test (field1,field2,field3)
VALUES('mysql full text', 'this is a test', 'mysql fulltext');
-
the problem is that when I execute this query I always get 0 as
relevance:
---cut---
A FULLTEXT search will not match return values that are within 50% of
the rows (or, index them) - this would just lead to returning too many
hits and make relevance not-so-relevant ;)
As you are only inserting one row, the this is certainly 50% of the
rows ;)
Add more rows, then re-run your test, keeping in mind the above.
Cheers,
Mark
--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB vs MyISAM
Hi, I have seen that by default some tables are created as InnoDB and some as MyISAM. I guess the table type is not chosen randomly. How is it chosen the table engine used? And is InnoDB recommended now? Does it support full text indexes? Or if not, is there a way of using full text indexes and foreign keys in MySQL? Thank you very much. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB vs MyISAM
On 2007-01-04 Octavian Rasnita wrote: I have seen that by default some tables are created as InnoDB and some as MyISAM. I guess the table type is not chosen randomly. How is it chosen the table engine used? You can set a global and IIRC a database specific default for the database type. And is InnoDB recommended now? It depends.. :) Does it support full text indexes? Or if not, is there a way of using full text indexes and foreign keys in MySQL? No fulltext indexes in InnoDB and no foreign keys in MyISAM as of 5.0. bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB vs MyISAM
InnoDB supports foreign keys, MyISAM does not. MyISAM supports full text indices, InnoDB does not. This is unfortunate. It has kept me using MyISAM where I'd rather use InnoDB, although fortunately none of my applications are really hampered by it. The only work-around I can think of is to create a separate MyISAM table that contains all of the fields you need for a full text index, and that links back to the InnoDB table. This is very wasteful, but at least the main table will have foreign keys and transaction recovery. The index table would be easy to recreate if it were lost. In fact, you might want to rebuild it periodically rather than maintain it in real time. This would make your application run faster when doing updates and inserts, if you can tolerate an index that isn't up-to-the-minute. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Octavian Rasnita [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:38 AM To: mysql@lists.mysql.com Subject: InnoDB vs MyISAM Hi, I have seen that by default some tables are created as InnoDB and some as MyISAM. I guess the table type is not chosen randomly. How is it chosen the table engine used? And is InnoDB recommended now? Does it support full text indexes? Or if not, is there a way of using full text indexes and foreign keys in MySQL? Thank you very much. Octavian -- 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: Fulltext problem
Hi Devy --
There are a couple of issues with your query below, and hopefully we can help
you figure it out.
First off, your table and query structure are fine. However, one can ask why
not use a TINYTEXT or even a TEXT field instead of VARCHAR(255). It's all in
the memory overhead. =)
Moving on, it's not that you don't have a relevance, it's that you don't have
enough records in your database to compare the MATCH() to. Read
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html for more
information, but pay particular attention to the last 4 paragraphs.
Your search text must match at most 49% of the rows in the database, or a 0
relevancy will appear. Since, in your test case, it matches 100%, you won't get
a relevance. Try adding 5-6 more records to your table and then do a search
with MySQL in the field1.
Try this:
CREATE TABLE as below.
INSERT INTO ft_test (field1,field2,field3) VALUES
('mysql full text', 'this is a test', 'mysql fulltext'),
('Email Tutorial','DBMS stands for DataBase ...','hi mom'),
('How To Use Yahoo Well','After you went through a ...','hi dad'),
('Optimizing your databases','In this tutorial we will show ...','hi sis'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...','hi bro'),
('MSSQL vs. YourSQL','In the following database comparison ...','hi uncle'),
('SQL Injection Security','When configured properly, MySQL ...','hi aunt');
Then, when you run your query, SELECT *, MATCH(field1) AGAINST('mysql') as
relevancy FROM ft_test; you get
id, field1, field2, field3, relevance
1, 'mysql full text', 'this is a test', 'mysql fulltext', 0.88573294878006
2, 'Email Tutorial', 'DBMS stands for DataBase ...', 'hi mom', 0
3, 'How To Use Yahoo Well', 'After you went through a ...', 'hi dad', 0
4, 'Optimizing your databases', 'In this tutorial we will show ...', 'hi sis', 0
5, '1001 MySQL Tricks', '1. Never run mysqld as root. 2. ...', 'hi bro',
0.88573294878006
6, 'MSSQL vs. YourSQL', 'In the following database comparison ...', 'hi uncle', 0
7, 'SQL Injection Security', 'When configured properly, MySQL ...', 'hi aunt',
0
(sorry about the spacing..., thanks to MySQL manual for the source of inserts)
Notice that in the last column, the relevance is 0.88 in two of the fields, as
MySQL is in those fields and no other.
Then you can try other search terms in other fields, such as SELECT *,
MATCH(field2) AGAINST('database') as relevancy FROM ft_test; . When looking at
the manual for the FULLTEXT searches, also make sure to pay attention to the
STOPWORDS section, IGNORED WORDS section, and the ft_min_word and ft_max_word
. The default minimum word length for FULLTEXT searches is 4, unless you change
it in the VARIABLES.
HTH!
J.R.
From: devy [EMAIL PROTECTED]
Sent: Thursday, January 04, 2007 8:54 AM
To: mysql@lists.mysql.com, [EMAIL PROTECTED]
Subject: Fulltext problem
Hi,
today I've experienced a problem that I don't understand and I can't solve!
I've created a table as follows:
---
CREATE TABLE `ft_test` (
`id` int(11) NOT NULL auto_increment,
`field1` varchar(255) NOT NULL ,
`field2` varchar(255) NOT NULL ,
`field3` text NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `field1` (`field1`),
FULLTEXT KEY `field2` (`field2`),
FULLTEXT KEY `field3` (`field3`),
FULLTEXT KEY `f1d2` (`field1`,`field2`)
) ENGINE=MyISAM CHARSET=utf8;
-
and then I've filled this table with the following statement:
-
insert into ft_test (field1,field2,field3)
VALUES('mysql full text', 'this is a test', 'mysql fulltext');
-
the problem is that when I execute this query I always get 0 as relevance:
select *, match(field1) against('mysql') as relevancy from ft_test;
id field1 field2 field3 relevancy
-- --- -- -- -
1 mysql full text this is a test mysql fulltext 0
I expected a value for relevancy! shouldn't I?
I tested with all other words and combination of fulltext index:
---
select *, match(field2) against('mysql') as relevancy from ft_test;
select *, match(field3) against('mysql') as relevancy from ft_test;
select *, match(field3) against('fulltext') as relevancy from ft_test;
---
but I always get 0!
This is a show variables of my mysql server
Variable_name Value
--
version 5.0.18-nt
ft_boolean_syntax + -()~*:|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
Any advice?
Thanks
Re: Deleting Foreign Key
Mungbeans wrote: ALTER TABLE `mytable` DROP FOREIGN KEY `keyname` #1025 - Error on rename of '.\database\mytable' to '.\database\#sql2-6ec-11' (errno: 152) ALTER TABLE `mytable` DROP INDEX `keyname` #1025 - Error on rename of '.\database\#sql-6ec_13' to '.\database\mytable' (errno: 150 Oh these are fun. Often times this is because adding foreign keys also adds an index key to the table (on top of the reference key). Why I don't know, but I'm assuming it adds it for optimization since it knows you'll be hitting that column (otherwise you wouldn't have created the reference in the first place). Moving along... what I do first is SHOW CREATE TABLE `table_name`: | table_name | CREATE TABLE `table_name` ( `id` int(20) unsigned NOT NULL auto_increment, `key_column` smallint(5) unsigned default '1', KEY `key_column` (`key_column`), CONSTRAINT `table_name_ibfk_1` FOREIGN KEY (`key_column`) REFERENCES `second_table` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | So, first you remove the key: ALTER TABLE table_name DROP KEY `key_column`; then the foreign key: ALTER TABLE table_name DROP FOREIGN KEY `table_name_ibfk_1`; Then that should do it. Also note that might also fail if you have, say, another table foreign key referencing to `key_column`. Hope this helps, I also wrote this at about 8AM in the morning while slowly taking in caffeine, so be warned :). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB vs MyISAM
And is InnoDB recommended now? It depends.. :) Depends on... what? I mean, if I don't need transactions, is there another reason for using InnoDB? If it is necessary I can build the client program without foreign keys support also. Thanks. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB vs MyISAM
Octavian, 1) You can use MyISAM for example when you use static information in a webpage. For example, only for store information of customers, something like that.. 2) Innodb is a engine that support ACID, you can use for transactions. For example, load information of sales from PDA ( field) of a lot of sales man ( 1000). please see below http://en.wikipedia.org/wiki/ACID Bye, Juan On 1/4/07, Octavian Rasnita [EMAIL PROTECTED] wrote: And is InnoDB recommended now? It depends.. :) Depends on... what? I mean, if I don't need transactions, is there another reason for using InnoDB? If it is necessary I can build the client program without foreign keys support also. Thanks. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
group by/select issue..
hi... i have the following test tbl dog name char statusint idint test data dog name status id tom 1 1 tom 2 2 sue 1 3 tom 3 4 sue 2 5 bob 1 6 i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' i'd also like to be able to get a single row for 'bob' if i wanted the 'name' (group) that do not have a status=2. i'm not sure how to craft the select using the group by/distinct, and i couldn't find examples via google to solve this... once i get my hands around this, i can apply it to a test tbl of 2000-3000 rows... thanks bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by/select issue..
bruce wrote: i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;` wouldn't give you what you'd want (or that's possibly what you're looking for?). If that's the answer then wee, if not I'll throw my lost flag in the air. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by/select issue..
hi chris... your query, SELECT name FROM dog WHERE status = 3 GROUP BY name will actually give the items where status=3 however, i can't get the resulting issues by doing 'status!=3', because the tbl has multiple status for a given name, so the query will still return the other status that aren't equal to '3' for the given name... -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:07 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: group by/select issue.. bruce wrote: i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;` wouldn't give you what you'd want (or that's possibly what you're looking for?). If that's the answer then wee, if not I'll throw my lost flag in the air. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error: Duplicate entry 'xxxx-L' for key 6
Key 6 relates to the 6th key, or index, in the CREATE TABLE statement for this table: PRIMARY KEY (`prod_id`), UNIQUE KEY `prod_no` (`prod_no`), KEY `products_index1` (`prod_status`), KEY `products_index2` (`prod_start_date`,`prod_end_date`), KEY `on_sale` (`on_sale`), FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`), FULLTEXT KEY `prod_no_2` (`prod_no`) Key is another name for Index, so this is about your FULLTEXT index: FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`) I noticed that the INSERT you listed was for the products table and the CREATE TABLE was for the web_products table. Just to be sure, please can you post the CREATE TABLE statement for the products table? Im -- http://www.atomdatabase.com MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by/select issue..
Bruce, Try: SELECT DISTINCT NAME FROM DOG WHERE STATUS != 3 Should do the trick. You obviously don't want the STATUS field. If you include it, you'll get more than one line per name. Similarly for ID. If you want to include the STATUS or ID fields, then you obviously want more than one line (otherwise what would you expect to go in there?). HTH Peter Ysgrifennodd bruce: hi chris... your query, SELECT name FROM dog WHERE status = 3 GROUP BY name will actually give the items where status=3 however, i can't get the resulting issues by doing 'status!=3', because the tbl has multiple status for a given name, so the query will still return the other status that aren't equal to '3' for the given name... -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:07 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: group by/select issue.. bruce wrote: i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;` wouldn't give you what you'd want (or that's possibly what you're looking for?). If that's the answer then wee, if not I'll throw my lost flag in the air. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by/select issue..
hi peter i must be missing something. the following is my actual schema. i have a test tbl with ~2900 rows... only a few of the rows have an actionID=3. each universityID can have multiple actionIDs mysql describe SvnTBL; +--+--+--+-+++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+---++ | universityID | int | NO | | 0 || | actionID | int | NO | | 0 || | statusID | int | NO | | 0 || | _date| timestamp| YES | | CURRENT_TIMESTAMP | | | ID | int | NO | PRI | NULL | auto_increment | | semseterID | int | NO | | 0 || +--+---+--+-+---++ 6 rows in set (0.09 sec) when i do: select distinct universityID, from SvnTBL where actionID !=3; i get return of 2879 rows, which is the same thing i get when i do: select distinct universityID, from SvnTBL; when i do: mysql select universityID, actionID from SvnTBL - where actionID =3; +--+--+ | universityID | actionID | +--+--+ |1 |3 | |2 |3 | |3 |3 | +--+--+ 3 rows in set (0.00 sec) which tells me that i have 3 'groups' (on universityID) that have actionID=3. however, each of these universityID, can also have actionID=(1,2) as well. so how can a query be created to return the universityID (groups) that don't have an actionID=3... when i tried, SELECT DISTINCT universityID FROM SvnTBL WHERE actionID != 3 i got the same as if i did: SELECT DISTINCT universityID FROM SvnTBL; thanks.. -Original Message- From: Peter Bradley [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:32 AM To: [EMAIL PROTECTED] Cc: 'Chris White'; mysql@lists.mysql.com Subject: Re: group by/select issue.. Bruce, Try: SELECT DISTINCT NAME FROM DOG WHERE STATUS != 3 Should do the trick. You obviously don't want the STATUS field. If you include it, you'll get more than one line per name. Similarly for ID. If you want to include the STATUS or ID fields, then you obviously want more than one line (otherwise what would you expect to go in there?). HTH Peter Ysgrifennodd bruce: hi chris... your query, SELECT name FROM dog WHERE status = 3 GROUP BY name will actually give the items where status=3 however, i can't get the resulting issues by doing 'status!=3', because the tbl has multiple status for a given name, so the query will still return the other status that aren't equal to '3' for the given name... -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:07 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: group by/select issue.. bruce wrote: i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;` wouldn't give you what you'd want (or that's possibly what you're looking for?). If that's the answer then wee, if not I'll throw my lost flag in the air. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by/select issue..
Use a derived table (untested query): select distinct universityID from SvnTBL s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.university ID is NULL I'm not sure if derived tables are in all versions of MySQL, I use MySQL 5.0. If your tables are big, you'll probably need to add an index on universityID. Hope that helps. Donna bruce [EMAIL PROTECTED] 01/04/2007 12:49 PM Please respond to [EMAIL PROTECTED] To 'Peter Bradley' [EMAIL PROTECTED] cc 'Chris White' [EMAIL PROTECTED], mysql@lists.mysql.com Subject RE: group by/select issue.. hi peter i must be missing something. the following is my actual schema. i have a test tbl with ~2900 rows... only a few of the rows have an actionID=3. each universityID can have multiple actionIDs mysql describe SvnTBL; +--+--+--+-+++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+---++ | universityID | int | NO | | 0 || | actionID | int | NO | | 0 || | statusID | int | NO | | 0 || | _date| timestamp| YES | | CURRENT_TIMESTAMP | | | ID | int | NO | PRI | NULL | auto_increment | | semseterID | int | NO | | 0 || +--+---+--+-+---++ 6 rows in set (0.09 sec) when i do: select distinct universityID, from SvnTBL where actionID !=3; i get return of 2879 rows, which is the same thing i get when i do: select distinct universityID, from SvnTBL; when i do: mysql select universityID, actionID from SvnTBL - where actionID =3; +--+--+ | universityID | actionID | +--+--+ |1 |3 | |2 |3 | |3 |3 | +--+--+ 3 rows in set (0.00 sec) which tells me that i have 3 'groups' (on universityID) that have actionID=3. however, each of these universityID, can also have actionID=(1,2) as well. so how can a query be created to return the universityID (groups) that don't have an actionID=3... when i tried, SELECT DISTINCT universityID FROM SvnTBL WHERE actionID != 3 i got the same as if i did: SELECT DISTINCT universityID FROM SvnTBL; thanks.. -Original Message- From: Peter Bradley [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:32 AM To: [EMAIL PROTECTED] Cc: 'Chris White'; mysql@lists.mysql.com Subject: Re: group by/select issue.. Bruce, Try: SELECT DISTINCT NAME FROM DOG WHERE STATUS != 3 Should do the trick. You obviously don't want the STATUS field. If you include it, you'll get more than one line per name. Similarly for ID. If you want to include the STATUS or ID fields, then you obviously want more than one line (otherwise what would you expect to go in there?). HTH Peter Ysgrifennodd bruce: hi chris... your query, SELECT name FROM dog WHERE status = 3 GROUP BY name will actually give the items where status=3 however, i can't get the resulting issues by doing 'status!=3', because the tbl has multiple status for a given name, so the query will still return the other status that aren't equal to '3' for the given name... -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:07 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: group by/select issue.. bruce wrote: i'm trying to figure out how to create a select query that groups the tbl around 'name' such that if i want all names that do not have a status=3, i'd get a single row for 'sue' and 'bob' I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;` wouldn't give you what you'd want (or that's possibly what you're looking for?). If that's the answer then wee, if not I'll throw my lost flag in the air. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the
RE: Fulltext problem
With so few rows, are you hitting the 50% rule? Try again with a decent
number of rows.
-Original Message-
From: devy [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 04, 2007 5:46 AM
To: mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: Fulltext problem
Hi,
today I've experienced a problem that I don't understand and
I can't solve!
I've created a table as follows:
---
CREATE TABLE `ft_test` (
`id` int(11) NOT NULL auto_increment,
`field1` varchar(255) NOT NULL ,
`field2` varchar(255) NOT NULL ,
`field3` text NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `field1` (`field1`),
FULLTEXT KEY `field2` (`field2`),
FULLTEXT KEY `field3` (`field3`),
FULLTEXT KEY `f1d2` (`field1`,`field2`)
) ENGINE=MyISAM CHARSET=utf8;
-
and then I've filled this table with the following statement:
-
insert into ft_test (field1,field2,field3)
VALUES('mysql full text', 'this is a test', 'mysql fulltext');
-
the problem is that when I execute this query I always get 0
as relevance:
select *, match(field1) against('mysql') as relevancy from ft_test;
id field1 field2 field3 relevancy
-- --- -- -- -
1 mysql full text this is a test mysql fulltext 0
I expected a value for relevancy! shouldn't I?
I tested with all other words and combination of fulltext index:
---
select *, match(field2) against('mysql') as relevancy from ft_test;
select *, match(field3) against('mysql') as relevancy from ft_test;
select *, match(field3) against('fulltext') as relevancy from ft_test;
---
but I always get 0!
This is a show variables of my mysql server
Variable_name Value
--
version 5.0.18-nt
ft_boolean_syntax + -()~*:|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
Any advice?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by/select issue..
thanks for the derived tbl approach. it solved my 1st problem/issue. the final query that i used is: select distinct s1.universityID from SvnTBL as s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.universityID is null; this works, in that i get the unique universityID data... i now have two additional questions... 1) in the SvnTBL, how can i also get the actionID value? if i attempt to do something like: select distinct s1.universityID, s1.actionID from SvnTBL as s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ONs1.universityID=s2.universityID where s2.universityID is null group by universityID; the query eventually returns with what appears to be the correct information. i get a distinct universityID/actionID, but the query takes ~65 secs to run... the tbl only has ~2900 rows... 2) also, if i want to do a join with another tbl, where i also want to have the select pull information from the joined tbl, is there a 'better' way to handle this... the 2nd tbl is: mysql describe universityTBL; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | name | varchar(75) | NO | UNI | NULL|| | svn_dir_name | varchar(50) | NO | | NULL|| | city | varchar(20) | YES | | NULL|| | stateVAL | varchar(5) | NO | | NULL|| | userID| int(10) | NO | | 0 || | ID| int(10) | NO | PRI | NULL| auto_increment | | parsefilename | varchar(50) | NO | | NULL|| | statusID | int(1) | NO | | 1 || +---+-+--+-+-++ 8 rows in set (0.01 sec) the join would take place on SvnTBL.universityID=universityTBL.ID thanks for helping me to see what's going on... my initial approach is to simply do the unique select on only the SvnTBL, and then have an iterative loop through the resulting data, where i then query the universityTBL each time... however, this results in the app having to hit the db a number of times... thoughts/comments/ thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 10:01 AM To: [EMAIL PROTECTED] Cc: 'Chris White'; mysql@lists.mysql.com; 'Peter Bradley' Subject: RE: group by/select issue.. Use a derived table (untested query): select distinct universityID from SvnTBL s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.university ID is NULL I'm not sure if derived tables are in all versions of MySQL, I use MySQL 5.0. If your tables are big, you'll probably need to add an index on universityID. Hope that helps. Donna bruce [EMAIL PROTECTED] 01/04/2007 12:49 PM Please respond to [EMAIL PROTECTED] To 'Peter Bradley' [EMAIL PROTECTED] cc 'Chris White' [EMAIL PROTECTED], mysql@lists.mysql.com Subject RE: group by/select issue.. hi peter i must be missing something. the following is my actual schema. i have a test tbl with ~2900 rows... only a few of the rows have an actionID=3. each universityID can have multiple actionIDs mysql describe SvnTBL; +--+--+--+-+++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+---++ | universityID | int | NO | | 0 || | actionID | int | NO | | 0 || | statusID | int | NO | | 0 || | _date| timestamp| YES | | CURRENT_TIMESTAMP | | | ID | int | NO | PRI | NULL | auto_increment | | semseterID | int | NO | | 0 || +--+---+--+-+---++ 6 rows in set (0.09 sec) when i do: select distinct universityID, from SvnTBL where actionID !=3; i get return of 2879 rows, which is the same thing i get when i do: select distinct universityID, from SvnTBL; when i do: mysql select universityID, actionID from SvnTBL - where actionID =3; +--+--+ | universityID | actionID | +--+--+ |1 |3 | |2 |3 | |3 |3 | +--+--+ 3 rows in set (0.00 sec) which tells me that i have 3 'groups' (on universityID) that have actionID=3. however, each of these universityID, can also have actionID=(1,2) as well. so how can a query be
RE: Error: Duplicate entry 'xxxx-L' for key 6
This is structure of the table before I fixed.
CREATE TABLE `products` (
`prod_id` int(8) unsigned NOT NULL auto_increment,
`prod_no` varchar(50) NOT NULL default '',
`prod_name` varchar(255) NOT NULL default '',
`prod_description` text,
`prod_colors` text,
`prod_includes` text,
`prod_catalog` varchar(45) default NULL,
`prod_status` enum('hidden','live','new') NOT NULL default 'new',
`prod_supplier` varchar(45) default NULL,
`prod_start_date` date default '-00-00',
`prod_end_date` date default '-00-00',
`prod_featured` enum('0','1') default NULL,
`on_sale` enum('Yes','No') NOT NULL default 'No',
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_no` (`prod_no`),
KEY `products_index1` (`prod_status`),
KEY `products_index2` (`prod_start_date`,`prod_end_date`),
KEY `on_sale` (`on_sale`),
FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`),
FULLTEXT KEY `prod_no_2` (`prod_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6151 ;
And, after I deleted prod_no_2 key (it doesn't make a sence to have it
though) everything was ok. I'm no t getting error message anymore.
-afan
Key 6 relates to the 6th key, or index, in the CREATE TABLE statement
for this table:
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_no` (`prod_no`),
KEY `products_index1` (`prod_status`),
KEY `products_index2` (`prod_start_date`,`prod_end_date`),
KEY `on_sale` (`on_sale`),
FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`),
FULLTEXT KEY `prod_no_2` (`prod_no`)
Key is another name for Index, so this is about your FULLTEXT index:
FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`)
I noticed that the INSERT you listed was for the products table and
the CREATE TABLE was for the web_products table.
Just to be sure, please can you post the CREATE TABLE statement for
the products table?
Im
--
http://www.atomdatabase.com
MySQL Database Management Design Services
--
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: group by/select issue..
select s1.universityID, s1.actionID, ut.svn_dir_name (or other columns of your choice) from from SvnTBL as s1 inner join universityTBL ut on s1.univeristyID=ut.ID left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.universityID is null; I think you should put indexes, if you don't already have them, on SvnTBL.universityID and universityTBL.id. You also shouldn't need the group by you have in your first query below, but I can't tell you if that is hurting performance. Try putting EXPLAIN in front of the query and it'll give you some details of the query plan. Donna bruce [EMAIL PROTECTED] 01/04/2007 01:45 PM Please respond to [EMAIL PROTECTED] To [EMAIL PROTECTED] cc mysql@lists.mysql.com Subject RE: group by/select issue.. thanks for the derived tbl approach. it solved my 1st problem/issue. the final query that i used is: select distinct s1.universityID from SvnTBL as s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.universityID is null; this works, in that i get the unique universityID data... i now have two additional questions... 1) in the SvnTBL, how can i also get the actionID value? if i attempt to do something like: select distinct s1.universityID, s1.actionID from SvnTBL as s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ONs1.universityID=s2.universityID where s2.universityID is null group by universityID; the query eventually returns with what appears to be the correct information. i get a distinct universityID/actionID, but the query takes ~65 secs to run... the tbl only has ~2900 rows... 2) also, if i want to do a join with another tbl, where i also want to have the select pull information from the joined tbl, is there a 'better' way to handle this... the 2nd tbl is: mysql describe universityTBL; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | name | varchar(75) | NO | UNI | NULL|| | svn_dir_name | varchar(50) | NO | | NULL|| | city | varchar(20) | YES | | NULL|| | stateVAL | varchar(5) | NO | | NULL|| | userID| int(10) | NO | | 0 || | ID| int(10) | NO | PRI | NULL| auto_increment | | parsefilename | varchar(50) | NO | | NULL|| | statusID | int(1) | NO | | 1 || +---+-+--+-+-++ 8 rows in set (0.01 sec) the join would take place on SvnTBL.universityID=universityTBL.ID thanks for helping me to see what's going on... my initial approach is to simply do the unique select on only the SvnTBL, and then have an iterative loop through the resulting data, where i then query the universityTBL each time... however, this results in the app having to hit the db a number of times... thoughts/comments/ thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 10:01 AM To: [EMAIL PROTECTED] Cc: 'Chris White'; mysql@lists.mysql.com; 'Peter Bradley' Subject: RE: group by/select issue.. Use a derived table (untested query): select distinct universityID from SvnTBL s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.university ID is NULL I'm not sure if derived tables are in all versions of MySQL, I use MySQL 5.0. If your tables are big, you'll probably need to add an index on universityID. Hope that helps. Donna bruce [EMAIL PROTECTED] 01/04/2007 12:49 PM Please respond to [EMAIL PROTECTED] To 'Peter Bradley' [EMAIL PROTECTED] cc 'Chris White' [EMAIL PROTECTED], mysql@lists.mysql.com Subject RE: group by/select issue.. hi peter i must be missing something. the following is my actual schema. i have a test tbl with ~2900 rows... only a few of the rows have an actionID=3. each universityID can have multiple actionIDs mysql describe SvnTBL; +--+--+--+-+++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+---++ | universityID | int | NO | | 0 || | actionID | int | NO | | 0 || | statusID | int | NO | | 0 || | _date| timestamp| YES | | CURRENT_TIMESTAMP | | | ID | int | NO | PRI | NULL | auto_increment | | semseterID | int
Re: Global Unique Identifiers
On 1/4/07, Daniel Kiss [EMAIL PROTECTED] wrote: Hi All, I'm designing a database where it is required for each row in a table to have a unique identifier accross a bunch of running MySQL servers on a network. I'm considering two options: 1. Have a two-column primary key, where the first column is a normal auto incremented ID field, the second identifies the database and contains a constant ID per server. Something like this: CREATE TABLE MyTable ( ROWID int unsigned not null auto_increment, DBID int unsigned not null, AnyData varchar(10) not null, PRIMARY KEY (ROWID, DBID) ); INSERT INTO MyTable (DBID, AnyData) VALUES (8, 'Any text'); 2. I would use a traditional one-column binary primary key populated by the built-in uuid() fuction of MySQL. Like this: CREATE TABLE MyTable ( ID binary(36) not null, AnyData varchar(10) not null, PRIMARY KEY (ID) ); INSERT INTO MyTable (ID, AnyData) VALUES (uuid(), 'Any text'); In my view both solutions have their adventages and disadvantages. The first is more optimal in storage space and speed, the second is easier to maintain, administer and query. And there is another aspect, which is needed to be tested, I guess... How fast is the second solution when I execute complex queries based on primary key relations? Does it pay to use the ease of the second solution? Anyone has any experience in similar problems? What is your opinion? Human readability is important in case something goes wrong, and I like the first solution better. I looked at the format of UUID in the MySQL documentation ... not all that human-friendly. In all the database code I've written (admittedly, all on a single server), I've always had a function (written in PHP) that returns server unique identifiers (and a globally unique identifier is along the same lines). They have typically been fairly long character strings that include the Unix time (seconds and microseconds) and the PID. I typically also spin-lock until the microtime changes--that way it is guaranteed that two processes can't have the same PID at the same time. I'd say go with (a)generation that you understand and control, and (b)human-readability in case you have to dissect it. The server-unique identifiers that I described above have the advantage that they double as timestamps. Just an opinion. Dave.
5.1.14-beta with ssl build failure
I attempted to build 5.1.14-beta with ssl support and it failed (output below). It builds quite happily on this system without the ssl support parameter to configure. Since it appears to fail in an area (timezone system) that to me seems rather unrelated, I wondered if something odd was afoot. x86-suse9.3, openssl-0.9.8d, gcc-3.4.6 (or gcc-4.1.1) using: CFLAGS=-O3 CXX=gcc \ CXXFLAGS=-O3 -felide-constructors \ -fno-exceptions -fno-rtti \ ./configure --with-ndbcluster \ --with-ssl=/usr/local/openssl \ --enable-assembler make Any suggestions? -- Duncan Hutty System Administrator, ECE Carnegie Mellon University gcc -O3 -felide-constructors -fno-exceptions -fno-rtti -fno-implicit-templates -fno-exceptions -fno-rtti -rdynamic -o mysql_tzinfo_to_sql mysql_tzinfo_to_sql.o ../vio/libvio.a ../mysys/libmysys.a ../dbug/libdbug.a ../regex/libregex.a ../strings/libmystrings.a -lz -lpthread -lcrypt -lnsl -lm -lpthread /usr/lib/crt1.o(.text+0x18): In function `_start': ../sysdeps/i386/elf/start.S:115: undefined reference to `main' mysql_tzinfo_to_sql.o(.text+0x3ab): In function `Time_zone_system::TIME_to_gmt_sec(st_mysql_time const*, char*) const': mysql_tzinfo_to_sql.cc: undefined reference to `my_system_gmt_sec' mysql_tzinfo_to_sql.o(.text+0x3ef): In function `Time_zone_system::gmt_sec_to_TIME(st_mysql_time*, long) const': mysql_tzinfo_to_sql.cc: undefined reference to `localtime_to_TIME(st_mysql_time*, tm*)' mysql_tzinfo_to_sql.o(.text+0x45f): In function `Time_zone_utc::gmt_sec_to_TIME(st_mysql_time*, long) const': mysql_tzinfo_to_sql.cc: undefined reference to `localtime_to_TIME(st_mysql_time*, tm*)' mysql_tzinfo_to_sql.o(.text+0x167a): In function `my_tz_find(String const*, st_table_list*)': mysql_tzinfo_to_sql.cc: undefined reference to `sql_print_error(char const*, ...)' mysql_tzinfo_to_sql.o(.text+0x1729):mysql_tzinfo_to_sql.cc: undefined reference to `sql_print_error(char const*, ...)' mysql_tzinfo_to_sql.o(.text+0x1749):mysql_tzinfo_to_sql.cc: undefined reference to `sql_print_error(char const*, ...)' mysql_tzinfo_to_sql.o(.text+0x1764):mysql_tzinfo_to_sql.cc: undefined reference to `sql_print_error(char const*, ...)' mysql_tzinfo_to_sql.o(.text+0x1e91):mysql_tzinfo_to_sql.cc: undefined reference to `sql_print_error(char const*, ...)' mysql_tzinfo_to_sql.o(.text+0x1fe9):mysql_tzinfo_to_sql.cc: more undefined references to `sql_print_error(char const*, ...)' follow mysql_tzinfo_to_sql.o(.text+0x20e7): In function `my_tz_init(THD*, char const*, char)': mysql_tzinfo_to_sql.cc: undefined reference to `THD::THD()' mysql_tzinfo_to_sql.o(.text+0x2117):mysql_tzinfo_to_sql.cc: undefined reference to `THD::store_globals()' mysql_tzinfo_to_sql.o(.text+0x23c4):mysql_tzinfo_to_sql.cc: undefined reference to `simple_open_n_lock_tables(THD*, st_table_list*)' mysql_tzinfo_to_sql.o(.text+0x24ed):mysql_tzinfo_to_sql.cc: undefined reference to `sql_print_error(char const*, ...)' mysql_tzinfo_to_sql.o(.text+0x250e):mysql_tzinfo_to_sql.cc: undefined reference to `sql_print_warning(char const*, ...)' mysql_tzinfo_to_sql.o(.text+0x2571):mysql_tzinfo_to_sql.cc: undefined reference to `global_system_variables' mysql_tzinfo_to_sql.o(.text+0x25a6):mysql_tzinfo_to_sql.cc: undefined reference to `close_thread_tables(THD*, bool, bool)' mysql_tzinfo_to_sql.o(.text+0x25d8):mysql_tzinfo_to_sql.cc: undefined reference to `THD::store_globals()' mysql_tzinfo_to_sql.o(.text+0x25f6):mysql_tzinfo_to_sql.cc: undefined reference to `sql_print_error(char const*, ...)' mysql_tzinfo_to_sql.o(.text+0x2615):mysql_tzinfo_to_sql.cc: undefined reference to `THR_THD' mysql_tzinfo_to_sql.o(.text+0x262f):mysql_tzinfo_to_sql.cc: undefined reference to `THR_MALLOC' mysql_tzinfo_to_sql.o(.text+0x2645):mysql_tzinfo_to_sql.cc: undefined reference to `sql_print_error(char const*, ...)' mysql_tzinfo_to_sql.o(.text+0x268b):mysql_tzinfo_to_sql.cc: undefined reference to `sql_print_error(char const*, ...)' mysql_tzinfo_to_sql.o(.text+0x26de):mysql_tzinfo_to_sql.cc: undefined reference to `sql_print_error(char const*, ...)' mysql_tzinfo_to_sql.o(.text+0x2748):mysql_tzinfo_to_sql.cc: undefined reference to `sql_print_error(char const*, ...)' mysql_tzinfo_to_sql.o(.text+0x2770):mysql_tzinfo_to_sql.cc: undefined reference to `sql_print_error(char const*, ...)' mysql_tzinfo_to_sql.o(.text+0x28cb): In function `my_tz_find_with_opening_tz_tables(THD*, String const*)': mysql_tzinfo_to_sql.cc: undefined reference to `simple_open_n_lock_tables(THD*, st_table_list*)' mysql_tzinfo_to_sql.o(.text+0x28fd):mysql_tzinfo_to_sql.cc: undefined reference to `close_thread_tables(THD*, bool, bool)' collect2: ld returned 1 exit status make[4]: *** [mysql_tzinfo_to_sql] Error 1 make[4]: Leaving directory `/home/dhutty/build/mysql-5.1.14-beta/sql' make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory `/home/dhutty/build/mysql-5.1.14-beta/sql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/home/dhutty/build/mysql-5.1.14-beta/sql'
Re: How scaleable is MySQL 5's Innodb with multi-core CPU's?
what do you consider a high number of updates/sec? I'm the DBA for a popular website in that league (well, maybe not google or yahoo but certainly ticketmaster) and we average ~210 DMLs/sec with peaks in excess of 1,000. we use a mixture of myisam for static (or infrequently updated) reference tables and innodb for the high DML ones. in benchmark tests (re: load/stability) I was able to sustain ~4,500 selects/sec against this for eight hours without any problem. this on only a dual Opteron. throw in a good (at least I like to think) replication implementation and we've gone as high as 14,000 selects/sec under high site load (we have enough slaves to do WAY more). I think the reason we have not seen the innodb performance degredation you are describing is that we serialize all our DML into the main replication master across eight channels (by type of data being updated) - data change requests come in as XML, are parsed by proprietary (re: homebrew) code and translated into DML which is then executed on the replication master. this ends up looking someting like Oracle AQ (or concurrent managers in ERP) but way more stable (we used to get ORA-00600s like most people get 942s for the Oracle literate out there). we never have more than 10 threads concurrently doing DML operations. so to answer your original ? we (a big, well-known website) use a mixture of table handlers, serialized DML and a bunch of replication. I know every site is different (ours happens to be ideally suited to MySQL's replication architecture) and there are certainly sites/apps with much higher requirements than us that but those are not trivial #s... hope that helps... On 1/1/07, mos [EMAIL PROTECTED] wrote: At 12:49 PM 1/1/2007, Jochem van Dieten wrote: On 1/1/07, mos wrote: Is there a problem with InnoDb scaling with multi-processor CPU's? Apparently after reading the Tweakers.net article, with only 40 simultaneous users the performance of MySQL 5 will collapse. http://www.mysqlperformanceblog.com/2006/11/30/interesting-mysql-and-postgresql-benchmarks/ http://tweakers.net/reviews/649/6 Has this been fixed? As the article on the MySQL Performance Blog mentioned, a fix from InnoDB has been integrated into 5.30. Tweakers.net has already tested this fix and it does show some improvement, but it still has a long way to go: http://tweakers.net/reviews/661/6 Jochem Jochem, Yes Innodb has a long ways to go and I'm wondering if it is fixable so the performance is more linear. As it is, performance in the Tweakers' charts drop dramatically (tanks?) after 7 concurrent users even for version 5.03. I know Innodb works best if the table fits into memory, but for me that isn't practical (at least on one machine) because the tables will grow over time and I don't want to crash into a wall when the table exceeds memory capacity of the machine. The MySQL Cluster database looks like it is an alternative but I hear it doesn't do well on table joins (I may be able to get around that). But using a Cluster database means I'll need to start off with 4 or 5 computers instead of just one so startup costs are a lot more. :( So I'm wondering how high traffic websites that use Innodb can overcome this problem? Google GMail, Craigs List, TIcket Master, Yahoo etc all have high number of updates per second, so there must be an InnoDb solution, right? They can't be using MyISAM for transactional updates, and InnoDb is the only engine of choice until recently (Cluster Db is too new). If this problem is as bad as Tweakers claim it is, then the only solution I see is to run multiple database servers instead of adding processors to a central server. So if they were going to do it over again, I wonder if they would still stick with InnoDb or go with a MySQL cluster or choose something else like PostgreSQL that scales better? I know these questions are pretty much rhetorical, but I thought I'd bounce this off of you guys to see what the best approach is for a high traffic transactional web site. If you were going to write one of these web sites I mentioned, would you still use InnoDb? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.1.14-beta with ssl build failure
Duncan Hutty wrote: I attempted to build 5.1.14-beta with ssl support and it failed (output below). It builds quite happily on this system without the ssl support parameter to configure. Since it appears to fail in an area (timezone system) that to me seems rather unrelated, I wondered if something odd was afoot. x86-suse9.3, openssl-0.9.8d, gcc-3.4.6 (or gcc-4.1.1) using: CFLAGS=-O3 CXX=gcc \ CXXFLAGS=-O3 -felide-constructors \ -fno-exceptions -fno-rtti \ Kill all this and try changing -O3 to -O2 as -O3 optimizations can get dicey sometimes, especially when it comes into C++ oddities. ./configure --with-ndbcluster \ --with-ssl=/usr/local/openssl \ --enable-assembler make Also, it seems to be failing during the linking stage, so take a look at throwing out --enable-assembler and see what happens. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query question...
hi... continuing with my test (i was able to get the correct information earlier.. thanks to all who helped!) i have the following test tbl/information: dog name char statusint _date timestamp idint test data dog name status _dateid tom 1 01/20/07 1 tom 2 01/21/07 2 sue 1 01/20/07 3 tom 3 01/22/07 4 sue 2 01/21/07 5 bob 1 01/20/07 6 tom 4 01/23/07 7 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 using a query similar: select distinct s1.universityID from SvnTBL as s1 left outer join (select universityID,_date from SvnTBL where actionID =5) as s2 ON s1.universityID=s2.universityID where s2.universityID is null and s1.universityID='1000' group by s1.universityID; i can get the unique 'name's that don't have a given actionID. however, i'm now trying to figure out how to craft a query to get the unique 'name' (group) for when the tbl has an actionID=3 with a _date later than the _date of the actionID=4 i've played with the inner 'select' but can't quite seem to get what i'm looking for... any thoughts/comments.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Foreign Key
Chris White-4 wrote: Moving along... what I do first is SHOW CREATE TABLE `table_name`: ...snipped... So, first you remove the key: ALTER TABLE table_name DROP KEY `key_column`; then the foreign key: ALTER TABLE table_name DROP FOREIGN KEY `table_name_ibfk_1`; ...snipped... Thank you Chris. The SHOW CREATE TABLE `table_name` was just the ticket. It showed both the key name and the foreign key name. The foreign key hadn't shown up in phpMyAdmin or MySQLAdministrator - all very frustrating. I dropped the foreign key first and then the index. I'm sure leaving the work until after my own caffeine boost also helped considerably! Cheers! -- View this message in context: http://www.nabble.com/Deleting-Foreign-Key-tf2905915.html#a8171880 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Err1114 The table 'sbtest' is full
Hi, 1. The table full error was due to temporary table size being large, which is created during transaction processing. Try setting SQL_BIG_TABLES, to avoid over-allocating memories for other queries. 2. Since Innodb engine, the innodb tablespace might run out of space. Try to increase the size of the tablespace,by adding another datafile, setting to innodb_autoextend_increment system variable. 3. Since you are doing with 2000 threads, try increasing thread_stack size.Hope with this FATAL error can be fixed. Thanks ViSolve DB Team - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 04, 2007 8:08 AM Subject: Err1114 The table 'sbtest' is full I used sysbench to test mysql with 2000 threads and then sysbench threw some alerts below: ALERT: failed to execute mysql_stmt_execute(): Err1114 The table 'sbtest' is fullFATAL: database error, exiting...I don't know what does it mean because sbtest is not full.mysql show create table sbtest\G*** 1. row *** Table: sbtestCreate Table: CREATE TABLE `sbtest` ( `id` int(10) unsigned NOT NULL auto_increment, `k` int(10) unsigned NOT NULL default '0', `c` char(120) NOT NULL default '', `pad` char(60) NOT NULL default '', PRIMARY KEY (`id`), KEY `k` (`k`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql show table status from sbtest\G*** 1. row *** Name: sbtest Engine: InnoDBVersion: 10 Row_format: Compact Rows: 100092 Avg_row_length: 225Data_length: 22593536Max_data_length: 0 Index_length: 1589248 Data_free: 0 Auto_increment: 11Create_time: 2007-01-04 09:25:16Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: InnoDB free: 58368 kB1 row in set (0.01 sec)[EMAIL PROTECTED] mysql]# ls -l ibdata1 -rw-rw 1 mysql mysql 320864256 Jan 4 09:27 [EMAIL PROTECTED] mysql]# cat /etc/my.cnf[mysqld]log-bin=masterlogmax_connections=3000innodb_data_file_path=ibdata1:306M:autoextendmax_prepared_stmt_count=32765#logquery_cache_size=16M#innodb_buffer_pool_size=512M#key_buffer_size=512Mserver-id=1 binlog-do-db=backup master-host=172.20.23.28master-user=backupmaster-password=backupmaster-port=3306 mysql show variables like 'innodb_data%';+---+-+| Variable_name | Value |+---+-+| innodb_data_file_path | ibdata1:306M:autoextend | | innodb_data_home_dir | | +---+-+2 rows in set (0.00 sec)MySQL's version is 5.0.27-standard-log.OS is RHEL 4.0What should I do?ThanksGu Lei --- 惠普商用台式机dc5750,高性能，低功耗！( http://ad4.sina.com.cn/sina/limeng3/mail_zhuiyu/2006/mail_zhuiyu_20061225.html ) === 注册新浪2G免费邮箱（ http://mail.sina.com.cn/chooseMode.html ） -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql question regarding distinct/group by...
Hi, dog fooID int size int id int dog fooIDsizeid 1 2 1 2 5 2 1 5 3 if i do a query select * from dog where fooID='1'; i get 1,2,1 1,5,3 Try, select * from dog where fooID=1 group by fooID; - which retrieves the first instance; resulting in, 1,2,1 Thanks ViSolve DB Team - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 04, 2007 6:07 AM Subject: mysql question regarding distinct/group by... hi... i've asked something similar before.. but it appears something is going wrong... so, back to basics... i have the following test tbl. dog fooID int size int id int dog fooIDsizeid 1 2 1 2 5 2 1 5 3 if i do a query select * from dog where fooID='1'; i get 1,2,1 1,5,3 how can i do a distinct/group by select such that if i do a select on fooID=1, i'll only get '1,2,1', or '1,5,3', meaning that i don't get both of the items where fooId=1. thanks... -- 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]
db/query question...
hi... a further test... the following test tbl/information: dog name char statusint _date timestamp idint test data dog name status _dateid tom 1 01/20/07 1 tom 2 01/21/07 2 sue 1 01/20/07 3 tom 3 01/22/07 4 sue 2 01/21/07 5 bob 1 01/20/07 6 tom 4 01/23/07 7 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 if i do a regular group, i can get (for tom) tom 1 01/20/07 1 tom 2 01/21/07 2 tom 3 01/22/07 4 tom 4 01/23/07 7 sue 1 01/20/07 3 sue 2 01/21/07 5 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 here's the tricky part. if i want to get the row with the status=3, but only if there's not a status=4 that has a later date, how do i accomplish this...?? so, for tom, i would return 'null', and for sue, i'd return '3' for the '01/24/07' the date for the last '3' is later than the date for the last '4'... any thoughts/comments.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
db/query question...
hi... a further test... the following test tbl/information: dog name char statusint _date timestamp idint test data dog name status _dateid tom 1 01/20/07 1 tom 2 01/21/07 2 sue 1 01/20/07 3 tom 3 01/22/07 4 sue 2 01/21/07 5 bob 1 01/20/07 6 tom 4 01/23/07 7 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 if i do a regular group, i can get (for tom) tom 1 01/20/07 1 tom 2 01/21/07 2 tom 3 01/22/07 4 tom 4 01/23/07 7 sue 1 01/20/07 3 sue 2 01/21/07 5 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 here's the tricky part. if i want to get the row with the status=3, but only if there's not a status=4 that has a later date, how do i accomplish this...?? so, for tom, i would return 'null', and for sue, i'd return '3' for the '01/24/07' the date for the last '3' is later than the date for the last '4'... i imagine that there's a way to accomplish this using subselects. any thoughts/comments.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB vs MyISAM
At 08:38 AM 1/4/2007, you wrote: Hi, I have seen that by default some tables are created as InnoDB and some as MyISAM. I guess the table type is not chosen randomly. How is it chosen the table engine used? And is InnoDB recommended now? If you need transactions or RI. Does it support full text indexes? Or if not, is there a way of using full text indexes and foreign keys in MySQL? Innodb does not support full text indexes and MyISAM does not support foreign keys. InnoDb has been working on full text search for over a year (or two?) and it should be completed about a week before the sun turns into a red giant (some 3 billion years from now). If you can't wait that long you can use Sphinx and recompile MySQL to use that. It is faster and more efficient than MySQL's full text search and will work with InnoDb. Get it from http://www.sphinxsearch.com/. It is open source. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql data into CSV file.
Hi For example , suppose i have a database - Music, and a table in it is mp3. under mp3 there is more fields , like, id, artist, name , mp3_file...etc. I wanted to get that info which has stored in relavent fields into a CSV fle . Can somebody help me to do that please ? Can i do it mysql commands it self ? Thank you, Shaine. Send instant messages to your online friends http://uk.messenger.yahoo.com
Re: mysql question regarding distinct/group by...
SELECT * FROM Dog GROUP BY FooId HAVING FooId = 1; This should also work. On 1/5/07, ViSolve DB Team [EMAIL PROTECTED] wrote: Hi, dog fooID int size int id int dog fooIDsizeid 1 2 1 2 5 2 1 5 3 if i do a query select * from dog where fooID='1'; i get 1,2,1 1,5,3 Try, select * from dog where fooID=1 group by fooID; - which retrieves the first instance; resulting in, 1,2,1 Thanks ViSolve DB Team - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 04, 2007 6:07 AM Subject: mysql question regarding distinct/group by... hi... i've asked something similar before.. but it appears something is going wrong... so, back to basics... i have the following test tbl. dog fooID int size int id int dog fooIDsizeid 1 2 1 2 5 2 1 5 3 if i do a query select * from dog where fooID='1'; i get 1,2,1 1,5,3 how can i do a distinct/group by select such that if i do a select on fooID=1, i'll only get '1,2,1', or '1,5,3', meaning that i don't get both of the items where fooId=1. thanks... -- 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]
Fwd: mysql data into CSV file.
-- Forwarded message -- From: Jake Peavy [EMAIL PROTECTED] Date: Jan 4, 2007 10:48 PM Subject: Re: mysql data into CSV file. To: Shain Lee [EMAIL PROTECTED] On 1/4/07, Shain Lee [EMAIL PROTECTED] wrote: I wanted to get that info which has stored in relavent fields into a CSV fle . Can somebody help me to do that please ? Can i do it mysql commands it self ? You could do with with SELECT INTO OUTFILE as outlined at http://dev.mysql.com/doc/refman/5.0/en/select.html#id3041795 Or you could make use of the new CSV storage engine http://dev.mysql.com/doc/refman/5.0/en/csv-storage-engine.html (forgot the bloody list...) -- -jp Switzerland isn't really neutral. They just haven't figured out what side Chuck Norris is on yet.
Re: mysql data into CSV file.
Hi, Try using, SELECT * INTO OUTFILE 'tmp/music_mp3.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' ' LINES TERMINATED BY ' \n ' FROM mp3; Thanks ViSolve DB Team - Original Message - From: Shain Lee [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Friday, January 05, 2007 11:05 AM Subject: mysql data into CSV file. Hi For example , suppose i have a database - Music, and a table in it is mp3. under mp3 there is more fields , like, id, artist, name , mp3_file...etc. I wanted to get that info which has stored in relavent fields into a CSV fle . Can somebody help me to do that please ? Can i do it mysql commands it self ? Thank you, Shaine. Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: db/query question...
Hi, This will do: select name,status, max(_date) from dog where status (select max(status) from dog where status=dog.status) group by name; Thanks ViSolve DB Team. - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, January 05, 2007 10:23 AM Subject: db/query question... hi... a further test... the following test tbl/information: dog name char status int _date timestamp id int test data dog name status_date id tom 1 01/20/07 1 tom 2 01/21/07 2 sue 1 01/20/07 3 tom 3 01/22/07 4 sue 2 01/21/07 5 bob 1 01/20/07 6 tom 4 01/23/07 7 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 if i do a regular group, i can get (for tom) tom 1 01/20/07 1 tom 2 01/21/07 2 tom 3 01/22/07 4 tom 4 01/23/07 7 sue 1 01/20/07 3 sue 2 01/21/07 5 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 here's the tricky part. if i want to get the row with the status=3, but only if there's not a status=4 that has a later date, how do i accomplish this...?? so, for tom, i would return 'null', and for sue, i'd return '3' for the '01/24/07' the date for the last '3' is later than the date for the last '4'... any thoughts/comments.. thanks -bruce -- 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]
