Re: combined or single indexes?
Hi, MySQL can use a single index in a query as you've seen in the result of explain. Of course it is better to have an index made of 2 or more columns because it will match better the query. But if I remember well, the in() function can't use an index. And I think it also can't use an index if you use OR operators like: select foo from table where a=1 or a=2; So for your query the single-column index for the second column is enough. I've seen some tricks for using a faster method by using union and 2-column index, something like: select foo from table where a=1 and b1234 union select foo from table where a=2 and b1234 union select foo from table where a=3 and b1234 This might be faster in some cases because the query would be able to use the 2-column index, and especially if the content of those columns is made only of numbers, because in that case the query will use only the index, without getting data from the table. -- Octavian - Original Message - From: Bryan Cantwell bcantw...@firescope.com To: mysql@lists.mysql.com Sent: Tuesday, July 06, 2010 6:41 PM Subject: combined or single indexes? Is there a benefit to a combined index on a table? Or is multiple single column indexes better? If I have table 'foo' with columns a, b, and c. I will have a query like: select c from foo where a in (1,2,3) and b 12345; Is index on a,b better in any way than an a index and a b index? An explain with one index sees it but doesn't use it (only the where) and having 2 indexes sees both and uses the one on b. Am I right to think that 2 indexes are better than one combined one? thx, Bryancan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=octavian.rasn...@ssifbroker.ro __ Information from ESET NOD32 Antivirus, version of virus signature database 5257 (20100707) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 5257 (20100707) __ 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
Re: [Spam][78.6%] Re: Differences between 2 MySQL instances
From: Joerg Bruehe joerg.bru...@sun.com Hi! Octavian Rasnita wrote: I have tried, but with no difference. I have changed some indexes and made the queries run faster, but I still found a problem: I use a module that does paging and it makes a select(*) and this query takes a very long time. I have also tried to do select(id) where the id column is the primary key, but it runs very slow also. I have also seen that select(*) with various where conditions is still very slow. Is there any trick to make the select(*) queries run faster within an InnoDB table? You should accept the fact that 5.1 has improvements over 5.0, some of them being better optimization resulting in faster execution. Upgrade the 5.0 installation to 5.1 to profit from the newer version. I have tried the select(*) with InnoDB under 5.1, but it is still very slow (dozens of seconds) while with MyISAM is instant. I know that InnoDB works differently and cannot do that select so fast, but... dozens of seconds is too much. I have succeeded to make the main select very fast, but then the select(*) needed for paging the results is hundreads times slower. I have also tried select SQL_CALC_FOUND_ROWS and select found_rows() but it is much slower than a simple select(*). Thanks. Octavian __ Information from ESET NOD32 Antivirus, version of virus signature database 5224 (20100624) __ 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
Re: Differences between 2 MySQL instances
I have tried, but with no difference. I have changed some indexes and made the queries run faster, but I still found a problem: I use a module that does paging and it makes a select(*) and this query takes a very long time. I have also tried to do select(id) where the id column is the primary key, but it runs very slow also. I have also seen that select(*) with various where conditions is still very slow. Is there any trick to make the select(*) queries run faster within an InnoDB table? Thanks. -- Octavian - Original Message - From: John Daisley To: Octavian Rasnita Cc: mysql@lists.mysql.com Sent: Wednesday, June 23, 2010 3:55 PM Subject: Re: Differences between 2 MySQL instances Have you tried running 'OPTIMIZE TABLE' on the tables in question to make sure statistics are up to date. I would expect the vast majority of queries to run faster on MySQL 5.1 (with identical settings, hardware and operating system). 2010/6/23 Octavian Rasnita octavian.rasn...@ssifbroker.ro Hello, I have the following table under MySQL 5.1.43-community under Windows, and under MySQL 5.0.82sp1 Source distribution under Linux): CREATE TABLE `table_name` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `tip_ticker` tinyint(1) NOT NULL, `symbol` varchar(20) NOT NULL, `market` varchar(20) NOT NULL, `date` date DEFAULT NULL, `time` time DEFAULT NULL, `price` decimal(20,4) unsigned DEFAULT NULL, `price_adjusted` double DEFAULT NULL, `volume` bigint(20) unsigned DEFAULT NULL, `volume_adjusted` double(255,0) unsigned DEFAULT NULL, `bid` decimal(20,4) unsigned DEFAULT NULL, `ask` decimal(20,4) unsigned DEFAULT NULL, `bid_volume` bigint(20) unsigned DEFAULT NULL, `ask_volume` bigint(20) unsigned DEFAULT NULL, `trades` int(10) unsigned DEFAULT NULL, `change_percent` decimal(20,4) DEFAULT NULL, PRIMARY KEY (`id`), KEY `i1` (`date`,`time`,`id`), KEY `i2` (`symbol`,`date`,`time`,`id`), KEY `i3` (`tip_ticker`,`date`,`time`,`id`), KEY `i4` (`symbol`,`market`,`date`,`time`), KEY `i5` (`trades`,`date`,`time`,`symbol`,`market`), KEY `i6` (`change_percent`,`date`,`time`,`symbol`,`market`), KEY `i7` (`date`,`time`,`symbol`,`market`) ) ENGINE=InnoDB AUTO_INCREMENT=1154030054 DEFAULT CHARSET=latin1 I have tried the following query under both MySQL servers: explain select * from table_name where symbol='etc' and market='etc2' and date='2010-01-01' and tip_ticker=1 order by trades, date, time, symbol, market limit 20\G The result under Windows is: id: 1 select_type: SIMPLE table: table_name type: index possible_keys: i1,i2,i3,i4,i7 key: i5 key_len: 57 ref: NULL rows: 4058 Extra: Using where But the result under Linux is: id: 1 select_type: SIMPLE table: table_name type: range possible_keys: i1,i2,i3,i4,i7 key: i4 key_len: 48 ref: NULL rows: 96000 Extra: Using where; Using filesort This query obviously takes a much longer time than the one under Windows. I have also tried to force index(i5) under Linux in order to force using the same index as under Windows: explain select * from table_name force index(i5) where symbol='etc' and market='etc2' and date='2010-01-01' and tip_ticker=1 order by trades, date, time, symbol, market limit 20\G But the result is: id: 1 select_type: SIMPLE table: table_name type: index possible_keys: NULL key: i5 key_len: 57 ref: NULL rows: 11020086 Extra: Using where Even though this query uses the same index as the one under Windows, the number of estimated rows is approximately the total number of rows in the table and it also takes a very long time to complete. Do you have any idea why this works differently under Linux? Is it because under Linux I have MySQL 5.0 and under Windows MySQL 5.1 and I definitely need to upgrade? There are some differences between the global variables that start with innodb_ under Windows and Linux, but I don't know if those differences make InnoDB to choose another index. Thank you. -- Octavian __ Information from ESET NOD32 Antivirus, version of virus signature database 5220 (20100623) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Microsoft SQL Server 2005/2008 Database Administrator Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk __ Information from ESET NOD32 Antivirus, version of virus signature database 5223 (20100623
Differences between 2 MySQL instances
Hello, I have the following table under MySQL 5.1.43-community under Windows, and under MySQL 5.0.82sp1 Source distribution under Linux): CREATE TABLE `table_name` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `tip_ticker` tinyint(1) NOT NULL, `symbol` varchar(20) NOT NULL, `market` varchar(20) NOT NULL, `date` date DEFAULT NULL, `time` time DEFAULT NULL, `price` decimal(20,4) unsigned DEFAULT NULL, `price_adjusted` double DEFAULT NULL, `volume` bigint(20) unsigned DEFAULT NULL, `volume_adjusted` double(255,0) unsigned DEFAULT NULL, `bid` decimal(20,4) unsigned DEFAULT NULL, `ask` decimal(20,4) unsigned DEFAULT NULL, `bid_volume` bigint(20) unsigned DEFAULT NULL, `ask_volume` bigint(20) unsigned DEFAULT NULL, `trades` int(10) unsigned DEFAULT NULL, `change_percent` decimal(20,4) DEFAULT NULL, PRIMARY KEY (`id`), KEY `i1` (`date`,`time`,`id`), KEY `i2` (`symbol`,`date`,`time`,`id`), KEY `i3` (`tip_ticker`,`date`,`time`,`id`), KEY `i4` (`symbol`,`market`,`date`,`time`), KEY `i5` (`trades`,`date`,`time`,`symbol`,`market`), KEY `i6` (`change_percent`,`date`,`time`,`symbol`,`market`), KEY `i7` (`date`,`time`,`symbol`,`market`) ) ENGINE=InnoDB AUTO_INCREMENT=1154030054 DEFAULT CHARSET=latin1 I have tried the following query under both MySQL servers: explain select * from table_name where symbol='etc' and market='etc2' and date='2010-01-01' and tip_ticker=1 order by trades, date, time, symbol, market limit 20\G The result under Windows is: id: 1 select_type: SIMPLE table: table_name type: index possible_keys: i1,i2,i3,i4,i7 key: i5 key_len: 57 ref: NULL rows: 4058 Extra: Using where But the result under Linux is: id: 1 select_type: SIMPLE table: table_name type: range possible_keys: i1,i2,i3,i4,i7 key: i4 key_len: 48 ref: NULL rows: 96000 Extra: Using where; Using filesort This query obviously takes a much longer time than the one under Windows. I have also tried to force index(i5) under Linux in order to force using the same index as under Windows: explain select * from table_name force index(i5) where symbol='etc' and market='etc2' and date='2010-01-01' and tip_ticker=1 order by trades, date, time, symbol, market limit 20\G But the result is: id: 1 select_type: SIMPLE table: table_name type: index possible_keys: NULL key: i5 key_len: 57 ref: NULL rows: 11020086 Extra: Using where Even though this query uses the same index as the one under Windows, the number of estimated rows is approximately the total number of rows in the table and it also takes a very long time to complete. Do you have any idea why this works differently under Linux? Is it because under Linux I have MySQL 5.0 and under Windows MySQL 5.1 and I definitely need to upgrade? There are some differences between the global variables that start with innodb_ under Windows and Linux, but I don't know if those differences make InnoDB to choose another index. Thank you. -- Octavian __ Information from ESET NOD32 Antivirus, version of virus signature database 5220 (20100623) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
The query doesn't use the specified indexes
Hi, I have made an InnoDB table and I am trying to search using some keys, but they are not used, and the query takes a very long time. Here is a test table: CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `symbol` varchar(20) NOT NULL, `market` varchar(20) NOT NULL, `id_symbol` int(10) unsigned NOT NULL, `id_market` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `symbol` (`symbol`), KEY `market` (`market`), KEY `id_symbol` (`id_symbol`), KEY `id_market` (`id_market`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 The search query is: mysql explain select * from test where symbol='etc' order by market limit 20\G *** 1. row *** id: 1 select_type: SIMPLE table: test type: ref possible_keys: symbol key: symbol key_len: 62 ref: const rows: 1 Extra: Using where; Using filesort The bad part is Using filesort, and I thought that this is because it doesn't like varchar or char columns for indexes, so I tried to use columns that contain integers: mysql explain select * from test where id_symbol=2 order by id_market limit 20\G *** 1. row *** id: 1 select_type: SIMPLE table: test type: ref possible_keys: id_symbol key: id_symbol key_len: 4 ref: const rows: 1 Extra: Using where; Using filesort It still uses Using filesort and it doesn't use the index id_market in the query. So I tried to force using the indexes: mysql explain select * from test force index(symbol, market) where symbol='etc' order by market limit 20\G *** 1. row *** id: 1 select_type: SIMPLE table: test type: ref possible_keys: symbol key: symbol key_len: 62 ref: const rows: 1 Extra: Using where; Using filesort So, no matter I do, the query doesn't want to use the specified index. Please tell me what am I doing wrong. Or it is a MySQL/InnoDB bug? The current table I am testing has no records. I have also tried this on a table that has more than 10 million records, with exactly the same results. Please tell me what can I do. Thanks. -- Octavian __ Information from ESET NOD32 Antivirus, version of virus signature database 5214 (20100621) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
Re: Problems posting to the list lists.mysql.com
Is the MySQL mailing list admin subscribed to the list? I have also sent a message or more telling about this issue, with no response. -- Octavian mos mo...@fastmail.fm wrote in message news:6.0.0.22.2.20100215120015.02cd4...@mail.messagingengine.com... At 10:51 AM 2/15/2010, Frank Becker wrote: Hello, I tried to response to a posting on the list named in the subject. But it is rejected with the following message: SMTP error from remote mail server after end of data: host lists.mysql.com [213.136.52.31]: 552 spam score exceeded threshold (#5.6.1) Neither this emailaddress is used for spam purposes nor I offered products. Please, how can I use the list so that I can response to posts? I read the list via gmane.org Best regards Frank Becker Frank, This has happened before. Please contact webmas...@mysql.com and they may be able to solve the problem. http://www.google.ca/#q=552+spam+score+exceeded+threshold+mysqlhl=ensa=2fp=8e6c6930b7d53e73 Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Inserting a default null date
Hi, I have a table with a column like: date date default null, If I enter an empty string in it, the default null value is added (as it should). But if I enter an invalid date by mistake, the date -00-00 date date is entered instead of the default null, and this is not good. Can I do something to force MySQL to insert a null date if the entered date is an invalid one? Thank you. -- Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Can't send messages to the mailing list
Hello, I was subscribed for a long time to MySQL general mailing list but now I see that I can't send messages to the list because they are rejected, and in the error mail message that returns, I saw that it says something about SPAM. I didn't send spam mails to the list or to somewhere else but I have also seen that I couldn't even subscribe with another email address to the list because the subscription email is also rejected. If somebody knows, please tell me what can I do to be able to subscribe to the mailing list and send messages to it again. I was subscribed with the email orasn...@gmail.com. Thank you and sorry for offtopicness. -- Octavian -- 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, perl, last_insert_id() question
Do you have the latest version of DBI and DBD::mysql installed? First try: $ cpan cpan install DBI cpan install DBD::mysql You can also do: my $sth = $dbh-prepare(select last_insert_id()); $sth-execute(); my ($last_insert_id) = $sth-fetchrow_array(); Octavian - Original Message - From: Dean Karres [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, January 28, 2008 10:29 PM Subject: MySQL, perl, last_insert_id() question Hi, I know that someone is going to say, go ask the perl module guys and I will but they are likely to say, go ask the MySQL guys. I'll be asking in both groups. I am installing a script on a brand new RedHat, Fedora Core 7 (x86_64) box that is running MySQL 5.0.45 (rpm install). I also have a mix of other rpm MySQL installs on older Redhat boxes that are also a mix of 32 and 64 bit machines. The script works fine on the older installs. For example another 64 bit machine has a MySQL rpm rev of 5.0.27 I have a valid DB handle and after an insert I try to get the ID of that inserted row, a la: my $id = $DBH-last_insert_id(); This has been working everywhere until I tried installing the exact same script on this newest machine. When the script runs now I get: DBI last_insert_id: invalid number of arguments: got handle + 0, expected handle + between 4 and 5 Usage: $h-last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ]) at /usr/local/bin/myScript line 454. Ok, I know the DBI Docs say that some versions of the drivers may demand an argument list for last_insert_id() but since I have not seen this behavior in the earlier versions is this new or is there a problem? -- 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]
like in()
Hi,
I can do:
where word in('one','two','three')
and I can also do:
where word like 'thr%'
Is there a way of combining these 2 ways?
I want to select something like:
where word like 'on%' or word like 'tw%' or word like 'thr%'
but using a single expression, without or.
Is it possible to do this? To use something like
where word inlike('on%','tw%','thr%')
Thank you.
Octavian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Embedding MySQL
Hi, I want to embed MySQL and install it with a freeware application I make. I hope it is legally to do this. Please tell me where can I found more information about how can I do this. The app will run under Windows. Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
grouping
Hi, I have a table with the following columns: symbol date value I want to select all the symbols (grouping by symbols) between 2 specified dates, and calculate the percent of change for each symbol. The percent of change is (the value from the last date of the symbol - the value from the first date) / the value from the first date. For example I have: SMB1, 2007-01-01, 1000 SMB1, 2007-03-15, 2100 SMB1, 2007-10-10, 1300 ... (other symbols) And the result of the select should be: SMB1, 0.3 Is it possible to do this with an MySQL query? Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting data - speed
Unfortunately I tried to change one of the tables in InnoDB, but the inserts in that table continue to be slower than in the MyISAM table. (From the MyISAM table no program is searching for data). Octavian - Original Message - From: Brent Baisley [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, July 31, 2007 5:27 AM Subject: Re: inserting data - speed The most obvious is to make sure you are doing bulk inserts, which you may already be doing. MyISAM tables use table locking, so you usually can't insert while a search is occurring. There are a few exceptions and v5 (5.1?) has another option you can set so inserts are always added to the end of the file (so selects can occur during inserts). If you are using v4, your options are limited. Read up on concurrent inserts and it should make everything fairly clear. http://dev.mysql.com/doc/refman/5.0/en/concurrent-inserts.html You'll run into other problems with Innodb when the tables get really large, but they are usually the solution when locking is an issue. An alternative solution is to have one server handling inserts/ updates/deletes and replicated to another server that handles the selects. One interesting solution for extremely high insert rates is to use the BLACK HOLE table type with replication. On Jul 30, 2007, at 5:37 AM, Octavian Rasnita wrote: Hi, I made 2 similar programs that insert data continuously in 2 similar MyISAM tables, each one in its own table. Both tables have the same data (3.5 million records), but one of the tables is update much slower. The slower table is also accessed by other programs for getting data from it. Is there something I can do to make it faster? (Using locking, special select queries...) Or might it work faster if I will use an InnoDB table instead? Thank you very much. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting data - speed
Hi, I made 2 similar programs that insert data continuously in 2 similar MyISAM tables, each one in its own table. Both tables have the same data (3.5 million records), but one of the tables is update much slower. The slower table is also accessed by other programs for getting data from it. Is there something I can do to make it faster? (Using locking, special select queries...) Or might it work faster if I will use an InnoDB table instead? 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]
error
Hi, I have exported a database using mysqldump from MySQL 5.0.27, and I have tried importing it in a MySQL 5.0.41, but it gives the following error: ERROR 1071 (42000) at line 483: Specified key was too long; max key length is 1000 bytes Isn't mysqldump exporting the data correctly? Can I solve this problem? Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: off-topic unsubscribe concern
There are just spam messages. The spam robots probably found some messages from the list somewhere and our addresses, and it put all the addresses in its database. Then, as usually, it sends spam messages to all addresses, using another address from the same message as the source address. This way it sent an email to the address for unsubscribing from the list using our addresses as a source address. So we receive a confirmation for unsubscribing. So it is no problem. Octavian - Original Message - From: B. Keith Murphy [EMAIL PROTECTED] To: Michael Dykman [EMAIL PROTECTED] Cc: mysql mysql@lists.mysql.com Sent: Friday, July 06, 2007 10:48 PM Subject: Re: off-topic unsubscribe concern I have been having the same problems. Have no idea what is up. - Original Message - From: Michael Dykman [EMAIL PROTECTED] To: MySQL General mysql@lists.mysql.com Sent: Friday, July 6, 2007 11:55:41 AM (GMT-0500) America/New_York Subject: off-topic unsubscribe concern Hi everyone. I have been on this list for a pretty long time but in the last few months I have started to receive random 'confirm unsubscribe' messages.. They always seem to originate from a Roadrunner IP (I have not thoroughly tested that hypothesis). I have no accounts on or near roadrunner, so I doubt I am inadvertantly kicking these off, which was my first theory. Is anyone else suffering from this or is it just me? -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Last X rows
Hi, Try something like this: select * from (select * from table_name where ... order by last_update desc limit 10) as tbl order by tbl.last_update; Octavian - Original Message - From: Rich [EMAIL PROTECTED] To: Submit MySQL mysql@lists.mysql.com Sent: Saturday, June 30, 2007 3:45 PM Subject: Select Last X rows Hi folks. Just wanting to know the best way to grab the last 10 rows from a table. Looking twice to the db to see how many records there are will be outdated by the time the SELECT is done, so it's moot. This is a fast moving db with records coming and going. Instead of having an offset in LIMIT, is there a way to ask for the last 10 rows directly? Cheers Rich in Toronto -- 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: Problem about fulltext search.
Hi, Try: select * from test where match(name) against(hello in boolean mode); Octavian - Original Message - From: Niu Kun [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, June 30, 2007 6:23 PM Subject: Problem about fulltext search. Dear all, I'm planning to add fulltext search to my database. I've got the following test command: create table test(id int, name varchar(20)); alter table test add fulltext(name); insert into test values(1,hello world); insert into test values(1,hello); When I execute the fulltext search command, I've got the following. mysql select * from test where match(name) against(hello); Empty set (0.00 sec) Would anyone be kind enough to tell me how I can find hello in my text? Any help would be appreciated. Thanks in advance. -- 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: select statement with variable for table_reference?
I am not sure I understand what you want. If you want to search for all cds, and books, and dvds based on a certain criteria, you can use that method I've told you about. May you want sometimes to search only in fewer tables that you know before making the query? If yes, then you can create more separate queries that search only in those tables. If you want to search only in the tables that have data about you want to search, you can't do it, because you don't know if those tables contain what you want before searching in them. But if those tables have well defined indexes on the keys you are searching for, that search will be very fast, especially if some of the tables don't contain records that match your criteria. I hope I understood correctly what you want. If you just want to specify a search criteria for each table separately, you can do it for each table in the (select ... where ...) and if you want to specify a search criteria for all the records of those unions, you can do it in a final where ... that's outside of those params. Octavian - Original Message - From: Ed Lazor [EMAIL PROTECTED] To: 'Octavian Rasnita' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, June 26, 2007 11:37 PM Subject: RE: select statement with variable for table_reference? Ok, I used your approach like this: -- select i.scanned_barcode, v.title from inventory as i left join version as v on i.record_id = v.id where i.database_id = '1' AND i.table_id = '1' AND i.user_id = '33' and category_id = '766') UNION (select i.scanned_barcode, v.title from inventory as i left join amg.dvd as v on i.record_id = v.id where i.database_id = '2' AND i.table_id = '3' AND i.user_id = '33' and category_id = '766') order by title DESC -- It works like you're suggesting. I have to add a union for every one of the tables data is being stored in. That means I end up selecting something from every product table, regardless of whether the user actually has something in there or not. Improving on this idea would be finding a way to just query the relevant tables... some sort of conditional union. Any ideas? -Ed -Original Message- From: Octavian Rasnita [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 1:02 PM To: Ed Lazor; mysql@lists.mysql.com Subject: Re: select statement with variable for table_reference? I am using the following method for doing this, but I am sure it is not the best one: (select id, title, author, 'book' as type from books) union (select id, title, author, 'cd' as type from cds) union (select id, title, author, 'dvd' as type from dvds) where ... order by ... limit ...; Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select statement with variable for table_reference?
Yes it is a good idea to store in a table information about which other
tables should be searched.
I don't know how these queries can be made using only SQL. I think that it
could be made in the programming language you use.
For example, first get the list of tables that should be searched (from
that index table), then create that SQL query that uses union using only
those tables.
It shouldn't be too hard to do.
For example, after searching the index table, it could return that you need
to search in the books and CDS tables.
Then you could create that sql query like:
$sql = ;
for(books, cds) {
$sql .= join union , (select id, title, from $_);
}
$sql .= where ... order by ... limit ...;
So the sql query will search only in the needed tables.
Octavian
- Original Message -
From: Ed Lazor [EMAIL PROTECTED]
To: 'Octavian Rasnita' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Wednesday, June 27, 2007 8:02 PM
Subject: RE: select statement with variable for table_reference?
Hi Octavian,
First, thanks for helping out. I really appreciate it. Thanks to you
also
Randall.
I am not sure I understand what you want.
If you want to search for all cds, and books, and dvds based on a certain
criteria, you can use that method I've told you about.
Randall said it best. I have one table that has information about what
other tables to search in.
One table serves as an index of what's in a user's inventory while the
actual product information resides in other tables.
The user inventory table has fields for user_id, database_id, table_id,
and
record_id.
There are also two other helper tables. One table (inventory_databases)
contains a list of databases with their id and name. Another table
(inventory_tables) contains a list of tables with their id and name. Both
of these tables help map from the user's inventory to where product
information resides.
An example record from the inventory table would have data like this:
user_id 33
database_id 1
table_id1
record_id 234234
I can look up the name of the database using database_id in the
inventory_databases table. I can look up the name of the table using
table_id in the inventory_tables table.
All of the other product tables have an id field that corresponds to the
record_id.
Back to the example above, database_id 1 is the products1 database and
table_id 1 is the books table. That means user id 33 has the book id
234234
in products1.books.
Ok, that describes what I'm working with. As for what I'm trying to
accomplish, I'm trying to reduce the number of queries required for
pulling
together basic information about the user's inventory. Why is that?
Well...
Right now I run one query the inventory table for all information specific
to the user. Next, I use this information and run additional queries to
get
the actual product information. If the user has 1000 items in their
inventory, I end up having to run 1 + 1000 queries. This can't be helped
in
situations where I refer to fields that are unique to each type of item
(aka
table). It seems like I should be able to avoid this though when dealing
with a common field like title. I'm just not sure how to go about it.
Using UNIONS is the only single query to work so far. Like I mentioned
though, this requires a UNION for every table that product information is
being stored in. If the first table can tell us where the data resides,
it
seems like we can use it to reduce the number of UNIONS required.
Randall, your use of prepared statements and stored procedures seems like
a
good approach. It might provide a way to dynamically generate the product
query. When I look at this:
SET @strOtherTable = (SELECT other_table_name FROM first_table WHERE
...);
SET @strSQL = CONCAT(SELECT ... FROM , @strOtherTable, WHERE...);
The first thing I automatically think of is that the first select will
very
likely have more than one record in the result set. Is there a way to
loop
through the results to concatenate everything into the set of UNIONS?
Ed
May you want sometimes to search only in fewer tables that you know
before
making the query? If yes, then you can create more separate queries that
search only in those tables.
If you want to search only in the tables that have data about you want to
search, you can't do it, because you don't know if those tables contain
what
you want before searching in them.
But if those tables have well defined indexes on the keys you are
searching
for, that search will be very fast, especially if some of the tables
don't
contain records that match your criteria.
I hope I understood correctly what you want.
If you just want to specify a search criteria for each table separately,
you
can do it for each table in the (select ... where ...) and if you want to
specify a search criteria for all the records of those unions, you can do
it
in a final where ... that's outside
Re: select statement with variable for table_reference?
I am using the following method for doing this, but I am sure it is not the best one: (select id, title, author, 'book' as type from books) union (select id, title, author, 'cd' as type from cds) union (select id, title, author, 'dvd' as type from dvds) where ... order by ... limit ...; Octavian - Original Message - From: Ed Lazor [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 26, 2007 10:39 PM Subject: select statement with variable for table_reference? Is there a way to get something like this to work? Set @tname=mytable; Select * from @tname; Here's what I'm trying to really accomplish in case there is yet another way to approach this... I have to work with product data from multiple databases and multiple tables. For example, one database has a books table, another database has a dvds table and an albums table. One table in my primary database maps between these other databases and tables. I call this one table inventory and it has fields for user_id, database_id, table_id, record_id. select * from inventory where user_id = 'xxx' I'd like to create one query that selects all of the data for the user's inventory, independent of which database and table it resides in. I have some additional tables I can use to help: inventory_databases with id, name inventory_tables with id, name That allows me to get the actual name of the database or table. And that leads me why I'm trying to find another way to handle the table_reference. I figure there's a way to specify the value of one table's field as the name of the table when doing a join or something. Any ideas? Thanks, Ed -- 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]
error
Hi, I have tried using: mysqlcheck -u username -p database And the result: ... intranet.company_sectorOK intranet.comunicat OK mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... ' I have tried for more times, but with no luck. At this point it stops and gives that error. The system frozen and I needed to force restart it, and after this, I think something bad happened to my database. Some of the database tables (MyISAM) have more than 3 million records and new records are inserted by a few programs, and in the meantime more users query them. Is this too much for MySQL? Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
repairing/verifying tables
Hi, Is there a way of checking which of the tables of a database have errors without checking each table separately? Thanks. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Illegal mix of collations
Hi, I have tried to select data from more tables using union, but it gaves the following error: ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'UNION' I have verified the tables (using show create table table_name) and all of them are: engine=InnoDB default charset=utf8 and there are no fields with another charset. The same database works under Windows, but it gives that error under Linux. Please tell me what can I do to make it work. Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show tables
Hi, I want to print the list of tables (one on a line) from a database into an external file. I have tried: mysql -u user -p database -e 'show tables;' file.txt mysql -u root -p information_schema -e 'select table_name from tables where table_schema=database_name;' file.txt But the result was the help file displayed by MySQL when a command is not correct. The SQL queries I gave are correct, because they work when I give them at the mysql prompt. The list of tables is big, and it doesn't fit into a screen and that's why I need to print it into a file. Please tell me how can I do this if it possible. Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show tables
Ok, thank you all. It was my mistake. I have quoted the SQL command with single quotes under Windows. Octavian - Original Message - From: Stijn Verholen [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 01, 2007 10:57 AM Subject: Re: show tables Octavian Rasnita wrote: Hi, I want to print the list of tables (one on a line) from a database into an external file. I have tried: mysql -u user -p database -e 'show tables;' file.txt mysql -u root -p information_schema -e 'select table_name from tables where table_schema=database_name;' file.txt But the result was the help file displayed by MySQL when a command is not correct. The SQL queries I gave are correct, because they work when I give them at the mysql prompt. The list of tables is big, and it doesn't fit into a screen and that's why I need to print it into a file. Please tell me how can I do this if it possible. Thank you. Octavian Hey, Try: mysql -uuser -ppassword -e SHOW TABLES FROM database; file.txt Stijn -- metastable Stijn Verholen Camille Huysmanslaan 114, bus 2 B-2020 ANTWERPEN +32 (0)3 707 08 08 +32 (0)473 47 62 88 [EMAIL PROTECTED] http://www.metastable.be BTW-BE 0873.645.643 bankrek.nr. ING 363-0106543-77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
renaming database
Hi, How can I rename a database if it contains InnoDB tables? I have tried renaming the directory where it is located, but it doesn't work this way. Is there a method that works faster than dumping it with mysqldump then re-create it under another name? Thanks. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comparing storing engines
Thank you.
I have made some tests, and I have inserted 1 records in a MyISAM table,
in a InnoDB table, and in an InnoDB table using a transaction.
I have seen that the insert in an InnoDB table without using a transaction
takes much much more than in a MyISAM table.
I knew that it will take much time, but not so much.
For inserting those 1 records in the MyISAM table it took 11 - 12
seconds.
For inserting them in the InnoDB table, it took 206 - 220 seconds!
When inserting those records in the InnoDB table using a single transaction,
it also took 11 - 12 seconds, just like in case of using MyISAM.
I have a table that is accessed very often and in the same time new records
are added often, and I have read that for this type of table, InnoDB is
prefered.
So maybe I will try changing the table to InnoDB, to see if it really works
faster.
Octavian
- Original Message -
From: Rolando Edwards [EMAIL PROTECTED]
To: Octavian Rasnita [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, March 22, 2007 3:40 PM
Subject: Re: comparing storing engines
Assuming you have a database, let's call it DAT1 which contain all MyISAM
tables,
you could make a copy of an entire database to DAT2
On host2 create DAT2 using
CREATE DATABASE DAT2;
Then copy all data from DAT1 to DAT2 like this.
mysqldump -hhost2 -u... -p... --triggers --routines DAT1 |
mysql -hhost2 -u... -p... -DDAT2
Next, create a script that converts all tables into another storage
engine.
mysql -hhost2 -u... -p... -A -eSELECT CONCAT('ALTER TABLE
',table_name,' ENGINE='InnoDB';') FROM information_schema.tables WHERE
table_schema='DAT2'; conv_engine.sql
Now, execute the script.
mysql -hhost2 -u... -p... -A -DDAT2 conv_engine.sql
rm conv_engine.sql
At this point, every table in DAT2 has the same data as DAT1 but all the
tables are InnoDB.
You can repeat this process for engines MEMORY, PBXT, or the upcoming
Falcon in MySQL 5.2.
Now you set up tests between DAT1 and DAT2 to compare how all queries
behave: SELECTs OUTER JOINS, VIEWs, ect.
Have fun.
- Original Message -
From: Octavian Rasnita [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, March 21, 2007 4:14:25 PM (GMT-0500) Auto-Detected
Subject: comparing storing engines
Hi,
Is there somewhere a speed comparison between the storage engines that can
be used in MySQL?
Thank you.
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]
comparing storing engines
Hi, Is there somewhere a speed comparison between the storage engines that can be used in MySQL? Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
corrupted tables
Hi, Sometimes I see that some tables from my database get corrupted. Why does this happpen and how can I avoid it? It is not hard to go and use repair table but it seems that in this way some records could be deleted and this is not ok. If I want to have a very secure database, can I use MySQL? I hope the answer won't be that I need to make backups regularily. Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: corrupted tables
From: Steve Edberg [EMAIL PROTECTED] Sometimes I see that some tables from my database get corrupted. Why does this happpen and how can I avoid it? It is not hard to go and use repair table but it seems that in this way some records could be deleted and this is not ok. If I want to have a very secure database, can I use MySQL? I hope the answer won't be that I need to make backups regularily. You'll have to give us some more information...at least: * What MySQL version, OS platform, and file system used for database? I am using MySQL 5, under Fedora Core 4, installed with its default options. * Does this happen at a regular time, or apparently randomly? It happends apparently randomly. Sometimes I just see that the programs are not working. Sometimes I can do some simple queries in the table with problems (like select count(*) from table_name), and the query works fine, but only when trying some more complex queries I can find that the table is corrupt and I need to fix it. Sometimes after fixing the table no records are deleted, but sometimes one or more records are deleted after fixing it. * Does this happen to the same tables all the time, or is that random as well? I found that it happends in more tables, but especially with one of them. That table has more than 2 million records and it is a MyISAM table. Should I use InnoDB instead? (Or another storage system?) That table is updated by a single program which runs continuously a few hours every day, and the program add (just addings and no updates) aproximately 1 records in those few hours... so they are not very very many. But other programs query that table very often. * Is this a precompiled binary from MySQL or did you build it yourself? It is a precompiled version from MySQL. I could see that if you compiled it yourself against some buggy libraries you could have problems; perhaps a cronjob is doing some copy/restore process on the underlying files without shutting mysql down or flushing logs; perhaps a lot of things...more information is needed. I have also seen (in most of the tables if not all) that after using check table table_name for the first time, I receive the message that the table was not closed by a few processes (from 2 to 6 processes). If I use that query a second time, I receive the message that the table is ok, and that message doesn't appear again. It has been my experience (on Windows NT, Solaris and Linux platforms) that MySQL has been one of the more reliable programs out there. Even after system crashes I haven't lost any data; a repair table and index rebuild fixed things. Yes in some cases it is the same for me, but after reparing a table, sometimes it tells me that some records were deleted because before that repair query the number of records reported is bigger. steve Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
grouping
Hi, I want to use: select column1, column2, max(column3) as maximum from table_name group by column 1; Please tell me if the values from column2 will contain the values from those records where the column3 has the maximum value. If it doesn't, please tell me how to do this as fast as possible. I know that I could get the maximum values, than make another query and get the values from the lines that have that max value, but I think this will take too much time. Thank you. Octavian -- 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
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]
selecting the last 10 rows
Hi, Is it possible to select the last 10 rows from a table in the order of their IDS, without making a separate query for finding the number of the rows? I am thinking to something like: select * from table_name order by date desc, time desc limit 10; This will select the last 10 rows, but in descending order, and I need them in ascending order. Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what are those MySQL files for?
Hi, In the /data/database_name directory I have found a few files I don't know what they are used for. I have seen that some of them are pretty big. I don't think it is safe to delete them, but can I do something to decrease their size at least? Here are those files and their sizes in MB: 1 #sql-2a91_cdf.frm 397 #sql-2a91_cdf.MYD 253 #sql-2a91_cdf.MYI 1 #sql-6094_2.frm 397 #sql-6094_2.MYD 1 #sql-6094_2.MYI 2 #sql-6094_2.TMD Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
increase the search speed
Hi,
I have the following table:
CREATE TABLE `z` (
`hash` varchar(16) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`body` text NOT NULL,
FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
I have tried the following query:
select sql_calc_found_rows hash, title, substr(body, 1, 250) as preview,
match(title, body) against('deputat') as rank from z where match(title,
body) against('deputat' in boolean mode) order by rank desc limit 0,20;
The table has almost 200.000 records.
I am using MySQL 5.0.16-standard.
I want to search the records that contain a certain word (or more
complicated expressions), so I need using a boolean mode search.
However, I want to return only the first 20 records ordered by rank, so
that's why I
also need to use a common search (not in boolean mode) for getting that
rank.
I have set MySQL to also index the 3 chars words.
The problem is that this query takes more than 12 seconds, and for some
other one-word searches it takes almost 30 seconds, and this is very
much for a table with only less than 200.000 records.
Can I do something to increase the search speed?
I think that maybe if I will change some MySQL settings, the search might
work faster, but I don't know what I need to change.
Thank you.
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
increasing the search speed
Hi,
I have the following table:
CREATE TABLE `z` (
`hash` varchar(16) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`body` text NOT NULL,
FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
I have tried the following query:
select sql_calc_found_rows hash, title, substr(body, 1, 250) as preview,
match(title, body) against('deputat') as rank from z where match(title,
body) against('deputat' in boolean mode) order by rank desc limit 0,20;
The table is made just for testing, with as few fields as possible (only 3).
The table has almost 200.000 records.
I am using MySQL 5.0.16-standard.
I want to search the records that contain a certain word (or more
complicated expressions), so I need using a boolean mode search.
However, I want to return only the first 20 records ordered by rank, so I
also need to use a common search (not in boolean mode) for getting that
rank.
I have set MySQL to also index the 3 chars words, and not only the words
with 4 or more chars because I need to be able to search for those words.
The problem is that this query takes more than 12 seconds, and this is very
much for a table with only less than 200.000 records.
Can I do something to increase the search speed?
I think that maybe if I will change some MySQL settings, the search might
work faster.
I also have a list with stop words and the program I use doesn't search for
those words anyway. Can I tell MySQL to not index the words from that list,
and if yes, please tell me how. I have read some things in the docs and I
have tried them under Windows, but without any effect.
Can I configure MySQL to use that list of stop words without re-compiling
it?
I would also like to define some chars as word-chars, for example:
ăâşţî-ĂÂŞŢÎ
Can this be done without re-compiling MySQL? (But this is not so important.
The most important is the search speed increase.)
Thank you very much.
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
searching for words with special chars
Hi, Is it possible to create a query that searches for records which contains words with special chars and with their english correspondents? For example, if a user searches for mata, I want to return all the records that contain the words: mata măta mâţa mâţă (just like Google does). Is it possible with MySQL, or I need to create all the possible combinations in the client program, then search for all those words? Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fultext search issues
From: Gabriel PREDA [EMAIL PROTECTED]
You ought to use the *Boolean Full-Text Searches.*
You would then do a:
SELECT title, Comment FROM table_name WHERE MATCH (Comment) AGAINST ('+foo
+bar' IN BOOLEAN MODE);
This way the rows that contain both words have higher relevance... those
that have only one... will have lower relevance.
I thought that using a + char before a word will match *only* those
records that contain that word.
(And using - before words, will find only those records that don't contain
the specified words.)
And using the words with no special signs before, I thought it will give a
higher precedence to the records which contain more searched words.
And I also thought that the rows are not sorted automaticly when searching
in boolean mode.
So I usually search using:
select id, title, match(body) against('word') as rank from table_name where
match(body) against('word' in boolean mode) order by rank;
The search doesn't work slower (or much slower) because it uses twice the
match, the search is made in boolean mode so the +, -, *, , , ,
characters can be used, and the results are sorted.
But is there a better way?
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication
Hi, I have 2 servers. On one of them I have MySQL 4.1 (the main server) and on the second I have MySQL 5.0. I want to use the second server to replicate the first server. Is it possible or the servers should have the same version? Or I will need to install one more MySQL 4.1 on the second server and use it for replicating the first server? Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting based on a max() condition
Hi, I have a table with 3 relevant columns: symbol varchar(10) not null price decimal not null and last_update datetime not null I want to select the list of unique symbols and the corresponding price and last_update fields for each line where the last_update is equal to the last_update field for each symbol. I have tried without succes: select symbol, price, last_update from table where last_update = (select max(last_update) from table); This gave me a single row. I have tried more complex queries that took a lot of time and I needed to break them with ^C. Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting min, max
Hi, I have a table with the following fields: symbol, date_time, price, volume I need to extract a list which the following values from this table, for each hour (in date_time field): - symbol - min(price) - max(price) - price where date_time is the earliest for that certain hour. - price where the date_time is the last from that hour. - The sum of volume from that hour. I have tried to get the list of symbols, then get each hourly period and calculate those 6 values for each period, but there are many symbols and very many periods, and it takes very very much time. Is there a more intelligent way of getting those values in another way than symbol by symbol and period by period? Thank you very much. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting min, max
Hi, From: Rhino [EMAIL PROTECTED] ... I need to extract a list which the following values from this table, for each hour (in date_time field): - symbol - min(price) - max(price) - price where date_time is the earliest for that certain hour. - price where the date_time is the last from that hour. - The sum of volume from that hour. I have tried to get the list of symbols, then get each hourly period and calculate those 6 values for each period, but there are many symbols and very many periods, and it takes very very much time. Is there a more intelligent way of getting those values in another way than symbol by symbol and period by period? It's hard to answer your question since you haven't given us any examples of the SQL you've already tried. You haven't told us which version of MySQL you are using, either. That makes a big difference since newer versions offer many more SQL capabilities like views and subqueries that could really help you. You certainly shouldn't have to write separate queries for each different symbol that you are using! Here is the table definition. The table is simple, but what I want is complicated: CREATE TABLE `tickers` ( `symbol` varchar(20) NOT NULL, `last_volume` bigint(20) unsigned default NULL, `last_price` decimal(20,4) unsigned default NULL, `last_update` datetime default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I want to get a list of values for more periods of time, 5 minutes, 15 minutes, and hourly. I need to get: symbol date_format(last_update, '%Y-%m-%d') as date date_format(last_update, '%H:%i:%s') as time min(last_price) as low (The min value of last_price for that period) max(last_price) as high (the max price from that period) last_price as open (where last_update=min(last_update) from that period) last_price as close (where last_update=max(last_update) from that period) The result data should look something like: Symbol,data,time,low,high,open,close simb1,2006-02-08,10:15:00,1000,1200,1050,1150 simb1,2006-02-08,10:30:00,1100,1150,1150,1150 simb1,2006-02-08,10:45:00,1000,1200,1050,1150 simb1,2006-02-08,11:00:00,1050,1200,1050,1150 simb1,2006-02-08,11:15:00,1000,1200,1050,1150 ... then here follow the rest of records for simb1 and for other symbols. You may see that the first time is 10:15:00, the next time is 10:30:00, the next is 10:45, so the period of time is 15 minutes. The first low is the lowest price between 10:15:00 and 10:30:00 and the high is the highest price in that period. The first open value is the last_price of the first trade from that period and the close price is the last_price of the latest trade from that period. I don't know if MySQL can create a query that can get those values fast enough. Thank you very much. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting min, max
PS, I have forgotten to tell that I am using MySQL 5. Thank you. Teddy - Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, February 13, 2006 10:00 PM Subject: Re: selecting min, max Hi, From: Rhino [EMAIL PROTECTED] ... I need to extract a list which the following values from this table, for each hour (in date_time field): - symbol - min(price) - max(price) - price where date_time is the earliest for that certain hour. - price where the date_time is the last from that hour. - The sum of volume from that hour. I have tried to get the list of symbols, then get each hourly period and calculate those 6 values for each period, but there are many symbols and very many periods, and it takes very very much time. Is there a more intelligent way of getting those values in another way than symbol by symbol and period by period? It's hard to answer your question since you haven't given us any examples of the SQL you've already tried. You haven't told us which version of MySQL you are using, either. That makes a big difference since newer versions offer many more SQL capabilities like views and subqueries that could really help you. You certainly shouldn't have to write separate queries for each different symbol that you are using! Here is the table definition. The table is simple, but what I want is complicated: CREATE TABLE `tickers` ( `symbol` varchar(20) NOT NULL, `last_volume` bigint(20) unsigned default NULL, `last_price` decimal(20,4) unsigned default NULL, `last_update` datetime default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I want to get a list of values for more periods of time, 5 minutes, 15 minutes, and hourly. I need to get: symbol date_format(last_update, '%Y-%m-%d') as date date_format(last_update, '%H:%i:%s') as time min(last_price) as low (The min value of last_price for that period) max(last_price) as high (the max price from that period) last_price as open (where last_update=min(last_update) from that period) last_price as close (where last_update=max(last_update) from that period) The result data should look something like: Symbol,data,time,low,high,open,close simb1,2006-02-08,10:15:00,1000,1200,1050,1150 simb1,2006-02-08,10:30:00,1100,1150,1150,1150 simb1,2006-02-08,10:45:00,1000,1200,1050,1150 simb1,2006-02-08,11:00:00,1050,1200,1050,1150 simb1,2006-02-08,11:15:00,1000,1200,1050,1150 ... then here follow the rest of records for simb1 and for other symbols. You may see that the first time is 10:15:00, the next time is 10:30:00, the next is 10:45, so the period of time is 15 minutes. The first low is the lowest price between 10:15:00 and 10:30:00 and the high is the highest price in that period. The first open value is the last_price of the first trade from that period and the close price is the last_price of the latest trade from that period. I don't know if MySQL can create a query that can get those values fast enough. Thank you very much. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fulltext searches
Hi,
I have tried:
select title from table where match(title, body) against('IT' in boolean
mode);
The result was 0 records.
I have checked the min word lenght which is allowed with:
mysql show variables like '%ft_min_word_len%';
+-+---+
Variable_name | Value |
+-+---+
ft_min_word_len | 2 |
So the word IT should be found, because if I search using ... like '% IT
%'... there are found some records.
Is IT a stop word? If yes, how can I make it be a normal word?
Thank you.
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dictionary
Try searching on www.dict.org Teddy - Original Message - From: Peter of Pedsters Planet [EMAIL PROTECTED] To: Mysql mysql@lists.mysql.com Sent: Wednesday, February 01, 2006 8:27 PM Subject: Re: Dictionary I'd like to know too if posible :) On 01/02/06, Scott Hamm [EMAIL PROTECTED] wrote: I've been trying to google to no avail for English dictionary (with definitions) in any format that I can download and import into MySQL. Do anyone know where I can find it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.0/248 - Release Date: 2/1/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I do a boolean search and get the row count in 1 SQL query?
Hi,
You can do:
select sql_calc_found_rows [and here follow the rest of the select query];
And then you can get the number of all found rows, not only those got by
limit 10 as follows:
select found_rows();
Teddy
From: Grant Giddens [EMAIL PROTECTED]
Hi,
I have a web app where I am doing a boolean search. I only want
to return 10 results per page, but I'd also like to know how many total
rows match the search query. I'm currently performing this with 2
query statements:
1. (To get the actual rows via the search)
SELECT $product_column[title],
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT $lower_limit,10
2. (To get the total number of results)
SELECT COUNT(*) as num_results
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
The queries might be a bit off as I don't have the exact code in
front of me right now.
Is there a way I can combine this into 1 query?
Thanks,
Grant
-
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays,
whatever.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2 questions
Hi, I have used mysqldump with 5.0.15-nt and I have seen that it saves the file in UTF-8 format. This is OK, but if I try to run: mysql database saved_file.sql It gives an error near some special chars (because the file is not ANSI). If I convert the file as ANSI, I can import the data from it into MySQL without problems. Am I doing something wrong? The second question is related to explain. Is there a explain function for update as there is for select? Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 questions
Hi, Here is the error: mysql -u odbc database zzz.sql ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL serv er version for the right syntax to use near '´¬¬ /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */' at line 1 But I have discovered that this happends only if I re-save the .sql file with TextPad. After saving the file, even without modifying it, the file size decreases with 3 bytes, and I think TextPad saves the file but without the byte order mark for UTF-8. So MySQL might think that the file is ANSI, and detect some bad chars. Teddy - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, December 24, 2005 23:47 PM Subject: Re: 2 questions Hello. It gives an error near some special chars (because the file is not ANSI). Please, could you provide the error message. Check if it disappears if you perform the dump with --quote-names option. The second question is related to explain. Is there a explain function for update as there is for select? An update statement is optimized like a SELECT query with the additional overhead of a write. So you can take the optimizer plan from the corresponding SELECT statement (with the same WHERE clause and table references). Octavian Rasnita wrote: Hi, I have used mysqldump with 5.0.15-nt and I have seen that it saves the file in UTF-8 format. This is OK, but if I try to run: mysql database saved_file.sql It gives an error near some special chars (because the file is not ANSI). If I convert the file as ANSI, I can import the data from it into MySQL without problems. Am I doing something wrong? The second question is related to explain. Is there a explain function for update as there is for select? Thank you. Teddy -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- 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]
fulltext search
Hi, Please tell me how can I configure MySQL 5 in order to be able to search (using fulltext indexes) for combined words like s-au. This is a single word and not 2 words but I think MySQL thinks that there are 2 words, one of them having a single character, and the second 2 chars, so it is not found because I have configured MySQL to index only the words that have at least 3 chars. I don't think it would be a good idea to configure it to index one-char words, so I hope there is another method. Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
AFAIK you are right - MySQL treats a hypen as a word-break. And, AFAIK you
cannot modify that behaviour.
The only possibility, I think, would be to modify the source and compile
your own MySQL. :-(
However if you do a full-text search using IN BOOLEAN MODE, then you can
put quotes around hypenated words.
... MATCH (col_name) AGAINST ('s-au' IN BOOLEAN MODE) ...
HTH,
James Harvard
I have tried that, but it doesn't found anything. I think this is because
MySQL doesn't put the words s and au in the fulltext index at all, so it
is not able to find s-au.
Isn't possible to set somewhere which are the word chars?
I am not sure how other special chars (which are real chars) like s, t, â,
a, î, S, T, Â, Î, A are viewd by MySQL fulltext index... as word chars, or
as word break chars.
If I could add some of these chars, maybe I could also add the -
character.
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bug in MySQL 5?
Hi, I have tried: mysql create table z(id int unsigned not null primary key, first_name varchar(20), last_name varchar(20)); Query OK, 0 rows affected (0.06 sec) mysql insert into z values(1, 'John', 'Smith'), (2, 'George', 'Washington'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select * from z; ++++ | id | first_name | last_name | ++++ | 1 | John | Smith | | 2 | George | Washington | ++++ 2 rows in set (0.00 sec) mysql replace into z values(1, 'Michael', 'Rifle'); Query OK, 2 rows affected (0.00 sec) mysql select * from z; ++++ | id | first_name | last_name | ++++ | 1 | Michael| Rifle | | 2 | George | Washington | ++++ 2 rows in set (0.00 sec) mysql Is there a bug that MySQL says Query OK, 2 rows affected (0.00 sec) although only a single row was modified (as it should)? I am using the version 5.0.15-nt. Thanks. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bug in MySQL 5?
From: Gleb Paharenko [EMAIL PROTECTED]
Hello.
In my opinion, it is not a bug. REPLACE has returned the sum
of affected rows - one was deleted, and one was inserted. See:
http://dev.mysql.com/doc/refman/5.1/en/replace.html
Thank you. I have seen that's the true.
Is there any MySQL command that instead of
if(record exists) {
delete line
insert new line
}
else {
insert line
}
does something like:
if (record exists) {
update record with the specified fields
}
else {
insert record
}
Thank you.
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: install trouble, perl DBI
From: Lewis Ashley Foster [EMAIL PROTECTED] I'm trying to get mySQL installed on my machine but im having a bit of trouble, obviously :) When i come to install mySQL server as below: rpm -ivh MySQL-server-standard-5.0.16-0.rhel3.i386.rpm I get this result: warning: MySQL-server-standard-5.0.16-0.rhel3.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: perl(DBI) is needed by MySQL-server-standard-5.0.16-0.rhel3 Suggested resolutions: perl-DBI-1.32-5.i386.rpm But I have already installed perl dbi 1.40 like this with no errors: rpm -ivh perl-DBI-1.40-5.src.rpm I really cant think how to get round this, unless i downgrade the dbi to 1.32 but that seems daft. Hi, Try this command: perl -MDBI -e1 If it will tell you that it can't find the DBI module, this means that DBI was not properly installed. If it will give no results, this means that you have the DBI module installed, so try the following command: perl -M'DBI 999' -e1 It will give an error telling that you don't have DBI version 999 installed but only the version ... and here it will tell the version number. If it is a newer version that the version required by MySQL RPM, then perhaps there is an error in the format of the version of DBI, or most probably in the way MySQL checks that version and don't like a newer one. You have some solutions: 1. Manually edit the file DBI.pm after it is installed on the computer and write the older version instead. Then install MySQL and then change the version back in DBI.pm file. The newer DBI will surely work with MySQL. 2. Install the older version of DBI, install MySQL, then install again the latest version of DBI module. If rpm doesn't allow you to do this because you might need to remove the older version of DBI, you can download the module DBI from search.cpan.org, and compile and install it using: perl Makefile.pl make make test make install or use the command: cpan (But in this case you will need to answer to some configuration settings) Then do: cpan install DBI This will download and install the latest version of DBI. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set issue ( maybe )
From: Daniel Kasak [EMAIL PROTECTED] OK then. Lets re-word the question ... Has anyone been able to successfully enter text of a non-standard character set ( Latin 1, UTF8 ) into Query Browser? How about upload via a Perl script? Yes you can insert those chars in MySQL using a perl program, but you cannot do it using Windows' copy and paste from a web page. I think this is because of Windows clipboard that doesn't copy the text correctly. For example, you can copy some special chars like s t a from a web page into a common text processor and you will see that they are not copied correctly. They are changed with other chars like ?. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot start MySQL under Win2000
From: Stephen Cook [EMAIL PROTECTED] Subject: Re: Cannot start MySQL under Win2000 You must not install 5.0 over 4.1, put it somewhere else. Also, instead of naming the service MySQL both times, call it something else (i.e. MySQL41 and MySQL50). If you use the Windows installer distribution, it is one of the options; if you are doing it by hand then you already know how to name the services whatever you like. HTH. I have installed MySQL 4.1 in e:\mysql and MySQL 5.0 in e:\mysql5. MySQL 4.1 service name is mysql and MySQL 5.0 service name is mysql5, so they are 2 totally separate programs. However, I want them both use the same port, because I will run only one of them in a certain moment, and not both, so there should be no conflict. I want this because I want to test the speed difference between MySQL 4 and MySQL 5 and I don't want to make any change in the program, but change only the service that listen to MySQL port... However, I've just read that the port should be different, even though I don't know why, and maybe this is the reason I cannot start MySQL 4 even if MySQL 5 is stopped. Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cannot start MySQL under Win2000
Hi, Is it possible to install 2 versions of MySQL on the same computer? I want to have MySQL 4.1 and 5.0 installed, and use only one of them at a time. I have installed MySQL 5.0 over MySQL 4.1 and now MySQL 5 works fine, however, I cannot start MySQL 4.1 although I have stopped MySQL 5 first. When I try to: net start mysql (where mysql is the name of MySQL 4.1 service) or tried to start the service from the Windows management console (Services), the following error appears: Could not start the MySQL service on Local Computer. Error 1067: The process terminated unexpectedly. Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error with PHP: undefined function: mysql_connect()
From: Ciprian Constantinescu [EMAIL PROTECTED] In PHP 4.1 you have to include the mysql.so extension. To do so, you have to modify php.ini, extensions section Or better, add that extension runtime because otherwise it will just consume computer resources, and maybe not every PHP program will need to connect to MySQL. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two MySQL databases on different computers
From: [EMAIL PROTECTED] Hi, I have two databases. Database A is located on a server that I run my web hosting from. The other database B is located on a computer with a fixed IP. How can I configure database B so I can access database B from my web server? From my A system I would like to be able to do INSERT, SELECT and UPDATE queries on the database B. Best regards, Peter Lauri All you need is do is to setup a MySQL user (not a system user) account on B that your application on A can use. Use the GRANT statement to do this. I am also interested in this and I know that I need to create a MySQL user on the second database, but I don't know if it is possible to use a query like: select database1.users.address from database1, database2 where database1.users.username=database2.members.username; I kno this is possible if both databases are using the same database management system (on the same computer), but I don't know if it is possible to do it with 2 different systems. (But I think the client application will need to make 2 separate queries to each database.) Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
low speed select
Hi, I have tried: mysql select count(*) from table_name where date='2005-11-07' and id=11; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (46.42 sec) As you may see, this query took more than 46 seconds and I don't know why. I am the single person that was using the database in the moment I've made that query, and there are no programs that use to lock the tables until finishing some other queries anyway. The table has an index on the data field and another index on the id field, and usually takes much less time for such a query. This database is used in a web site and I see sometimes that it takes a very long time for displaying some pages and now I know that the problem is the database. The version of MySQL which is installed on the computer I work is: 4.1.5-gamma-standard-log I know it could be a little older, but this could be the only problem? (and the fact that is a gamma version?) Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: renaming the database
Thanks. I want to rename the database in order to keep it as a backup, then to temporarily create a new one with the same name as a test, because more programs use that database name. Teddy - Original Message - From: Jigal van Hemert [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, October 17, 2005 12:11 PM Subject: Re: renaming the database Octavian Rasnita wrote: Is there a command for renaming a MySQL database? Sorry, there is no command for that. For small databases you can use administration tools like phpMyAdmin, etc. which will do it for you by duplicating the database structure and data, and then drop the 'old' database. For big databases this will take quite long, so it's probably better to stick with the old name (what's in a name? ;-) ) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
renaming the database
Hi, Is there a command for renaming a MySQL database? Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
creating socket
Hi, I have installed mysql and loaded mysqld, but I cannot connect to it with mysql because it gives an error telling that it cannot connect using socket /var/lib/mysql/mysql.sock. I have checked that dir, but I don't have that socket file there. Please tell me how to create it. I don't know Linux very well, so please excuse my ignorance in this field. Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: creating socket
From: Sujay Koduri [EMAIL PROTECTED] This error means that the mysql server has not started in the first place. So try to have a look in the logs (generally this should be in /var/lib/mysql/) and find out the reason why the server hasn't started. sujay I have discovered that mysql creates the socket in /tmp/mysql.sock instead of /var/lib/mysql/mysql.sock. I have created a link from /tmp/mysql.sock to /var/lib/mysql/mysql.sock and now MySQL works fine. However, I think I should change somewhere in the file my.ini the location of the socket... Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table names with periods
From: Chance Ellis [EMAIL PROTECTED] Point taken and yes the manual was reviewed. However, I thought in the past that I had seen someone post a method in which table names could be created with special characters. Can't you use a single table instead of more tables? Or each table has different columns? If all the tables have the same fields, I think you could add a new column named ip, and in each of your queries you can add... and ip='...'. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
too many connections
Hi,
I guess it is a stupid simple question:
I have seen the following error in the log files:
DBI connect('database=[database]','[username]',...) failed: #08004Too many
connections at /[path_to_script] line 12
I have taken a look in my.cnf but I couldn't find some settings for
increasing the possible number of connections or something like that.
Can you tell me what can I do to do this?
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Behaviour of like expression
Yes, in perl is OK, but in MySQL it is not really ok. Perl regular expressions use ^ and $ for specifying the start and end of the string while MySQL like operator doesn't For example, if $string = 'a', // with match in perl, but /^$/ won't match. MySQL like operator could use '%' for matching everything but it don't have any way to match only the empty string, because it doesn't have a way of specifying the start and end of the string. Teddy - Original Message - From: Josh Chamas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Sunday, September 11, 2005 16:18 PM Subject: Re: Behaviour of like expression [EMAIL PROTECTED] wrote: Hi all, I've got a question. We were testing something on our mysql server ( 4.0.21) with MyISAM tables. When we executed the query select * from people where name like ''; we expected the same results as select * from people where name=''; but it didn't. The like function returned everything instead of only the people without a name. Is this known (and correct) behaviour? It does not sound logical to me. I couldn't find anything about it on the mysql website. Usually, pattern matching done where a pattern is nothing matches everything. Use LIKE for pattern matching, and = for equivalency. I don't know what the ANSI SQL spec says here, and whether our behavior is correct there, but this behavior makes sense to me at least. I would expect similar behavior from perl, for example: $ perl -e 'my $a = qw(a); print $a =~ //;' 1 Regards, Josh -- Josh Chamas Director, Professional Services MySQL Inc., www.mysql.com Get More with MySQL! www.mysql.com/consulting -- 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]
renaming a database
Hi, Please tell me how to rename a database. I couldn't find this in the manual. I have tried to rename manually the name of the directory that holds that database, but some tables can't be accessed after that. (I have found that they are InnoDB tables, even though I never specified that I want this type of engine when creating the tables). Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query
Hi, I want to create a query that selects the diference between the value of a field from the current record and the value of the same field from the previous record. Is this possible? It should return a list of diferences. Or, at least I should be able to do that query to do this diference between 2 specified records. Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query
Hi, I define the previous record by date. For each record corresponds a date which is unique and the previous record is that that contains the yesterday date. Thank you. Teddy - Original Message - From: Felix Geerinckx [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 16, 2005 2:45 PM Subject: Re: query On 16/08/2005, Octavian Rasnita wrote: I want to create a query that selects the diference between the value of a field from the current record and the value of the same field from the previous record. How do you define current record and previous record? (relational databases are not spreadsheets) -- felix -- 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: Question about BLOB
Hi, I think that I have seen many times the advice not to store the images in MySQL, but to store them on the hard disk while putting the path to them in the database. They could be accessed much faster this way. Teddy - Original Message - From: Gelu Gogancea [EMAIL PROTECTED] To: Jay Blanchard [EMAIL PROTECTED]; MySQL mailing list mysql@lists.mysql.com Sent: Tuesday, August 02, 2005 13:50 PM Subject: RE: Question about BLOB Hi, Is not the first time when i read this this page. So, the conclusion is that until MySQL version 5.0.3 the empty spaces are remove if i wish to store the binary data in a table. Is not any other solutions?Because for the moment i convert the binary data into ASCII(printing ASCII) string,i add slashes after each converted character and after all this i store in the BLOB field on the table.And i wish to avoid this procedure...wasting a lot of time. Thanks, _ G.NET SOFTWARE COMPANY SYSTEM INTEGRATOR - AUTOMATION SOFTWARE DEVELOPER http://www.gonetsoftware.com Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Monday, 01 August, 2005 8:41 PM To: Gelu Gogancea; MySQL mailing list Subject: RE: Question about BLOB [snip] I have a table with a MEDIUMBLOB field and i wish to store a picture(.BMP) which the size is 10622 bytes.But when i look into the table i see only 9582 bytes. Please, i wish to know for what reason on the table are few bytes than original. I use MySQL 5.0 - Fedora Core 3 [/snip] Have a look here, http://dev.mysql.com/doc/mysql/en/blob.html -- 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]
general question
Hi, I am using a MySQL database on a web site, and I would like to know what happends if someone searches in the database using a form, but after a few seconds MySQL starts the query, that user hit the Stop button of the browser. Will MySQL continue its searching and also create the cache, or it will stop automaticly? If it will also stop, can I do something to let it continue searching in order to create the cache and the next time another visitor searches for the same thing it will get the results from the cache? Sorry if this is a stupid question and thank you very much. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: more queries vs a bigger one
Hi,
I have finally modified that long query and splitted into smaller ones.
Now the main query is:
select sql_calc_found_rows
a.pre_title, a.title, a.post_title, substring(a.body, 1, 250) as preview,
a.hash, a.date, a.time, length(a.body) as size, a.id_categories,
n.name as newspaper, sc.category
from articles a, newspapers n, sections_categories sc
where a.id_newspapers=n.id
and a.id_categories=sc.id
and a.active_view=1
and a.id_categories=20
limit 0,30;
For each of those 30 records which are returned, I make other 3 queries:
select count(*) from articles_comments where hash_articles='[an MD5 hash
with 16 characters]';
select count(*) from articles_count where hash_articles='[an MD5 hash with
16 characters]';
select name, email, query from articles_authors where hash_articles='[an MD5
hash with 16 characters]';
Well, now instead of making a big query, MySQL will make 91 queries.
I have tested the program, but it doesn't work faster at all.
Do you have any idea what could be wrong?
There are almost 100.000 records in the database, and this query should
return 10121 records.
Here is the data structure. Please tell me if you think there is something
bad in it.
Thank you.
Teddy
DROP TABLE IF EXISTS `articles`;
CREATE TABLE `articles` (
`id_newspapers` smallint(3) unsigned NOT NULL default '0',
`id_sections` smallint(3) unsigned NOT NULL default '0',
`id` int(6) unsigned NOT NULL auto_increment,
`hash` char(16) NOT NULL default '',
`url` varchar(255) NOT NULL default '',
`full_url` varchar(255) NOT NULL default '',
`pre_title` varchar(255) not null default '',
`title` varchar(255) NOT NULL default '',
`post_title` varchar(255) not null default '',
`body` text NOT NULL,
`body_hash` char(32) NOT NULL default '',
`article_ident` varchar(255) not null default '',
`date` date NOT NULL default '-00-00',
`time` time NOT NULL default '00:00:00',
`id_categories` tinyint(3) unsigned not null default '0',
`active_view` tinyint(1) unsigned not null default '1',
PRIMARY KEY (`id_newspapers`,`id_sections`,`id`),
UNIQUE KEY `id_newspapers` (`id_newspapers`,`body_hash`),
UNIQUE KEY `hash` (`hash`),
KEY `date` (`date`),
KEY `id_categories` (`id_categories`),
FULLTEXT KEY `full` (`pre_title`,`title`,`post_title`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `articles_authors`;
CREATE TABLE `articles_authors` (
`hash_articles` char(16) NOT NULL default '',
`name` varchar(30) NOT NULL default '',
`email` varchar(255) not null default '',
`query` varchar(255) not null default '',
UNIQUE KEY `hash` (`hash_articles`,`name`),
KEY `hash_articles` (`hash_articles`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `articles_comments`;
CREATE TABLE `articles_comments` (
`hash` char(16) NOT NULL default '',
`hash_articles` char(16) NOT NULL default '',
`hash_users` char(16) NOT NULL default '',
`body` text NOT NULL,
`hash_original` char(16) not null default '',
`comment_type` enum('public','private') NOT NULL default 'public',
`date` date NOT NULL default '-00-00',
`time` time NOT NULL default '00:00:00',
`remote_address` varchar(255) not null default '',
PRIMARY KEY (`hash`),
KEY `hash_articles` (`hash_articles`),
KEY `hash_users` (`hash_users`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `articles_count`;
CREATE TABLE `articles_count` (
`id` int(10) unsigned NOT NULL auto_increment,
`hash_articles` char(16) NOT NULL default '',
`hash_users` char(16) not null default '',
`date` date NOT NULL default '-00-00',
`time` time NOT NULL default '00:00:00',
`remote_address` varchar(255) NOT NULL default '',
`user_agent` varchar(255) not null default '',
PRIMARY KEY (`id`),
UNIQUE KEY `hash` (`hash_articles`,`date`,`remote_address`),
KEY `hash_articles` (`hash_articles`),
KEY `hash_users` (`hash_users`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `newspapers`;
CREATE TABLE `newspapers` (
`id` smallint(3) unsigned NOT NULL auto_increment,
`label` varchar(20) NOT NULL default '',
`name` varchar(255) NOT NULL default '',
`script` varchar(20) NOT NULL default '',
`first_page` varchar(255) NOT NULL default '',
`base_url` varchar(255) not null default '',
`email` varchar(255) not null default '',
`importance` tinyint(3) unsigned not null default '0',
`category` tinyint(1) unsigned not null default '3',
`frequency` enum('week','month') NOT NULL default 'week',
`sect_skip` smallint(5) unsigned default NULL,
`sect_end` smallint(5) unsigned default NULL,
`art_skip` smallint(5) unsigned default NULL,
`art_end` smallint(5) unsigned default NULL,
`preview_size` smallint(3) unsigned not null default '250',
`view_size` smallint(5) unsigned default NULL,
`active_download` enum('yes','no') not null default 'yes',
`active_view` enum('yes','no') not null default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY
What does this error mean?
Hi, I have tried the following query and it works fine. It takes 11 seconds and this is a little too much, but this is another issue. The problem is that if I delete the following condition from it: a.id_categories=31 The query gives the following error: ERROR 1032 (HY000): Can't find record in '' What can I do to make it work? Thank you. select straight_join sql_calc_found_rows a.hash, a.pre_title, a.title, a.post_title, substring(a.body, 1, 250) as preview, a.hash, a.date, a.time, length(a.body) as size, a.id_categories, n.name as newspaper, sc.category, count(act.id) as visitors, count(aco.hash) as comments from articles a inner join newspapers n on(a.id_newspapers=n.id) inner join sections s on(a.id_sections=s.id and n.id=s.id_newspapers) inner join sections_categories sc on(a.id_categories=sc.id) left join articles_count act on(a.hash=act.hash_articles) left join articles_comments aco on(a.hash=aco.hash_articles) where a.id_categories=31 and a.date between '2005-01-01' and '2005-12-31' and a.active_view=1 group by a.hash order by visitors limit 0,30; Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: more queries vs a bigger one
From: [EMAIL PROTECTED] Subject: Re: more queries vs a bigger one Hello, approx. how long does it take your big query to run as it is now? Are these queries appending a table? or are they buiding a result (from a chain of queries)? Have you tried separating them out? Any difference? -sam That query takes more than 2 minutes. I have a table with less than 90.000 records, and this is the bigger table from the database, so the query is very slow, because as you may see, the tables are not very big. But that query might return more than 9.000 records, even though I limit it to first 30. I will split it into more smaller queries and I will report if it works faster. The query is something like the example below, but I don't have it here right now to send it to the list, but I will send it soon: select a.pre_title, a.title, a.post_title, substring(a.body, 1, n.preview_size) as preview, n.title as publication, a.id_category, cs.label, count(aco.hash_articles) as comments, count(act.hash_articles) as counter from articles a left join newspapers n on(a.id_newspapers=n.id) left join sections s on(a.id_sections=s.id and s.id_newspapers=n.id) left join sections_categories cs on(a.id_categories=cs.id) inner join articles_counters act on(a.body_hash=act.hash_articles) inner join articles_comments aco on(a.body_hash=aco.hash_articles) where a.id_category=20 and a.date between '2005-01-01' and '2005-12-31' group by a.body_hash order by rand(); The table newspapers has only 20 records. The table sections has under 300 records, but the tables articles_counter and articles_comments might have many records... hundread of thousands millions. Thanks. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slow query
Hi, I have a problem with a query, because it works very slow and I am trying to analyze it with explain. I have read that if I use STRAIGHT_JOIN in a select query, the server will take the tables in the order I specified them in that query. I have tried to explain a query that uses STRAIGHT_JOIN , but I have seen that the tables that appear as a result, don't appear in that order. Here is the query: explain select STRAIGHT_JOIN sql_calc_found_rows a.pre_title, a.title, a.post_title, substring(a.body, 1, n.preview_size) as preview, a.body_hash, a.date, a.time, length(a.body) as size, a.id_categories, n.name as newspaper, sc.category, count(act.id) as visitors, count(aco.hash) as comments from articles a inner join newspapers n on(a.id_newspapers=n.id) inner join sections s on(a.id_sections=s.id and n.id=s.id_newspapers) inner join sections_categories sc on(a.id_categories=sc.id) left join articles_count act on(a.body_hash=act.hash_articles) left join articles_comments aco on(a.body_hash=aco.hash_articles) where n.active_view='yes' and s.active_view='yes' and a.date between '2005-01-01' and '2005-12-31' group by a.body_hash order by visitors desc, comments desc, a.title limit 0,30\G The result is below, but I have no idea if there is something wrong with my query or what could be the problem. Thank you for your advice. Teddy *** 1. row *** id: 1 select_type: SIMPLE table: aco type: system possible_keys: hash_articles key: NULL key_len: NULL ref: NULL rows: 0 Extra: const row not found *** 2. row *** id: 1 select_type: SIMPLE table: a type: index possible_keys: PRIMARY,id_newspapers,date,id_categories,id_sections key: body_hash key_len: 32 ref: NULL rows: 89285 Extra: Using where; Using temporary; Using filesort *** 3. row *** id: 1 select_type: SIMPLE table: n type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: presa.a.id_newspapers rows: 1 Extra: Using where *** 4. row *** id: 1 select_type: SIMPLE table: s type: eq_ref possible_keys: PRIMARY,id_newspapers,active_view key: PRIMARY key_len: 8 ref: presa.n.id,presa.a.id_sections rows: 1 Extra: Using where *** 5. row *** id: 1 select_type: SIMPLE table: sc type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: presa.a.id_categories rows: 1 Extra: Using where *** 6. row *** id: 1 select_type: SIMPLE table: act type: ref possible_keys: hash_articles key: hash_articles key_len: 32 ref: presa.a.body_hash rows: 1 Extra: Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
more queries vs a bigger one
Hi, I have a big query that involves searching in more tables, and I think this might be slower than creating more smaller queries. What do you think, is this true generally? The query searches in a big table but it also counts the number of records from other 2 tables based on a criteria, and usually the result is a big number of records, but the final result is limited using limit 0,30. So I am wondering... Could it work faster if I won't count(*) the number of records in those 2 tables, but get the result (only 30 records), then for each separate record use a separate query that gets that number? I don't know, could 31 queries work faster than a single bigger and complex query? Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
creating a faster query
Hi, I have a table with a DATE type column and I want to search for more records that have the same year and month. I have tried searching with: select ... where date_format(date, '%Y-%m')='2005-06' ...; I know that if I apply a function to the date column, the index on that column is not useful and I have seen that this query works very very very slow, even though I have defined an index on the date column. Are there any other ways to create this query in order to make it work faster? Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fulltext searching using special chars
Hi, I am trying to search for a word that contains special chars like ş or ţ, but I find only the words and like when the special chars are not a part of the word. I found that if I search for ş (in boolean mode), I am able to find the records that contain the full word, but I would like to be able to do this without needing to use quotes. If it is possible, pleas tell me how. Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting more sum()
Hi, I have the following tables: create table articles( id int unsigned not null primary key, title varchar(255) not null, body text not null ); create table newspapers( id int unsigned not null primary key, name varchar(255) not null ); create table visitors( id int unsigned not null primary key, id_articles int unsigned not null ); create table comments( id int unsigned not null primary key, id_articles int unsigned not null ); I would like to select: - the title from `articles` - the length of the body from `articles` - the name of the newspaper which correspond to the title of the article - the number of visitors each articles have (count(*) from visitors where articles.id=visitors.id_articles) - the number of comments each articles have (count(*) from comments where articles.id=comments.id_articles) I don't know how to select the last 2 elements (the number of visitors and the number of comments). I want to select all the articles from `articles` even if there are no visitors or no comments in the `visitors` and `comments` tables, so I might need using left join. I have tried a few ways of doing this, but without any result. Please help me if you can. (I have written the table definitions above right in the email client, so they are not tested, but I think they are correct) Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl/mysql question
Hi all, I am using DBI and DBD::mysql to connect to a MySQL database from perl and I would like to get the execution time period after a query, like MySQL standard client shows. Sometimes when I work in the standard console client it tells me that there are x wornings. How can I see which are those warnings? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Howell, Scott [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 6:38 PM Subject: Perl/mysql question Has anyone written a perl script to get records from another database and write it to a file on mysql's LOAD DATA INFILE format? I just need some pointers in perl so a LOAD DATA INFILE will be read correctly. For example, a perl script: Select * from emp from a Progress database Will write to a text file called emp.txt in the LOAD DATA INFILE format, Then, I can to a direct LOAD DATA INFILE into my mysql database. I tried doing direct select/inserts in perl but found dumping to a text file and doing a LOAD DATA to be MUCH faster. Am I missing something here? -- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 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]
Dumping a database
Hi all, I've seen that it is very easy to export an entire database using mysqldump. Can I export any database using this program? Will it export the sql statements for creating indexes, and the binary fields? Can I restore any database exported with this method without any problem? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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
Re: Counting null values
Ok, I've tried that but it gave me an error. I have a more complex query that use more tables, and I need to specify something like: count(table_name.*) ... but here MySQL tells me that there is an error near this expression, so I need to use instead: count(table_name.a_column_name) I would like to use: count(table_name.*) ... group by a_column_name ... but this won't work because this query not only that will give me that error, but I want to take the distinct values of the a_column_name only for non null values, and for null values I want to count them all thinking that they are different values. In fact, the null values should be considered different values when using distinct. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED]; Daniel Kiss [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 12, 2003 7:09 PM Subject: Re: Counting null values At 9:41 +0200 2/12/03, Octavian Rasnita wrote: It won't work because MySQL doesn't count null values. It depends. count(FieldName) will not count NULL values count(*) will, because it counts rows, not values. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Daniel Kiss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 11, 2003 8:18 AM Subject: Re: Counting null values Hi Octavian, Try this: select FieldName, count(*) from TableName group by FieldName Bye, Danny At 16:39 2003.02.08._+0200, you wrote: Hi all, I have a table where I have something like this: | abc | | abc | | xxx | | null | | null | | null | I want to count these lines to give the result 5, meaning a distinct count for values which are not null, and counting all the null values. This means 1 for abc, one for xxx, and 3 for null values. Can you tell me what sql query should I use for counting this? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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
Re: Counting null values
It won't work because MySQL doesn't count null values. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Daniel Kiss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 11, 2003 8:18 AM Subject: Re: Counting null values Hi Octavian, Try this: select FieldName, count(*) from TableName group by FieldName Bye, Danny At 16:39 2003.02.08._+0200, you wrote: Hi all, I have a table where I have something like this: | abc | | abc | | xxx | | null | | null | | null | I want to count these lines to give the result 5, meaning a distinct count for values which are not null, and counting all the null values. This means 1 for abc, one for xxx, and 3 for null values. Can you tell me what sql query should I use for counting this? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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 - 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
Re: How to modify ft_min_word_len?
Can I create my own my.cnf file if I am not the administrator of MySQL but I just have a database? Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 11, 2003 9:24 PM Subject: re: How to modify ft_min_word_len? On Monday 10 February 2003 17:55, Octavian Rasnita wrote: Please tell me how to modify the variable ft_min_word_len. I've tried: set ft_min_word_len=2; set @ft_min_word_len=2; The second creates a new variable I think and the first one tells me that there is no such variable. You should define it in my.cnf file and then rebuild FULLTEXT indexes. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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 - 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
Counting null values
Hi all, I have a table where I have something like this: | abc | | abc | | xxx | | null | | null | | null | I want to count these lines to give the result 5, meaning a distinct count for values which are not null, and counting all the null values. This means 1 for abc, one for xxx, and 3 for null values. Can you tell me what sql query should I use for counting this? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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
Re: cannot locate dbi.pm while running perl scripts
Get DBI.pm from search.cpan.org. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Paul Choy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 07, 2003 10:53 PM Subject: cannot locate dbi.pm while running perl scripts Hi : When I try running my perl scripts using DBI interface with mysql, I got this message:Can't locate DBI.pm in @INC((@INC contain : /usr/lib/perl5/5.6.0/i386-linux /usr/lib/perl5/5.6.0./usr/libperl5/site_perl/5.6.0/i386-linux /usr/lib/perl5/site_perl/5.6.0 /usr/lib/perl5/site_perl .) Where can I get DBI.pm Thanks - 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 - 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
Caching queries
Hi all, Please tell me what should I do to make MySQL cache all the queries that can be cached. Can I do this if I am not the administrator of MySQL server? (on a session basis...). Thanks. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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
How to modify ft_min_word_len?
Hi all, Please tell me how to modify the variable ft_min_word_len. I've tried: set ft_min_word_len=2; set @ft_min_word_len=2; The second creates a new variable I think and the first one tells me that there is no such variable. Thank you. sql Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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
Re: Applications for creating reports for MySQL
I want to create reports that can be printed to a printer, select a font and a size for each field, etc. Perl and PHP can't do that. Well, perl could do that if using a post script printer... but I don't have such a printer and it is very hard to do it anyway. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Joshua J.Kugler [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Thursday, February 06, 2003 12:08 AM Subject: Re: Applications for creating reports for MySQL How much work do you want done for you? Perl and Python are great apps for writing MySQL reports, and you can output all the report to a text file, which would be great for a reader. But as report applications, can't help you there. Sorry. j- k- On Tuesday 04 February 2003 09:10, Octavian Rasnita wrote: Hi all, Does anyone know a program for Windows that can create reports for MySQL databases? If you know more, please tell me more, because I need to check which of them are accessible for the blind. -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - 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
Re: Applications for creating reports for MySQL
Yes I have a question regarding Crystal Reports, but ... I don't know if you can help me. I am blind and I would like to know if this program has an interface accessible for a screen reader. Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Scott Pippin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, February 06, 2003 5:42 PM Subject: Re: Applications for creating reports for MySQL Does anyone know a program for Windows that can create reports for MySQL databases? Check out Crystal Reports http://www.crystaldecisions.com or ReportMill http://www.reportmill.com hope these help. We use Crystal Reports. If you have any questions about crystal please let me know. (query, sql) Scott Pippin [EMAIL PROTECTED] - 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
Sorting with null values
Hi all, Please tell me how can I sort a column and force placing the null values to the end of list? Thank you sql query Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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
Applications for creating reports for MySQL
Hi all, Does anyone know a program for Windows that can create reports for MySQL databases? If you know more, please tell me more, because I need to check which of them are accessible for the blind. Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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
Bug in DBI module for Perl 5.8.0 under Win 2k
Hi all,
I've discovered that I cannot use the fork() function in a Perl program that
uses the DBI module under Windows 2000.
I've tried the following script:
#!/perl/bin/perl
use DBI;
print Content-type: text/html\n\n;
$| = 1;
my $pid;
if ($pid = fork()) {
}
elsif (defined $pid) {
}
else {
}
If I run this script, it gives me the error:
perl.exe - Application Error The instruction at 0x28068533 referenced
memory at 0x0004. The memory could not be read. Click on OK to
terminate the program Click on CANCEL to debug the program OK Cancel
If I press cancel, Visual C++ appears after that and tells that:
perl - Microsoft Visual C++ [run] Debug Microsoft Visual C++ Unhandled
exception in perl.exe (PERL58.DLL): 0xC005: Access Violation. OK
Context: Name Value Name Value Auto Locals this Watch1 Watch2 Watch3 Watch4
Ready
--
The script works fine if I comment out the line with use DBI;.
Do you have any idea what could be the problem that DBI doesn't work with
fork() function in the same script?
Could it be a bug in DBI module, or a bug in Perl?
Thank you very much!
query queries sql
Teddy,
Teddy's Center:
http://teddy.fcc.ro/
Email:
[EMAIL PROTECTED]
Teddy,
Teddy's Center: http://teddy.fcc.ro/
Email: [EMAIL PROTECTED]
-
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
Re: Is it normal?
Is it the same in all database servers? Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] Cc: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Friday, January 24, 2003 4:12 PM Subject: Re: Is it normal? Any calculation involving a floating point number will be imprecise. It does not mater what column types are involved. Octavian Rasnita wrote: And does anyone know what column type I should use if I want a precise calculation? I've even tried with text and blob, but they are not working either. Only the int type is precise? Thanks. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Monday, January 20, 2003 11:42 PM Subject: Re: Is it normal? Octavian, mysql create table test(id float); mysql insert into test values(1.123), (3.1495); mysql select * from test where id=1.123; Empty set (0.00 sec) Shouldn't this last query show me the record that has the id=1.123? Well ... 1.123 is greater than 1.123, see: mysql select id from octavian where id 1.123; ++ | id | ++ | 1.123 | | 3.1495 | ++ 2 rows in set (0.00 sec) Now here's why. Floats are simply not precise: mysql select id*1 from octavian where id 1.123; +-+ | id*1| +-+ | 11230.000257492 | -- | 31494.998931885 | +-+ 2 rows in set (0.00 sec) What query should I use to do this? I leave this one to Paul or one of the other gurus. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Monday, January 20, 2003 9:34 AM Subject: Is it normal? Hi all, I've tried the following SQL queries: mysql create table test(id float); Query OK, 0 rows affected (0.00 sec) mysql insert into test values(1.123), (3.1495); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select * from test where id=1.123; Empty set (0.00 sec) Shouldn't this last query show me the record that has the id=1.123? What query should I use to do this? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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 - 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 - 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
What's wrong with mysql 4.0.9?
Hi all, I have mysql 4.0.9 gama for Windows installed and I have a problem because I cannot set a password for a username. I've tried: mysql grant all on *.* to 'teddy@localhost' identified by 'password'; All went right but I can't access mysql using this newly created account. I can see this new account in mysql.user and there is a password set for it. However, if I try: mysql -u teddy -p ... and give that password it tells me that I don't have access to mysql. Thanks. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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
Re: Windows InnoDB
Yes, InnoDB is available in the compiled version for Windows. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Darren Young [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 25, 2003 12:03 AM Subject: Windows InnoDB Is the InnoDB table type available on Windows? Darren Young [EMAIL PROTECTED] mysql, query - 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 - 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
Re: Is it normal?
And does anyone know what column type I should use if I want a precise calculation? I've even tried with text and blob, but they are not working either. Only the int type is precise? Thanks. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Monday, January 20, 2003 11:42 PM Subject: Re: Is it normal? Octavian, mysql create table test(id float); mysql insert into test values(1.123), (3.1495); mysql select * from test where id=1.123; Empty set (0.00 sec) Shouldn't this last query show me the record that has the id=1.123? Well ... 1.123 is greater than 1.123, see: mysql select id from octavian where id 1.123; ++ | id | ++ | 1.123 | | 3.1495 | ++ 2 rows in set (0.00 sec) Now here's why. Floats are simply not precise: mysql select id*1 from octavian where id 1.123; +-+ | id*1| +-+ | 11230.000257492 | -- | 31494.998931885 | +-+ 2 rows in set (0.00 sec) What query should I use to do this? I leave this one to Paul or one of the other gurus. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Monday, January 20, 2003 9:34 AM Subject: Is it normal? Hi all, I've tried the following SQL queries: mysql create table test(id float); Query OK, 0 rows affected (0.00 sec) mysql insert into test values(1.123), (3.1495); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select * from test where id=1.123; Empty set (0.00 sec) Shouldn't this last query show me the record that has the id=1.123? What query should I use to do this? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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 - 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
Which is the upper limit?
Hi all,
I've tried the repeat function for testing some tables, but it cannot
create very big strings.
Do you know which is its upper limit?
I've tried the following query:
mysql select length(repeat('abracadabra', 10));
+---+
| length(repeat('abracadabra', 10)) |
+---+
| NULL |
+---+
1 row in set (0.00 sec)
If I use smaller numbers, it works.
Thank you.
Teddy,
Teddy's Center: http://teddy.fcc.ro/
Email: [EMAIL PROTECTED]
-
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
