General DB Design Question - How to avoid redundancy in table relationships
These are the tables in question: RFQ (Request for Quote) Part Inventory Inventory items ALWAYS have a partID. RFQ items ALWAYS have a partID. However, sometimes, RFQ items have an inventoryID as well. Now, we have a redundancy problem. Because, in those instances when the RFQ has an inventoryID, the partID should be derived from the inventoryID. If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. We don't want to have both the inventoryID and the partID in the RFQ table, because it opens up data integrity issues. ie, what if the RFQ item shows inventoryID 2, and partID 1...but inventoryID 2 is associated to partID 2. Now which partID is correct? They can't both be right. I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. This was a simple example; however, we are running into the problem system wide. For example, a quote table has an OPTIONAL RFQ ID, and a mandatory contactID. The RFQ table has a mandatory contactID. If the quote table has an RFQID, we want to derive the contactID from the RFQID. If the quote has NO RFQID, then we need to store the contactID directly in the quote table. In those instances where there IS an RFQID in the quote table, we end up storing the contactID twice. Once in the quote table, and once in the association between the RFQ/Contact table. Same problem as above: integrity and poor overall design. Thanks for your advice.
Re: MySQL LEFT JOIN Optimization Using LIMIT CLAUSE
Thanks a lot Shawn. As always, your advice has been very helpful. On 2/3/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 02/02/2006 02:01:11 PM: I have a table `requirement` which is left joining to a table `inventory` based on a matching `partNumber` column. The inventory table has millions of records, the requirement table has tens of thousands. I'm noticing that the left join between requirement and inventory doesn't take advantage of a LIMIT clause. So whether I select all records from requirement or limit it to 50, the LEFT JOIN operation still seems to be calculating for ALL requirement records against ALL inventory records. (The query takes the exact same amount of time, whether I pull 50 requirement records or 10,000). How can I force mysql to only join the inventory table for the those 50 records brought back by the LIMIT clause? What I would do in a more powerful DB like SQL Server, is build a temporary table with my 50 requirement rows, and then perform the inventory join on the temp table. But due to MySQL SPROC limitations (ie, LIMIT clauses must have integer constants, not parameters) and View limititations (ie, no indexing of views), I'd have to build this temporary table and the rest of query in PHP first, which is really ugly. I'm hoping there is a nice SQL trick I can use with MySQL to restrict the join to only those records that would come back from the limit set. Thanks, Scott Klarenbach Yes, and no. You cannot apply a LIMIT specifically to a JOIN clause unless you break your query into separate pieces and put limits on each of them. What happens during the normal execution of a query is that after parsing and planning the engine begins collecting and combining the source data. Which records are combined and matched against which others is defined in the FROM clause and all of the JOIN clauses. The equivalent to a large virtual table (similar to saying SELECT * FROM all involved tables) is created in memory. The only restrictions to which rows of data make it into this first processing stage come from the ON clauses (and any WHERE clauses the optimizer _may_ choose to include) defined between the JOINed tables. Next comes WHERE clause processing, then GROUP BY processing, HAVING processing, ORDER BY processing, and finally LIMIT processing. As you can see by the flow of query execution, LIMIT clauses are really only useful for restricting how much data is finally sent to the user. In order to minimize how much processing your CPU has to do to compute a particular query you have several tools at your disposal: indexes, temporary tables, and stepwize result construction. JOINing tables is a geometrically expensive action. The number of potential row matches increase by the product of the number of rows in each table involved in the join. If you can preselect certain target rows from your really large tables into smaller temporary tables and build your final result set from them, the query processor will only need to compute a small fraction of the row comparisons it would have had to perform compared to the number of row comparisons necessary to JOIN your original tables. Take this rough math as an example: TABLE A: 1 rows TABLE B: 1 rows SELECT * from A INNER JOIN B ON A.id http://a.id/ = B.A_ic; There are potentially 1 x 1 = 1 (1.0e+08) row combinations to be checked. If instead of joining A to B, we create two derivative tables called C and D (assuming we don't change the column names) TABLE A - TABLE C: 5000 rows TABLE B - TABLE D: 1000 rows SELECT * from C INNER JOIN D ON C.id http://c.id/ = D.A_ic; That means there are now 5000 x 1000 = 500 (5.0e+06) or 1/20th the number of comparisons to run. Computing tables C and D should be in linear or logarithmic time (because you should have good index coverage) so there will usually be a net gain in performance. This is the secret to stepwize result construction. To help you to optimize your particular query, I would need to see it and the table definitions it is working against (SHOW CREATE TABLE works best for me). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
MySQL LEFT JOIN Optimization Using LIMIT CLAUSE
I have a table `requirement` which is left joining to a table `inventory` based on a matching `partNumber` column. The inventory table has millions of records, the requirement table has tens of thousands. I'm noticing that the left join between requirement and inventory doesn't take advantage of a LIMIT clause. So whether I select all records from requirement or limit it to 50, the LEFT JOIN operation still seems to be calculating for ALL requirement records against ALL inventory records. (The query takes the exact same amount of time, whether I pull 50 requirement records or 10,000). How can I force mysql to only join the inventory table for the those 50 records brought back by the LIMIT clause? What I would do in a more powerful DB like SQL Server, is build a temporary table with my 50 requirement rows, and then perform the inventory join on the temp table. But due to MySQL SPROC limitations (ie, LIMIT clauses must have integer constants, not parameters) and View limititations (ie, no indexing of views), I'd have to build this temporary table and the rest of query in PHP first, which is really ugly. I'm hoping there is a nice SQL trick I can use with MySQL to restrict the join to only those records that would come back from the limit set. Thanks, Scott Klarenbach
Import File / Insert Optimization Help
I am importing records from a text file into my DB. Each record in the text
file corresponds to six tables in the DB due to normalization.
So for example, an inventory record containing a part, vendor,
contactPerson, mpn etc...in ONE line of a text file, requires 6 inserts into
the DB.
Further more, before each insert, I need to check for redundancy. So, if an
inventory line in the text file has vendor 'Scott', BEFORE I add 'Scott' to
the vendor table, I check the vendor table for whether 'Scott' exists. If
'Scott' does exist, then I just pull the ID and use that in the inventory
insert - if 'Scott' DOESN'T exist yet, I insert 'Scott' into the Vendor
Table, get the last_insert_id() and use that in the inventory table.
Each LINE in the text file can result in more than 20 Select/Insert
statements of the underlying DB before I can insert the record with all the
properly allocated foreign keys.
Considering many of these text files have thousands of lines of inventory,
as you can imagine, I have a massive performance problem. Each complete
line of the text file requires about 1 full second to validate and insert
into the underlying schema.
I'm using InnoDB tables so alot of the Insert Optimization techniques I
found from MySQL don't seem to apply too well. I'm hoping for some
experienced feedback in alternative techniques for performing this sort of
import.
Some further info:
In PHP 5, I get my connection object, and then in a loop I'm calling
$connecion-query(); for each line of the text file. I'm assuming this is
always using the same connection and that each query doesn't require a
re-connect to the DB...if it does, that's a major bottleneck that could be
avoided (with persistent connections?)
ie
$conn = mysqli_init(); //null connection object
$conn-real_connect('host', 'user', 'pass', 'db') or die('connection');
foreach($file as $line)
{
buildQuery();
$result = $conn-query($sql);
}
I hope each time I call $conn-query($sql) it's using the SAME connection
resource and not having to reconnect to the DB.
Secondly, $sql involves a call to a Stored Procedure, which in turn ends up
calling other stored procedures to faciliate all the transactions,
validations and inserts...I assume that's not too much more innefficient
than using sql insert statements directly.
Any help is appreciated.
Scott.
Re: MySQL View Optimization Help
Thanks a lot Shawn. I didn't realize that views don't take advantage of indexing. This is the cause of my major performance hits. I'm basically using views as a form of DB abstraction over the tables. So, many of my views pull all records from all tables they join, and it is up to the user to submit a where query to the view. In many cases, I'm getting 20-30 second queries, whereas the underlying (indexed) tables return results in .33 seconds. The views themselves aren't using criteria. This runs contrary to what I imagine to be a common use of views, ie, vwSelectAllArizonaResidents sort of thing, where the view internally compiles the where criteria from the underlying table. Scott Klarenbach On 1/6/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 01/06/2006 08:13:10 PM: Hello, I'm new to views and am discovering massive performance hits in the views I've created once the records start to climb above 20,000 or so. Does anyone know of a great primer/tutorial site for optimizing views in MySQL, or even generally? What are the best practices etc...? I find when I create the same view in SQL by joining the tables directly, it's much faster than the views which invariably are joining other views. Is there a recursion problem with this method? Should views only join underlying tables and not other views? Thanks. Scott. Treat views as you would any other query. All of the optimizations that normally apply to SELECT query performance should also apply to view performance. Views differ from tables in that they cannot be indexed. That is probably why you are getting performance hits by building views on views. Any query against a view (such as a second-tier derivative view) will end up performing the equivalent of a full table scan on any view it uses. There is no hard and fast rule about building views based on other views or based on tables. What works best for you should be which solution you stick with. If you have millions of rows in a base table and a view can reduce that to about ten thousand rows of summary information, I would be very tempted to stick with the view as the basis of a future query. You still have to generate that view each time you want to use it but its data may be sitting there in the query cache so it has the potential to be very fast. If I were you I would review the entire optimization chapter: http://dev.mysql.com/doc/refman/5.0/en/optimization.html It's loaded with useful information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
MySQL View Optimization Help
Hello, I'm new to views and am discovering massive performance hits in the views I've created once the records start to climb above 20,000 or so. Does anyone know of a great primer/tutorial site for optimizing views in MySQL, or even generally? What are the best practices etc...? I find when I create the same view in SQL by joining the tables directly, it's much faster than the views which invariably are joining other views. Is there a recursion problem with this method? Should views only join underlying tables and not other views? Thanks. Scott.
Re: How to use Logic in View Statment?
As an update to my earlier question, is it possible to have logic in select statements including the join? ie, select IF(CHAR_LENGTH(broker)0,broker,vendor) as company from table 1 IF(CHAR_LENGTH(broker)0,INNER JOIN tblBroker,INNER JOIN tblVendor) Thanks. On 11/28/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 11/28/2005 01:58:22 PM: I'd like to do the following in my view select fieldOne, fieldTwo, if(fieldThree.length0) then fieldThree as Company else fieldFour as Company from table; I realize this syntax isn't correct and length doesn't exists, but is this possible? I've seen it done in SQLServer, but can't remember the syntax. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] LENGTH does exist (as a function): http://dev.mysql.com/doc/refman/5.0/en/string-functions.html and here is how to use the IF() function: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html I believe this is the equivalent statement you wanted: SELECT fieldOne, fieldTwo, IF(CHAR_LENGTH(fieldThree)0,fieldThree,fieldFour) as Company FROM table; Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to use Logic in View Statment?
I'd like to do the following in my view select fieldOne, fieldTwo, if(fieldThree.length0) then fieldThree as Company else fieldFour as Company from table; I realize this syntax isn't correct and length doesn't exists, but is this possible? I've seen it done in SQLServer, but can't remember the syntax. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order By Statement in Views not working properly
Mysql seems to have a problem returning ordered lists from views. ie, 'select * from myView order by column' does not return a properly ordered list. (it does change the ordering sequence, but to an erroneous list) converting myView to be a Temporary Table using 'ALGORITHM=TEMPTABLE' in the 'CREATE VIEW' statement works, but I imagine this is a performance hit, especially on large tables. Has anybody figured out why this is and come up with a solution, or am I missing something that wasn't in the manual? Note: I'm aware that any order by clauses in the create view statement itself are overridden by an order by clause when calling the view, but this isn't what I'm referring to. The myView statement has no order by clause, yet I can't seem to order the list when calling the view. Thanks, Scott Klarenbach -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Addition: Order By Statement in Views not working properly
Must be a bug of sorts: If you add an ORDER BY clause to the view itself, then mysql allows the caller to override with their own ORDER BY clause and it works as expected. However; if no ORDER BY clause is present in the CREATE VIEW statement, then the caller receives erroneous results if they call the view with their own ORDER BY clause. Scott Klarenbach. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible Bug? Left Join With OR Clause Takes Minutes
I'm using Mysql 5.0.7 and I've noticed the following very strange functionality, perhaps someone can shed some light on it for me. 2 Tables (Request and Inventory) Request id (int), partNumber varchar(60) Inventory id(int), MPN varchar(60), MPNClean varchar(60) I have about 1500 request records, and 20,000 inventory records. The following query takes over 1.5 minutes to execute. select r.id from request r LEFT JOIN inventory i ON (i.MPN=r.partNumber OR i.MPNClean=r.partNumber); [~1.5 minutes to execute] I have no Idea why it would take so long. So I started playing around with the joins, and noticed something. If there is only one join criteria, it returns quickly: select r.id from request r LEFT JOIN inventory i ON (i.MPN=r.partNumber) [~1 second to execute] Similarly, an INNER join, regardless of the number of parameters, also returns quickly select r.id from request r INNER JOIN inventory i ON (i.MPN=r.partNumber OR i.MPNClean=r.partNumber); [~1 second to execute] select r.id from request r INNER JOIN inventory i ON (i.MPN=r.partNumber); [~1 second to execute] If someone could enlighten me as to why this is happening, I'd really appreciate it. Thanks, Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump ONLY Schema
I've used msyqldump to retrieve the structure and data of my db, but can I use it to only spit out the structure, ie, the Create Table statements, but none of the inserts. Thanks. Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Native XML Support
Does MySQL 5 provide native XML support? ie, can I have a stored procedure return an XML string instead of a recordset? Can I pass in an XML string/doc and have the DB update relational tables based on it? Thanks. Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select Query Optimization - 1 minute long :(
The following query is in desperate need of optimization. Any gurus out there who can share some insights, I'd greatly appreciate it. I have a request table, from which I'm pulling all records. Each request record has a part number, and for each requested part number, I'd like to query the inventory table for all matching inventory items for that part number, and calculate the potential value of the inventory. Each inventory record has 4 price fields, and it is the HIGHEST value of these fields used in the calculation. As you can tell by running the query, it is incredibly expensive. We have potentially millions of inventory records and 10,000+ requests, so the query is going to take several minutes to complete. Also, I found that due to the joining on each record(?), I couldn't implement a limit clause to save time. Whether or not a limit clause is tacked onto the end of the query, it completes the entire calculation process for both tables, and THEN returns the limited number of records. This of course saves me no time. :( Any help is greatly appreciated, Thanks. ps, all pertinent fields have already been indexed. This reduced the query time by half, but half of an eternity is still not that impressive. I'm also aware that the use of LIKE in my JOIN is expensive, but after replacing it with a =, I achieved only modest performance gains. SELECT r.id, r.company, r.dateSent, r.fullName, r.phone, r.fax, r.email, r.address1, r.address2, r.city, r.province, r.country, r.comments, r.partNumber, r.description, r.dateCode, r.qty, r.targetPrice, r.manufacturer, r.expiryDate, r.companyType, r.yearEstablished, r.url, r.languages, GREATEST(i.distySellCost, i.originalCost,i.unitCost,i.unitSellCost)*r.qty AS 'highestValue', count(i.id) as 'matches', SUM(i.qty) as 'qtyAvailable' FROM request r LEFT JOIN inventory i ON ( i.MPN LIKE CONCAT(r.partNumber, '%') OR i.MPNClean LIKE CONCAT(r.partNumber, '%')) AND i.status=1 WHERE r.deleted=0 GROUP BY r.id; Any help is appreciated. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL preg_split functionality?
Thanks.
On 6/1/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005 06:57:19
PM:
-- How is 'xFxIxExLxDx1x' easier to search than 'FIELD1'?
It's not easier. It is; however, accurate for the purpose at hand.
FIELD1 isn't completely inclusive...it would miss FIELD-1...
-- However, It seems to me that this kind of data manipulation
(cleanup) needs to happen BEFORE the data enters the databse.
Sometimes FIELD-1 is the ACTUAL data, with no erroneus
characters...and sometimes it is FIELD1 with an erroneous (or
unwanted) - character before the 1)...so cleaning the data would
actually be corrupting some of it.
I need to keep the data in it's original form, but also allow for
querying without worrying about the special characters inside the
column.
FYI, these are part numbers off of electronic components, many of them
coming from China...so, a Cisco part may have an MPN of RX321, or
RX321-TR...either is valid. Now, the corresponding Chinese part
number for the first one, may come back as RX32-1...which is out of my
control.
Cleaning the data would be the wrong approach, because it would
actually invalidate the second Cisco part number, which MEANT to
include the special characters.
For this reason, the user wants to be able to search for 'RX321' and
'RX321TR' respectively, and not worry about whether the data is
erroneous or valid; just to basically ignore all the characters and
let a human decide what they want.
If you have a more elegant solution, I'm all ears :-).
On 5/27/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005
04:38:40
PM:
Ya, it is a little too specific...here's why I need it.
I have a client that wants to search for part numbers in his DB. The
problem is, they come into his DB from external sources, with all
sorts of special characters in them...
So, he has fields like
field_one!,
fi--eld 2,
@fi#eld__3xxx
etc
but, he wants to do a search for 'fieldone' and return the first one,
'field2' returns the second, etc...basically disregard all non-alphas
padding every character in the search string.
On 5/27/05, Eric Bergen [EMAIL PROTECTED] wrote:
I'm working on a set of UDFs for preg functions.
[EMAIL PROTECTED] wrote:
I have a hard time figuring out when you would use such a function.
I
do
not believe you will be able to duplicate this behavior without
constructing your own UDF or by writing a stored procedure. BTW,
why
*do*
you want this function?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005
01:30:35
PM:
I'm trying to replicate this PHP behavior in a MySQL stored
procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character is 'x' and thestring
is
'STRING', the result is 'xSxTxRxIxNxGx'.
Here is the PHP code if it helps. I'd like to use a regular
expression to replace, but I guess I could loop through the string
char by char and build a new one, it's just less elegant. Thanks
in
advance.
PHP:
-
$regPattern = implode('x', preg_split('//', STRING, -1,
PREG_SPLIT_NO_EMPTY));
Thank you very much. I find this whole padding process very
counterintuitive. I have a few minor questions, if you don't mind. How
is
'xFxIxExLxDx1x' easier to search than 'FIELD1'? Would you, could you
please
explain the theory behind why and when this kind of padding should be
done?
What problem does it solve and how is it a solution to that problem?
This
is completely baffling to me and I thought I had seen a lot of weird
data
before :-)
However, It seems to me that this kind of data manipulation (cleanup)
needs
to happen BEFORE the data enters the databse. What data import
tool/process
is your client using? Can you not change the import process to scrub the
data and does it not have a better facility to interleave padding into a
string than a MySQL stored procedure or UDF?
Thanks for you patience!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
I would suggest the following technique. Keep your original data (trimmed of
leading and trailing whitespace characters) in one field and a cleansed
version of the same information in a second field. By cleansing you should
eliminate all non-alphanumerics from your search string.
I have some direct insight to searching similar but different product codes
(same product/different codes different products/same codes). My wife
works
Re: Select MAX(column1,column2)
You guys have been so helpful with this, I'm hoping that I can ask for one more favor... The reason I needed the greatest(max()) functionality, was to run the following query...I can make it work from the command line, but everytime I run it from PHP, the MySQL service shuts down, and needs to be restarted manually. I'm calling a stored procedure 'selectAllRequests' which is the following query: SELECT r.id, r.partNumber, r.OtherFields, functionGetHighestValue(r.partNumber, r.qty) AS 'highestValue' FROM request r WHERE r.deleted=0 ORDER BY highestValue DESC, r.dateSent DESC; the function I'm calling is as follows: CREATE FUNCTION `functionGetHighestValue`(`MPNParam` varchar(60), `qtyParam` DOUBLE(10,4)) RETURNS DOUBLE(10,4) BEGIN DECLARE dHighest DOUBLE(10,4) DEFAULT 0; SELECT GREATEST(MAX(i.distySellCost), MAX(i.originalCost), MAX(i.unitCost), MAX(i.unitSellCost))*qtyParam FROM inventory i WHERE i.MPN = 'MPNParam' AND i.status=1 INTO dHighest; RETURN dHighest; END| As I say, I can call this procedure from the command line and it works, but calling it from PHP results in the MySQL service crashing on my Windows 2003 server. I'm using PHP 5.0.4 and MySQL 5.0.4. Any help is appreciated. Thanks. On 5/27/05, Scott Klarenbach [EMAIL PROTECTED] wrote: select greatest(max(col1), max(col2), max(col3), max(col4)) from table works the best, as Keith pointed toward initially. Remember, I forgot to mention that I wanted the greatest for the whole table, not just for each rowso, 10, 12, 8 is not what I wanted...out of 10 2 3 5 4 8 1 12 7 i want 12. thanks again. On 5/27/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I forgot : 10, 12, 8 is not a row !!! Mathias Selon [EMAIL PROTECTED]: Hi Keith, yes concat makes an associative lost for max. But if we split the desc on all the columns, it works : mysql select * from numbers - order by a desc,b desc,c desc - limit 1; +--+--+--+ | a| b| c| +--+--+--+ | 10 |2 |3 | +--+--+--+ 1 row in set (0.00 sec) it's a real desc ordering. Thanks Mathias Selon Keith Ivey [EMAIL PROTECTED]: [EMAIL PROTECTED] wrote: Hi all, what is max ? it's the first row when we sort data in descending order. so select col1,col2,col3,col4 ... from table order by concat(col1,col2,col3,col4 ... ) desc LIMIt 1; should be silar to what is needed. I say should :o) That would only work if the greatest values for col2, col3, col4, etc., all occurred in the same row with the greatest value for col1, and if all the values for col1 had the same number of digits (and the same for col2, col3, etc.). Consider this table: 10 2 3 5 4 8 1 12 7 Your query would give 5, 4, 8 (because 548 as a string is greater than 1023 or 1127), but he wants 10, 12, 8. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Call a function from a select statement?
Is this not the proper way to use a function in a select statement? SELECT t.field1, t.field2, functionPerformAdditionalQueryFromField(t.field2) AS 'customField' FROM Table t I'd like to perform the function on every row in the result set, and store the returned value of that function in EACH row, as a custom field. It works from the command line, but in PHP... the result set comes back the FIRST time, but then I lose my connection to the database, and need to restart the service in 2003 server. Very frusterating...any help is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Call a function from a select statement?
Tried that, but to no avail. I'm running Mysql 5.0.4 and PHP 5.0.4, and was hoping it is a bug in one of these, but I don't think so... I can run the query as I said from the command line, but in PHP, I get a real_connect_error, immediately after executing the query... that is...the windows service shuts down at the completion of the query and needs to be manually restarted...(the service crashing, is what causes the real_connect_error)... On 5/31/05, Dathan Pattishall [EMAIL PROTECTED] wrote: Are you getting a error Lost Connection to Server error? If you are reconnect to the server every time you issue the SQL request. OR issue the command set GLOBAL wait_timeout=28000; Then issue the select DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Scott Klarenbach [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 10:58 AM To: My SQL Subject: Call a function from a select statement? Is this not the proper way to use a function in a select statement? SELECT t.field1, t.field2, functionPerformAdditionalQueryFromField(t.field2) AS 'customField' FROM Table t I'd like to perform the function on every row in the result set, and store the returned value of that function in EACH row, as a custom field. It works from the command line, but in PHP... the result set comes back the FIRST time, but then I lose my connection to the database, and need to restart the service in 2003 server. Very frusterating...any help is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL preg_split functionality?
I'm trying to replicate this PHP behavior in a MySQL stored procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character is 'x' and the string is
'STRING', the result is 'xSxTxRxIxNxGx'.
Here is the PHP code if it helps. I'd like to use a regular
expression to replace, but I guess I could loop through the string
char by char and build a new one, it's just less elegant. Thanks in
advance.
PHP:
-
$regPattern = implode('x', preg_split('//', STRING, -1,
PREG_SPLIT_NO_EMPTY));
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select MAX(column1,column2)
Can I select the maximum value across multiple columns? ie, I'd like to select the highest value of buyCost AND sellCost in a table...where buy and sell are two different columns in the same table. i actually have 4 comparisons to run, and don't want to have to execute 4 queries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Preg_split functionality
Here's what I came up with in case anyone else needs a quick fix. A regular expression replace would've been nicer, but, you do what you gotta do... CREATE PROCEDURE `test`(`par` varchar(60)) BEGIN DECLARE nChars INT DEFAULT CHAR_LENGTH(par); DECLARE nCounter INT DEFAULT 1; DECLARE sPattern VARCHAR(300) DEFAULT ''; DECLARE sRegEx VARCHAR(60) DEFAULT '[^a-zA-Z0-9]*'; -- pad the regex pattern on each side of every character in the search string WHILE nCounter = nChars DO SET sPattern = CONCAT(sPattern, sRegEx, SUBSTR(par, nCounter, 1)); SET nCounter = nCounter + 1; END WHILE; -- add the pattern to the end of the string too SET sPattern = CONCAT(sPattern, sRegEx); END| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL preg_split functionality?
Ya, it is a little too specific...here's why I need it.
I have a client that wants to search for part numbers in his DB. The
problem is, they come into his DB from external sources, with all
sorts of special characters in them...
So, he has fields like
field_one!,
fi--eld 2,
@fi#eld__3xxx
etc
but, he wants to do a search for 'fieldone' and return the first one,
'field2' returns the second, etc...basically disregard all non-alphas
padding every character in the search string.
On 5/27/05, Eric Bergen [EMAIL PROTECTED] wrote:
I'm working on a set of UDFs for preg functions.
[EMAIL PROTECTED] wrote:
I have a hard time figuring out when you would use such a function. I do
not believe you will be able to duplicate this behavior without
constructing your own UDF or by writing a stored procedure. BTW, why *do*
you want this function?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005 01:30:35
PM:
I'm trying to replicate this PHP behavior in a MySQL stored procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character is 'x' and the string is
'STRING', the result is 'xSxTxRxIxNxGx'.
Here is the PHP code if it helps. I'd like to use a regular
expression to replace, but I guess I could loop through the string
char by char and build a new one, it's just less elegant. Thanks in
advance.
PHP:
-
$regPattern = implode('x', preg_split('//', STRING, -1,
PREG_SPLIT_NO_EMPTY));
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select MAX(column1,column2)
Thanks Keith. It didn't quite work as expected, but it helps me a lot none the less. The Documentation says it returns the max value, so select greatest(1, 2, 3, 4) will return 4. But, across multiple column names, it returns all the values in one column, not just the greatest one...so select greatest(fieldone, fieldtwo, fieldthree, fieldfour) from table returns 10 12 14 29 6 3 444 etc...from all 4 of those fields. So, by going Select MAX(GREATEST(fieldone, fieldtwo, fieldthree)) I can accomplish exactly what I've been banging my head against a wall for...thanks. On 5/27/05, Keith Ivey [EMAIL PROTECTED] wrote: Scott Klarenbach wrote: Can I select the maximum value across multiple columns? You want the GREATEST() function: http://dev.mysql.com/doc/mysql/en/comparison-operators.html -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select MAX(column1,column2)
select greatest(max(col1), max(col2), max(col3), max(col4)) from table works the best, as Keith pointed toward initially. Remember, I forgot to mention that I wanted the greatest for the whole table, not just for each rowso, 10, 12, 8 is not what I wanted...out of 10 2 3 5 4 8 1 12 7 i want 12. thanks again. On 5/27/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I forgot : 10, 12, 8 is not a row !!! Mathias Selon [EMAIL PROTECTED]: Hi Keith, yes concat makes an associative lost for max. But if we split the desc on all the columns, it works : mysql select * from numbers - order by a desc,b desc,c desc - limit 1; +--+--+--+ | a| b| c| +--+--+--+ | 10 |2 |3 | +--+--+--+ 1 row in set (0.00 sec) it's a real desc ordering. Thanks Mathias Selon Keith Ivey [EMAIL PROTECTED]: [EMAIL PROTECTED] wrote: Hi all, what is max ? it's the first row when we sort data in descending order. so select col1,col2,col3,col4 ... from table order by concat(col1,col2,col3,col4 ... ) desc LIMIt 1; should be silar to what is needed. I say should :o) That would only work if the greatest values for col2, col3, col4, etc., all occurred in the same row with the greatest value for col1, and if all the values for col1 had the same number of digits (and the same for col2, col3, etc.). Consider this table: 10 2 3 5 4 8 1 12 7 Your query would give 5, 4, 8 (because 548 as a string is greater than 1023 or 1127), but he wants 10, 12, 8. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL preg_split functionality?
-- How is 'xFxIxExLxDx1x' easier to search than 'FIELD1'?
It's not easier. It is; however, accurate for the purpose at hand.
FIELD1 isn't completely inclusive...it would miss FIELD-1...
-- However, It seems to me that this kind of data manipulation
(cleanup) needs to happen BEFORE the data enters the databse.
Sometimes FIELD-1 is the ACTUAL data, with no erroneus
characters...and sometimes it is FIELD1 with an erroneous (or
unwanted) - character before the 1)...so cleaning the data would
actually be corrupting some of it.
I need to keep the data in it's original form, but also allow for
querying without worrying about the special characters inside the
column.
FYI, these are part numbers off of electronic components, many of them
coming from China...so, a Cisco part may have an MPN of RX321, or
RX321-TR...either is valid. Now, the corresponding Chinese part
number for the first one, may come back as RX32-1...which is out of my
control.
Cleaning the data would be the wrong approach, because it would
actually invalidate the second Cisco part number, which MEANT to
include the special characters.
For this reason, the user wants to be able to search for 'RX321' and
'RX321TR' respectively, and not worry about whether the data is
erroneous or valid; just to basically ignore all the characters and
let a human decide what they want.
If you have a more elegant solution, I'm all ears :-).
On 5/27/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005 04:38:40
PM:
Ya, it is a little too specific...here's why I need it.
I have a client that wants to search for part numbers in his DB. The
problem is, they come into his DB from external sources, with all
sorts of special characters in them...
So, he has fields like
field_one!,
fi--eld 2,
@fi#eld__3xxx
etc
but, he wants to do a search for 'fieldone' and return the first one,
'field2' returns the second, etc...basically disregard all non-alphas
padding every character in the search string.
On 5/27/05, Eric Bergen [EMAIL PROTECTED] wrote:
I'm working on a set of UDFs for preg functions.
[EMAIL PROTECTED] wrote:
I have a hard time figuring out when you would use such a function. I
do
not believe you will be able to duplicate this behavior without
constructing your own UDF or by writing a stored procedure. BTW, why
*do*
you want this function?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005
01:30:35
PM:
I'm trying to replicate this PHP behavior in a MySQL stored procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character is 'x' and the string is
'STRING', the result is 'xSxTxRxIxNxGx'.
Here is the PHP code if it helps. I'd like to use a regular
expression to replace, but I guess I could loop through the string
char by char and build a new one, it's just less elegant. Thanks in
advance.
PHP:
-
$regPattern = implode('x', preg_split('//', STRING, -1,
PREG_SPLIT_NO_EMPTY));
Thank you very much. I find this whole padding process very
counterintuitive. I have a few minor questions, if you don't mind. How is
'xFxIxExLxDx1x' easier to search than 'FIELD1'? Would you, could you please
explain the theory behind why and when this kind of padding should be done?
What problem does it solve and how is it a solution to that problem? This
is completely baffling to me and I thought I had seen a lot of weird data
before :-)
However, It seems to me that this kind of data manipulation (cleanup) needs
to happen BEFORE the data enters the databse. What data import tool/process
is your client using? Can you not change the import process to scrub the
data and does it not have a better facility to interleave padding into a
string than a MySQL stored procedure or UDF?
Thanks for you patience!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
String Literals ONLY for REGEXP, LIMIT and LOAD DATA clauses in MySQL Stored Procedures?
Am I correct in assuming that MySQL requires string literals in stored
procedures for the following clauses:
REGEXP, LIMIT and LOAD DATA INFILE?
For example, I cannot seem to pass in the {pattern} as a parameter to
my SPROC, and then query for ...WHERE field REGEXP pattern
I'm having the same problem passing in the {count,offset} parameters
for LIMIT clauses, and the {filename} for LOAD DATA INFILE.
Thanks for any insights.
Scott Klarenbach
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
repetition-operator operand invalid
I've upgraded from 5.0.2 to 5.0.3 Beta, and now there is a glitch in one of my regular expression queries. The expression works like this: a query for 'search' returns true for a matching 'search' field, but, querying 's$$#e%ar^c)(h' must also return true for a 'search' field. In other words, I need to pad every letter of the search string and tell it to allow any number of non-alphanumeric characters. Here is the expression I'm using below, for the term SEARCH: '[^a-zA-Z0-9]*S[^a-zA-Z0-9]*E[^a-zA-Z0-9]*A[^a-zA-Z0-9]*R[^a-zA-Z0-9]*C[^a-zA-Z0-9]*H[^a-zA-Z0-9]*' As I said, it worked fine until I upgraded. Is it to do with the double parsing MySQL does with REGEXP's? In some instances, I get emtpy result set where I used to get a match, and in other instances, I get a 'repetition-operator operand invalid'. Is there a simple way I can prevent errors from a search string that contains ^*$ or other sensitive expression characters? addslashes() in php maybe? Any help is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pessimistic Record Locking
Thanks for your help, that's going to work great! sk On 5/2/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 05:39:36 PM: Thanks Shawn, that helps a lot. I like your general idea of handling it at application level. I guess my main concern w/ web apps and locking the record (even at app level) is orphaned locks, resulting from browser closes or other events that don't go through the normal channels of updating or cancelling the update. So, if you set a lock to have a timeout of say 15 minutes, and the user WAS simply taking longer than that 15 minutes, how do you handle that when they try and save it? I was thinking, just go ahead with the commit if the record hasn't been locked in the meantime by another user, otherwise, prompt them saying it's now been locked by another user and your changes won't be saved. But this is the same user inconvenience caused by optomistic locking. That's basically what I do. Here's my general logic. It may or may not fit your situation. If userA has a record locked and that lock has that timed out but userA still had the page open for editing, userB could follow along and change the same record (it appears to be unlocked because userA took too long to save their changes). If userA finally decides to save their changes, the application code detects that they no longer have a lock on the record (either because userB has the record locked or because the lock was cleared when userB released it) and offers userA two choices: restart the edit from the current state or abandon their changes. Either way, you avoid changing userB's update unintentionally. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thanks, Scott. On 4/29/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 02:28:25 PM: Hello, I'm using MySQL 5.0.3 Beta, and I'm hoping for some general tips/experience you guys may have had regarding optomistic vs pessimistic locking in a web app (PHP 5.0.3) I'm more of a windows programmer, and I've always implemented pessimistic over optomistic as it's much more professional and attractive to the end user. The problem as you know, is that web development makes pessimistic locking much more difficult, because of the user closing the browser, and a bunch of other factors I can't control. Question: which type of locking do you usually implement in your web apps, and do you do it at a DB level or in your application layer? Any thoughts on a custom locking scheme (ie, a lock table that is written to with a user id and record id and timestamp)? Other solutions/suggestions are greatly appreciated. Thanks in advance. Scott. I don't use record locking until it's time to actually do something to the data. Even then it depends on what I am doing. As Mathias already suggested, InnoDB works better for this because it can lock individual rows and has full transactional support. In my webapp the users do mostly reads (which do not require locks) and few edits. In order to prevent another user from editing the same record that someone else is already editing, I have added a field to those table that require concurrency checking and fill in that field with the application-login of the user requesting to edit the record. It looks something like this: UPDATE datatable SET mtxEditor = 'user_id' WHERE pkid = AND mtxEditor is null; SELECT mtxEditor FROM datatable WHERE pkid = ; If I get a match, then I allow the user to navigate to the edit web page, otherwise they get the view web page and a popup saying that therecord is already being edited by insert name here. That way I don't have 2 users trying to make concurrent changes and the second or later users are told who has that record open so they can check with that person to see if they are done or if they just forgot to save their changes. Now, if the user exits the page manually (the page gets the onunload event) or decides to cancel their edit, I request a page that cleares themtxEditor field. That works something like this: UPDATE datatable SET mtxEditor = null WHERE pkid = AND mtxEditor ='userid'; When it comes time to apply the effects of the edit, I check the mtxEditor field one more time to make sure that nobody has hijacked the page or that the user didn't navigate away (causing their edit lock to go away) and come back (a user can't update the record unless they are the one editing it). It's not perfect but it works remarkably well for the application-level locking I need to provide. One enhancement to this would
Pessimistic Record Locking
Hello, I'm using MySQL 5.0.3 Beta, and I'm hoping for some general tips/experience you guys may have had regarding optomistic vs pessimistic locking in a web app (PHP 5.0.3) I'm more of a windows programmer, and I've always implemented pessimistic over optomistic as it's much more professional and attractive to the end user. The problem as you know, is that web development makes pessimistic locking much more difficult, because of the user closing the browser, and a bunch of other factors I can't control. Question: which type of locking do you usually implement in your web apps, and do you do it at a DB level or in your application layer? Any thoughts on a custom locking scheme (ie, a lock table that is written to with a user id and record id and timestamp)? Other solutions/suggestions are greatly appreciated. Thanks in advance. Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pessimistic Record Locking
Thanks Shawn, that helps a lot. I like your general idea of handling it at application level. I guess my main concern w/ web apps and locking the record (even at app level) is orphaned locks, resulting from browser closes or other events that don't go through the normal channels of updating or cancelling the update. So, if you set a lock to have a timeout of say 15 minutes, and the user WAS simply taking longer than that 15 minutes, how do you handle that when they try and save it? I was thinking, just go ahead with the commit if the record hasn't been locked in the meantime by another user, otherwise, prompt them saying it's now been locked by another user and your changes won't be saved. But this is the same user inconvenience caused by optomistic locking. Thanks, Scott. On 4/29/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 02:28:25 PM: Hello, I'm using MySQL 5.0.3 Beta, and I'm hoping for some general tips/experience you guys may have had regarding optomistic vs pessimistic locking in a web app (PHP 5.0.3) I'm more of a windows programmer, and I've always implemented pessimistic over optomistic as it's much more professional and attractive to the end user. The problem as you know, is that web development makes pessimistic locking much more difficult, because of the user closing the browser, and a bunch of other factors I can't control. Question: which type of locking do you usually implement in your web apps, and do you do it at a DB level or in your application layer? Any thoughts on a custom locking scheme (ie, a lock table that is written to with a user id and record id and timestamp)? Other solutions/suggestions are greatly appreciated. Thanks in advance. Scott. I don't use record locking until it's time to actually do something to the data. Even then it depends on what I am doing. As Mathias already suggested, InnoDB works better for this because it can lock individual rows and has full transactional support. In my webapp the users do mostly reads (which do not require locks) and few edits. In order to prevent another user from editing the same record that someone else is already editing, I have added a field to those table that require concurrency checking and fill in that field with the application-login of the user requesting to edit the record. It looks something like this: UPDATE datatable SET mtxEditor = 'user_id' WHERE pkid = AND mtxEditor is null; SELECT mtxEditor FROM datatable WHERE pkid = ; If I get a match, then I allow the user to navigate to the edit web page, otherwise they get the view web page and a popup saying that the record is already being edited by insert name here. That way I don't have 2 users trying to make concurrent changes and the second or later users are told who has that record open so they can check with that person to see if they are done or if they just forgot to save their changes. Now, if the user exits the page manually (the page gets the onunload event) or decides to cancel their edit, I request a page that cleares the mtxEditor field. That works something like this: UPDATE datatable SET mtxEditor = null WHERE pkid = AND mtxEditor ='userid'; When it comes time to apply the effects of the edit, I check the mtxEditor field one more time to make sure that nobody has hijacked the page or that the user didn't navigate away (causing their edit lock to go away) and come back (a user can't update the record unless they are the one editing it). It's not perfect but it works remarkably well for the application-level locking I need to provide. One enhancement to this would be to provide a sunset timer. When a user is assigned as the editor set a datetimefield to 10 or 20 minutes from NOW(). If the user hasn't submitted their updates by then, they have to re-request to edit the page. That way, in case someone manages to leave the page without tripping the onunload event (which would trigger the reset of the mtxEditor field) you still have a way of unlocking the record for the next user. BTW, I use Hungarian notation only if a field is used more like a variable than the other data-related fields. In this case the 'mtx' is my shorthand for 'mutex' because that field is acting as a mutual exclusion flag to be read by the application. The name of the application user editing a record adds no useful value to the rest of the data stored on the table (if I listed the properties of the object being stored in the table, the editing user isn't one of them. Am I making sense?). It's generally a BAD idea to lock any rows for longer than what is absolutely necessary to complete the transaction. That means you should not lock a row (at the database level) and wait for a user to update it before you release the lock. To do that completely ruins
Re: Pessimistic Record Locking
I could set the user's session timeout to be the same duration as the record lock timeout...that way, in any event where the user's lock would have expired, he would have to log back into the system anyway...but this may be inconvenient as well, as I know a lot of user's could be idle for some time, and would be annoyed if they had to log back in every time... On 4/29/05, Scott Klarenbach [EMAIL PROTECTED] wrote: Thanks Shawn, that helps a lot. I like your general idea of handling it at application level. I guess my main concern w/ web apps and locking the record (even at app level) is orphaned locks, resulting from browser closes or other events that don't go through the normal channels of updating or cancelling the update. So, if you set a lock to have a timeout of say 15 minutes, and the user WAS simply taking longer than that 15 minutes, how do you handle that when they try and save it? I was thinking, just go ahead with the commit if the record hasn't been locked in the meantime by another user, otherwise, prompt them saying it's now been locked by another user and your changes won't be saved. But this is the same user inconvenience caused by optomistic locking. Thanks, Scott. On 4/29/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 02:28:25 PM: Hello, I'm using MySQL 5.0.3 Beta, and I'm hoping for some general tips/experience you guys may have had regarding optomistic vs pessimistic locking in a web app (PHP 5.0.3) I'm more of a windows programmer, and I've always implemented pessimistic over optomistic as it's much more professional and attractive to the end user. The problem as you know, is that web development makes pessimistic locking much more difficult, because of the user closing the browser, and a bunch of other factors I can't control. Question: which type of locking do you usually implement in your web apps, and do you do it at a DB level or in your application layer? Any thoughts on a custom locking scheme (ie, a lock table that is written to with a user id and record id and timestamp)? Other solutions/suggestions are greatly appreciated. Thanks in advance. Scott. I don't use record locking until it's time to actually do something to the data. Even then it depends on what I am doing. As Mathias already suggested, InnoDB works better for this because it can lock individual rows and has full transactional support. In my webapp the users do mostly reads (which do not require locks) and few edits. In order to prevent another user from editing the same record that someone else is already editing, I have added a field to those table that require concurrency checking and fill in that field with the application-login of the user requesting to edit the record. It looks something like this: UPDATE datatable SET mtxEditor = 'user_id' WHERE pkid = AND mtxEditor is null; SELECT mtxEditor FROM datatable WHERE pkid = ; If I get a match, then I allow the user to navigate to the edit web page, otherwise they get the view web page and a popup saying that the record is already being edited by insert name here. That way I don't have 2 users trying to make concurrent changes and the second or later users are told who has that record open so they can check with that person to see if they are done or if they just forgot to save their changes. Now, if the user exits the page manually (the page gets the onunload event) or decides to cancel their edit, I request a page that cleares the mtxEditor field. That works something like this: UPDATE datatable SET mtxEditor = null WHERE pkid = AND mtxEditor ='userid'; When it comes time to apply the effects of the edit, I check the mtxEditor field one more time to make sure that nobody has hijacked the page or that the user didn't navigate away (causing their edit lock to go away) and come back (a user can't update the record unless they are the one editing it). It's not perfect but it works remarkably well for the application-level locking I need to provide. One enhancement to this would be to provide a sunset timer. When a user is assigned as the editor set a datetimefield to 10 or 20 minutes from NOW(). If the user hasn't submitted their updates by then, they have to re-request to edit the page. That way, in case someone manages to leave the page without tripping the onunload event (which would trigger the reset of the mtxEditor field) you still have a way of unlocking the record for the next user. BTW, I use Hungarian notation only if a field is used more like a variable than the other data-related fields. In this case the 'mtx' is my shorthand for 'mutex' because that field is acting as a mutual exclusion flag to be read by the application. The name of the application user
Re: create database+tables
Do this. Save the create tables commands in a textfile on your harddrive called 'C:\tables.sql' Load mysql from the command line. @ the prompt, type the following mysqlcreate database `myDBName`; mysqluse `myDBName`; mysqlsource C:\tables.sql; that's it! On Apr 2, 2005 10:26 AM, Niki Lampropoulou [EMAIL PROTECTED] wrote: better description instructions to be followed for installation of ALICE PHP chatbot. It is the first time I am using MySQL 2. Create a database for the program to use in MySQL. 3. Create the tables in the new database using db.sql which is in the sql directory. tables # # Table structure for table `bot` # CREATE TABLE bot ( id int(11) NOT NULL auto_increment, bot tinyint(4) NOT NULL default '0', name varchar(255) NOT NULL default '', value text NOT NULL, PRIMARY KEY (id), KEY botname (bot,name) ) TYPE=MyISAM; # # # Table structure for table `bots` # CREATE TABLE bots ( id tinyint(3) unsigned NOT NULL auto_increment, botname varchar(255) NOT NULL default '', PRIMARY KEY (botname), KEY id (id) ) TYPE=MyISAM; # # # Table structure for table `conversationlog` # CREATE TABLE conversationlog ( bot tinyint(3) unsigned NOT NULL default '0', id int(11) NOT NULL auto_increment, input text, response text, uid varchar(255) default NULL, enteredtime timestamp(14) NOT NULL, PRIMARY KEY (id), KEY botid (bot) ) TYPE=MyISAM; # # # Table structure for table `dstore` # CREATE TABLE dstore ( uid varchar(255) default NULL, name text, value text, enteredtime timestamp(14) NOT NULL, id int(11) NOT NULL auto_increment, PRIMARY KEY (id), KEY nameidx (name(40)) ) TYPE=MyISAM; # # # Table structure for table `gmcache` # CREATE TABLE gmcache ( id int(11) NOT NULL auto_increment, bot tinyint(3) unsigned NOT NULL default '0', template int(11) NOT NULL default '0', inputstarvals text, thatstarvals text, topicstarvals text, patternmatched text, inputmatched text, combined text NOT NULL, PRIMARY KEY (id), KEY combined (bot,combined(255)) ) TYPE=MyISAM; # # # Table structure for table `gossip` # CREATE TABLE gossip ( bot tinyint(3) unsigned NOT NULL default '0', gossip text, id int(11) NOT NULL auto_increment, PRIMARY KEY (id), KEY botidx (bot) ) TYPE=MyISAM; # # # Table structure for table `patterns` # CREATE TABLE patterns ( bot tinyint(3) unsigned NOT NULL default '0', id int(11) NOT NULL auto_increment, word varchar(255) default NULL, ordera tinyint(4) NOT NULL default '0', parent int(11) NOT NULL default '0', isend tinyint(4) NOT NULL default '0', PRIMARY KEY (id), KEY wordparent (parent,word), KEY botid (bot) ) TYPE=MyISAM; # # # Table structure for table `templates` # CREATE TABLE templates ( bot tinyint(3) unsigned NOT NULL default '0', id int(11) NOT NULL default '0', template text NOT NULL, pattern varchar(255) default NULL, that varchar(255) default NULL, topic varchar(255) default NULL, PRIMARY KEY (id), KEY bot (id) ) TYPE=MyISAM; # # # Table structure for table `thatindex` # CREATE TABLE thatindex ( uid varchar(255) default NULL, enteredtime timestamp(14) NOT NULL, id int(11) NOT NULL auto_increment, PRIMARY KEY (id) ) TYPE=MyISAM; # # # Table structure for table `thatstack` # CREATE TABLE thatstack ( thatid int(11) NOT NULL default '0', id int(11) NOT NULL auto_increment, value varchar(255) default NULL, enteredtime timestamp(14) NOT NULL, PRIMARY KEY (id) ) TYPE=MyISAM; Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General Table Locking Question
I've got a good deal of experience using mysql, but never in a large production environment with many concurrent users. Using the InnoDB engine, what is the general practice for ensuring data integrity when multiple users are writing to the same table? Should I explicitly lock the table before I write to it, or does mysql do this automatically? Thanks, sk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Infinity as field value
Is there a way to represent infinity in mysql? I've got a range field in my GUI, which is x... if the user chooses this field, in the DB, I store it as: id | from | to | other 2 | x | infinity | etc... this is because there are situations of x and between x AND y, so from and to is the easiest way to store it... I could make infinity default to 100,000,000 or some other number I know will never be reached, but it seems less elegant a solution... thanx, Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding fields to db table (primary key and other type)
http://dev.mysql.com/doc/mysql/en/alter-table.html ALTER TABLE dtd_test ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id); On Wed, 16 Mar 2005 14:56:59 -0500, Ed [EMAIL PROTECTED] wrote: Hi all, I am using MySQL Command Line and have created a table called dtd_test. It has two varchar fields at the moment. How can I add more fields? I want to add a primary key column which autoincrements, how can I do that? Thanks a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Adding fields to db table (primary key and other type)
See my original post: ALTER TABLE dtd_test ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id); Primary key could've just as easily been another column. You have to include another ADD command after the comma. On Wed, 16 Mar 2005 15:25:14 -0800, Scott Klarenbach [EMAIL PROTECTED] wrote: See my original post: ALTER TABLE dtd_test ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id); Primary key could've just as easily been another column. You have to include another ADD command after the comma. On Wed, 16 Mar 2005 18:13:54 -0500, Ed [EMAIL PROTECTED] wrote: Thanks for the replies, works fine, I checked out the alter table syntax and added a new field. How can you add two new fields I tried with ALTER TABLE DTD_Test add template_header varchar(255), template_footer varchar(255); but i get an error. Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two columns query from a single column table?
SELECT id, id FROM data should work just fine. On Tue, 15 Mar 2005 17:43:29 -0600, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi there! I need to do this: From this table +--+ |id|Data | |--|---| | 1|Something 1| | 2|Something 2| | 3|Something 3| | 4|Something 4| | 5|Something 5| | 6|Something 6| +--+ Get this query +-+ |id|Data |id|Data | |--|---|--|---| | 1|Something 1| 4|Something 4| | 2|Something 2| 5|Something 5| | 3|Something 3| 6|Something 6| +-+ Any idea? TIA Servicio de Correo Unidad Central - CETI - http://www.ceti.mx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two columns query from a single column table?
Oh, sorry, I didn't look close enough at your question...never mind :-) On Tue, 15 Mar 2005 16:02:59 -0800, Scott Klarenbach [EMAIL PROTECTED] wrote: SELECT id, id FROM data should work just fine. On Tue, 15 Mar 2005 17:43:29 -0600, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi there! I need to do this: From this table +--+ |id|Data | |--|---| | 1|Something 1| | 2|Something 2| | 3|Something 3| | 4|Something 4| | 5|Something 5| | 6|Something 6| +--+ Get this query +-+ |id|Data |id|Data | |--|---|--|---| | 1|Something 1| 4|Something 4| | 2|Something 2| 5|Something 5| | 3|Something 3| 6|Something 6| +-+ Any idea? TIA Servicio de Correo Unidad Central - CETI - http://www.ceti.mx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two columns query from a single column table?
SELECT (SELECT id from data WHERE id=1), (SELECT id from data WHERE id=4); This willl return you the 2 columns in one row. Otherwise, if you're looking to return multiple queries into one result set, then UNION is what you're looking for. ie (select id from data) UNION (select id from data). On Tue, 15 Mar 2005 17:43:29 -0600, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi there! I need to do this: From this table +--+ |id|Data | |--|---| | 1|Something 1| | 2|Something 2| | 3|Something 3| | 4|Something 4| | 5|Something 5| | 6|Something 6| +--+ Get this query +-+ |id|Data |id|Data | |--|---|--|---| | 1|Something 1| 4|Something 4| | 2|Something 2| 5|Something 5| | 3|Something 3| 6|Something 6| +-+ Any idea? TIA Servicio de Correo Unidad Central - CETI - http://www.ceti.mx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-Table Query Problem...
What are the results? sk On Mon, 14 Mar 2005 14:22:38 -0800, Nick Zukin [EMAIL PROTECTED] wrote: I'm trying to do a multitable query and am having problems. I have three tables: vendors, products, and vendorproducts. The vendorproducts table creates a many to many relationship between the vendors and the products. There is nothing more than the vendor and product ids in the vendorproducts table. I want to be able to create a query that will find vendors who have certain products. However, I'm trying to make a keyword search (PHP/MySQL) so that using form data I can search multiple columns for the same keyword. Here's how I am currently doing the query: $query = SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid ; $query .= FROM vendorproducts AS vp ; $query .= INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid ; $query .= INNER JOIN products AS p ON vp.vpvendorid = p.productid ; $query .= WHERE (p.productname LIKE '%.$_GET['keyword'].%') ; $query .= OR (p.productfamily LIKE '%.$_GET['keyword'].%') ; $query .= OR (v.vcategory LIKE '%.$_GET['keyword'].%') ; $query .= GROUP BY v.vbusiness ; As an example, it might look like this: SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid FROM vendorproducts AS vp INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid INNER JOIN products AS p ON vp.vpvendorid = p.productid WHERE (p.productname LIKE '%Apples%') OR (p.productfamily LIKE '%Apples%') OR (v.vcategory LIKE '%Apples%') GROUP BY v.vbusiness Where am I going wrong? The results aren't random, but I can't see how they're coming up with what they're coming up with. TIA, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-Table Query Problem...
Because, with the '%keyword%' operator, you're going to match any of those columns that contain the keyword inside of it. This can be a little confusing as 'ef' will return true on 'abcdefghijk'? Instead, you might try 'keyword%' so that 'apple' returns true for 'apples', 'apple juice', 'apple cider', BUT returns false for 'ple'. But, without seeing the results and what you'd hoped them to be, it's tough to narrow down the problem. sk On Mon, 14 Mar 2005 15:08:28 -0800, Scott Klarenbach [EMAIL PROTECTED] wrote: What are the results? sk On Mon, 14 Mar 2005 14:22:38 -0800, Nick Zukin [EMAIL PROTECTED] wrote: I'm trying to do a multitable query and am having problems. I have three tables: vendors, products, and vendorproducts. The vendorproducts table creates a many to many relationship between the vendors and the products. There is nothing more than the vendor and product ids in the vendorproducts table. I want to be able to create a query that will find vendors who have certain products. However, I'm trying to make a keyword search (PHP/MySQL) so that using form data I can search multiple columns for the same keyword. Here's how I am currently doing the query: $query = SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid ; $query .= FROM vendorproducts AS vp ; $query .= INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid ; $query .= INNER JOIN products AS p ON vp.vpvendorid = p.productid ; $query .= WHERE (p.productname LIKE '%.$_GET['keyword'].%') ; $query .= OR (p.productfamily LIKE '%.$_GET['keyword'].%') ; $query .= OR (v.vcategory LIKE '%.$_GET['keyword'].%') ; $query .= GROUP BY v.vbusiness ; As an example, it might look like this: SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid FROM vendorproducts AS vp INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid INNER JOIN products AS p ON vp.vpvendorid = p.productid WHERE (p.productname LIKE '%Apples%') OR (p.productfamily LIKE '%Apples%') OR (v.vcategory LIKE '%Apples%') GROUP BY v.vbusiness Where am I going wrong? The results aren't random, but I can't see how they're coming up with what they're coming up with. TIA, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Column Order
ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext AFTER ColumnNameToPutAfter Note that long text is required (put in the correct column type you intend to move) Alternate: INSERT INTO new_table SELECT columns-in-new-order FROM old_table; DROP table old_table; ALTER TABLE new_table RENAME old_table; On Mon, 14 Mar 2005 23:54:56 +, shaun thornburgh [EMAIL PROTECTED] wrote: Hi, Is it possible to change the order of columns in a table after the table has been created? Thanks for your help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Column Order
Also: ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext BEFORE ColumnNameToPutBefore ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext FIRST ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext LAST will work, depending on what you're looking to do. sk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump alternative due to bug
Ya, all my tables are InnoDB unfortunately, and they need to stay that way ;-). On Sun, 13 Mar 2005 13:48:26 +0200, Amr Mostafa [EMAIL PROTECTED] wrote: There is the mysqlhotcopy, it's faster too. However, It will only work if all your tables are MyIsam. Scott Klarenbach wrote: I'm using Mysql 5.0.2 w/ Windows 2003 server, and there is a bug with the mysqldump utility. The bug is actually with the Describe table statement, or Show fields from table statement... It's been documented on mysql.com, so I'm wondering if there is a simple alternative to mysqldump that I can use to backup the database, until this bug is resolved in a future release? Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump alternative due to bug
Awesome, I guess that's the best alternative. In fact, I was logging on to ask you that very question. Thanks, Scott. On Sun, 13 Mar 2005 20:03:15 +0200, Amr Mostafa [EMAIL PROTECTED] wrote: You can copy mysql tables files directly from /var/lib/mysql/data directory (or wherever your path/to/mysql is :) For more details/information, read this : http://dev.mysql.com/doc/mysql/en/disaster-prevention.html - Amr Scott Klarenbach wrote: Ya, all my tables are InnoDB unfortunately, and they need to stay that way ;-). On Sun, 13 Mar 2005 13:48:26 +0200, Amr Mostafa [EMAIL PROTECTED] wrote: There is the mysqlhotcopy, it's faster too. However, It will only work if all your tables are MyIsam. Scott Klarenbach wrote: I'm using Mysql 5.0.2 w/ Windows 2003 server, and there is a bug with the mysqldump utility. The bug is actually with the Describe table statement, or Show fields from table statement... It's been documented on mysql.com, so I'm wondering if there is a simple alternative to mysqldump that I can use to backup the database, until this bug is resolved in a future release? Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqldump alternative due to bug
I'm using Mysql 5.0.2 w/ Windows 2003 server, and there is a bug with the mysqldump utility. The bug is actually with the Describe table statement, or Show fields from table statement... It's been documented on mysql.com, so I'm wondering if there is a simple alternative to mysqldump that I can use to backup the database, until this bug is resolved in a future release? Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing a big query...
That's incredibly slow. I pull 1000 records through PHP in 1 second on a P4 2.4 Ghz. Are you pulling the entire recordset and then limiting it in your app code? Or are you using a limit clause in the DB? Also, Peter's point about indexing might help. Without specific SQL examples, it's difficult to suggest optimizations. Mysql does have a great section in their manual re: optimizing select queries. Scott. On Wed, 9 Mar 2005 16:10:19 -0500, Peter J Milanese [EMAIL PROTECTED] wrote: Does the app display all 1000 rows at once? Does your app require all fields? Only retrieve what you need for the page. If the app displays all 1000 rows, it may remain slow depending on how you get them (order, group, function) and indexing.. Also, the link and disk may matter depending on the size of the row. I do not know what you could do on the client side to help. --Original Message-- From: Carlos Savoretti To: MySQL List Sent: Mar 9, 2005 5:49 PM Subject: Optimizing a big query... Hi all: I programming a GUI which retrieve big tables oftenly. So, I retrieve chunks of 1000 rows and paginate then to browse the entire table. It works fine, but it's rather slow. I would like to know if I could set some option thru mysql_option() to optimize the client side (mysql-client-3.23.58) and what is the the recommended value to clamp the `page' for a gui app. (For 1000 rows it uses about 12 seconds). Thanks a lot... -- Carlos Savoretti [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Sent from my NYPL BlackBerry Handheld. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auto Escape characters
Is there a flag in MYSQL to automatically escape special characters like single quotes with a backslash? Instead of using a C API or PHP addslashes() funciton for each field I'd need to escape? Thanks, Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto Escape characters
Haha! Great point. I guess it's time for me to call it a day and get some sleep :-). Thanks, Scott. On Tue, 08 Mar 2005 18:23:24 -0500, Keith Ivey [EMAIL PROTECTED] wrote: Scott Klarenbach wrote: Is there a flag in MYSQL to automatically escape special characters like single quotes with a backslash? Instead of using a C API or PHP addslashes() funciton for each field I'd need to escape? I don't think you've thought that through completely. How would MySQL know which quotes you intended to escape? If what you're asking for were possible, there'd be no need for escaping in the first place. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
Gary's got another point about the transactions. I'd still look to using mysqldump first if possible, if they have the ability it will be remarkably faster. Otherwise, turning transactions off before the insert, and locking the table as well, (if you haven't already done that) could prove to save you a lot of time. Scott. On Tue, 1 Mar 2005 15:32:54 -0800, Gary Richardson [EMAIL PROTECTED] wrote: What have you actually done to 'tune' the server? How are you doing the inserts? InnoDB uses transactions. If you are doing each row as a single transaction (the default), it would probably take a lot longer. I assume you're doing your copying as a INSERT INTO $new_table SELECT * FROM $old_table. Try wrapping that in a BEGIN; INSERT INTO $new_table SELECT * FROM $old_table; COMMIT; How do you have your table space configured? Just some random thoughts.. On Tue, 1 Mar 2005 17:24:32 -0600, Alfredo Cole [EMAIL PROTECTED] wrote: Hi: I have switched from MyISAM tables to InnoDB, using MySQL 4.1.10 under SuSE 8.2. My application, an ERP system developed in-house, uses 70 tables, the largest one holding a little over one million rows. To assist when changing table structures, we developed a software that creates a new table for each of the 70 tables, one at a time, using the new structure, copies all of the records from the old table to the new one, drops the old one and renames the new one. Using MyISAM tables, this process takes 10 minutes using a two Xeon 2.4 Ghz server, with 4 Gb RAM and SCSI RAID 5 disks. The same system takes 2 1/2 hours using InnoDB tables with the same configuration. We have followed the guidelines for tuning the server, and still, we find this to be excessive. Can somebody point to some docs, guidelines or web sites we can consult to improve InnoDB's performance? It seems inserting many rows decreases performance significantly. Thank you and regards. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regular Expression Query
I have a client that wants to search table fields for strings, and ignore any-non alphanumeric character in the field. (match only the alphanumeric portion of the field, and discard the rest) for example, a search for apple would return true on the following record a**__-p p + l ^^ @e I have some experience with Regular expressions, but I'm stumped on this one. Any help is appreciated. Thanks, Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIMIT clause as Stored Procedure Parameter
I can't seem to pass in the LIMIT clause variables into a stored procedure i.e. Select * from table limit param1, param2; I get a syntax error compiling the procedure, but when I replace param1 and param2 with hard coded ints, ie, 10, 10, it compiles and works fine. Is this functionality not available? I had a similar problem w/ the Load Data Infile statement, in that it wouldn't allow me to pass in the file name as a parameter to the SPROC. If the LIMIT clause doesn't work this way either, I will really regret building the system using SPROCS, as most of the complicated queries will have to be hard-coded in PHP to allow variable passing on the query line, err! I hope I'm missing something. Thanks a lot for any help you can provide. Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Load Data Infile in Stored procedure
I can't seem to make the Load Data statement work inside of a stored procedure. ie LOAD DATA LOCAL INFILE file.txt INTO my_table this works fine in PHP, but when I use it in a procedure, and pass in the file name as a parameter, it won't compile. LOAD DATA LOCAL INFILE fileParameter INTO my_table I think it's because the statement requires the file to be in quotes, but if I concatenate the parameter into quotes, then it looks for the EXACT variable string, not the file. ie LOAD DATA LOCAL INFILE ' +fileParam+ ' INTO my_table --this returns an error saying it can't find the file fileParam :-). I also tried creating a prepared statement and then executing it USING my file parameters, but no luck either. Thanks. Any help is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
