What to do if a table is supposed to get > 3GB data per day?

Hi, Would you like to express your opinion as to what design strategy to take if a table (used for read operations only) is supposed to get more than 3GB of data per day? With 1000 simultaneous users ? -- Thanks in advance, Asif

Re: float numbers

Hi Ahmad, On 11/18/06, Ahmad Al-Twaijiry <[EMAIL PROTECTED]> wrote: I have many tables that use float (in production database), if I convert all of the float column to DOUBLE or DECIMAL (using alter ), is there any impact or anything I should know that could happen to me ? You'll get a slight

Re: Deadlock

Hi Ahmad, On 11/13/06, Ahmad Al-Twaijiry <[EMAIL PROTECTED]> wrote: also I want to mention that I have 3 primary key in my table: ShopID CustomerID OrderID Could this be the problem ? Yes, may be. Cause I am sure I have read somewhere in the docs that having multiple cols in the primary key o

Re: Inline View

Hi Melissa, What's wrong with phrasing your query as : select distinct i.reportid, ivalue as IPAddress, ivalue as Computer From item where reportid=1; /* LIMIT 3 */ -- or something like this if you do want 3 rows anway. Am I putting LIMIT right? -- Asif On 10/19/06, Melissa Dougherty <[EMAI

Re: How to get into mysql command line?

Hi Cornelia, On 10/16/06, Cornelia Menzel <[EMAIL PROTECTED]> wrote: Anyhow, if somebody has a glue of what was or could have been the problem, I am interested to know what it was. What really annoys me, is that I have not found what it was. Though I have used only 5.x versions but I got simil

USE_INDEX

Hi, I remember USE_INDEX optimization hint in Oracle and I have seen something like this on this list as well but I can't seem to look it up in the online documentation or the mysql manual sitting on my hard disk. Can anybody give me pointers as to where I can get documentation on this function/

Avoiding multi-col indexes & increasing speed inspite of fully-enforced constraints on a fully-normalized db

Hi, I have a multi-column index (TrnsxType, TrnsxDate, TrnsxID, DepartID). This index along with a multi-column index of some child tables results in 8-column indexes (TrnsxType, TrnsxDate, TrnsxID, DepartID, OrderType, OrderDate, OrderNo, DepartmentID), etc. I cannot eliminate Department ID be

Re: Security settings won't take during installation

Hi Adrian, On 8/25/06, Adrian Greeman <[EMAIL PROTECTED]> wrote: "The security settings could not be applied to the database because the .. I am pasting here the text of one of my earlier posts to this list: I got MySQL 5.0.22 running successful

Parameterized stored procedures via ADO Command Object

Hi, I have been trying very hard to get this working but I have NOT been able to call the stored procedures in my MySQL database using the ADO (**NOT** Ado.NET) Command object. I want to use stored procedures 'cos I want to restrict access to stored procedures and views only. However, it seems l

Re: Strange MySQL behavior - 5.0.22/InnoDB Tables/WinXP-SP2

about the absence of the Group By clause any more. This is __very__ strange behavior. Can anyone tell what's wrong? This is driving me nuts now! -- Thanks in advance, Asif On 8/18/06, Asif Lodhi <[EMAIL PROTECTED]> wrote: I posted the following code in one or two of my earlie

Strange MySQL behavior - 5.0.22/InnoDB Tables/WinXP-SP2

Hi, I posted the following code in one or two of my earlier posts and _then_ it WAS working! I know there is no GROUP BY clause but IT WAS WORKING somehow - the procedure ran fine and inserted quite a good few records. However, NOW, after I have dropped and re-created the database/tables/all th

ddmmyyyy-format date hangs MySQL - 5.0.22/InnoDB/WinXP-SP2

