Re: The size of an index (INDEX_LENGTH)
Hi, It's InnoDB on 5.0.51. The only thing I can think of that *may* be different about this is that this index used to be on a composite key (some_id, some_varchar) but then the VARCHAR column got dropped. Other than that, it's just a plain index on an INT(11). Morten On Jun 16, 2009, at 5:51 AM, Andrew Braithwaite wrote: Hi, Is your table MyISAM or InnoDB? Andrew -Original Message- From: Morten [mailto:my.li...@mac.com] Sent: 15 June 2009 21:23 To: mysql@lists.mysql.com Subject: The size of an index (INDEX_LENGTH) Hi, I dropped an index on a table with 25M records today. The INDEX_LENGTH in information_schema.tables shrank from 3834642432 to 3215982592, ie. ~618Mb difference The index was on an int(11) column. That means each index key takes up ~618Mb/25M ~= 25 bytes but that doesn't sound right? Is that true, or is information_schema.tables unreliable or? Thanks, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.braithwa...@lovefilm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how to define the correct index
Hi
I have the following problem
I have a table with 3 fields ('Field_A', 'Field_B', 'Field_C')
My query is:
Select Field_A from table where Field_b=10 order by Field_c ASC;
I have performance issues even when i have tried to define index for
this table
How should i define the corerct index (by the where or by the priority
or both?)
Regards, Yariv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to define the correct index
I would create two separate indices on fields B and C
Olaf
On 6/16/09 4:25 AM, Yariv Omer yar...@jungo.com wrote:
Hi
I have the following problem
I have a table with 3 fields ('Field_A', 'Field_B', 'Field_C')
My query is:
Select Field_A from table where Field_b=10 order by Field_c ASC;
I have performance issues even when i have tried to define index for
this table
How should i define the corerct index (by the where or by the priority
or both?)
Regards, Yariv
- Confidentiality Notice:
The following mail message, including any attachments, is for the
sole use of the intended recipient(s) and may contain confidential
and privileged information. The recipient is responsible to
maintain the confidentiality of this information and to use the
information only for authorized purposes. If you are not the
intended recipient (or authorized to receive information for the
intended recipient), you are hereby notified that any review, use,
disclosure, distribution, copying, printing, or action taken in
reliance on the contents of this e-mail is strictly prohibited. If
you have received this communication in error, please notify us
immediately by reply e-mail and destroy all copies of the original
message. Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Tools on http://www.severalnines.com do not work.
Hi List, I have been trying to use bencher and chkfrag from http://www.severalnines.com. But I continue to receive the following errors../chkfrag: error while loading shared libraries: /usr/local/mysql/lib/libndbclient.so.4: cannot restore segment prot after reloc: permission denied. I am running this with NDB 7.0.6 and the owner of the library is mysql.mysql. Any ideas? Thanks Joshua Gordon.
myisamchk buffer_size warnings
Hi I have MySQL 5.0.64 compiled from source. When I run myisamchk on any table I get the following warnings: Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 /etc/my.cnf contains the following: [myisamchk] key_buffer_size=20971520 sort_buffer_size=20971520 read_buffer_size=2097152 write_buffer_size=2097152 What's wrong here? Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqld not writing to err-log
Hi, mysqld is not writing to err-log and i do not see any log generated upon mysql service restart. In my.cnf = [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/lib/mysql/mysql.pid open_files_limit=16384 r...@server[~]# ll /var/log/mysqld.log -rw-r--r-- 1 mysql root 0 Nov 24 2008 /var/log/mysqld.log r...@server[~]# Please advise what i can look for to fix this issue.
Re: mysqld not writing to err-log
It is probably writing to syslog. Try cat /var/log/messages | grep -i mysqld to search for entries made by mysqld. Walter On Tue, Jun 16, 2009 at 11:48 AM, Madan Thapamadan.feedb...@gmail.com wrote: Hi, mysqld is not writing to err-log and i do not see any log generated upon mysql service restart. In my.cnf = [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/lib/mysql/mysql.pid open_files_limit=16384 r...@server[~]# ll /var/log/mysqld.log -rw-r--r-- 1 mysql root 0 Nov 24 2008 /var/log/mysqld.log r...@server[~]# Please advise what i can look for to fix this issue. -- Walter Heck, Engineer @ Open Query (http://openquery.com) Affordable Training and ProActive Support for MySQL related technologies Follow our blog at http://openquery.com/blog/ OurDelta: free enhanced builds for MySQL @ http://ourdelta.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: IDE - Toad
I was skeptical of a tool with a goofy name like Toad and the website looks like absolute 1992 ass, so it didn't give me a solid and professional impression. Then a DBA here at work mentioned it and I had to give him a chance to speak and convince me... Well, after a few minutes of a demonstration, I have to say I was impressed. It is far more feature rich than I expected and blows away the crappy mySQL Workbench (and the other free tools they offer). I'm not sure if it is better than SQLYog Enterprise, but they are certainly the two front-runners in my book now. -Original Message- From: Jason Trebilcock [mailto:jason.trebilc...@gmail.com] Sent: Wednesday, June 10, 2009 12:04 PM To: mysql@lists.mysql.com Subject: Re: IDE - SQLYog I think we've got almost all of the big ones listed. To complete the list (or at least grow the list by one), let me offer up Toad for MySQL: http://www.toadsoft.com/toadmysql/Overview.htm On Tue, Jun 9, 2009 at 8:57 PM, Daevid Vincent SQLYog by Webyog is the best mySQL GUI client for Windows. Hands down. not even a question. I've used them all I think. http://webyog.com/en/ There's even a free community version, but honestly it's worth purchasing the extended one for all the added features. They also release new ones all the time so it's very actively developed. http://daevid.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: mysqld not writing to err-log
Hi Rick, It was't a permission issue though. I did compare with another server, that had the same thing working. [r...@server1 mysql]# ls -lh /var/log/mysqld.log -rw-r--r-- 1 mysql root 60M Jun 16 18:40 /var/log/mysqld.log [r...@server1 mysql]# grep err-log /etc/my.cnf err-log=/var/log/mysqld.log [r...@server1 mysql]# On this server now, I have commented err-log and use log_error , which happily writes to the log file r...@server [~]# grep err-log /etc/my.cnf #err-log=/var/log/mysqld.log r...@server [~]# r...@server [~]# ls -lh /var/log/mysqld.log -rw-r--r-- 1 mysql root 728 Jun 16 17:51 /var/log/mysqld.log r...@server [~]# err-log was under safe_mysqld, so neither any fault in its position in my.cnf ( http://mysqldatabaseadministration.blogspot.com/2006/07/err-log-or-log-error.html) [safe_mysqld] #err-log=/var/log/mysqld.log On Wed, Jun 17, 2009 at 12:21 AM, lists-mysql replies-lists-b3z1-my...@listmail.innovate.net wrote: This might be an issue of permissions (/var/log is controlled by root). So, you might try touch-ing /var/log/mysqld.log and setting ownership to mysql.mysql. Then [re-]start mysqld and see if the log gets written to. Related, the logging entry in my my.cnf is log-error= not your err-log=. I haven't looked in the documentation to see if your version should work, but mine does, so you might want to try it. - Rick Original Message Date: Tuesday, June 16, 2009 11:18:25 PM +0530 From: Madan Thapa madan.feedb...@gmail.com To: mysql@lists.mysql.com Subject: mysqld not writing to err-log Hi, mysqld is not writing to err-log and i do not see any log generated upon mysql service restart. In my.cnf = [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/lib/mysql/mysql.pid open_files_limit=16384 r...@server[~]# ll /var/log/mysqld.log -rw-r--r-- 1 mysql root 0 Nov 24 2008 /var/log/mysqld.log r...@server[~]# Please advise what i can look for to fix this issue. End Original Message
Re: mysqld not writing to err-log
Thanks Eric, it worked. = On Tue, Jun 16, 2009 at 11:55 PM, Eric Bergen eric.ber...@gmail.com wrote: Madan, Try: [mysqld] log_error=/var/log/mysqld.log On Tue, Jun 16, 2009 at 10:48 AM, Madan Thapamadan.feedb...@gmail.com wrote: Hi, mysqld is not writing to err-log and i do not see any log generated upon mysql service restart. In my.cnf = [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/lib/mysql/mysql.pid open_files_limit=16384 r...@server[~]# ll /var/log/mysqld.log -rw-r--r-- 1 mysql root 0 Nov 24 2008 /var/log/mysqld.log r...@server[~]# Please advise what i can look for to fix this issue.
SELECT command denied to user 'user'@'localhost' for table 'table'
Hello, We are experience an estrange problem with mysql and we need help to debug this properly. We have a mysql server 5.0.67 on a ubuntu intrepid. Sometimes we have one unique query that cannot be executed and we get this error: SELECT command denied to user 'user'@'localhost' for table 'table' When we test that query with that user everything works fine, and that query does not fail always. The query that we are executing is quite simple, something like: SELECT column FROM table WHERE column1=1 OR (column2=1 AND column3=312) OR (column4=1 AND (column5=0 OR column5=6 OR column5=12 OR column5=13 OR column5=14)) The user has SELECT privileges: GRANT USAGE ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD '*xxx' GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `database`.* TO 'user'@'localhost' Any help to resolve this problem will be appreciated. Thanks in advance, Ruben Rubio Rey
