mysql

MySQL + Visual Studio 2008 + GoDaddy = FAIL?

Hi, I'm trying to connect my ASP.NET web application to a mysql database that is hosted by GoDaddy. I downloaded and installed the MySQL connector and then tried to use the "Connect to Database" operation in Visual Studio. It shows an option for "MySQL Database" and I put in my credentials, but it gives me an error, "Unable to connect to...

What's the most efficient way to do weighted fulltext search in MYSQL?

In order to give different columns different weights,of course one can use: select ... from table_name where match(column1,column2...columnn) against('+test..') order by weight1*(match (column1) against('test..')) + weight2*(match (column2) against('+test..')) + ... + weightn*(match (column1) against('...

Mysql - Can I fetch a subselect result and use it in a WHERE clause?

I want to both fetch and use in a where clause the value returned from a sub-select in MySQL. Is this possible? It seems unecessary to write the sub-query out twice - however if I need to, will MySQL be clever enough to only run it one? I have tried the following which does not work: SELECT (SELECT 1 FROM table WHERE somereallycompl...

MySQL trigger/procedure execution delay

Hi! Is there a decent way to delay execution of mysql trigger? WHILE @condition = 0 sleep for awhile insert into some_table values(NEW.value1, NEW.value2); ...

Is it possible to create an event and handle it, if the connection to a MySQL server is lost?

Let's say I have my client application and it makes a connection to the MySQL server. Fantastic. I don't implement this as a thread. I just utilise the MySQLConnection class. And now let's say Jim who's really careless about the office accidently reboots the MySQL server without asking permission first. I want to know when the disconne...

Two different login pages according to ID using PHP and MySQL.

$query = "SELECT * FROM users WHERE username = '".mysql_escape_string($username)."' AND password = '".mysql_escape_string($password)."'"; $result = mysql_fetch_array(mysql_query($query)); Now this is my query for general visitors, now if i need a admin and moderator how can i process from t...

Jquery - finding range between two unique id's in mysql

Another question which has me perplexed: I have a table which enables users to enter as many rows as they like based on their userid and unique id (auto incremental). I need to be able to get this information from mysql and place the previously entered information into the fields on the web application (they may need to be edited befor...

MySQL query with join optimization

I got a query: SELECT a.nick,grp,count(*) FROM help_mails h JOIN accounts a ON h.helper=a.id WHERE closed=1 GROUP BY helper, grp, a.nick What is wrong with this join? When I made 2 queries: SELECT helper,grp,count(*) FROM help_mails h WHERE closed=1 GROUP BY helper, grp; SELECT nick FROM accounts WHERE id IN (...) It is 100 times fast...

compare differences between two tables in mysql

Same as http://stackoverflow.com/questions/688537/ except in mysql. Suppose I have two tables, t1 and t2 which are identical in layout but which may contain different data. What's the best way to diff these two tables? To be more precise, I'm trying to figure out a simple SQL query that tells me if data from one row in t1 is ...

MySQL - SQL_BIG_SELECTS

Hey, I've been investigating SQL_BIG_SELECTS, but the MySQL documentation so far has been pretty unhelpful. I'm looking for some insight as to preventing errors like the one below from appearing. ERROR 1104: The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SEL...

MySQL: How to get n latest rows of a distinct type

Putting this as simply as I can, I have the following table structure: Date | Type | Title Say Type is a value in the range 1-10, I have 1,000s of records in the table, and I want the 5 most recent records of unique type. So the result would be something like: 2009-06-04 14:32:00 | 4 | Zeppo 2009-06-04 14:31:00 | 2 | Groucho 2009-06...

MySQL grouping with a comma separated field

Here's a tricky one. I have data as follows: User ID Name Skill Sets 1 Jim Install, Configure 2 Jack Install 3 John Configure, Setup, Blah 4 Bill Setup, Install This isn't my design, and it's not an option to change the way the data is formatted. The trouble is that I need to group ...

Why does MySQL not use an index when executing this query?

mysql> desc users; +-------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | email | varc...

How can I compose this query?

I have a problem coming up with a query in MySQL. I have the following query: SELECT tm.ticket_message_id, tm.ticket_id, tm.message_from, tm.message_to, t.member_id, t.member_to FROM `ticket_messages` AS tm INNER JOIN `tickets` AS t ON ( t.ticket_id = tm.ticket_id ) The primary key of table ticket_messages i...

Need help optimizing a lat/Lon geo search for mysql

I have a mysql (5.0.22) myisam table with roughly 300k records in it and I want to do a lat/lon distance search within a five mile radius. I have an index that covers the lat/lon fields and is fast (milisecond response) when I just select for lat/lon. But when I select for additional fields in the table is slows down horribly to 5-8 ...

MySQL: count grouped items?

Basically, ok say my DB table has 2 columns: keyword and date. I want to select rows but if multiple rows have the same keyword and date I want to group them and get a count of how many there are. for example instead of getting this: keyword: diamond, date: 20090601 keyword: diamond, date: 20090601 keyword: diamond, date: 20090602 key...

MySQL: group by date RANGE?

OK I have this query that groups 2 columns together quite nicely: SELECT search_query_keyword, search_query_date, COUNT(1) as count FROM search_queries WHERE search_query_date >= '.$from.' AND search_query_date <= '.$to.' GROUP BY search_query_keyword, search_query_date ORDER BY count DESC LIMIT 10 But w...

caching issues in MySQL response with MySQLdb in Django

I use MySQL with MySQLdb module in Python, in Django. I'm running in autocommit mode in this case (and Django's transaction.is_managed() actually returns False). I have several processes interacting with the database. One process fetches all Task models with Task.objects.all() Then another process adds a Task model (I can see it in ...

Generic "Killed" error in PHP CRON job

I am working on a CRON job in PHP which has to do a lot of heavy lifting via the database. Think lots and lots of loops. It executes properly when I limit the data set, but when I run it against the full data set, the script errors out with a simple "Killed" message. set_time_limit is (0) and memory_limit is (-1) Here is the code s...

Multiple-column foreign key in MySQL?

I have a table that has a primary key consisting of two columns (product_id, attribute_id). I have another table that needs to reference this table. How can I make a foreign key in the other table to link it to a row in the table with two primary keys? ...