mysql

MySQL Query Select using sub-select takes too long

I noticed something strange while executing a select from 2 tables: SELECT * FROM table_1 WHERE id IN ( SELECT id_element FROM table_2 WHERE column_2=3103); This query took approximatively 242 seconds. But when I executed the subquery SELECT id_element FROM table_2 WHERE column_2=3103 it took less than 0.002s (and resulted 2 ...

Asp.net web application with MySQL

I see a lot of asp.net projects coupled with mssql2005 and mssql2008. Most of them don’t use MySQL for some reason.This is even true when an ORM like nhibernate is being used. Is this a pattern only to me or are there quite a lot of asp.net projects with MySQL as the db? If this is true, what might be the reason for not choosing M...

Writing a system for User Accounts

Hi, I am currently working on building my own blogging website from scratch (instead of using WordPress or Drupal). I have the articles set up and stored in databases and the website runs queries on the databases to display the content. That all works fine. What I would like to know is how do I write a system for users to register wit...

iWebKit/iPhone: Chatbox with MySQL, AJAX, PHP and Javascript

Hello, i am busy with a chatbox that contains MySQL, AJAX, PHP and Javascript. Now i want to have this on my iPod Touch/iPhone, so i am building a website with iWebKit. The following codes i provided is done now; just asking why it does not display the chatbox itself (so not the senders) after more than 2 lines are entered... What code...

How do I change the permissions that MySQL uses to create database directories and files?

I have a self-installed MySQL service running on my OS X Snow Leopard machine, compiled using the automated installer from mysql.com. The problem I'm having is that I want to sync my database files, from the "data" directory, using Dropbox. All directories and files created within that folder are being assigned to the user "_mysql" and ...

Search Engine Friendly POST in php and MySql

i have one blog where users come and post their views. i store all information in mysql Data Base, when i use following syntax to retrieve information Datasbe using PHP viewpost.php?pid=12 but after Google many tips for SEO, i found that this syntax is not good, i need to change it to some other way which is readable to search engine....

COUNT(id) vs. COUNT(*) in MySQL

Is there a difference between the following queries, assuming there is a primary field 'id' in the table (as in speed, etc)? SELECT COUNT(id) FROM table vs. SELECT COUNT(*) FROM table ...

Keep database information secure

Hello, there's this interesting problem i can not solve myself. I will be very glad, if you help me. Here's it: there are many client applications that send data records to one MySQL server. Few data records are not very important, but the whole database is. (You can imagine it is facebook DB :) ) Is there any way to ensure that data f...

PHP/MySQL Timezone Clarification

Hello all, I'm currently having an issue wrapping my brain around the notion of converting my MySQL time to a specific timezone, depending on the user's settings. All of my MySQL times are stored in UTC time, in the following format: 2009-11-08 17:06:40 Once I query the time, I'm not quite sure how to convert it to the appropriate...

large mysql (innodb) database - slow query performance, disappearing tables and long time to restore backups

I've a database with 3 of the tables having rows in excess of 20 million each. I've used GUIDs as primary keys (unfortunately). Now our database is about 20GB and growing 5GB per month. It takes about 2 hrs to take full backup of the database, and 30hrs to restore on a box with 4GB RAM. We once have all the tables from database disappe...

add every update, delete, insert query to a new record in mysql

Is there a way that if there's a change in records, that a query that changed the data (update, delete, insert) can be added to a "history" table transparently? For example, if mySQL detects a change in a record or set of records, is there a way for mySQL to add that query statement into a separate table so that way, we can track the ch...

Suggestions for gateway please

I almost completed my first shopping cart with Codeigniter(PHP/MySQL). I want to connect to payment systems. I've never developed a shopping cart before. I do not have any intention to develop my own gateway and I don't have any knowledge and skills to do it. Could anyone give me suggestions about the best way to connect my shopping ca...

Getting "Access denied for user 'root'@'localhost' (using password: NO)" in production mode and no errors getting logged

I deployed my Rails app and am getting 500 Errors on all pages. My production.log isn't showing anything (which is a problem), but I did a 'script/console production' and tried to run a simple query (User.find :first) and it throws this: Access denied for user 'root'@'localhost' (using password: NO) My database.yml file definitely has...

MySQL: When updating, ignore empty strings

I know how to do this using PHP, but I was wondering if MySQL provides for a simpler solution. UPDATE users SET fname = 'badooka', lname = '' WHERE user_id='322'; If a value is empty (lname, in this particular case), how do I ignore it instead of overwriting the original with an empty string? ...

JNDI and javax.sql.DataSource

I'm someone that used to do some J2EE coding in the past and I'm coming back into the fold to work on a new J2EE project. A lot has changed since 2001, so I need to ask this very basic question. I'm using syntax like this in my database class: @Resource(name = "jdbc/MyDatabase") private javax.sql.DataSource dataSource; I understa...

connecting external mysql database with php

Hi Is that possible to connect mysql database between servers. I mean, lets say i have 2 accounts on 2 different hosting service, one of them has database, and i want to connect it from other one with php. When i try it, i get "Warning: mysql_connect() [function.mysql-connect]: Lost connection to MySQL server during query..." my data...

Keeping ordering in a WHERE IN clause in my SQL

I am running the following SQL query: SELECT * FROM cms_albums WHERE id IN (SELECT album_id FROM cms_albums_collections WHERE collection_id = 1 ORDER BY position ASC) Now, assume the inner query SELECT album_id FROM cms_albums_collections WHERE collection_id = 1 ORDER BY position ASC returns the following: album_id "4" "2" This...

Best GUI for managing MySQL 5.1?

What is the best GUI for managing MySQL 5.1 installation? Would like something as close to SQL Server's management tools as possible as that's where my experience is. The management client would need to run under Windows (XP, Vista (32 and 64-bit flavors), and 7 (32 and 64-bit flavors). ...

Repetitively Retrieve Data from Site via PHP

When accessing http://www.example.net, a CSV file is downloaded with the most current data regarding that site. I want to have my site, http://www.example.com, access http://www.example.net on an hour by hour basis in order to get updated information. I want to then use the updated information stored in the CSV file to compare change...

Is there a better place to store large amounts of unused data than a the database?

So the application we've got calls the API's of all the major carriers (UPS, FedEx, etc) for tracking data. We save the most recent version of the XML feed we get from them in a TEXT field in a table in our database. We really hardly ever (read, never so far) access that data, but have it "just in case." It adds quite a bit of additio...