mysql

Select max 5 integers from set of 20

I'm trying to select the 5 most viewed articles out of a list of the 20 most recent entries in a table. My table structure is essentially this: id | date | title | content | views My first thought was just to use an inner select to get the 20 most recent articles, then select from that, but I have yet to have any luck. //doesn't wor...

MySQL: SELECT from another server

I'm afraid that I already know the answer to my question, but I'll ask it anyway: When there are two MySQL DB servers, can I access data that is stored on the other server? In other words: Can I somehow do this: INSERT INTO table (x, y, z) SELECT x, y, x+y FROM [otherserver].[database].[table] Is the answer really as short...

How do I execute a query once a day and output results to flat file using cron?

Hello all. I have a PHP/MySQL web site where I want to output the # of entries in a DB/table on the main page, but I don't want to make this query every time a user hits the home page. I want it to automatically run once a day and output the query results to a flat file which I can then just include. I figure that should reduce load...

deleting queries in mySQL

Hi, Reading the bottom of this document, specifically: "You can still retrieve properties of deleted objects, but you cannot save deleted objects." what the heck? how?...is it only if you enable a setting? http://propel.phpdb.org/docs/user_guide/chapters/ManipulatingObjects.html#ManipulatingObjects.Deleting explanation would be appr...

MySQL query to return only duplicate entries with counts

I have a legacy MySQL table called lnk_lists_addresses with columns list_id and address_id. I'd like to write a query that reports all the cases where the same list_id-address_id combination appears more than once in the table with a count. I tried this... SELECT count(*), list_id, address_id FROM lnk_lists_addresses GROUP BY list_id, ...

mysql count only for distinct values in joined query

WOW! That's a weird title, but I'm happy you're looking, cause I couldn't find another way to say it. I have a table of people and a table of links to photos and videos. I join the people to the media, and of course a person can have more than one piece of media. I am attempting to grab the first 30 people and all of their media in ...

MySQL Query GROUP BY day / month / year

Heyall, Is it possible I make a simple query to count how many records I have in a determined period of time like a Year, month or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.YEAR = 2009 GROUP BY record_date.YEAR Or even: SELECT COUNT(id) FROM stats GROUP BY record_date.YEAR, record_date.MONTH...

Broadcast address based on IP and MASK with mysql

hello, I have the following two columns: SELECT b.ip_address AS IP ,b.mask AS MASK FROM interfaces b WHERE b.ip_address = 167804290; +-----------+------------+ | IP | MASK | +-----------+------------+ | 167804290 | 4294967168 | +-----------+------------+ Where is an actual IP address an its subnet mask SELECT INET_NTO...

Need help with an SQL query involving multiple tables - Join not an option

SELECT i.*, i.id IN ( SELECT id FROM w WHERE w.status='active') AS wish FROM i INNER JOIN r ON i.id=r.id WHERE r.member_id=1 && r.status='active' ORDER BY wish DESC LIMIT 0,50 That's a query that I'm trying to run. It doesn't scale well, and I'm wondering if someone here can tell me where I could improve things. I don't joi...

mysqli_stmt::bind_param and mysqli_stmt::prepare

Ok so i want to do a prepared insert such as: prepare("INSERT INTO `analyzeditemsdetails` SET analyzeditem_id = ? , start_time = ? , end_time = ? "); start_time and end_time are stored ...

A backup persistence store. What are my options ?

I have a service that accepts callbacks from a provider. Motivation: I do not want to EVER lose any callbacks (unless of course my network becomes unreachable). Let's suppose the impossible happens and my mysql server becomes unreachable for some time, I want to fallback to a secondary persistence store once I've retried several times ...

Get a list of dates between two dates

Using standard mysql functions is there a way to write a query that will return a list of days between two dates. eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values: 2009-01-01 2009-01-02 2009-01-03 2009-01-04 2009-01-05 2009-01-06 2009-01-07 2009-01-08 2009-01-09 2009-01-10 2009-01-11 2009-01-12...

How do I store the lucene index in a database?

This is my sample code: MysqlDataSource dataSource = new MysqlDataSource(); dataSource.setUser("root"); dataSource.setPassword("ncl"); dataSource.setDatabaseName("userdb"); dataSource.setEmulateLocators(true); //This is important because we are dealing with a blob type data field try{ JdbcDirectory jdbcDir = new JdbcDirectory(d...

Getting table metadata in MySQL

I'm trying to find out how to get the following constraint information from a table in MySQL 5.0: primary key foreign keys and table references unique columns What is the syntax of the query or queries to do so? I have a feeling I'm close with this, but there is no example. ...

Connection pooling in the MySQL .NET Connector

I'm creating a website in asp.net and mysql, I noticed that the performance of connection pooling in the mysql .net connector is horrible to say the least! For example sql server is 10x times faster in connection pooling (the sql server .net provider connecting to sql server), is there anything I could do to speed up connection pooling? ...

MySQL - Need help to figure out multiple joins

I am using the following query to get the transactions from a table made to and from a user. I then want to retrieve the username for the sender_id and for the recipient_id. However I can only seem to get it for the recipient_id or the sender_id. Anyone have any ideas how I can get both. SELECT us.name, ta.amount, ta.recipient_id, ta.se...

Union being overridden in OBIEE

I have used a 'Union' to combine two reports showing different dates. When I open the report on it's own it works fine with the two dates shown. However when I drill to the (union) report from another on the dashboard it only shows one date. Is there a dashboard filter that I may need to un apply or is there some other explanation!? I ca...

How fast is MySQL compared to a C/C++ program running in the server?

Ok, I have a need to perform some intensive text manipulation operations. Like concatenating huge (say 100 pages of standard text), and searching in them etc. so I am wondering if MySQL would give me a better performance for these specific operations, compared to a C program doing the same thing? Thanks. ...

SQL query : inner joins optimization between big tables

Hello, I have the 3 following tables in a MySQL 4.x DB : hosts: (300.000 records) id (UNSIGNED INT) PRIMARY KEY name (VARCHAR 100) paths: (6.000.000 records) id (UNSIGNED INT) PRIMARY KEY name (VARCHAR 100) urls: (7.000.000 records) host (UNSIGNED INT) PRIMARY KEY <--- links to hosts.id path (UNSIGNED INT) PRIMARY KEY <--- links ...

mySQL large text comparisson performance... best practices?

I've got a largish (~1.5M records) table that holds text strings of varying length for which I run queries against looking for matches: CREATE TABLE IF NOT EXISTS `shingles` ( `id` bigint(20) NOT NULL auto_increment, `TS` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `shingle` varchar(255) NOT NULL, `...