Hi, In my earlier post, I was making a mistake (though I didn't do so in the posted text!) - I was passing the dob (the date field) in the ddmm format. When I passed the date field in mmdd format, the stored procedure ran fine and the record got inserted. The problem is MySQL hangs/goes

Re: MySQL - HHHHangs when a stored procedure is called - 5.0.22/InnoDB/WinXP-SP2

stored procedure, MySQL hangs. Any help? -- Thanks in advance, Asif On 8/16/06, Asif Lodhi <[EMAIL PROTECTED]> wrote: Hi, I have the following database objects in a purely InnoDB da

MySQL - HHHHangs when a stored procedure is called - 5.0.22/InnoDB/WinXP-SP2

Hi, I have the following database objects in a purely InnoDB database: -- CREATE TABLE Person ( PersonIDint not null PRIMARY KEY,

Re: Get a Numeric Zero instead of NULL in SELECT

an speed it up? Any suggestions? -- Asif On 8/10/06, Asif Lodhi <[EMAIL PROTECTED]> wrote: I have a query: insert into tmp2 (x) select ((t3.m * 100)+b.id ) as x2 /* I AM USING CASE WHEN cond .. here */ fromtmp3 t3 LEFT JOIN (select ((max(x)) - ((max(x)

Get a Numeric Zero instead of NULL in SELECT

Hi, I have a query: insert into tmp2 (x) select ((t3.m * 100)+b.id ) as x2 fromtmp3 t3 LEFT JOIN (select ((max(x)) - ((max(x) div 100) * 100))+1 as pid from tmp2 where ((x div 100)=2147)

Insert ... Select Max() .. transactional Query optimization on an InnoDB table

Hi, Using MySQL-5.0.22/Win-XP-SP2 and storing data in InnoDB tables. Clients connect from VB6. Enclosing all transactions in "START TRANSACTION ... COMMIT" statements from VB6 clients. I have the following query for a table tmp2 with a column x of data-type INT. Insert into tmp2(x,y,x) Se

Re: SSH Server for MySQL-5.0.22 Server on WinXP-SP2

Hi Alan and others, On 8/7/06, Alan Vinh <[EMAIL PROTECTED]> wrote: I would like to know how they got MySql to work with SSL... :) Sorry fellows. I should have gone through the docs first. As it happens, My

Expressions, SQL Aggregate functions & SELECT Performance - index usage

Hi, I am having to write queries that contain operators such as MOD and DIV in addition to other normal arithmetic operators to add, multiply, divide, etc. I am also using MAX(), MIN() in the same queries. Actually, I am using INSERT ... SELECT MAX() * X ... FROM TABLE idiom to calculate the max

How to set "User" variables (not system/session ones) at database startup

Hi, I want to set certain _user_ variables, which I will create myself, at the database startup time so that all clients can use the values stored [only one time at the beginning] in those variables instead of computing the same each time in triggers or other code. It's just like the package-lev

Code snippet to run at database service startup

Hi, What if I want certain variables initialized and certain code to run at the database service startup? I am running 5.0.22 on Windows XP Service Pack 2. Are there any tirggers that can tell me that the database is starting up? -- Thanks in advance, Asif -- MySQL General Mailing List For l

Re: Can't get v5.0.22 to work;alternatives?

Hi, Earlier, I forgot to CCed the reply to the list and, accidentally, posted a blank reply instead. So, here is the copy of the reply that I sent to the original poster - in case, someone else has the same problem. Additionally, I have found that the MySQL ODBC driver installs best when you do

MySQL+OpenVPN

Hi, Can anyone on this list tell me _how_ i can connect to my MySQL-5.0.22 server running on Windows-XP-SP2 using OpenVPN on Windows? It looks like a good VPN/SSL implementation. -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubs

Re: delimiter in mysql client

Hi, I am replying back to my own post. Sorry, I forgot to mention that I am using mysql client on WinXP Pro SP2 and MySQL-5.0.22 is installed on the same machine. Any hints as to why mysql client is showing this behavior? Thanks in advance, -- Asif On 8/4/06, Asif Lodhi <[EMAIL PROTEC

Re: Can't get v5.0.22 to work;alternatives?

On 8/1/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I filed bug #20941 (mysqld seg faults during instance configuration on XP pro, http://bugs.mysql.com/?id=20941) some time ago and have seen no real movement on fixing it. I'm dead in the water. I can't get MySQL v5.0 to work on my system.

ROLLBACK/COMMIT in Stored Procedures

Hi, In my stored procedures, i want to ROLLBACK when I encounter any invalid values. However, as it happens, I cannot because MySQL does not support COMMIT/ROLLBACK functionality right now (as of ver. 5.0.22 on WinXP Pro). I am setting session variables (Set @XX="Error Message') according ot ea

delimiter in mysql client

Hi, When I create scripts, I change the delimiter at the beginning of the create_table script from ";" (without quotes) to "//". At the end of the script, I change the delimiter back to the original ";". This is according to what is described in doc at www.mysql.com. However, when I run these

Re: Database Return Errors

Thanks for replying, John. On 8/2/06, John Meyer <[EMAIL PROTECTED]> wrote: Have you checked out MyConnector/NET and the MySqlException class? From: Asif Lodhi [mailto:[EMAIL PROTECTED] Subject: Database Return Errors The question is: Can I get the error-codes or error-names that MySQL r

Re: Monitoring Slow Queries

Thanks, Philip. On 8/2/06, Philip Hallstrom <[EMAIL PROTECTED]> wrote: .. http://hackmysql.com/mysqlsla . That's definitely of immense help. -- Thanks a zillion, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscri

Database Return Errors

Hi, I am developing a VB6 app with a MySQL-5.0.22/WinXP backend. I have skimmed the Stored Procedures/Triggers docs and it looks like I can define custom error-names or number - though I have also seen the "Handlers" in the same doc. The question is: Can I get the error-codes or error-names th

Monitoring Slow Queries

Hi, Though I understand very well that it has been discussed lots of time before but I don't have time to browse through the previous archives and dig out the stuff I need. So, guys, I would be thankful if you could give me your valuable advice that I need right now. I just need to know

Returning Resultsets from Stored Routines

Hi All, I want to restrict all direct access to tables and allow users access only through the stored procedures. However, I haven't seen any explicit mention in the docs that one can return a resultset/recordset from a stored routine - that's what I can do using MS-SQL Server. If this cannot be

Re: is there a way to optimize like '%..%' searches ?

What about partitioning the database? On 7/1/06, Dan Buettner <[EMAIL PROTECTED]> wrote: Wow, that is a tough one. My question would be, how often is older data really accessed? Could you start incorporating a default date range like "past 3 months" or "past 1 year" into all searches, but al

multi-column indexes on InnoDB tables

Hi, I have created 4, 5 and 8 column unique indexes ( I had to - the application requirements dictated me to do so) on some tables in an InnoDB only database on a 5.0.22 MySQL server running on XP-SP2 machine. The 4-col indexes are on order, invoice and receipt tables; the 5-col indexes are on d

Re: QUERY

Hi John, On 6/24/06, John Hicks <[EMAIL PROTECTED]> wrote: Karl Larsen wrote: > ..I was glancing through the mamouth > MySQL reference manual ... I worked on an Oracle-9i development project around two years back. If I recall correct

CREATE TABLE Failure

Hi All, I have been repeatedly trying to create the following table - without __ANY__ success. Looks like there is REALLY something wrong with the MySQL engine or something! Here is the script: CREATE TABLE Order ( DID int not null, DeskNo int

InnoDB obeyance of PRIMARY KEY constraint - confirmation

Hi, I did read in the manual that the field level REFERENCES constraints on InnoDB tables do not work as expected and one has to first define a table level index and then create a table-level FOREIGN KEY constraint for the field to make it work. I just would like to know if that's the case with

SSL on MySQL 5.0.22

Hi, How can I connect from a Windows client (VB/ODBC Connector) to a MySQL Server 5.0.22, also running on Windows (Max version installed as a service using Local-Service Account) with all tables using InnoDB storage engine USING SSL? Perhaps using OpenSSL on a Linux-based installation would be e

SSL on MySQL 5.0.22

Hi, I am using 5.0.22 on Windows. As I read in the reference manual, almost "ALL" SSL values/options are specific to 5.1.x versions. Do I stand _ANY_ chance to use SSL anyway? Specifically, I want to know exactly HOW I can enable SSL support for MySQL on Windows using which options, variables,

Re: Version Numbers - Precedence

Hi, I am replying to my own post to clarify my query. Actually, I have 5.0.22 installed and I want to use the BIT data-type on InnoDB Engine. Can I do it or do I have to install 5.0.5 version (which is not stable) ? -- TIA Asif On 6/24/06, Asif Lodhi <[EMAIL PROTECTED]> wrot

Version Numbers - Precedence

Hi, Mathematically speaking, the 5.0.22 I am using came _before_ 5.0.5. Is that correct? Or 5.0.5 came first and then came 5.0.22? -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Embedded MySQL

Hi Chris and Melvin, Thanks a lot for the quick responses. I have found it. Actually, it comes bundled with the main distribution. The documentation is in the main reference manual and the header can be found in the include directory. -- Thanks again. Asif I don't see a binary version aval

Embedded MySQL

Hi All, Can some kind soul tell me from where I can download the "Embedded Library" version of MySQL? -- TIA, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]