Hi, I'm writing a facebook style messaging system for a Rails App and I'm having trouble selecting the Messages for the inbox (with will_paginate). The messages are organized in threads, in the inbox the most recent message of a thread will appear with a link to it's thread. The thread is organized via a parent_id 1-n relationship with...
I currently have a database with over 6 million rows and growing. I currently do SELECT COUNT(id) FROM table; in order to display the number to my users, but the database is getting large and I have no need to store all of those rows except to be able to show the number. Is there a way to select the auto_increment value to display so t...
Hello, I need to make the databse structure for a real estate website where users can create properties of many types and with many features related to the property. The main categories will be: 1. House (subtype apartment, house, loft) 2. Commercial (subtype: hotels, buildings, offices, factory) 3. Terrains (subtype: urban, agricola, ...
I have my MySQL server running with 4 databases and innodb_data_file_path set to the default configuration[ibdata1]. I see the size of this file on disk growing as big as 1GB. This file will only grow as the application that runs this server is collecting the monitoring data. Just not sure how this will have performance issue with such...
example: making a payment transfer from user A to user B. Account of user A: -10 USD Account of user B: +10 USD if there is an transaction, and something goes wrong, everything gets undone. So with transactions it will not happen that account of user A gets decreased by 10, while account of user B does not get increased by 10. I know t...
Hello. I don't have much experience with Javascript. However i wanted to do a small thing with js and mysql. And i could use some help. I have a page in PHP wich you search for something and its gives the results based on the search query. For each result he adds 3 images, one which as a url where you can view the content. other where ...
Within a php/mysql system we have a number of configuration values (approx 200) - these are mostly booleans or ints and store things such as the number of results per page and whether pages are 2 or 3 columns. This is all stored in a single mysql table and we use a single function to return these values as they are requested, on certain ...
When designing a schema for a DB (e.g. MySQL) the question arises whether or not to completely normalize the tables. On one hand joins (and foreign key constraints, etc.) are very slow, and on the other hand you get redundant data and the potential for inconsistency. Is "optimize last" the correct approach here? i.e. create a by-the-bo...
Say I want to select rows with a date range or date_feild > 2009-06-01 && date_field < 2009-07-01. and I want to select the first_name_field and last_name_field but I only want the same name (F+L) to show up once per date. So the same name can show up, multiple times as long as their dates are different; but not if the names are on the s...
I'm trying to create a directory and I have two tables, entry and location. Each entry will be able to have multiple locations so I'm assuming the best idea (correct if wrong) is to create a third table where I specify 'links'. Example: Marly:France Karla:Argentina Smith:USA Katie:France Smith:United Kingdom Katie:USA When I want to l...
I'm looking at converting our Rails 2.3 application to correctly handle time zones (at the moment everything is in UTC which isn't right, but is convenient!). I have these settings in environment.rb: config.active_record.default_timezone = :utc config.time_zone = "UTC" Going forward, on each request in our app I plan on making the fo...
I'm looking for a query (or series of) to TRUNCATE all tables in my schema (which has a few hundred tables) EXCEPT for a 4 specific ones. How might I go about doing that? Thanks! ...
I have the following two tables: system - id - systemName - idOrganization organization - id - officeSymbol I am running the following query and receiving an id is ambiguous error: SELECT system.systemName, organization.officeSymbol FROM system LEFT JOIN (organization) ON (system.idOrganization = organization.id) As you can see, I...
Is it possible to do mysqldump by single SQL query? I mean tu dump the whole database, like phpmyadmin does when you do export to SQL ...
I am trying to copy one table over another one "atomically". Basically I want to update a table periodically, such that a process that reads from the table will not get an incomplete result if another process is updating the table. To give some background info, I want a table that acts as a leaderboard for a game. This leaderboard wil...
Hi, I have the following table Id Value 1 3 1 12 1 67 2 7 2 99 5 30 5 33 5 4 5 87 5 12 5 1 I'd like to update it to have this table. Id UniqueIdBySubSet Value 1 1 3 1 2 12 1 3 67 2 1 7 2 ...
Which is the easiest way to perform the following in MySQL 5.1? I have a table with a primary key as an integer, currently running from 1 to 220. The PK runs sequentially depending on the order in which the rows were written to the table. I want to be able to randomly reassign this primary key value, so that, for example, row 1 (with a...
Hello, I'm having what seems to be a concurrency problem while using MySQL and PHP + Propel 1.3. Below is a small example of the "save" method of a Propel object. public function save(PropelPDO $con = null) { $con = Propel::getConnection(); try { $con->beginTransaction(); sleep(3); // ignore this, used for testi...
I have a MySQL table that has price requests in it, it has date, first, last, and product_id fields. The product brand can be found from the product table from the product_id. Given a date range, they want to know the total number of requests, by people and by brand for each day in the date range, and total for date range. Here is the t...
In an ActiveRecord (CakePHP flavored) setup I have a HasAndBelongsToMany association setup on Videos and Bins: A Bin can store n references to Videos, and Videos can belong to n Bins. I need to be able to manually set and store the display order of the Videos within a particular Bin (so the client can have his Videos in a particular ord...