Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
mos schrieb: I posted this message twice in the past 3 days, and it never gets on the mailing list. Why? Here it is again: I have a Text field that contains paragraph text and for security reasons I need to have it encrypted. If I do this, how can I still implement full text search on it? Also, I have a lot of Float columns that need to be protected but the user has to use comparison operators like and on them. Any recommendations? Mike, What size ist the database? Could you create some kind of (temporary) table holding the data unencrypted? As fulltext search is only possible on myisam tables, you might want to put this on a ramdisk and create it during mysql startup (just make a symlink like /var/lib/mysql/UnencryptedDatabase - /ramdisk/ and use the |--init-file-Paramter for mysqld to create and fill the table). It would at least make it more difficult to get the data for somebody who has physical access to the machine as long as you have all your partitions encrypted as well have to enter your password during startup. ||I know there is still danger: somebody at the ISP could shut down the server and modify your initrd and try to get you password when you enter it during startup, but as long as you won't host the machine yourself, there probably is no better option. Get rackspace that has doors and can be locked... a little more security, but usually the ISP has a second key in their safe :( ||Or you might set it up so you have to enter 2 Passwords, the first one to decrypt and start a small program that checksums the kernel and initrd that is in memory, and then a second one to mount the partitions...| | If the value of the data is really a million, host it on your own and install security systems etc. and a 24/7 NOC keeping an eye on your server looking for hackers and so on. If your budget is only $100/month I would do the way I described above. ||| |Jan| || -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Record Counting
Hi, I have a table of records all of which have a timestamp against them like 2007-10-25 10:10:19, and category like red, blue etc and a unique key. Using a SELECT statement is it possible to retrieve the count and number of records for each day e.g 2007-10-25 for all red, and all blue etc Thanks. Neil _ Feel like a local wherever you go. http://www.backofmyhand.com
Re: Record Counting
Hi Neil, Try this: SELECT LEFT(DATE_FIELD_NAME, 10), COLOR_FIELD_NAME, COUNT(T.*) AS CNT FROM TABLE_NAME GROUP BY LEFT(DATE_FIELD_NAME, 10), COLOR_FIELD_NAME. Post the table structure if this does not help. Regards, Ravi On 10/27/07, Neil Tompkins [EMAIL PROTECTED] wrote: Hi, I have a table of records all of which have a timestamp against them like 2007-10-25 10:10:19, and category like red, blue etc and a unique key. Using a SELECT statement is it possible to retrieve the count and number of records for each day e.g 2007-10-25 for all red, and all blue etc Thanks. Neil _ Feel like a local wherever you go. http://www.backofmyhand.com
Index not being used when Selecting a certain column
Hello, I encountered weird problem. I upgraded MySQL on my database on my app from 5.0 to the latest 5.1.22. After the upgrade, the app suddenly got very slow. A query, which was executed within about 0.1 sec, now took longer than a few minutes. (Actually, I don't know exactly how long it would take because the time executing the query is so long, I Ctrl-C'd the query) Some explain showed that on 5.1.22, when I include a certain column(VARCHAR(300)) in select list, MySQL ignore the index that should be used. The query has force index to use the right index, but that seems to be ignored. After downgrading to 5.0, MySQL picks the right index. Unfortunately, I cannot connect to the system now, cannot show any explain result, table defijnition, show index etc, but the query that's got slow is a two innodb table joined query with order by. order by column is datetime field and correctly indexed. eacy table contains 2,3 million rows. Is there anybody that has the same experience like this? are there known issues that causes slow query when selecting some columns? Any suggestions would be appreciated. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Size
Hello, I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. I have one particular table that has 2 columns: rolID int(10) unsigned repID int(10) unsigned Both are foreign keys to other tables. A show table status shows that this table has a Data_length=1081344 bytes. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that integers take up 4 bytes of space. There are currently 10472 rows in the table. 10472 x 4 x 2 = 83776 What am I calculating wrong? Why is the Data_length value so much larger? Thanks.
Re: Table Size
Josh wrote: Hello, I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. I have one particular table that has 2 columns: rolID int(10) unsigned repID int(10) unsigned Both are foreign keys to other tables. A show table status shows that this table has a Data_length=1081344 bytes. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that integers take up 4 bytes of space. There are currently 10472 rows in the table. 10472 x 4 x 2 = 83776 What am I calculating wrong? Why is the Data_length value so much larger? It depends on the storage engine, indexes, and possibly other things. What's the output of SHOW CREATE TABLE for this table? If you can't tell us that, what's the storage engine? (yes, I know Index_length is separate, but humor me). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Size
mysql show create table UserReports\G *** 1. row *** Table: UserReports Create Table: CREATE TABLE `UserReports` ( `rolID` int(10) unsigned NOT NULL, `repID` int(10) unsigned NOT NULL, PRIMARY KEY (`rolID`,`repID`), KEY `repID` (`repID`), CONSTRAINT `UserReports_ibfk_1` FOREIGN KEY (`rolID`) REFERENCES `UserRoles` (`rolID`) ON DELETE CASCADE, CONSTRAINT `UserReports_ibfk_2` FOREIGN KEY (`repID`) REFERENCES `Reports` (`repID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) - Original Message From: Baron Schwartz [EMAIL PROTECTED] To: Josh [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Saturday, October 27, 2007 10:17:32 AM Subject: Re: Table Size Josh wrote: Hello, I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. I have one particular table that has 2 columns: rolID int(10) unsigned repID int(10) unsigned Both are foreign keys to other tables. A show table status shows that this table has a Data_length=1081344 bytes. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that integers take up 4 bytes of space. There are currently 10472 rows in the table. 10472 x 4 x 2 = 83776 What am I calculating wrong? Why is the Data_length value so much larger? It depends on the storage engine, indexes, and possibly other things. What's the output of SHOW CREATE TABLE for this table? If you can't tell us that, what's the storage engine? (yes, I know Index_length is separate, but humor me). Baron
Re: Table Size
Have you tried optimize table? On 10/27/07, Josh [EMAIL PROTECTED] wrote: Hello, I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. I have one particular table that has 2 columns: rolID int(10) unsigned repID int(10) unsigned Both are foreign keys to other tables. A show table status shows that this table has a Data_length=1081344 bytes. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that integers take up 4 bytes of space. There are currently 10472 rows in the table. 10472 x 4 x 2 = 83776 What am I calculating wrong? Why is the Data_length value so much larger? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Size
InnoDB has the following extra things, plus some things I might forget: a) the primary key B-Tree b) row versioning information for every row c) 16k page size; each page might not be completely full Those are all counted towards the table size. Actually, the primary key B-Tree might not be; I'd need to look that up. But I think it is. H. I just tested -- yes, the PK counts towards table size. Josh wrote: mysql show create table UserReports\G *** 1. row *** Table: UserReports Create Table: CREATE TABLE `UserReports` ( `rolID` int(10) unsigned NOT NULL, `repID` int(10) unsigned NOT NULL, PRIMARY KEY (`rolID`,`repID`), KEY `repID` (`repID`), CONSTRAINT `UserReports_ibfk_1` FOREIGN KEY (`rolID`) REFERENCES `UserRoles` (`rolID`) ON DELETE CASCADE, CONSTRAINT `UserReports_ibfk_2` FOREIGN KEY (`repID`) REFERENCES `Reports` (`repID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) - Original Message From: Baron Schwartz [EMAIL PROTECTED] To: Josh [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Saturday, October 27, 2007 10:17:32 AM Subject: Re: Table Size Josh wrote: Hello, I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. I have one particular table that has 2 columns: rolID int(10) unsigned repID int(10) unsigned Both are foreign keys to other tables. A show table status shows that this table has a Data_length=1081344 bytes. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that integers take up 4 bytes of space. There are currently 10472 rows in the table. 10472 x 4 x 2 = 83776 What am I calculating wrong? Why is the Data_length value so much larger? It depends on the storage engine, indexes, and possibly other things. What's the output of SHOW CREATE TABLE for this table? If you can't tell us that, what's the storage engine? (yes, I know Index_length is separate, but humor me). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Size
Forgot to send the rest of 'show table status' Name: UserReports Engine: InnoDB Version: 10 Row_format: Compact Rows: 10388 Avg_row_length: 104 Data_length: 1081344 Max_data_length: 0 Index_length: 212992 Data_free: 0 Auto_increment: NULL Create_time: 2007-05-19 21:17:58 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL - Original Message From: Baron Schwartz [EMAIL PROTECTED] To: Josh [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Saturday, October 27, 2007 10:17:32 AM Subject: Re: Table Size Josh wrote: Hello, I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. I have one particular table that has 2 columns: rolID int(10) unsigned repID int(10) unsigned Both are foreign keys to other tables. A show table status shows that this table has a Data_length=1081344 bytes. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that integers take up 4 bytes of space. There are currently 10472 rows in the table. 10472 x 4 x 2 = 83776 What am I calculating wrong? Why is the Data_length value so much larger? It depends on the storage engine, indexes, and possibly other things. What's the output of SHOW CREATE TABLE for this table? If you can't tell us that, what's the storage engine? (yes, I know Index_length is separate, but humor me). Baron
Re: Record Counting
Neil, Do you mean ... SELECT DATE(datetimecol) AS date,colourcol,COUNT(*) FROM tbl GROUP BY date,colourcol; PB -- Neil Tompkins wrote: Hi, I have a table of records all of which have a timestamp against them like 2007-10-25 10:10:19, and category like red, blue etc and a unique key. Using a SELECT statement is it possible to retrieve the count and number of records for each day e.g 2007-10-25 for all red, and all blue etc Thanks. Neil _ Feel like a local wherever you go. http://www.backofmyhand.com No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.15.11/1094 - Release Date: 10/26/2007 8:50 AM
Re: correct way to simulate 'except' query in mysql 4.1
Baron Schwartz wrote: I don't think it will be any better to count distinct values. I think the query is just slow because the index lookups are slow. Is the 'word' column really 150 bytes? huh. it's a varchar(50) on table1 and a varchar(50) on table2. i wonder why explain is reporting 150 as key_len? That's probably the culprit. How slow is this, by the way? this is also interesting. as you can see in the slow query log reported before, it took 94 seconds. i'd say i see between 15 and 90 seconds in the slow query log for this normally. however, i just ran the query now, at a time when the application is not heavily loaded, and it finished quickly - less than a second. another run a few minutes later took around 3 seconds. so there seems to be some interaction with load. 370k rows in one table, verifying the non-existence of index records in a 4M-row table with 150-byte index values... what does slow mean for your application? How big is the index for the 4M-row table (use SHOW TABLE STATUS)? the larger table has 95M index. the smaller has a 5M index. key_buffer is set to 2G, and when i look at top mysql never actually get's above 1.5G, so i'm under the impression that all the indexes are in memory. it's a search table, so it does get a lot of inserts, but slow log never reports any lock time. is there anything else i can investgate? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: correct way to simulate 'except' query in mysql 4.1
Russell Uman wrote: Baron Schwartz wrote: I don't think it will be any better to count distinct values. I think the query is just slow because the index lookups are slow. Is the 'word' column really 150 bytes? huh. it's a varchar(50) on table1 and a varchar(50) on table2. i wonder why explain is reporting 150 as key_len? utf8? That's probably the culprit. How slow is this, by the way? this is also interesting. as you can see in the slow query log reported before, it took 94 seconds. i'd say i see between 15 and 90 seconds in the slow query log for this normally. however, i just ran the query now, at a time when the application is not heavily loaded, and it finished quickly - less than a second. another run a few minutes later took around 3 seconds. so there seems to be some interaction with load. 370k rows in one table, verifying the non-existence of index records in a 4M-row table with 150-byte index values... what does slow mean for your application? How big is the index for the 4M-row table (use SHOW TABLE STATUS)? the larger table has 95M index. the smaller has a 5M index. key_buffer is set to 2G, and when i look at top mysql never actually get's above 1.5G, so i'm under the impression that all the indexes are in memory. it's a search table, so it does get a lot of inserts, but slow log never reports any lock time. is there anything else i can investgate? Do you need utf8? :-) Check your cache hits. I can't remember if you said, but is it an InnoDB table? I'm guessing MyISAM since you have a 2G key buffer. Check key_read_requests and key_reads for the query (mysql-query-profiler is a handy way to do this). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: correct way to simulate 'except' query in mysql 4.1
huh. it's a varchar(50) on table1 and a varchar(50) on table2. i wonder why explain is reporting 150 as key_len? utf8? yes. that does make sense. is there anything else i can investgate? Do you need utf8? :-) yes. it's an internationalized application :) Check your cache hits. I can't remember if you said, but is it an InnoDB table? I'm guessing MyISAM since you have a 2G key buffer. yes. we do have some tables as innodb - those that get many many inserts and don't require any count(*) queries which as i understand it are slow in innodb - if there's some reason that this kind of query would be faster under innodb i'm happy to give it a try... Check key_read_requests and key_reads for the query (mysql-query-profiler is a handy way to do this). awesome. i will look into it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Record Counting
Sorry a correction SELECT DISTINCT LEFT(DATE_FIELD_NAME, 10), COLOR_FIELD_NAME, COUNT(T.*) AS CNT FROM TABLE_NAME T GROUP BY LEFT(DATE_FIELD_NAME, 10), COLOR_FIELD_NAME Ravi On 10/28/07, Peter Brawley [EMAIL PROTECTED] wrote: Neil, Do you mean ... SELECT DATE(datetimecol) AS date,colourcol,COUNT(*) FROM tbl GROUP BY date,colourcol; PB -- Neil Tompkins wrote: Hi, I have a table of records all of which have a timestamp against them like 2007-10-25 10:10:19, and category like red, blue etc and a unique key. Using a SELECT statement is it possible to retrieve the count and number of records for each day e.g 2007-10-25 for all red, and all blue etc Thanks. Neil _ Feel like a local wherever you go. http://www.backofmyhand.com No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.15.11/1094 - Release Date: 10/26/2007 8:50 AM
Re: Table Size
In the last episode (Oct 27), Baron Schwartz said: InnoDB has the following extra things, plus some things I might forget: a) the primary key B-Tree b) row versioning information for every row c) 16k page size; each page might not be completely full Those are all counted towards the table size. Actually, the primary key B-Tree might not be; I'd need to look that up. But I think it is. H. I just tested -- yes, the PK counts towards table size. In fact, in InnoDB, all indexes count towards table size, since there is a single .ibd file for the whole thing. So you've got the space taken up by your `repid` index to consider as well. Useful reading: http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-structure.html Note the 5-byte header per record in all indexes, plus another 13 bytes per record in the primary key, and the fact that the columns in your primary key are not omitted from the record data. So repid is actually stored three times in the .ibd file; once in the primary key, once in the record, and once in the `repid` index. There's quite a lot of overhead in making a transaction-safe multiversioned table. What I've seen is that for small row lengths (under 50 bytes) an InnoDB table is about twice the size as the same data in MyISAM format (including indexes). For your particular table, you're probably seeing the effect of 16k page sizes. With only 10K rows, your leaf pages are mostly empty. Try putting 100K rows in and see how big the .ibd file is. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Magazine Call for articles
Hey everyone, I am starting on putting together the Winter issue of the MySQL Magazine. This will be our third issue and it is getting better each time! Here is your opportunity to contribute to the community. I am accepting ideas/outlines/proposals for articles for this issue through November the 9th. That gives you almost two weeks to get back to me about that great idea you have in your head. Articles will be due to be completed by December the 1st. If you aren't sure about an idea send me an email. Hope to hear from you soon! Keith Murphy Editor-Mysql Magazine www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
