Re: how to use index with order by here
Rob Wultsch schrieb: On Mon, Mar 17, 2008 at 4:36 AM, Arthur Fuller [EMAIL PROTECTED] wrote: I love when this happens. I woke in the middle of the night with an idea for you. It now occurs to me that the query you want is dead simple. It just took me a while to see: SELECT * FROM messages WHERE id_from = 1 AND id_to = 2 UNION SELECT * FROM messages WHERE id_from = 2 AND id_to = 1 ORDER BY time Assuming an index on id_from (or id_from, id_to), it will be used. This will be very quick. I bench'ed the union before sending in my original response. For the generic data set I created as an example his original query is faster (not by much) and simpler. If it were me writing the query I would use a union, probably. IN and OR never end well ;) this will not prevent filesort, because the results still needs to be sorted for ORDER BY time, or? Yes. Adding the extra column to the index will not result in losing the filesort. hu? ... i am pretty sure i had a similar problem, i solved this by adding the column with the order to the index, which 'solved' the filesort (except the ORDER is in reverse) The filesort will not be any sort of a problem unless the result is large. yes. of course, size matters, if your whole DB is small enough you will not even get any performance impacts without any index ... ;-) but it requires more resources, or? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to import oracle dump?
Hello I have got one question. I need to convert oracle dump file and import it to MySQl server. I have found some utitlity OraDump-to-MySQL but it is not free and convert only 5 record from each table. Can somebody help me ? Thanks -- View this message in context: http://www.nabble.com/How-to-import-oracle-dump--tp16115624p16115624.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [ANN] PBXT storage engine version 1.0-Alpha released
Paul McCullagh schrieb: Hi All, I have just released the first fully durable version of PBXT. Because of the amount of new code I have reverted PBXT to Alpha status. This version, 1.0-alpha, can be downloaded from: http://www.primebase.org/download. will there be any Windows builds available sooner or later? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [ANN] PBXT storage engine version 1.0-Alpha released
Yes, definitely. Previous versions of PBXT built on Windows, so there can't be much work to get it going. But I don't think I will get around to firing up my Windows VM until after the conference... On Mar 18, 2008, at 8:30 AM, Sebastian Mendel wrote: Paul McCullagh schrieb: Hi All, I have just released the first fully durable version of PBXT. Because of the amount of new code I have reverted PBXT to Alpha status. This version, 1.0-alpha, can be downloaded from: http:// www.primebase.org/download. will there be any Windows builds available sooner or later? -- Sebastian -- 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]
mysql privileges
We are currently running MySQL 4.0.15a on a Sun Solaris server. We are moving to a Linux machine running MySQL 5.0.45. I am having a problem with permissions in MySQL. On the current machine running 4.0.15a, when I connect to MySQL as the user super and give the command: select lname from hr where fname = shlomit; I get the expected result. On the new machine running MySQL 5.0.45, when I connect as the user super and give the same command, I get the following error: ERROR 1142 (42000): SELECT command denied to user 'super'@'localhost' for table 'hr' The MySQL permissions are the same on both machines. When I give the following command: select * from tables_priv where user=super and db =web_positions and table_name = hr; I get the following result on both machines: | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv +--+---+---+++-++-+ | %| web_positions | super | hr | [EMAIL PROTECTED] | 2002-07-21 15:07:17 | Select | | When I give the following command, I aslo get the same results on both machines: select * from user where user =super; The results are N for all the different privileges. Has anything changed in MySQL 5.0.45 that would cause this behavior? Thanks for any help. Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036
Re: mysql privileges
Hi, On Tue, Mar 18, 2008 at 8:49 AM, Malka Cymbalista [EMAIL PROTECTED] wrote: We are currently running MySQL 4.0.15a on a Sun Solaris server. We are moving to a Linux machine running MySQL 5.0.45. I am having a problem with permissions in MySQL. On the current machine running 4.0.15a, when I connect to MySQL as the user super and give the command: select lname from hr where fname = shlomit; I get the expected result. On the new machine running MySQL 5.0.45, when I connect as the user super and give the same command, I get the following error: ERROR 1142 (42000): SELECT command denied to user 'super'@'localhost' for table 'hr' The MySQL permissions are the same on both machines. When I give the following command: select * from tables_priv where user=super and db =web_positions and table_name = hr; I get the following result on both machines: | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv +--+---+---+++-++-+ | %| web_positions | super | hr | [EMAIL PROTECTED] | 2002-07-21 15:07:17 | Select | | When I give the following command, I aslo get the same results on both machines: select * from user where user =super; The results are N for all the different privileges. Has anything changed in MySQL 5.0.45 that would cause this behavior? Probably not. You are probably not logged in as the user you think you are. Instead of checking privileges by selecting from the mysql system tables, use SHOW GRANTS to see what your privileges are and who you're logged in as. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to import oracle dump?
Have you tried the MySQL Migration Toolkit? Check http://www.mysql.com/products/tools/migration-toolkit/ Raj Mehrotra HCCS - Experts in Healthcare Learning -Original Message- From: Metalpalo [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 18, 2008 3:25 AM To: mysql@lists.mysql.com Subject: How to import oracle dump? Hello I have got one question. I need to convert oracle dump file and import it to MySQl server. I have found some utitlity OraDump-to-MySQL but it is not free and convert only 5 record from each table. Can somebody help me ? Thanks -- View this message in context: http://www.nabble.com/How-to-import-oracle-dump--tp16115624p16115624.htm l Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql privileges
Thanks for your reply. When I do show grants, I get back GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'super' @ 'localhost' ( mailto:super'@ 'localhost' ) identified by password... So it looks like super doesn't have rights to select from the hr table. But why not? According to the tables_priv table, super should have right to select. I tried giving the command (as root) grant select on web_positions.hr to [EMAIL PROTECTED] identified by . I get back ERROR 2013 (HY000): Lost connection to MySQL server during query I can give any other command but when I try to give the grant command I keep getting the same error. Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 On 3/18/2008 at 3:20 PM, in message [EMAIL PROTECTED], Baron Schwartz [EMAIL PROTECTED] wrote: Hi, On Tue, Mar 18, 2008 at 8:49 AM, Malka Cymbalista [EMAIL PROTECTED] wrote: We are currently running MySQL 4.0.15a on a Sun Solaris server. We are moving to a Linux machine running MySQL 5.0.45. I am having a problem with permissions in MySQL. On the current machine running 4.0.15a, when I connect to MySQL as the user super and give the command: select lname from hr where fname = shlomit; I get the expected result. On the new machine running MySQL 5.0.45, when I connect as the user super and give the same command, I get the following error: ERROR 1142 (42000): SELECT command denied to user 'super'@'localhost' for table 'hr' The MySQL permissions are the same on both machines. When I give the following command: select * from tables_priv where user=super and db =web_positions and table_name = hr; I get the following result on both machines: | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv +--+---+---+++-++-+ | %| web_positions | super | hr | [EMAIL PROTECTED] | 2002-07-21 15:07:17 | Select | | When I give the following command, I aslo get the same results on both machines: select * from user where user =super; The results are N for all the different privileges. Has anything changed in MySQL 5.0.45 that would cause this behavior? Probably not. You are probably not logged in as the user you think you are. Instead of checking privileges by selecting from the mysql system tables, use SHOW GRANTS to see what your privileges are and who you're logged in as. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use index with order by here
On Mon, Mar 17, 2008 at 11:35 PM, Sebastian Mendel [EMAIL PROTECTED] wrote: hu? ... i am pretty sure i had a similar problem, i solved this by adding the column with the order to the index, which 'solved' the filesort (except the ORDER is in reverse) I don't think we can lose the filesort will the current criteria, but I am by no means an expert. I would be quite interested in any techniques to further optimize the query. but it requires more resources, or? 1. The index will use more disk space (not that it probably matters much). 2. Inserts and updates will take longer (might be important, might not). 3. I have had queries take a performance hit from adding columns to composite index's. Most of my work I have done on 3.23 and I am sure mysql has gotten smarter since then, but old habits are hard to lose. -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Information schema question.
Hi all! Well, question is how could I to retrieve information about types supported . In PostgreSQL the query would be: -- SELECT pg_type.oid, typname, usename, obj_description(pg_type.oid) FROM pg_type, pg_user WHERE typowner=usesysid AND typrelid = 0 AND typname !~ '^_' -- Is there some way to obtain equivalent columns from INFORMATION_SCHEMA, as similar as possible ? Thanks in advance... Carlos Savoretti. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
default my.cnf?
hi all... i have a 5.0.33 build from source on a freebsd 4.10 machine... i'm looking for a my.cnf file. ps tells me that the base dir is /usr/local but there is no my.cnf there. and i cant find one anywhere. i can get all the variables set up from the cli but i need to change some of them. i guess i can use mysqladmin but just wondering - can i just do a cnf under /usr/local/etc with the variables i need to change? ktrace is showing me that mysqld is not looking for any .cnf?! i mean i can try that but it's a very busy server and i wouldn't mess to much with it. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default my.cnf?
I had the same problem. I found the distribution contains some prototypes, with slightly more elaborate names. Regards, Mike kalin m [EMAIL PROTECTED] 03/18/08 01:06 PM To mysql@lists.mysql.com cc Subject default my.cnf? hi all... i have a 5.0.33 build from source on a freebsd 4.10 machine... i'm looking for a my.cnf file. ps tells me that the base dir is /usr/local but there is no my.cnf there. and i cant find one anywhere. i can get all the variables set up from the cli but i need to change some of them. i guess i can use mysqladmin but just wondering - can i just do a cnf under /usr/local/etc with the variables i need to change? ktrace is showing me that mysqld is not looking for any .cnf?! i mean i can try that but it's a very busy server and i wouldn't mess to much with it. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlimport load data infile
i read about mysqlimport load data infile for mysql, but i can't find a way to import text file using length of column, instead of delimiter my text file contains fixed length column: -- i can use ms excel to convert all files to .csv format and import, but it would take a long time and i have to escape delimiter. so, is there a way to import text file with fixed column size into mysql??? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default my.cnf?
Hi, On Tue, Mar 18, 2008 at 1:06 PM, kalin m [EMAIL PROTECTED] wrote: hi all... i have a 5.0.33 build from source on a freebsd 4.10 machine... i'm looking for a my.cnf file. ps tells me that the base dir is /usr/local but there is no my.cnf there. and i cant find one anywhere. i can get all the variables set up from the cli but i need to change some of them. i guess i can use mysqladmin but just wondering - can i just do a cnf under /usr/local/etc with the variables i need to change? ktrace is showing me that mysqld is not looking for any .cnf?! i mean i can try that but it's a very busy server and i wouldn't mess to much with it. You can ask mysqld where it looks for its configuration files. Here's a Debian system: [EMAIL PROTECTED] ~ $ mysql --help --verbose | grep cnf /etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
the limitaiton of table size
Dear all, I have a question about the limitatin of table size. If I use the InnoDB engine, I don't have to worry that the total size of table is greater than the single filesize? Thanks a lot! Best, Sookhyun.
