correct way to simulate 'except' query in mysql 4.1
howdy. i trying to find items in one table that don't exist in another. i'm using a left join with a where clause to do it: SELECT t1.field, t2.field FROM table1 t1 LEFT JOIN table2 t2 ON t1.word = t2.word WHERE t2.word IS NULL; both tables are quite large and the query is quite slow. the field column is indexed in both tables, and explain shows the indexes being used. is there a better way to construct this kind of query? thank you! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query performance plain/text versus AES_DECRYPT(): LIKE %..%
Auch, thanks for pointing that out, what a terrible mistake. I am aware of the performance issue, and so is the customer. But with a table that's only going to hold maximally 60.000 records in 10 years, I'm not afraid it'll cause significant problems. If it gets out of hand we'll have to think of a better solution. Once again, thanks! John -- / Humanique / Webstrategie en ontwikkeling / http://www.humanique.com/ - Humanique zoekt een ervaren Web-ontwikkelaar (PHP). Bekijk de vacature op http://www.humanique.com/ - Jeremy Cole wrote: Hi John, OK, no conspiracy here. Here is your problem: 25 $qry = sprintf(SELECT id, line FROM `encryptietest` WHERE AES_DECRYPT(`field`, '%') LIKE '%%%s%%', $enckey, $word); You are missing the s in %s for your first string argument, which causes the query to be syntactically incorrect and fail. So your AES test is only testing how quickly you can query with a syntax error. :) After adding the s, the results I get are: ([EMAIL PROTECTED]) [~/datisstom/bench]$ php -q bench.php Control test (plain/text LIKE %..%):1.383749s Decrypt test (AES_DECRYPT() LIKE %..%): 1.441944s done Nonetheless, I'd still argue that this entire concept is doomed to terrible performance anyway. Regards, Jeremy John Kraal wrote: I put it here: http://pro.datisstom.nl/tests/bench.tar.bz2 The encryption isn't really a *real* security measure, except for when somebody is stupid enough to install phpMyAdmin or anything equivalent and try to get personal data. The problem is the password needs to be anywhere on the application-server and if you're in, you're in. But it's a request and I'm happy to oblige. Even if it only stops them for 1 minute (which could be enough). Regards, John -- / Humanique / Webstrategie en ontwikkeling / http://www.humanique.com/ - Humanique zoekt een ervaren Web-ontwikkelaar (PHP). Bekijk de vacature op http://www.humanique.com/ - Jeremy Cole wrote: Hi John, Your attachment for the php code got stripped somewhere. Can you post it somewhere (http preferable)? In either case it's going to result in a full table scan, so they are actually both a bad strategy long term, but they should in theory perform as you would expect, with with encryption being slightly slower. Have you tried with longer strings? What is your customer's fear with having the data in plain text? Presumably in order to use this in your application, you will have the AES password stored in your application, and it will end up in logs (such as the slow query log) quite frequently. I would think your data can be safer and your security more effective by setting some policies which are less intrusive into the actual workings of the data, such as encrypting backups and setting system-level policies. Regards, Jeremy John Kraal wrote: Dear you, I've been working on encrypting some data for a customer. They want their personal/sensitive information encrypted in the database, but they want to be able to search it too, through the application. So we've been thinking a bit, and just started trying and benchmarking some solutions we thought up. The next one really got my attention, I created a table with 4 fields: 1. id (primary/auto_increment, not really interesting) 2. field, with encrypted data 3. md5sum (it has no special use, we benched it though.) 4. line, always containing three words (the same three as encrypted) When we started querying the table for random words (from lipsum.com), it seems that searching in the encrypted fields was _lots_ faster. Results below: 1.000 queries per field: ~$ php -q searchtest.php Control test (plain/text LIKE %..%):1.409699s Decrypt test (AES_DECRYPT() LIKE %..%): 1.226069s done 1.000.000 queries per field: ~$ php -q searchtest.php Control test (plain/text LIKE %..%):155.059671s Decrypt test (AES_DECRYPT() LIKE %..%): 137.003216s done Actually, the only thing I could think of to say was: Well, at least it's consistent. I've attached all the files I used for this test. Edit db.inc.php (add some more lipsum if you want), execute fill.php, and then have fun with bench.php. Does any of you know why this is, how come, etc? I'm just very curious. Regards, John Kraal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index usage for simple query
Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Is there a way to get MySQL to use the index for the second query? mysql explain select * from data_total where source=8; | id | select_type | table | type | possible_keys | key| | 1 | SIMPLE | data_total | ref | source| source | mysql explain select * from data_total where (source=8 or source=9); | id | select_type | table | type | possible_keys | key | | 1 | SIMPLE | data_total | ALL | source| NULL | (Output chopped for better formatting) Thanks, Colin Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index usage for simple query
Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Is there a way to get MySQL to use the index for the second query? mysql explain select * from data_total where source=8; | id | select_type | table | type | possible_keys | key| | 1 | SIMPLE | data_total | ref | source| source | mysql explain select * from data_total where (source=8 or source=9); | id | select_type | table | type | possible_keys | key | | 1 | SIMPLE | data_total | ALL | source| NULL | (Output chopped for better formatting) I'd need more info to know for sure, but I'm guessing you're using pre-MySQL 5 or MySQL is deciding it's cheaper to scan the table. If it's too slow, try using an IN(8, 9) clause instead and see if that helps. Or try turning the query into a UNION. 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
Hi, Russell Uman wrote: howdy. i trying to find items in one table that don't exist in another. i'm using a left join with a where clause to do it: SELECT t1.field, t2.field FROM table1 t1 LEFT JOIN table2 t2 ON t1.word = t2.word WHERE t2.word IS NULL; both tables are quite large and the query is quite slow. the field column is indexed in both tables, and explain shows the indexes being used. is there a better way to construct this kind of query? That is the right way, but if you show us the exact output of EXPLAIN we can help more. In particular, does it say Using distinct/not exists in Extra? Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index usage for simple query
Baron Schwartz wrote: Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Is there a way to get MySQL to use the index for the second query? mysql explain select * from data_total where source=8; | id | select_type | table | type | possible_keys | key| | 1 | SIMPLE | data_total | ref | source| source | mysql explain select * from data_total where (source=8 or source=9); | id | select_type | table | type | possible_keys | key | | 1 | SIMPLE | data_total | ALL | source| NULL | (Output chopped for better formatting) I'd need more info to know for sure, but I'm guessing you're using pre-MySQL 5 or MySQL is deciding it's cheaper to scan the table. If it's too slow, try using an IN(8, 9) clause instead and see if that helps. Or try turning the query into a UNION. Baron You're right that it is pre version 5. It's MySQL 4.1. Any particular reason this would make a difference in such a simple case? The table is about a million rows so I doubt MySQL would decide it's cheaper to scan the whole table, or at least if it is I can't figure out why! Unfortunately, an IN() clause gives the same result. I've even tried FORCE INDEX on it, but it doesn't seem to find a candidate key. If we're stuck with a UNION, then it'll take some re-writing of reports, especially as there may be as many as 5 or 6 different 'sources' required. Unfortunately going through a database upgrade isn't an option at the moment. Thanks very much for your help! Colin Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index usage for simple query
Hi, Colin Martin wrote: Baron Schwartz wrote: Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Is there a way to get MySQL to use the index for the second query? mysql explain select * from data_total where source=8; | id | select_type | table | type | possible_keys | key| | 1 | SIMPLE | data_total | ref | source| source | mysql explain select * from data_total where (source=8 or source=9); | id | select_type | table | type | possible_keys | key | | 1 | SIMPLE | data_total | ALL | source| NULL | (Output chopped for better formatting) I'd need more info to know for sure, but I'm guessing you're using pre-MySQL 5 or MySQL is deciding it's cheaper to scan the table. If it's too slow, try using an IN(8, 9) clause instead and see if that helps. Or try turning the query into a UNION. Baron You're right that it is pre version 5. It's MySQL 4.1. Any particular reason this would make a difference in such a simple case? The table is about a million rows so I doubt MySQL would decide it's cheaper to scan the whole table, or at least if it is I can't figure out why! Unfortunately, an IN() clause gives the same result. I've even tried FORCE INDEX on it, but it doesn't seem to find a candidate key. If we're stuck with a UNION, then it'll take some re-writing of reports, especially as there may be as many as 5 or 6 different 'sources' required. Unfortunately going through a database upgrade isn't an option at the moment. I think the UNION is your only option. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select question
I've got this statement to select the last two entries in my db: SELECT top 2 * FROM Content ORDER BY ContentID desc and it works fine because it selects the last two items entered into the db. However, I only want to be able to select item 2 rather than both 1 and 2. How do I do that? Thanks Mat
Re: Index usage for simple query
Hi all, Baron Schwartz wrote: Hi, Colin Martin wrote: Baron Schwartz wrote: Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Optimizing or is pretty complicated, because (in general) the alternatives may access different columns etc. I know your case is a simple one, but still ... Is there a way to get MySQL to use the index for the second query? mysql explain select * from data_total where source=8; | id | select_type | table | type | possible_keys | key| | 1 | SIMPLE | data_total | ref | source| source | mysql explain select * from data_total where (source=8 or source=9); | id | select_type | table | type | possible_keys | key | | 1 | SIMPLE | data_total | ALL | source| NULL | [[...]] Unfortunately, an IN() clause gives the same result. I've even tried FORCE INDEX on it, but it doesn't seem to find a candidate key. If we're stuck with a UNION, then it'll take some re-writing of reports, especially as there may be as many as 5 or 6 different 'sources' required. Unfortunately going through a database upgrade isn't an option at the moment. I think the UNION is your only option. Have you tried BETWEEN ? As long as your source values are continuous, it would be applicable, and source BETWEEN 8 and 9 is still a single condition. I do not claim it *will* be optimized better, but it *might*. If you also need to query for source value lists with gaps, things become different. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select question
Matthew Stuart schrieb: I've got this statement to select the last two entries in my db: SELECT top 2 * FROM Content ORDER BY ContentID desc and it works fine because it selects the last two items entered into the db. However, I only want to be able to select item 2 rather than both 1 and 2. How do I do that? Hi Mat, TOP 2 is not MySQL? However, MySQL knows LIMIT [1] which is more powerful, try: SELECT * FROM Content ORDER BY ContentID DESC LIMIT 1,1 regards -Ralf [1]: http://dev.mysql.com/doc/refman/5.0/en/select.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index usage for simple query
Hi, Joerg Bruehe wrote: Hi all, Baron Schwartz wrote: Hi, Colin Martin wrote: Baron Schwartz wrote: Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Optimizing or is pretty complicated, because (in general) the alternatives may access different columns etc. I know your case is a simple one, but still ... Is there a way to get MySQL to use the index for the second query? mysql explain select * from data_total where source=8; | id | select_type | table | type | possible_keys | key| | 1 | SIMPLE | data_total | ref | source| source | mysql explain select * from data_total where (source=8 or source=9); | id | select_type | table | type | possible_keys | key | | 1 | SIMPLE | data_total | ALL | source| NULL | [[...]] Unfortunately, an IN() clause gives the same result. I've even tried FORCE INDEX on it, but it doesn't seem to find a candidate key. If we're stuck with a UNION, then it'll take some re-writing of reports, especially as there may be as many as 5 or 6 different 'sources' required. Unfortunately going through a database upgrade isn't an option at the moment. I think the UNION is your only option. Have you tried BETWEEN ? As long as your source values are continuous, it would be applicable, and source BETWEEN 8 and 9 is still a single condition. I do not claim it *will* be optimized better, but it *might*. If you also need to query for source value lists with gaps, things become different. Whoops, I overlooked the obvious ;-) Thanks for catching this. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select question
Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Matthew Stuart [mailto:[EMAIL PROTECTED] Sent: Thursday, October 25, 2007 6:55 AM To: MySQL email support Subject: Select question I've got this statement to select the last two entries in my db: SELECT top 2 * FROM Content ORDER BY ContentID desc and it works fine because it selects the last two items entered into the db. However, I only want to be able to select item 2 rather than both 1 and 2. How do I do that? Thanks Mat [JS] What does adding LIMIT 2,1 do? I'm not sure what SELECT top 2 * means. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- 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
There's no using distinct, but there is not exists, and in fact no rows are returned. Slow query log reports #Query_time: 94 Lock_time: 0 Rows_sent: 0 Rows_examined: 370220 EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 150 NULL 338451 Using index 1 SIMPLE t2 ref word word 150 t2.field 4 Using where; Using index; Not exists These are two search tables (hence the large key_len i believe), one with ~400K rows, one row per search term the other with ~4M rows, relating search terms to content. Perhaps I could optimize by doing a count(distinct) on each table and only running the expensive query if the counts don't match? Would I see any benefit by making these InnoDB tables? Thanks for your help with this! Baron Schwartz wrote: Hi, That is the right way, but if you show us the exact output of EXPLAIN we can help more. In particular, does it say Using distinct/not exists in Extra? Russell Uman wrote: howdy. i trying to find items in one table that don't exist in another. i'm using a left join with a where clause to do it: SELECT t1.field, t2.field FROM table1 t1 LEFT JOIN table2 t2 ON t1.word = t2.word WHERE t2.word IS NULL; both tables are quite large and the query is quite slow. the field column is indexed in both tables, and explain shows the indexes being used. is there a better way to construct this kind of query? -- russell uman firebus d-_-b -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table type for high number of insert/delete operations
Jim, MyISAM tables tend to work best in situations where the proportions of reads to writes is either very high or very low. That is to say, either the data doesn't change much but it's being accessed a lot, or the data changes a lot but it's rarely accessed. MyISAM is quite a bit faster than InnoDB in some cases, so it could be that if the size of this table will remain small, it would be the faster choice. InnoDB will allow concurrent access, though, so depending on the level of concurrency you expect, things may move faster using it. Bottom line, no concrete answer for you - I'd test it each way if I were you. Also keep in mind you can switch back and forth without too much trouble, though of course if your table gets large it could take some time to switch. As for impact on your other applications - my knowledge of single tablespace InnoDB performance is limited; I've been using individual tablespaces for InnoDB tables for some time now. -Dan On 10/24/07, Jim [EMAIL PROTECTED] wrote: I have an application which will be inserting and then deleting many thousands of rows per hour within a single table. It essentially queues and then handles requests from a series of processes, deleting the requests after they've been dealt with. Our MySQL 5.0.45 server is set up to use InnoDB tables by default, in a single tablespace. Would MyISAM tables be a better fit for this type of application? The database server is used for other applications so the impact of this application on the others is a concern we have. Also, in terms of speed or server load, would it be better to mark records deleted and then periodically (say once an hour) run a delete query, or would this approach not make a difference? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
running a second mysql server
Hi List, Though new to mysql server, I was pretty happy with what I was doing till I needed to run another instance of mysql server on the same machine. My first installation is running on port 3307 instead of default which I achieved by adding a port attribute in /etc/my.cnf file. Rest of the /etc/my.cnf file contains the basedir, data dir . logs and pid etc. Now I need to install a second server running on 3306 and all different pid, datadir paths etc... When I install the second version giving all the enw paths to configure and then run mysqld, it always say mysqld is already running. I have tried other ways using the default-files option, changing the cnf file locations and none if it working. I will appreciate any pointers please! Thanks
Re: running a second mysql server
Naufal Sheikh a écrit : Hi List, Though new to mysql server, I was pretty happy with what I was doing till I needed to run another instance of mysql server on the same machine. My first installation is running on port 3307 instead of default which I achieved by adding a port attribute in /etc/my.cnf file. Rest of the /etc/my.cnf file contains the basedir, data dir . logs and pid etc. Now I need to install a second server running on 3306 and all different pid, datadir paths etc... When I install the second version giving all the enw paths to configure and then run mysqld, it always say mysqld is already running. I have tried other ways using the default-files option, changing the cnf file locations and none if it working. I will appreciate any pointers please! Thanks Hi, One of the best way to run multiple instance on the same box is by using mysqld_multi. I'll suggest looking at the doc for it : http://dev.mysql.com/doc/refman/5.0/fr/mysqld-multi.html Basically you need to redeclare the pid, datadir etc (looks at the exemple). You'll probably need to work out a bit the init script for your distribution too! Another one is the instance manager for which I have no experience. The documentation should provide the information needed however: http://dev.mysql.com/doc/refman/5.0/en/instance-manager-command-options.html Regards, -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I'm actually planning the application first instead of coding first!!! :)
Jason Pruim a écrit : Hi Everyone, So having learned my lesson with the last application, I am trying to plan out the addition of a feature to my database application. Basically, some of my customers go south for the winter (Snow Birds) what I would like to do is have away of storing both their addresses in the database, and have it so that the people administering the list can choose between wether they are up north or down south without having to erase the old address. For that I was thinking creating a second table SnowBirds and list their southern addresses in there and then when the list admin clicks on the edit button for their name, it would also be able to pull up a list of the the addresses stored and associated with that person. I'm also considering adding a date range for the addresses so that if they know they'll be south from November to March it will check the date and switch between the record accordingly BEFORE exporting to excel. Now... I haven't really asked a question yet but gave some background into what I want to do. So... Here's the question, does anyone have any advice on the best way to do it? Am I right in thinking that a second table is required? Would it be called a Relational database? Or have I missed the terminology? Any help would be greatly appreciated! Thanks for looking! ohhh... and in case it makes a difference it's MySQL 5.* and I'll be writing the stuff to access that database with php 5. -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] Hi, Well that's a way to add a second address to your current application. It may or may not be the best way tough. I think, the cleanest way of doing it would be to modify your current existing address table to add a field that say the type of address it is. Something along the line Type: Primary, Secondary, Temporary and possibly along that date field to keep the information about the date if needed. This however changes your join type, meaning that stuff that actually joins with the address table could now return more than 1 row per user so you'll have to adjust more code than your initial plan. So depending of the current size of the application and the maintenance/upgrade possiblity, you may want to consider other solutions. This one for example has the advantage to allow you to store more than 2 types of address along a user and keep all the address in the same table instead of having 2 tables that store more or less the same kind of information :) Regards, -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
