Re: total idiot
On Monday 09 December 2002 10:35, Justin French wrote: Great idea, but everything I think I need was done with POST, and it appears my host doesn't have the most detailed logs :) I think you are messing up httpd-Logs and MySQL logs here. The question was: Are there MySQL binlogs or ascii logs? If so, it would probably be possible to extract the data in question from these database logs. Kristian -- Kristian Köhntopp, NetUSE AG, Dr.-Hell-Straße, D-24107 Kiel Tel: +49 431 386 435 00, Fax: +49 431 386 435 99 - 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
Why are OR-Clauses handled so badly?
I am using mysql select version() as version; +-+ | version | +-+ | 3.23.48-log | +-+ 1 row in set (0.00 sec) on Suse Linux 8.0 and Solaris 8. The problem exists on both platforms. I have mysql select count(*) from auth_kn; +--+ | count(*) | +--+ |95000 | +--+ 1 row in set (0.00 sec) with kk@kris:~ mysqldump --no-data test auth_kn -- MySQL dump 8.21 -- -- Host: localhostDatabase: test - -- Server version 3.23.48-log -- -- Table structure for table 'auth_kn' -- CREATE TABLE auth_kn ( login varchar(16) NOT NULL default '', alias varchar(16) NOT NULL default '', pw varchar(16) NOT NULL default '', PRIMARY KEY (login), UNIQUE KEY alias (alias) ) TYPE=MyISAM; The table data is synthetically generated for performance testing and looks like this: mysql select * from auth_kn limit 3; +---+---++ | login | alias | pw | +---+---++ | 0 | a0| 0 | | 1 | a1| 1 | | 2 | a2| 2 | +---+---++ 3 rows in set (0.05 sec) I want to build a login that accepts either the user name (which is a 16 digit number that is hard to remember) or the user aliass (which is a unique name the user created for himself) and a password. If I fire a simple query such as mysql select * from auth_kn where ( login = 1000 and pw = 1000 ); +---+---+--+ | login | alias | pw | +---+---+--+ | 1000 | a1000 | 1000 | +---+---+--+ 1 row in set (0.00 sec) mysql explain select * from auth_kn where ( login = 1000 and pw = 1000 ); +-+---+---+-+-+---+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+-+-+---+--+---+ | auth_kn | const | PRIMARY | PRIMARY | 16 | const |1 | | +-+---+---+-+-+---+--+---+ 1 row in set (0.00 sec) all is well. The result for alias is exactly the same: mysql explain select * from auth_kn where ( alias = 1000 and pw = 1000 ); +-+ | Comment | +-+ | Impossible WHERE noticed after reading const tables | +-+ 1 row in set (0.00 sec) mysql explain select * from auth_kn where ( alias = a1000 and pw = 1000 ); +-+---+---+---+-+---+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+---+-+---+--+---+ | auth_kn | const | alias | alias | 16 | const |1 | | +-+---+---+---+-+---+--+---+ 1 row in set (0.00 sec) My problems start as soon as I combine both statements with an or clause. It does not matter if I multiply the operands out or not: mysql select * from auth_kn where ( login = 1000 or alias = 1000 ) and pw = 1000; +---+---+--+ | login | alias | pw | +---+---+--+ | 1000 | a1000 | 1000 | +---+---+--+ 1 row in set (0.23 sec) mysql explain select * from auth_kn where ( login = 1000 or alias = 1000 ) and pw = 1000; +-+--+---+--+-+--+---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+--+---++ | auth_kn | ALL | PRIMARY,alias | NULL |NULL | NULL | 95000 | where used | +-+--+---+--+-+--+---++ 1 row in set (0.00 sec) And multiplying the operands out giving mysql explain select * from auth_kn where ( login= 1000 and pw = 1000 ) or ( alias = 1000 and pw = 1000 ); +-+--+---+--+-+--+---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+--+---++ | auth_kn | ALL | PRIMARY,alias | NULL |NULL | NULL | 95000 | where used | +-+--+---+--+-+--+---++ 1 row in set (0.00 sec) yields the same result. What is the reason for this loss on performance on such a simple query? How would I handle this situation? The current workaround is to have two simple queries, which performs much better than the combined query, but incurs the double RTT and twice the context switches and page faults. Recommendations? Kristian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list
Re: Why are OR-Clauses handled so badly?
Am Mittwoch, 11. September 2002 15:19 schrieb Toni Strandell: If you have proper indexes on alias, the optimizer should not make a table scan. You can try to add an index on pw too. Or you could combine the two queries with an UNION. Sorry, UNION is not available, as it is implemented only in MySQL 4.x and we cannot upgrade the system to this MySQL version (it is a production system). But based on your hint I have searched the documentation again, and found http://www.mysql.com/doc/en/Searching_on_two_keys.html which is exactly my problem (but unfortunately offers no solution). You also reminded me of But beside this point you have a possible problem with the basic design. It is possible that someone adds an alias that is the same as an existing login chosen by the system. Thus I have chosen to normalize the design to login (either 1000, the login name in the previous example, or a1000, the alias name in the previous example) and make this a primary key. password (just like before) subscriber (the login name in the previous example) and make this a key, and mark it as a foreign key into an external customer table. This customer table does not actually exist in the MySQL web frontend, but only in the subscriber database in the backend, but I need this link for metering purposes. I now have at most 190.000 records instead of 95.000 previously, but the performance has improved greatly. Thank you for your quick assistance and for providing the proper search keyword for the documentation (searching for union leads directly to the URL Two Keys URL cited above). Kristian -- NP: Slaying the dreamer, Century Child (Nightwish) - 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
Read-Only MySQL
I want to create a CD-ROM which contains a copy of MySQL (for Windows) and a set of MySQL databases. Is it possible to set up a read-only MySQL, that is, a MySQL running from a r/o media? Kristian - 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: blob versus file
Am Mittwoch, 3. Juli 2002 20:43 schrieb central: More specific: Can I efficiently read the bytes x to y from any BLOB stored in a MySQL database? Why not just add another column, Char(3), that contains the file extension? That would fix this particular case, but my thought were more along a general comparison of BLOB and file APIs. While ext2 open is dominated by directory lookup times when opening files, reiserfs and MySQL perform logarithmically here. On the other hand, during read and write phases ext2 and reiserfs as well as all other file systems have an API that allows them partial object reads and writes as well as seeking within that object. The question is, what does the MySQL BLOB API provide and how does it relate to performance of applications making use of the MySQL BLOB API? Kristian - 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: Re: Transparent Encryption [was: encrypt myisam?]
Am Donnerstag, 4. Juli 2002 09:03 schrieben Sie: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Am Mittwoch, 3. Juli 2002 20:06 schrieb Tobias Bengtsson: how strong is the PASSWORD()-funtion? is it just some crypt(3)-variant or good shit? password() seems to be using some kind of one-way hashing=20 function, probably md5 or a related function. It does take=20 passwords of arbitrary length, and produces a password-entry of=20 a fixed length. Also, identical passwords used by different=20 users have identical encrpytions, so the user name or some other=20 secret or salt is not part of the hashed password (this is a=20 potential weakness!). Kristian - 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: blob versus file
Am Mittwoch, 3. Juli 2002 10:58 schrieb Elizabeth Mattijsen: Not meaning to put down MySQL, but have you tried this also with a ReiserFS filesystem? I had a similar number of files, about 70 GByte worth on an ext2 filesystem. Moved them to a ReiserFS filesystem and found I only needed 51 GByte. And got a much faster system... ext2 searches directories linearly. With 500.000 files in a directory where each file is subsequently accessed in a benchmark, you get quadratic access times. reiserfs uses a tree structure for directories, giving you almost linear (O(n log n)) access. It also packs tails of different files into singular blocks, saving much space as well. Question: Can the MySQL BLOB API access and transfer partial blobs. That is, if you want to do the equivalent of a file * to a BLOB table, the first 10 bytes or so of each BLOB must be read in order to guess the type of the BLOB. Is it possible to implement this efficiently using the MySQL API? More specific: Can I efficiently read the bytes x to y from any BLOB stored in a MySQL database? Kristian - 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
