Table # of rows changing?
I have table with 83,065 rows. Each time I go to MySQL Administrator and look at the catalogs, the number of Rows reported by the administrator changes. I can keep clicking refresh and the number of rows fluctuates between roughly 81,000 and 86,000. I also see similar behavior when I access the table through PHPMyAdmin in browse mode. The crazy part is, this is a static table. I am 100% positive the table is not being inserted to or deleted from. Here is the create statement for my table. Can anybody offer any insight? CREATE TABLE MyTable (Category varchar(100), Sub_Category varchar(100), Part_Number varchar(40), Description varchar(100), Service_Category varchar(10), Price integer, INDEX(Part_Number(10))) type=InnoDB; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table # of rows changing?
Thanks. Makes sense ... but strange. Jeff Brent Baisley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] It's because the table type is InnoDB. InnoDB tables can only give and estimate of how many rows are in the table. That's just the way InnoDB works. From the manual: SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization. On Nov 16, 2004, at 11:58 AM, Jeff Burgoon wrote: I have table with 83,065 rows. Each time I go to MySQL Administrator and look at the catalogs, the number of Rows reported by the administrator changes. I can keep clicking refresh and the number of rows fluctuates between roughly 81,000 and 86,000. I also see similar behavior when I access the table through PHPMyAdmin in browse mode. The crazy part is, this is a static table. I am 100% positive the table is not being inserted to or deleted from. Here is the create statement for my table. Can anybody offer any insight? CREATE TABLE MyTable (Category varchar(100), Sub_Category varchar(100), Part_Number varchar(40), Description varchar(100), Service_Category varchar(10), Price integer, INDEX(Part_Number(10))) type=InnoDB; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
Anybody? Jeff Burgoon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Sorry, I forgot to mention I am using version 4.0.20a (no subqueries supported) Jeff Burgoon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles Here is what I'd like to do (but can't because the current stable build of MySQL doesn't support subqueries) SELECT MyTable.* FROM (SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2) as Duplicates, MyTable WHERE Duplicates.Region = MyTable.Region Here is what I'm actually doing: CREATE TEMPORARY TABLE Duplicates SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2; SELECT MyTable.* FROM MyTable, Duplicates WHERE MyTable.Region = Duplicates.Region; Can anybody tell me if there is a more efficient way of doing this query? Thanks! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
Good one. I don't know how I missed this either! Thanks! gerald_clark [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] What about select distinct a.region, a.city from mytable a , mytable b where a.region=b.region and a.city b.city Jay Blanchard wrote: [snip] Anybody? I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles There is no good way to get this in a single query (w/o subqueries). Having applied all sorts of query mangling you would have to be able to carry forward some sort of count or variable in order to draw out the ones where the foreign key was 1. Grouping by the city does not work either as that reduces any count to a one for that record. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple SQL Question
I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles Here is what I'd like to do (but can't because the current stable build of MySQL doesn't support subqueries) SELECT MyTable.* FROM (SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2) as Duplicates, MyTable WHERE Duplicates.Region = MyTable.Region Here is what I'm actually doing: CREATE TEMPORARY TABLE Duplicates SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2; SELECT MyTable.* FROM MyTable, Duplicates WHERE MyTable.Region = Duplicates.Region; Can anybody tell me if there is a more efficient way of doing this query? Thanks! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
Sorry, I forgot to mention I am using version 4.0.20a (no subqueries supported) Jeff Burgoon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles Here is what I'd like to do (but can't because the current stable build of MySQL doesn't support subqueries) SELECT MyTable.* FROM (SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2) as Duplicates, MyTable WHERE Duplicates.Region = MyTable.Region Here is what I'm actually doing: CREATE TEMPORARY TABLE Duplicates SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2; SELECT MyTable.* FROM MyTable, Duplicates WHERE MyTable.Region = Duplicates.Region; Can anybody tell me if there is a more efficient way of doing this query? Thanks! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem
This will solve your problem and remove the need for the PHP correction. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, CASE WHEN ref_pts + adjust_ref_pts 15 THEN game_pts + 15 ELSE game_pts + ref_pts + adjust_ref_pts END AS total_pts, CASE WHEN ref_pts + adjust_ref_pts 15 THEN 15 ELSE ref_pts + adjust_ref_pts END AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC Albert Padley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Nothing? Not even a You're out of luck? Thanks. Albert On Oct 21, 2004, at 9:48 PM, Albert Padley wrote: I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- 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]
Backslash \ in query
Why does selecting backslash give me an error? select \ gives me... #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '\' at line 1 select / returns / select \\ returns \ What's the deal w/ backslash? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: **[SPAM]** Backslash \ in query
I'm populating a new table from excel sheets and for some silly reason several column values are populated with \ (not my doing!). I can easily fix the problem but I was just wondering. Thanks Everyone. Jay Blanchard [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] [snip] What's the deal w/ backslash? [/snip] It is an escape character. In order to get things with backslashes, as you have deduced, you SELECT \\ Do you have a column '\'? If so, why? Backslashes are often used in this way... SELECT businessName FROM table WHERE businessName = 'Doc\'s'; Because the apostrophe in Doc's would cause the field to be read as 'Doc', but escaping the apostrophe allows it to be searched properly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crosstab/Transpose Query Help
I'm trying to perform a query where I transpose cell values from a table into column names with totals. For example, I have the following table and I'd like to produce the view at the bottom: SitePartQuantity Site APart 15 Site APart 23 Site APart 31 Site BPart 17 Site BPart 310 Site CPart 22 Site CPart 45 I would like to take said view and produce a view as follows SitePart 1Part 2Part 3Part 4 Site A531 Site B7 10 Site C 25 As you can see, all Sites have been grouped and part names have been created as columns. Then part quantities are aggregated. Can anyone offer any guidance? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Batch Querying
I'm writing my first MySQL app in VB.net using myODBC. However I think this question applies to all languages using MySQL. From what I understand, I am unable to issue a batch statement of commands separated by commas to mySQL. I receive an error whenever I try to do so from my app. For this reason, I am unable to make use of SQL variables and temporary tables. I must instead use persistant tables. Can anyone tell me if this is in fact the case and if so, any suggestions on how to get over this hurdle? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Batch Querying
4.0.20a-max, just because I thought this was the most stable build. Victor Pendleton [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] What version of MySQL are you using? I believe multiple delimited statements are available in 4.1.x and later. Jeff Burgoon wrote: I'm writing my first MySQL app in VB.net using myODBC. However I think this question applies to all languages using MySQL. From what I understand, I am unable to issue a batch statement of commands separated by commas to mySQL. I receive an error whenever I try to do so from my app. For this reason, I am unable to make use of SQL variables and temporary tables. I must instead use persistant tables. Can anyone tell me if this is in fact the case and if so, any suggestions on how to get over this hurdle? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Batch Querying
Victor, I think I found what you're referring to: http://dev.mysql.com/doc/mysql/en/C_API_multiple_queries.html ... From version 4.1, MySQL supports the execution of multiple statements specified in a single query string. To use this capability with a given connection, you must specify the CLIENT_MULTI_STATEMENTS option in the flags parameter of mysql_real_connect() when opening the connection. You can also set this for an existing connection by calling mysql_set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_ON) Can anyone tell me if this functionality is implemented in the MyODBC? If so, can you tell me how you turn it on/off? Thanks again! Victor Pendleton [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] What version of MySQL are you using? I believe multiple delimited statements are available in 4.1.x and later. Jeff Burgoon wrote: I'm writing my first MySQL app in VB.net using myODBC. However I think this question applies to all languages using MySQL. From what I understand, I am unable to issue a batch statement of commands separated by commas to mySQL. I receive an error whenever I try to do so from my app. For this reason, I am unable to make use of SQL variables and temporary tables. I must instead use persistant tables. Can anyone tell me if this is in fact the case and if so, any suggestions on how to get over this hurdle? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Batch Querying
Hey. Thanks. First off, I meant to say semicolon (;) in my first post but I mistyped and said comma by accident. I am trying to use semicolon as my delimiter. This method works if I issue statements from PHPMyAdmin, but not through my ODBC app. However, your pseudocode made me realize something I didn't know was true. I had just assumed a temporary table would not persist between issued statements but they do if you have not closed the connection, as you stated. I don't know why I never tried this, but it works for me both with temporary tables and variables which is great. However, the syntax of your open statement still does not work for me. I get a SQL syntax error any time I try combining statements with Semicolons. Since I've been programming using SQL Server for a while now I feel like I SHOULD be able to issue commands in this way but somewhere I heard that mySQL prevents batch queries on purpose. [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] For multiple statements in one submission, have you tried using a semicolon ; not a comma , ? (http://dev.mysql.com/doc/mysql/en/Entering_queries.html) Each command may return a recordset of its own. Be prepared to either cycle through the returned recordsets or ignore them as they arrive. When you create a connection with a MySQL database, you establish an environment where variables and temporary tables can exist that is specifically yours. Just issue your commands in multiple statements WITHOUT CLOSING YOUR CONNECTION and you will be able to use those items. Here is some VB/ADO-like pseudocode to illustrate: set oConn = new Connection set oRS = new Recordset set oRSTimer = new Recordset oConn.open connection string, sUser, sPassword oConn.execute SET @Start_time = NOW() oConn.execute CREATE TEMPORARY TABLE tmpResults SELECT oRS.Open SELECT * FROM tmpResults where Col2='bluegills'; SET @End_Time=Now(), oConn, 0,1,1 oRStimer.Open [EMAIL PROTECTED], @end_time, oConn, 0,1,1 oConn.Execute DROP TABLE tmpResults oConn.Close What I did was open a connection, set a variable, create a temp table by populating it with the results of a SELECT query, get some records from that temp table AND set another variable, then finally get another record that contained the values of both temporary variables. After all that, I dropped my temporary table and closed the connection. I am nearly 100% certain that the combined statements in the oRS.Open... line will work for you. Let us know if it does or doesn't, OK?. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Burgoon [EMAIL PROTECTED] wrote on 09/21/2004 08:54:27 AM: I'm writing my first MySQL app in VB.net using myODBC. However I think this question applies to all languages using MySQL. From what I understand, I am unable to issue a batch statement of commands separated by commas to mySQL. I receive an error whenever I try to do so from my app. For this reason, I am unable to make use of SQL variables and temporary tables. I must instead use persistant tables. Can anyone tell me if this is in fact the case and if so, any suggestions on how to get over this hurdle? Thanks, Jeff -- 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]
