mysql

MySQL: How to ask for a date greater than today when a date is in a string

Hi, I'm try to extract info from a MySQL DB into a MS SQL DB. The DB is a mess and the developer is no longer available. All dates are in char fields, and I use SELECT concat( mid(DueDate, 7, 4), mid(DueDate, 4, 2), mid(DueDate, 1, 2)) as DueDate FROM TableName to get the date field in a format so MS sql server can import them. Now...

MySQL terminology "constraints" vs "foreign keys" difference?

I'm looking at the MySQL docs here and trying to sort out the distinction between FOREIGN KEYs and CONSTRAINTs. I thought an FK was a constraint, but the docs seem to talk about them like they're separate things. The syntax for creating an FK is (in part)... [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) R...

is filter_var sufficient to sanitise integer input for php-based mysql queries?

I've never liked wrapping the mysql_real_escape_string function around input I expect to be integer for inclusion in a mysql query. Recently I came across the filter_var function. Nice! I'm currently using the code: if (isset($idUserIN) && filter_var($idUserIN, FILTER_VALIDATE_INT) && 0 < filter_var($idUserIN, FILTER_...

MySQL A Search, A Select of multiple rows, Joining and all in one query

I am first doing a search on the table tags which results in all the rows returned together with their additional data from the table links (on which i do a join). The table tags works such that for an auto _id there can be multiple rows (1 tag per row so multiple tags is multiple rows). What I want to do is that instead of just gettin...

MySQL - Faster method for this complex query?

Is there a less resource intensive / faster way of performing this query (which is partly based upon: This StackOverflow question ). Currently it takes 0.008 seconds searching through only a dozen or so rows per table. SELECT DISTINCT * FROM ( ( SELECT DISTINCT ta.auto_id, li.address, li.title, GROUP_CONCAT( ta.tag ) , li.description, ...

MySQL – How do you find the time down to the Millisecond?

Is there any way to get the time down to the Millisecond in MySQL? ...

How do I optimize MySQL's queries with constants?

NOTE: the original question is moot but scan to the bottom for something relevant. I have a query I want to optimize that looks something like this: select cols from tbl where col = "some run time value" limit 1; I want to know what keys are being used but whatever I pass to explain, it is able to optimize the where clause to nothing...

Joins and where conditions

I have the following (shortened query): SELECT `Statistics`.`StatisticID`, COUNT(DISTINCT `Flags`.`FlagType`) AS `FlagCount` FROM `Statistics` LEFT JOIN `Flags` ON `Statistics`.`StatisticID` = `Flags`.`StatisticID` WHERE `FlagCount` = 0 GROUP BY `Statistics`.`StatisticID` ORDER BY `SubmittedTime` DESC LIMIT 0, 10 Now, neither...

Scaling LAMP

I have a client with a LAMP website serving mostly video. He is currently on one server with all components. He is having some scaling problems. What are some of the techniques that can be used to help. I used separating out the DB to another server with a GB Ethernet between it and the webserver. Maybe adding more web servers with some...

MySQL - Where IN together with GROUP_CONCAT

The query used inside the IN() returns: 1, 2. However the whole query returns 0 rows which is impossible since they are present. What am I doing wrong here? SELECT DISTINCT li.auto_id FROM links AS li JOIN group_data AS gi ON li.auto_id = gi.autoid AND li.user_id = gi.userid WHERE gi.groupid IN ( SELECT CA...

How can I define a BigInt primary key with Rails 2.1 and MySQL?

Since Rails 2.1, if you define a new column in a migration with the type set to :integer and the :limit set to 5 or more, the column actually created in your MySQL database will be of type BigInt. That's perfect. But I cannot figure out how to create a table with a BigInt primary key. Any clues? ...

Techniques for Caching SQL Query Data

Having read some on this subject: http://stackoverflow.com/questions/37157/caching-mysql-queries http://www.danga.com/memcached/ My SQL Caching problem: http://www.petefreitag.com/item/390.cfm http://framework.zend.com/manual/en/zend.cache.html#zend.cache.introduction I have a very unique (narrow) set of Queries and I think I could ...

MYSQL - What does STRAIGHT_JOIN do in this code ?

What does STRAIGHT_JOIN do in this code ? SELECT STRAIGHT_JOIN ClosingBalance FROM Accounts WHERE idAccounts = FidDebit; ...

PHP/MySQL: Retrieving the last *full* weeks entries

Hi, I'm currently using the following SQL for retrieving the last seven days worth of entries from a table: purchased >= date_sub(now() ,interval 7 day) However, I need to change this so it retrieves the last full weeks worth of entries (midnight Saturday to midnight Saturday). So basically, throughout the week the results never chan...

modifying a php sql query to take an ajax paramter

Hello, I have this code: <?php session_start(); if (isset($_GET["cmd"])) $cmd = $_GET["cmd"]; else die("You should have a 'cmd' parameter in your URL"); $con = mysql_connect("localhost","xxx","xxx"); if(!$con) { die('Connection failed because of' .mysql_error()); } mysql_select_db("ebay",$con); if($cmd=="GetAuctionData") { echo "<t...

How to change data for one column in a database?

UPDATE: I discovered that I was calling the permalink from another table, with the old values. How can I take the data from the permalinks table and change only the permalinks column in the other table? New id=7005 permalink=beef-ground-85%25-lean-meat-15%25-fat-raw Old id=7005 permalink=beef-ground-85pct-lean-meat-_15pct-fat-r...

Querying for N records associated with each record referenced by set of record keys.

Hello, Here's a simplified version of my scenario: I have a table named Project that I reference via an 'id' field. I have a table named Photo that has a field named 'project_id' which I use to relate multiple photos to a single project. The Photo table also has an auto-incrementing 'id' field that I use for ordering, amongst other th...

What is the best way to achieve speedy inserts of large amounts of data in MySQL?

I have written a program in C to parse large XML files and then create files with insert statements. Some other process would ingest the files into a MySQL database. This data will serve as a indexing service so that users can find documents easily. I have chosen InnoDB for the ability of row-level locking. The C program will be gene...

Which tags are not in the database?

Given a collection of user specified tags how do I determine which ones are not in the tags table with 1 SQL Statement? Assuming a table schema tags (id, tag) and I'm using mysql, if there's an optimization I'm unaware of. thanks ...

Setting innodb_log_file_size crashes MySQL

So, I'd like to be able to set the max log file size to 64M, but after doing so with innodb_log_file_size=64M MySQL starts OK, but nothing seems to work properly. EDIT: and by properly I mean not at all. Setting other InnoDB variables aren't causing any problems. How should I go about troubleshooting this one? ...