mysql

MySqlDataAdapter or MySqlDataReader for bulk transfer?

I'm using the MySql connector for .NET to copy data from MySql servers to SQL Server 2008. Has anyone experienced better performance using one of the following, versus the other? DataAdapter and calling Fill to a DataTable in chunks of 500 DataReader.Read to a DataTable in a loop of 500 I am then using SqlBulkCopy to load the 500 Da...

trouble connecting to MySql DB (PHP)

Hi I have the following PHP code to connect to my db. <?php ob_start(); $host="localhost"; // Host name $username="root"; // Mysql username $password=""; // Mysql password $db_name="test"; // Database name $tbl_name="members"; // Table name // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die...

querying huge database table takes too much of time in mysql

Hi all, I am running sql queries on a mysql db table that has 110Mn+ unique records for whole day. Problem: Whenever I run any query with "where" clause it takes at least 30-40 mins. Since I want to generate most of data on the next day, I need access to whole db table. Could you please guide me to optimize / restructure the deploymen...

Magento: Add (and retrieve) custom database field for CMS pages

I want to assign custom parameters to CMS pages in Magento (i.e. 'about', 'customer service', etc), so they can be grouped. The end goal is to use the parameters for each page to show (or hide) them in a nav menu. Writing a quick method in the page/html block to retrieve the pages (active only) for the menu was easy, but I can't figure...

Utilizing 5 physical servers in 1 cluster

Hi all, I have 5 physical servers with low end memory & cpu resources. I want to create 1 cluster using all these servers and want to run mysql db on the same such that mysql db would utilize 5 server's CPU power to execute db queries & same for memory. Could you please help me understanding how to achieve this? Regards, ...

MySQL where condition but not limited by it

Hello. I would like to run a query on my database like this: SELECT SUM( t1.value ) AS total1, SUM( t2.value ) AS total2, SUM( t3.value ) AS total3, SUM( t4.value ) AS total4 FROM pay1 t1, pay2 t2, pay3 t3, pay4 t4 WHERE t1.date = '2010-04-29' AND t2.date = '2010-04-29' AND t3.date = '2010-04-29' AND t4.date = '2010-04-29' I am genera...

Transmogrify into date from multiple columns

What is a better way to program the following SQL: str_to_date( concat(convert(D.DAY, CHAR(2)), '-', convert(M.MONTH, CHAR(2)), '-', convert(M.YEAR, CHAR(4))), '%e-%m-%Y' ) as AMOUNT_DATE I want to convert the columns D.DAY, M.MONTH, and M.YEAR to a date field using MySQL. The above works, but seems much more complicated t...

Dynamic sql vs stored procedures - pros and cons?

I have read many strong views (both for and against) SPs or DS. I am writing a query engine in C++ (mySQL backend for now, though I may decide to go with a C++ ORM). I cant decide whether to write a SP, or to dynamically creat the SQL and send the query to the db engine.# Any tips on how to decide? ...

Deleting partial data from a field in MySQL

I am trying to remove a specific set of data from a MySQL database field, however I am not sure what the best statement would be for this. For example, if I have a data in a field such as... The use of a secondary password will allow you to gain access to your account from a non-authenticated computer. A non-authenticated computer is ...

php session_start() error

Hi, i've used a sample found on online and applied it to my code: <?php session_start(); if (isset($_REQUEST["email"])) { $_SESSION["name"] = true; $host = $_SERVER["HTTP_HOST"]; $path = dirname($_SERVER["PHP_SELF"]); $sid = session_name() . "=" . session_id(); header("Location: index.php?$sid"); exit; } ?> <!DOC...

How to order by a known id value in mysql

Hi, is it possible to order a select from mysql by using a known id first. Example: $sql = "SELECT id, name, date FROM TABLE ORDER BY "id(10)", id DESC Not sure if the above makes sense but what I would like to do is first start ordering by the known id which is 10 which I placed in quotes "id(10)". I know that cannot work as is in t...

mysql delete and foreign key constraint

I'm deleting selected rows from both table in MYSQL, the two tables have foreign keys. delete d,b from A as b inner join B as d on b.bid=d.bid where b.name like '%xxxx%'; MYSQL complains about foreign keys even though I'm trying to delete from both tables: Error: Cannot delete or update a parent row: a foreign key constraint fails (yy...

MySQL: Insert row on table2 if row in table1 exists

I'm trying to set up a MySQL query that will insert a row into table2 if a row in table1 exist already, otherwise it will just insert the row into table1. I need to find a way to adapt the following query into inserting a row into table2 with the existing row's id. INSERT INTO table1 (host, path) VALUES ('youtube.com', '/watch') IF ...

How to UNION ALL two SELECT statements?

I have 2 tables, one looks like this: TABLE ONE id | Last Name | First Name | Username | Password | Secret Question and another that looks like this: TABLE TWO id | Hobby | Country | I want to combine a Select statement that grabs data from both tables and output the results. The following code: $select = mysql_query(" SEL...

iPhone connect to MySQL database on local network computer

I'm a bit of a noob to iPhone programming and I've read up on using PHP as the connection between an iPhone app and a remotely hosted MySQL database, but what I need to do is to connect directly to a MySQL database running on a local machine in my office. The machine is behind the same gateway and has an ip address similar to 192.169.x.x...

Problem with Executing Mysql stored procedure

The stored procedure builds without any problem. The purpose of this is to take backup of selected tables to a script file. when the SP returns a value {Insert statements}. I am using the below MySql stored procedure, created by SQLWAYS [Tool to convert MsSql to MySql]. The actual MsSql SP is from http://www.codeproject.com/KB/database/...

How to populate html combo box with mysql data

Please help, I'm having trouble loading mysql data on the combo box. The data that I'm loading is 1 column from a table. Here is my current code, and it crashed firefox for some reason: <td colspan=”2″>Religion</TD> <td> <select name="REL" onClick="submitCboSemester();"> <?php $query_disp="SELECT * FROM Religion ORDER BY RID"; $result_d...

Trying to convert existing production database table columns from enum to VARCHAR (Rails)

Hi everyone, I have a problem that needs me to convert my existing live production (I've duplicated the schema on my local development box, don't worry :)) table column types from enums to a string. Background: Basically, a previous developer left my codebase in absolute shit, migration versions are extremely out of date, and apparen...

MySQL Insert Data Question

Hi, assume I already created a table in MySQL as below CREATE TABLE IF NOT EXISTS `sales` ( `id` smallint(5) unsigned NOT NULL auto_increment, `client_id` smallint(5) unsigned NOT NULL, `order_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `sub_total` decimal(8,2) NOT NULL, `shipping_cost` deci...

How to pair users? (Like Omegle.com)

Hi, I'm trying to do an Omegle.com clone script, basically for learning purposes. I'm doing it in PHP/MySQL/AJAX. I'm having problems finding two users and connecting them. The purpose of omegle is connecting two users "randomly". What I'm doing right now is the following: When a user enters the website a session is assigned. There ...