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
RE: The size of an index (INDEX_LENGTH)
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
Re: restoring mysql db doesn't restore user passwords
you're a genius! I had old_passwords=1 in my.cnf, changed it to 0, restarted mysql, and then the users worked like a charm. thanks! Little, Timothy wrote: My theory would be that it's an OLD-PASSWORDS issue. It would seem that you might have used the old_passwords=1 in your original configuration my.cnf but it's not in your new configuration file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: restoring mysql db doesn't restore user passwords
My theory would be that it's an OLD-PASSWORDS issue. It would seem that you might have used the old_passwords=1 in your original configuration my.cnf but it's not in your new configuration file. -Original Message- From: Adam Williams [mailto:awill...@mdah.state.ms.us] Sent: Monday, June 15, 2009 7:41 PM To: mysql@lists.mysql.com Subject: restoring mysql db doesn't restore user passwords I'm running Fedora 11 i386 with Mysql 5.1.32. I dumped my mysql databases with: mysqldump -u root -pxxx --lock-all-tables --all-databases > /root/mysql-backup/all-db.sql and then wiped the operating system and reinstalled. Then I ran mysqladmin -u root password to set my root password. Then I needed to restore all of the databases so I ran: mysql --verbose -u root -p < /root/mysql-backup/all-db.sql and that seems to have restored everything, there is the correct data in my databases, however, users can not connect. For example, this user was working prior to the reinstall: mysql -u adam -px ERROR 1045 (28000): Access denied for user 'adam'@'localhost' (using password: YES) but in the mysql.user table, adam exists and has his password set from the dump file: mysql> select User,Password from mysql.user where user = 'adam'; +--+--+ | User | Password | +--+--+ | adam | 2bf6b1712b10928e | | adam | 2bf6b1712b10928e | | adam | 2bf6b1712b10928e | +--+--+ 3 rows in set (0.06 sec) It is the same with all of the other users also. They have their password set from the dump file, but they can't log in with what their password was either. Any ideas on how to get the passwords restored? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=tlit...@tgrnet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
restoring mysql db doesn't restore user passwords
I'm running Fedora 11 i386 with Mysql 5.1.32. I dumped my mysql databases with: mysqldump -u root -pxxx --lock-all-tables --all-databases > /root/mysql-backup/all-db.sql and then wiped the operating system and reinstalled. Then I ran mysqladmin -u root password to set my root password. Then I needed to restore all of the databases so I ran: mysql --verbose -u root -p < /root/mysql-backup/all-db.sql and that seems to have restored everything, there is the correct data in my databases, however, users can not connect. For example, this user was working prior to the reinstall: mysql -u adam -px ERROR 1045 (28000): Access denied for user 'adam'@'localhost' (using password: YES) but in the mysql.user table, adam exists and has his password set from the dump file: mysql> select User,Password from mysql.user where user = 'adam'; +--+--+ | User | Password | +--+--+ | adam | 2bf6b1712b10928e | | adam | 2bf6b1712b10928e | | adam | 2bf6b1712b10928e | +--+--+ 3 rows in set (0.06 sec) It is the same with all of the other users also. They have their password set from the dump file, but they can't log in with what their password was either. Any ideas on how to get the passwords restored? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Innodb Update_time
Hello List, how can i get table "update_time" using innodb engine? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: The size of an index (INDEX_LENGTH)
In the last episode (Jun 15), Morten said: > 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? Innodb or MyISAM? According to http://dev.mysql.com/doc/refman/5.1/en/key-space.html a MyISAM index should be around 25M*(4+8)/.67=450 MB, quite a bit smaller than your delta. Innodb, however, gets closer to your number. http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-structure.html http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html Each row in a secondary index contains the keys in that index, plus the primary keys, and given random insertion (which is common for secondary keys) index pages between 1/2 and 15/16ths full. So if your primary key is also a NOT NULL INT, the index should be 25M*16 = 400MB worth of data plus between 25 and 200MB of slack space. 600 is at the very top end of that range, so I think your primary key is larger than a plain INT. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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=arch...@jab.org
Re: Call a routine on mysqld startup
On Mon, 15 Jun 2009 10:00:00 -0400 Jim Lyons wrote: > There's an option called "init-file" that will invoke an sql script > on start up. That would probably work for you. Thanks you, that is what I was searching for. I guess I should clean my glasses :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: query question...
Hi Bruce, bruce wrote: hi. i've got a situation, where i'm trying to figure out how to select an item from tblA that may/maynot be in tblB. if the item is only in tblA, i can easilty get a list of the items select * from tblA if the item is in tblA but not linked to tblB, i can get the items as well select * from tblA where id not in (select id from tblB); but i have no idea how to combine the two selects.. i need to combine them, as the app can create tblA for a given item, and then later on create the data in tblB, with thblA.id = tblB.aid. thoughts/pointers on this would be appreciated. Just as the other reply said, you need a JOIN. Specifically you need one of the OUTER JOIN such as LEFT JOIN or RIGHT JOIN. Here's the pattern for "exists in A and is/is not in B" (using the fields you specified in your sample) SELECT ... FROM a LEFT JOIN b ON a.id = b.aid Any row where b.id is null would indicate a mismatch (the row exists in A but not in B). So you can test for things like "all rows in A that have no match in B" SELECT ... FROM a LEFT JOIN b ON a.id = b.aid WHERE b.id IS NULL "all rows in B that have no match in A" SELECT ... FROM b LEFT JOIN a ON a.id = b.aid WHERE a.id IS NULL - or - SELECT ... FROM a RIGHT JOIN b ON a.id = b.aid WHERE a.id is null "only rows from A or B that have a matching row in in the other table" SELECT ... FROM a INNER JOIN b ON a.id = b.id "All Rows from A and only those matching rows from B where b.datecol > '2009-06-13' " SELECT ... FROM a LEFT JOIN b ON a.id = b.id AND b.datecol > '2009-06-13' - this will NOT work - SELECT ... FROM a LEFT JOIN b ON a.id = b.id WHERE b.datecol > '2009-06-13' It will not work because the WHERE clause will filter from the results any rows from A where b.datecol is not > '2009-06-13' which would include those rows from A that had no matching row from B (effectively turning the LEFT join into an INNER join). Try it both ways and see. Yours, -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Call a routine on mysqld startup
There's an option called "init-file" that will invoke an sql script on start up. That would probably work for you. On Mon, Jun 15, 2009 at 5:17 AM, Gabriel Linder wrote: > Hi, > > I must call a routine when mysqld start (to populate a heap table). I > did not find any related options in mysqld --help --verbose. Is there a > way to achieve this, without modifying the startup script ? > > Thanks in advance. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com > > -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: query question...
It sounds to me like you want to join the two tables? http://dev.mysql.com/doc/refman/5.1/en/join.html On Mon, Jun 15, 2009 at 03:56, bruce wrote: > hi. > > i've got a situation, where i'm trying to figure out how to select an item > from tblA that may/maynot be in tblB. > > if the item is only in tblA, i can easilty get a list of the items > select * from tblA > > if the item is in tblA but not linked to tblB, i can get the items as well > select * from tblA where id not in (select id from tblB); > > but i have no idea how to combine the two selects.. > > i need to combine them, as the app can create tblA for a given item, and > then later on create the data in tblB, with thblA.id = tblB.aid. > > thoughts/pointers on this would be appreciated. > > thanks! > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fastest way to select on 0/1 flag
Hi Artem, There can be many malicious factors at play here, but if you are not using an index then definitely create on now. It will obviously help you with option 1 and you can still benefit from it with option 2. If you don't have an index, MySQL has to search for you data row by row which is much slower than using an index. 2009/6/15 Artem Kuchin : > Hello! > > I cannot figure out the fastest way to do a select on the floowing field: > > f_spec tinyint not null; > > It is a table of 100 000 records of products and f_spec is set only for > about 200 products. > > I figure it could be done in two ways: > > 1) create an index on f_spec and do simple > select * from products where f_spec=1; > > 2) create a separate table > > create table specs ( > product_id int; > primary key (product_id) > ); > > then select ids from this table and join with the products table if needed. > > What is the best way? > > Also, it is often needed to know only the fact that there is any product > with f_spec set. > Is using index and doing > select id from products where f_spec=1 limit 1 > will be very fast ? > > > > Regards, > Artem > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com > > -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Fastest way to select on 0/1 flag
Hello! I cannot figure out the fastest way to do a select on the floowing field: f_spectinyint not null; It is a table of 100 000 records of products and f_spec is set only for about 200 products. I figure it could be done in two ways: 1) create an index on f_spec and do simple select * from products where f_spec=1; 2) create a separate table create table specs ( product_id int; primary key (product_id) ); then select ids from this table and join with the products table if needed. What is the best way? Also, it is often needed to know only the fact that there is any product with f_spec set. Is using index and doing select id from products where f_spec=1 limit 1 will be very fast ? Regards, Artem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Call a routine on mysqld startup
Hi, I must call a routine when mysqld start (to populate a heap table). I did not find any related options in mysqld --help --verbose. Is there a way to achieve this, without modifying the startup script ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
