mysql

MySQL performance optimization: order by datetime field

I have a table with roughly 100.000 blog postings, linked to a table with 50 feeds via an 1:n relationship. When I query both tables with a select statement, ordered by a datetime field of the postings table, MySQL always uses filesort, resulting in very slow query times (>1 second). Here's the schema of the postings table (simplified): ...

MYSQL: Complex Query Question

Hello all, I am working with polls: Each poll has many options and users can vote in polls once. Thus I have a 'votes' table with the following fields: id (id of the vote) option_id (id of the poll option chosen) user_id (id of the user) poll_id (id of the poll) So here is what I'm trying to do: Given an array of poll_ids, I want ...

MySql Stored Procedure's parameter with the same name as affecting's column, is it possible?

Hi again, I have a sequence table with two columns, name, value, and I have a stored procedure for incrementing the value provided the name DROP PROCEDURE IF EXISTS p_generate_sequence; delimiter | CREATE PROCEDURE p_generate_sequence (name VARCHAR(30)) BEGIN START TRANSACTION; -- Variable "name" equal to column "name", how to ...

Best way to connect to database for this application

I have a Delphi application which hits a database (usually MySql) every 60 seconds through a TTimer. The application is more or less an unattended bulletin board. If the network drops the application needs to continue running and connect back to the database when the connection is back. Often it might be over broadband, so chances are...

MySQL Memory engine + init-file

I'm trying to set up a MySQL database so that the tables are ran by the memory engine. I don't really care about loosing some data that gets populated but I would like to dump it daily (via mysqldump in a cronjob) and have the init-file set to this dump. However I can't seem to figure out how to get the mysqldump to be compatable with ho...

How to transition from embedded software development to web development?

Hello, I am an embedded software developer with about 5+ years of experience working on mobile devices. I recently lost my job and most of the jobs in the embedded field (that I came across) require security clearance and I am not eligible for that. So, for this reason and also just to learn something new, I am planning to move to web d...

Run multiple MySQL server on a single machine

Can we run multiple MySQL servers on a single machine? Thanks. ...

PHP - Storing Text in MySQL Database

I have a textbox on my website and I need to store whatever the user enters into my database, and retrieve it at a later time. I need to store it exactly as the user entered, including special characters, carriage returns, etc. What process should I use in PHP to store this in my database field (which is a 'text' field)? Should I use PH...

Pivoting Concept.

Hiii, I have a Database design as: Table File (FileID, Name, Details) Table Attributes (AttID, AttName, AttType) Table AttValues (FileID, AttID, AttValue) Till the runtime, it is not known how many Attributes are in a File and of what names. And I want to display after insertion at Frontend in a manner like like: FileID, FileName...

Post MySQL Result Using Alax(Jquery).[SOLVED]

Hi all, Im afraid its another noob question...lol. Being relatively new to javascript/ajax/jquery..programming ingeneral... I am fumbling where blind men drive chariots, so please go easy:). I am wanting to post 3 values to the database, 2 of which are results of a mysql query(Im not sure if I am doing this right, so feel free to correc...

create mysql database from java

Is there any possability to create a database within mysql from java. I'm only aware of the String url="jdbc:mysql://localhost:3306/test"; Connection con = DriverManager.getConnection( url, "cb0", "xxx" ); syntax but here you have to specify a database name in the 'url'. But how do I create a mysql database when I only have a logi...

Is it true I can't edit a MySQL trigger, I have to drop it and create a new one?

Is it true I can't edit a MySQL trigger, I have to drop it and create a new one? Also, being a relative newcomer to triggers, it feels like they seem liable to causing 'erroneous' data. For example I might want a trigger to be fired (inserting data into another table) after one particular type of update query, but not others. Any tips ...

Getting the last record inserted into a select query

I am creating a small message board and I am stuck I can select the subject, the original author, the number of replies but what I can't do is get the username, topic or date of the last post. There are 3 tables, boards, topics and messages. I want to get the author, date and topic of the last message in the message table. The author...

Warning: Assignment in condition

One thing that has always bugged me is that when checking my php scripts for problems I get the warning "bool-assign : Assignment in condition" and I get them a lot. e.g.: $guests = array(); $sql = "SELECT * FROM `guestlist`"; $result = mysql_query($sql); while($row = mysql_fetch_assoc($result)) $guests[] = $row['name']; Is there ...

Mysql Connection Class

Hi - I'm writing some php code, and I'm frequently accessing my MySQL database to read and update fields. My current code includes a class called dbconnnect, and I use it as follows: class edit_data extends dbconnect { //<some php code> parent::connect(); //<get info from database> parent::disconnect(); //<evaluate data> My questio...

Generating SQL Tables on the fly as new content is uploaded - A bad idea?

I have an interesting problem which I've been looking into and would appreciate some advice: I'm trying to create a tool which mimics the basic capabilities of a requirements management tool as part of a company project. The basic design is a Windows Explorer-like setting of folders and documents. Documents can be opened in a GUI, edit...

inserting records in a MySQL table depending on existing values in another table

Hi guys, I am using MySQL. I want to insert some records in a table provided that they do not exist in another table. So I want to perform something like this: INSERT INTO sales (name, id) SELECT name, id FROM sales_h WHERE name AND id NOT IN (SELECT name, id FROM T_sales); The problem is that MySQL does not allow this kind of synta...

Is a mysql user defined function suitable for signing Amazon S3 url?

Hi, I need to retrieve a result from a mysql database using a user defined function that recreates the following PHP code - $signature = urlencode(base64_encode((hash_hmac("sha1", utf8_encode($string_to_sign), awsSecretKey, TRUE)))); Are UDF's up to the job? What are the security implications for storing the AWS secret key in the U...

Bulk Insertion in MYSQL from XML Files.

How can we load data to Mysql Tables from XML Files?? Is there any way to read data from XML Files and Write to MySql database.. I have a bulk of data in XML Files. Thanks in Advance for help. ...

Querying data for a Facebook-like news feed

I have a social networking site where users update their moods, profiles and add photos. I'm currently logging all updates in a table called "update_log" with the following structure: update_id int (auto), userid int, update_type int (1=profile, 2=photo, 3=mood) pictureid int mood_a int mood_b int mood_c int update_time int Profile...