mysql

mysql join is very slow

hi All I have two tables (MYISAM) create table A (email varchar(50)); create table B( email varchar(50) key 'email' (email)); Table A has 130K records Table B has 20K records why does this sql statement take very long time (more than two minutes, then i aborted query by Ctrl+C) Statement is: select count(*) from user A, tmp B where...

Accessing a XAMPP mysql via Python

Hi, I'm attempting to use mysql after only having worked with sqlite in the past. I've installed XAMPP on linux (ubuntu) and have mysql up and running fine (seems like that with phpMyadmin atleast). However, I'm having trouble getting the MySQLdb (the python lib) working {installed this using apt}. to be exact: >>> import MySQLdb >>> ...

Can I use Django F() objects with string concatenation ?

I want to run a django update through the ORM that looks something like this: MyModel.objects.filter(**kwargs).update(my_field=F('my_other_field')+'a string') This causes MySQL to throw an exception. Is there anyway to do this without writing raw SQL? ...

Configuring Sphinx to index a dynamic set of tables

I'm in the process of setting up a new WordPress 3.0 multisite instance and would like to use Sphinx on the database server to power search for the primary website. Ideally, this primary site would offer the ability to search against its content (posts, pages, comments, member profiles, activity updates, etc.) as well as all of the other...

MySQL - Increment an exisitng value problem

HEllo guys, I am having an issue that is anoing me. I have a very simple table (MyISAM) where i have a field "count(INT)(11)" It's just a counter that's increment every time one product/item is viewed. The problem is that query is incrementing 2 and not 1. Ie. Actualfield value = 10 than i run: mysql_query("update table_test set cou...

SQL Group By question - choosing which row gets grouped

I have a table that tracks serial numbers and the number of licenses associated with said serial number. A client can re-license a box, increasing or decreasing the number of users, and they are only billed for the changed amount. The following table structure exists: id - Auto Incrementing ID serial - The serial number numusers - the ...

mysql WHERE '2010-08-24 11:59:59' BETWEEN c.start_date AND c.end_date;

I'm currently trying to use this query SELECT a.id AS id, a.item AS item, c.id AS campaign_id, DATE(c.start_date) AS START, DATE(c.end_date) AS END FROM campaign c LEFT JOIN action_6 a ON c.id = a.campaign_id WHERE action_id = 6 AND TIMESTAMP('2010-08-24 11:59:59') BETWEEN c.start_date AND c.end_date; however i am getting an empty ...

This query doesnt appear to work? Looks ok to me....

$q = "UPDATE ".TBL_LEAGUETABLES." SET home_games_played = home_games_played + 1, home_wins = home_wins + '$w', home_draws = home_draws + '$d', home_losses = home_losses + '$l', ...

Remove duplicate MySQL record?

I've got a table where one month's worth of data got duplicated. So there are about 7 fields that are identical. For the time being I've moved that month (2x the records) into a separate table. How do I get rid of the duplicates? From my limited MySQL knowledge, I can't see a way to do this other than manually (but it's not practical si...

Create 2 records in 2 tables consistently using MySQL/InnoDB

Consider I have two tables, t1 and t2 t1 = (id, name), t2 = (id, fid) The fid in t2 is a Foreign Key to t1. Steps to create rows as following. Insert a row in t1, get the id Use that id and insert as fid in t2. My problem is: Since t1's id is unknow when the transaction is not committed, so how to perform the insertion to t2?...

Input string was not in a correct format.

Can anyone please advise where i have gone wrong with the following method / stored proceedure? I keep getting the following error... ** > Input string was not in a correct ** This is the Stored proceedure i using and the method. -- -------------------------------------------------------------------------------- -- Routine DDL -- ...

Select results from the middle of a sorted list?

I've got a simple table with 300 rows, and after ordering them I want to select rows 11-50. Do I limit by 50 and remove the top 10 rows somehow? ...

How to write special characters into MySQL via PHP?

In my database in some fields the data is showing like as in following screenshots: http://i31.tinypic.com/2637l9f.jpg http://i27.tinypic.com/1ihh6d.jpg http://i26.tinypic.com/2yklzb4.jpg http://i31.tinypic.com/2vbshtf.jpg I used mysql_real_escape_string while inserting my data into database and htmlspecialchars while displaying. ...

Mysql 1050 Error "Table already exists" when in fact, it does not

I'm adding this table: CREATE TABLE contenttype ( contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT, class VARBINARY(50) NOT NULL, packageid INT UNSIGNED NOT NULL, canplace ENUM('0','1') NOT NULL DEFAULT '0', cansearch ENUM('0','1') NOT NULL DEFAULT '0', cantag ENUM('0','1') DEFAULT '0', ...

How to execute two MySQL queries ?

I want to save the file path of a picture, AND the image name, in the same table, but separate fields of course. How can I execute it properly? I'm sure there is something significantly wrong in the code below, but I can't spot it. Thank you. $sess_userid = mysql_real_escape_string($_SESSION['userid']); $Image = mysql_real_escape_string...

Is this a good solution to remove duplicate MySQL rows?

I saw the solution to create an alternate temporary MySQL table with unique rows, but I didn't like that idea, as my tables are very large and would be a hassle to move them (and would create huge problems if there would be errors during the move). I did, however, find the following. What do you think of this (where the duplicates to ch...

MySQL - how to get english and encoded characters inserted?

I have a multi language website which requires both English language and Azeri characters to be input via a web form and inserted into a MySQL database table. Displaying them in HTML is fine, I can work with the page encoding to get hard coded text to display. Now the site requires developing and any static text will be pulled from the ...

How to add reviews and rating to each record (clinic) on webpage, that is store in the database?

Hi everyone! I want to add reviews and rating to each (clinic) record of the database. If people search a clinic, the webpage shows multiple (clinics) records on the webpage, and then user can rate and write review to any clinic or multiple clinics. Then each clinic will show there won rating stars. Like in this link http://search.y...

right way to catch a race condition with Django ORM + MySQL/InnoDB

One part of my application has a race condition where multiple threads could end up creating the same persistent object. So I have implemented code that looks like this: from foobar.models import Bar def testomatic(request): bar = None tries = 0 while not bar: try: bar = Bar.objects.get(b=2) ex...

Rails migration issue - foreign key not getting created

So I have the following migration - typical ratings table which captures ratings(value) for comments. I have some helper methods for primary-key(pk), foreign-key(fk), index(index) which is all fine. So everything runs fine, but what i notice is that the foregn key on comment_id does not get created, even though this statement is not rep...