The following query returns a single row, as desired. The 'contracts' table has 6 fields, each with a different username in it, for which I want to retrieve first/last names from a separate 'users' table. This works fine, but is there something more concise? I'm think the solution must be something using GROUP BY contracts.id to keep ...
I work on a big web application that uses a MySQL 5.0 database with InnoDB tables. Twice over the last couple of months, we have experienced the following scenario: The database server runs fine for weeks, with low load and few slow queries. A frequently-executed query that previously ran quickly will suddenly start running very slowl...
I want to show a random record from the database. I would like to be able to show X number of random records if I choose. Therefore I need to select the top X records from a randomly selected list of IDs (There will never be more than 500 records involved to choose from, unless the earth dramatically increases in size. Currently there...
I am trying to perform a search for users based on a full-text search. SELECT * FROM users WHERE MATCH ( name ) AGAINST ( 'FDR' IN BOOLEAN MODE ); However the query yields no results. I can replace the search value with other strings and yield results. I have even tried using a null stop word list with no success. The problem seems ...
Hi, I have a problem with an SQL database I'm creating. I'm trying to switch it over to use INNODB but I can't seem to get the syntax (or possibly logic) correct for cascading. Here's a part of the code erroring. It does not like line 40. Error output in the usual cryptic (to me at least) form: ERROR 1005 (HY000) at line 36: Can't ...
By Upper tens of thousands requests/second I want to see 60,000 -> +90,000 requests/second. My Setup consists of the following: user ---> web app --> message queue --> parser --> database? I should mention that the parser currently can parse/stuff around 18750 records/second using COPY so we are limited on that end until we start addi...
First off, I recognize the differences between the two: - Like makes available the wildcards % and _ - significant trailing whitespace - colation issues All other things being equal, for an exact string match which is more efficient: SELECT field WHERE 'a' = 'a'; Or: SELECT field WHERE 'a' LIKE 'a'; Or: Is the difference so insign...
Is there a better way to select empty datetime fields than this? SELECT * FROM `table` WHERE `datetime_field` = '0000-00-00 00:00:00' ...
I have a database table. It has several hundred entries and each entry is timestamped. I'm looking to only output each day and not have it duplicate any days. Data: 2009-02-04 06:20:27 2009-02-04 06:20:27 2009-02-05 06:20:27 2009-02-07 06:20:27 2009-02-07 06:20:27 2009-02-07 06:20:27 Output should only be (time is irrelevant): 20...
I'M NOT ASKING ABOUT THE CODE. I just want to know what are and all the steps involved in the connection other than coding. I'm using j2sdk1.4.0 and MySQL Server 4.1. Am very new to this area. Thanks in advance ...
For community websites like Facebook, myspace, etc.. They have a feature called "Friendship", means you can add friends, send "add friends request", etc.. How to structure it in database? I means, what tables required? What are the columns for each table required? ...
Hi, I have this in mind: On each server: (they all are set up identically) A free database like MySQL or PostgreSQL. Tomcat 6.x for hosting Servlet based Java applications Hibernate 3.x as the ORM tool Spring 2.5 for the business layer Wicket 1.3.2 for the presentation layer I place a load balancer in front of the servers and a rep...
Hi, I have this query: SELECT p.id, r.status, r.title FROM page AS p INNER JOIN page_revision as r ON r.pageId = p.id AND ( r.id = (SELECT MAX(r2.id) from page_revision as r2 WHERE r2.pageId = r.pageId AND r2.status = 'active') OR r.id = (SELECT MAX(r2.id) from page_revision as r2 WHERE r2.pageId = r.pageId) ) Which...
What is the equivalent of SQLServer function SCOPE_IDENTITY() in mySQL? ...
lets assume the same environments for php5 working with MySQL5 and CSV files. MySQL is on the same host as hosted scripts. Will the MySQL always faster than retriving/searching/changing/adding/deleting records to CSV ? Or is there some amount of data below which php+CSV performance is better than using database server ? ...
Given the following table: Table events id start_time end_time Is there a way to quickly search for a constant? E.g. SELECT * FROM events WHERE start_time<='2009-02-18 16:27:12' AND end_time>='2009-02-18 16:27:12' I am using MySQL. Having an index on either field still has to check a range. Moreover an index on both fields wi...
What does index keyword mean and what function it serves? I understand that it is meant to speed up querying, but I am not very sure how this can be done. When how to choose the column to be indexed? A sample of index usage is shown below in create table query: CREATE TABLE `blog_comment` ( `id` INTEGER NOT NULL AUTO_INCREMENT, ...
I want to update a table value on Mysql 5 but if the key does not exist create it. The way I found to do it is by: INSERT yyy ON DUPLICATE KEY UPDATE field; The question is : is the format above less efficient than other ways to do it (As the insert will happen only once and update will happen very often)? for example: $result = UP...
Okay, so im sure plenty of you have built crazy database intensive pages... I am building a page that I'd like to pull all sorts of different / unrelated databse information from....here are some sample different queries for this one page: -article content and info -IF the author is a registered user, their info -UPDATE the article's...
Hi everybody! Here's my problem: I have 3 MySql tables representing: photos a user post, videos a user post, comments a user post and I need to view the 10 (20, 30, 40...) most recent activities by the user. For example in photos table may be composed by: user_id | photo_id | photo_path | photo_name | date_added 5 | 18 ...