I need to run a query like: SELECT p.id, p.name, (SELECT name FROM sites s WHERE s.id = p.site_id) AS site_list FROM publications p But I'd like the sub-select to return a comma separated list, instead of a column of data. Is this even possible, and if so, how? ...
I was thinking about some stuff lately and I was wondering what would be the RIGHT way to do something like the following scenario (I'm sure it is a quite common thing for DB guys to do something like it). Let's say you have a products table, something like this (MySQL): CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increme...
I am trying to take one step towards optimizing a 90GB+ table: Old Table Every day the table grabs approx. 750,000 records from an external source and adds them to the table with the new date. This has been going on for three years from what I understand. 97% of the records don't change from one day to the next. New Table I am tryi...
I have a MySQL database that has on the order of 400 million innodb_data_pending_reads and innodb_pending_writes. My other databases are consistently at or near zero, so I noticed this large outlier. What can cause this situation? What adverse affects can it cause? How can I troubleshoot the situation to bring this down? ...
I'm using Hibernate's JPA implementation with MySQL 5.0.67. MySQL is configured to use InnoDB. In performing a JPA query (which is translated to SQL), I've discovered that using the IN clause is slower than performing individual queries. Example: SELECT p FROM Person p WHERE p.name IN ('Joe', 'Jane', 'Bob', 'Alice') is slower than fo...
Hi: I have a tar.gz with a full mysql database update that I can access via ftp. This tar.gz updates daily and I would like to create a php and/or mysql connection to that ftp account that would allow me to run that large sql query on my local mysql server. Any thoughts? ...
I have this MySQL query: SELECT DAYOFYEAR(`date`) AS d, COUNT(*) FROM `orders` WHERE `hasPaid` > 0 GROUP BY d ORDER BY d Which returns something like this: d | COUNT(*) | 20 | 5 | 21 | 7 | 22 | 12 | 23 | 4 | What I'd really like is another column on the end to show the running total: d | COUNT(*...
This has been asked before but not exactly in the same way (other users had Rails/servers issues, and I'm not having the issue with OLD_PASSWORDS) I'm trying to make my Ruby app work with MySQL using Ruby-MySQL, The setup is supposed to be quite simple: % ruby ./setup.rb % ruby ./test.rb hostname user passwd # ruby ./install.rb How...
I have a huge list of URLs in a MySQL InnoDB table, and worker processes that query MySQL for a set of URLs to process. The URLs should immediately be marked as being processed, so that other worker processes do not waste resources by starting to process the same ones. Currently I first do this to get some URLs: SELECT DISTINCT url FRO...
I have a column in my table titled 'authorised'. It's default is 0. It needs to be changed to 1 when the user is authorised, but it must be able to be reset to 0. I know I could do this easily with 2 queries like so: $authorised = Db::query('SELECT authorised FROM users WHERE id=2'); $newAuthValue = ($authorised['authorised']) ? 0 : 1;...
I've heard a lot of PostgreSQL but I always wanted to know why you'd choose it over MySQL. What makes it "the world's most advanced" and do those "advanced" features really matter? ...
For starters, this exercise in GNU make was admittedly just that: an exercise rather than a practicality, since a simple bash script would have sufficed. However, it brought up interesting behavior I don't quite understand. I've written a seemingly simple Makefile to handle generation of SSL key/cert pairs as necessary for MySQL. My goa...
I am getting a bunch of undefinded index warnings when i print out my data from a SQL query, when i remove the INNER JOINS most of the warnings disappear. I am not sure what is causing that error. My code is here: <?php $id = $_GET['id']; $sql = "SELECT * FROM updates INNER JOIN clients ON updates.c_id = clients.c_id I...
I have a small form where I want the values to be updated on change for my select boxes which can be done with: $("form#updateclient select").change(function(){ // use one selector for all 3 selects $.post("inc/process-update.php",{ // data to send completed: $("select#completed").val(), hours: $("select#hours").val(), upda...
How would you tackle the following storage and retrieval problem? Roughly 2.000.000 rows will be added each day (365 days/year) with the following information per row: id (unique row identifier) entity_id (takes on values between 1 and 2.000.000 inclusive) date_id (incremented with one each day - will take on values between 1 and 3.65...
I need to save a list of user ids who viewed a page, streamed a song and / or downloaded it. What I do with the list is add to it and show it. I don't really need to save more info than that, and I came up with two solutions. Which one is better, or is there an even better solution I missed: The KISS solution - 1 table with the primary...
When I say sudo /etc/init.d/mysql restart on Ubuntu 8.04.2 sometimes there remains a "mysql_safe" process eating 99% of cpu. Making the machine practically inusable. Is there a better way to restart mysql? I thought about writing a script: sudo /etc/init.d/mysql stop sleep 10 sudo killall mysql_safe sudo /etc/init.d/mysql start But t...
This is a follow up to my question "Efficiently storing 7.300.000.000 rows" (http://stackoverflow.com/questions/665614/efficiently-storing-7-300-000-000-rows). I've decided to use MySQL with partitioning and the preliminary schema looks like this: CREATE TABLE entity_values ( entity_id MEDIUMINT UNSIGNED DEFAULT 0 NOT NULL, # 3 bytes...
Anyone have any suggestions on how to set up both the php and the cocoa side of calling php functions? As a quick idea of what I want to do, I want to be able to to populate two tables with data and add/remove data from the db. So I want to set up a few functions in php that I can call from my iPhone code that will return values from my ...
I am using a MySQL function to find out the max record in that table. SQL data: id_ index Value1 Value2 Value 3 Max_idVal 1 'abc' 5 10 5 5 1 'abc' 0 12 4 5 1 'abc' 0 13 3 5 2 'abc' 4 9 10 8 2 'abc' 8 10 8 8 Max_...