mysql

MySQL efficient select / order on a range query with multiple / single column indices

I'm trying to do the most efficient select on a table with 3 million records. First some detailed info Table: CREATE TABLE IF NOT EXISTS `activities_index` ( `id` int(9) NOT NULL auto_increment, `activity_id` int(6) NOT NULL, `activity_status_id` int(2) NOT NULL, `activity_source_id` int(6) default NULL, `account_id` int(6) ...

Php/MySql Help needed

I am very new to php and MySql, but I am writing a project for school on ticket reservation. I need help with: creating a table in a db that has specified number of rows and the rows increase by mulitples of the specified number (Example: say I have a table for Bus A that has 15 seats so I want d db table to contain 15 rows but has the...

MySQL Connection Problem

Hi, I create web site and I use MYSQL database by MySQL Connection 5.1; Sometimes my web site freezing; usually in the login page; I cilck the login button and I wait 10 minutes but I can not login. After I restart the IIS, Everthing works fine. After 30 or 40 times loging the page , again page freezing. I wait again. After I restart ...

Mysql Update Field Contents

I am currently trying to edit my db named boh. The current table "files" has a field called "path". Inside the path field is an actualpath to files listed in a folder, syntax "F:\xxx\xxx\xxx\filename.xxx". How do I update the field information to replace the "F:\xxx\xxx\xxx" so that just the file name exists? ...

How do I avoid a full table scan with this basic inner join?

I have a table that has a foreign key to a table that stores some blob data. When I do an inner join on the tables with a condition on the main table the join type goes from 'index' to 'ALL'. I would like to avoid this as my blob table is on the order of tens of gigabytes. How can I avoid it? Here is the the basic inner join: EXPLAIN ...

Mysql Join, PHP

Just before i put the line of code in let me explain what i am trying to achieve. Id like to query a table where a specific page has a specific parent_id. Once i have all the results i would then like to take the id of the page which has this specific parent_id and fetch all its relative page parts from another table. Then i simpl...

How to join MySQL tables?

I basically have two tables: A(id int, name varchar(10), info varchar(10)) B(id int, item varchar(10)) A 1 A Hello 2 B World 3 C Foo B 1 apple 1 orange 1 hammer 2 glass 2 cup Note that C doesn't have any items. I want to join the two tables to get this: 1 A Hello apple|orange|hammer 2 B World glass|cup 3 C Foo null I am familiar ...

how to insert a record or update if it already exists?

Hi, I have a table with columns record_id, sender, sent_time, status and in case there isn't any record of a particular sender, for example sender1, I have to insert a new record otherwise I have to update the existing record which belongs to user1. So if there isn't any record already stored, I would execute INSERT INTO messages (se...

Using Stored Procedures in Rails

As I said in a previous post, our Rails app has to interface with an E-A-V type of table in a third-party application that we're pulling data from. I had created a View to make the data normal but it is taking way too long to run. We had one of our offshore PHP developers create a stored procedure to help speed it up. Now we run into ...

How to get the value of last mysql fetch data? PHP-MySQL

I want to do this: Check the top 10 values of points. Here is my condition: If there are less than 10 records (rows) found, e.g give bonus If the points is in top ten (among hundreds records/rows), give bonus. So, what i do is (wrong method): SELECT points FROM `scores` WHERE id = '1' ORDER BY score DESC LIMIT 9 , 1 That will on...

Should I break a larger mysql table into multiple?

I have a pretty large social network type site I have working on for about 2 years (high traffic and 100's of files) I have been experimenting for the last couple years with tweaking things for max performance for the traffic and I have learned a lot. Now I have a huge task, I am planning to completely re-code my social network so I am...

index on a float field seems to be ignored

I have a titles table of about 14000 records, with a float field average_rating, which is indexed. But when I try to get the first 48 records with the highest average_rating, the index doesn't do any work. What am I doing wrong? mysql> explain SELECT * FROM `titles` WHERE (average_rating is not null) \ ORDER BY average_rating desc LIMIT...

What should I do to make mysql 100% optimal?

Hi, Recently I've been doing quite a big project with php + mysql. And now I'm concerned about my mysql. What should I do to make my mysql as optimal as possible? Tell everything you know, I'll be really very grateful. Second question, I use one mysql query per page load which takes information from mysql. It's quite a big query, becau...

Why doesn't this if test work?

My site requires a login that establishes permissions based on the user. In the photo listing of the site, I must determine if a particular photo will be displayed if the user has guest access only. So I thought that this if else statement would work: if (!($mysql_row['guest_access'] == 'NO') AND ($_SESSION['user_level'] == 'GUEST')) { ...

Dashboard Application

I have a simple task that I feel there has to be an app out there for (or is easy to build or extend an open-source version). I need to run a mysql query repeatedly and look for changes in the results between runs (the data is coming in in real time). I have built several of these queries and throughout the day find myself jumping betw...

Why FULLTEXT index is not being populated - MYSQL

I am trying to add a fulltext index against a table, but it does not seem to apply. ADD FULLTEXT `genre` ( `genre` ) The query executes, but the cardinality remains at just 1 (even though i ahve a few thousand records) I tried ANALYZE, REPAIR and neither seemed to update the index. If I create a regular index the cardinality is upda...

Aggregrate all a single column in a grouped resultset in one query: is it possible?

At the moment I have the following MySQL query: SELECT COUNT(*) AS `count`, `v`.`value` FROM `client_entity_int` AS `v` INNER JOIN `client_entity` AS `e` ON e.id = v.entity_id WHERE (v.attribute_id = '1') AND (e.deleted = 0) GROUP BY `v`.`value` Which returns the following: +-------+-------+ | count | value | +-------+...

Mysql Constraign Database Entries in Rails

I am using Mysql 5 and Ruby On Rails 2.3.1. I have some validations that occasionally fail to prevent duplicate items being saved to my database. Is it possible at the database level to restrict a duplicate entry to be created based on certain parameters? I am saving emails to a database, and don't want to save a duplicate subject line...

What is mysql indexes?

I don't get what is that? It's id which auto increase number? ...

Error Executing Database Query. MySql, Dreamweaver and Coldfusion.

I am trying to insert data into my database, but keep getting error 1064. Which goes like this: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Add, Username, TalkID) VALUES ( 'trial' , 'andy' , 2 )' at line 1 The error occurred in C:\ColdFusion8\www...