Once in a while, I need to perform a massive update to a very large table. If users continue hitting the Web site while the update is being run, there will be a line-up of MySQL clients. It appears that the longer the line-up, the slower the main operation gets (i.e. it updates fewer rows per unit time). Killing those processes can spee...
I just want some advice about mysql database. I'm creating a new database for hired cars that need servicing and repairs. I have created 4 tables. Car_id is linked to a primary key table with car information. I was thinking of putting the repair_id and service_id with in the car data table. But that means it will have 3 primary keys in ...
I want to extract rows of group by rls_id but with latest/recent date SELECT * FROM `tbl_revisions` where `date` in (SELECT MAX(`date`) FROM `tbl_revisions` group by `rls_id`) group by `rls_id` The above query works well but i dont want to use subqeuries .I need some other way around. CREATE TABLE IF NOT EXISTS `tbl_revisions` ( `i...
What is an efficient and elegant way of performing a "find_or_create_all" in rails? Given: names = ["Bob","John","Jack","Sid"], Users table Needed: users = ["#User:0x3ae3a00", "#User:0x3ae3938", "#User:0x3ae3898", "#User:0x3ae37f8"] where a user is created only if it doesn't exist. An intermediate solution - ["#User:0x3ae3a00", ni...
So I have this function that gets data from database and echoes it. The function takes from the database the article id, article title, and some other data.. When the user is not logged in, the function works good and shows all the data, but when a user is logged in, suddenly only the article title is fetched.. All the data is in the sa...
We want to keep track of production data of a Rails app, so that we can analyse them in a few months. To be more precise, we have a few data Models with a "status" and dates column that can change frequently. The idea is to write somewhere a new line in a DB or log file every time the status or any other attribute changes. These line ...
I receive data, and use aes or blowfish to encrypt it before saving it to the database, so the encryption is done at the application level. If someone steals the database, the data will be relatively safe unless they stole the application also (where the key is stored/accessed). I'm now looking into database encryption with libraries l...
I am wanting to do some queries on a sales table and a purchases table, things like showing the total costs, or total sales of a particular item etc. Both of these tables also have a date field, which stores dates in sortable format(just the default I suppose?) I am wondering how I would do things with this date field as part of my que...
Hi, Given the following table format... id | date | value ___|____________|______ 11 | 2010-01-01 | 50 11 | 2010-01-02 | 100 12 | 2010-01-01 | 150 12 | 2010-01-02 | 200 ... I need to select the id that corresponds to the maximum value on a day that I specify. The only way I've figured out how to do this so far is using a sub-qu...
In MySQL, I can do something like: SELECT DATE_ADD('2010-07-02', INTERVAL 1 MONTH) And that returns: 2010-08-02 That's great... now is there a MySQL function that I can use to find what the date would be one month in advance on the same day. For example, 7/2/2010 falls on the first Friday of July, 2010. Is there an easy way...
It seems that all questions regarding this topic are very specific, and while I value specific examples, I'm interested in the basics of SQL optimization. I am very comfortable working in SQL, and have a background in hardware/low level software. What I want is the tools both tangible software, and a method to look at the mysql databas...
When using decimal in MySQL, does a value of (5,3) make it so the max number is 99.999? if not how do I specify that? My number is for currency so I need to use decimal, but it should not go over 99 and it must have 3 decimal places. ...
Is it possible to reference (InnoDB) a foreign key in another database in MySQL? Is this bad practice? ...
Hi, sorry about the question I am a newbie to sql. i am attempting to create a search query for our database and I was wondering how would you filter certain words from your query for example: Here is the sample data (the name column): Jean, Jain, Joan, Jorn, Juan, John, Juin Lets say that we are searching for the names that start with...
hi there i have two queries, querying the same table, but based on different parameters, i then need to mush these two result sets together based on certian parameters //get initial text Q1 SELECT campaign_id AS campaign_id, from_number AS mobile, received_msg AS join_txt, date_received AS join_txt_date FROM received_txts WHERE act...
Is there a command to check how much room MySql has left? ...
How would I select all but the first 3 rows in my MySql query? $query = mysql_query("SELECT * FROM comments WHERE approved = 1"); ...
I am trying to connect my Java Applet to a MySQL Database. I know that it works, because I can connect to it on localhost and it retreives a list of records just fine. But when I put it on the internet, it doesn't work. Here is my applet: http://mystikrpg.com/play It's signed, but I keep getting the following exception: SQLExceptio...
I have two tables, sales and costs, each with a costs fields, and a date field of type date. The rest of the fields are not relevant for what I want to do. I want to take the totals of each month, and display them together. I am unsure if most of this should be done in SQL, if it must be one or several queries, of if I must manipulate...
Hello, I am trying to set up a normalised MySQL database containing the three following tables. The first table contains a list of items which can be described by various tags. The third table contains the various tags used to describe the items in the first table. The middle table relates the other two tables to each other. In each...