Charset and French Diacritic Characters
Hello everybody, I'm french and use MySQL 3.23.40 on the Windows environment. And I have a problem with the special french characters (é, è, ê, à, â, etc.)... I want that the search queries are insensitive to those special characters. So that, for example, the é character is handled as the e character. and the query élégant finds elegant. A very common problem I think, but until now I have no simple solution (and find nothing interesting on the Net) ! I read the MySQL manual about the charsets and their handling. It seeems that, for a given charset, sorting, upper casing and lower casing are done according to the configuration stored into the charset.conf file... (with the 4 arrays ctype[ ], to_lower[ ], to_upper[ ] and sort_order[ ]). By default, the charset used by my MySQL is Latin1. According to the Latin1.conf file (and the sort_order[ ] array), the é character is sorted (and compared) as the e character... But it does not work ! WHY ? Obviously, there is something that I don't understand, but what ? What can I do ? What should I do to obtain that the queries are insensitive to french characters ? Any help will be appreciated ! Best Regards ! Xavier LENOIR - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Slooooow Performance with Full text index
Hello everybody, I have a big problem with the Full Text index functionality with MySQL 3.23. In fact, I really don't understand what is happening... I have a table built this way : CREATE TABLE MyTable ( Id bigint unsigned NOT NULL, Num varchar(20) NOT NULL, Title varchar(150) default NULL, Aut1 varchar(100) default NULL, Aut2 varchar(100) default NULL, Aut3 varchar(100) default NULL, Aut4 varchar(100) default NULL, Aut5 varchar(100) default NULL, Note text default NULL, Abs text default NULL, Tab text default NULL, PRIMARY KEY (Id), FULLTEXT (Title), FULLTEXT (Aut1,Aut2,Aut3,Aut4,Aut5) ) TYPE=MyISAM; This table contains approximatively 150 000 rows (and in the end, should contain more than 1 000 000 rows). I use queries like this : SELECT count(*) FROM MyTable WHERE (MATCH(Title) AGAINST('a word')); SELECT * FROM MyTable WHERE (MATCH(Title) AGAINST('a word')) LIMIT 10; And the performance seems very weird to me : 1) If the query returns a few or no results at all, it is very fast 2) If the query returns a lot of results, it is very slow... the more results returned, the more slower... Sometimes (for 10 000 results), it takes more than 40 seconds... Why the select count(*) is so slow with a full text index when there is a lot of matching rows ? Why performance depends on the number of results ? Why the select * limit 10 is so slow with a full text index when there is a lot of matching rows ? Why performance depends on the number of results ? I suppose that the problem is due to the fact that MySQL automatically sorts (by relevance) all the results collected before producing the first 10 ? What can I improve performance significantly ? Any idea ? Please help me ! Thanks Xavier LENOIR - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HELP : Performance with FULLTEXT Index
Hi everybody, I'm trying to use the Full Text index functionality with MySQL 3.23 and I have a problem I can't understand. I have a table built this way : CREATE TABLE MyTable ( Id bigint unsigned NOT NULL, Num varchar(20) NOT NULL, Title varchar(150) default NULL, Aut1 varchar(100) default NULL, Aut2 varchar(100) default NULL, Aut3 varchar(100) default NULL, Aut4 varchar(100) default NULL, Aut5 varchar(100) default NULL, Note text default NULL, Abs text default NULL, Tab text default NULL, PRIMARY KEY (Id), FULLTEXT (Title), FULLTEXT (Aut1,Aut2,Aut3,Aut4,Aut5) ) TYPE=MyISAM; This table contains approximatively 150 000 rows (and in the end, should contain more than 1 000 000 rows). I use queries like this : SELECT count(*) FROM MyTable WHERE (MATCH(Title) AGAINST('a word')); SELECT * FROM MyTable WHERE (MATCH(Title) AGAINST('a word')); And the performance seems very weird to me : 1) If the query returns a few or no results at all, it is very fast 2) If the query returns a lot of results, it is slower... the more results returned the more slower... Sometimes (for 10 000 results), it takes more than 35 seconds... I have supposed that the problem is due to the fact that MySQL automatically sorts (by relevance ?) the results collected... So, I try to add a LIMIT 10 to the SELECT... but it does not change anything at all... Any idea ? I really need your help... Thanks Xavier LENOIR - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php