mysql

"Specified key was too long; max key length is 1000 bytes"

I can't create index on varchar(500). Mysql:"Specified key was too long; max key length is 1000 bytes" ...

Most Efficient MySQL query to update a table from identical memory table

I'm implementing a memory cache for a table that looks like this (simplified): Item1 (integer), Item2 (integer), cnt (Integer) The original table includes millions of pairs like this. and it updates rapidly. To make it all more efficient I want to write new pairs to an identical memory table and update the real table on disk periodica...

Duplicate entry error in MySQL/Hibernate

Hi all, I am facing the following problem and can't find a proper solution. I have a relation A with the attributes id, x (foreign key), y (sequence number) and z (content). Further there is a uniqueness constraint on x-y. That is usually I have tuples like (455, 159, 1, ...), (456, 159, 2, ...), (457, 159, 3, ...), etc. I'm using Hiber...

How much more performant is Postgres than MYSQL on fulltext search?

I've been a MYSQL user,never tried Postgres . But MYSQL has bottle neck on fulltext search when the data set is huge. ...

mysql select into outfile /tmp no output

Hello, I cannot get the following code to generate any output. The MySQL user has "all" grant level, /tmp is writable, the query returns a results set. mysql> SELECT field FROM test_table WHERE condition='test' -> INTO OUTFILE '/tmp/test.csv' -> FIELDS TERMINATED BY ',' -> ENCLOSED BY '"' -> LINES TERMINATED BY '\n'; ...

Error in MySQL update command. (in php)

Good Morning everyone, I am using an update command in php to update data in mysql. This is my code: $sql=mysql_query("UPDATE blpublication SET JournalName = '$_POST[journal]', AcceptanceDate = '$_POST[acceptancedate]', PublishedDate = '$_POST[publisheddate]', Comment = '$_POST[comment]' WHERE JobNo = '$_POST[jobno]'"); if (!mysql_que...

Is there a way to get field => value pairs from a subselect in MySQL?

I'm considering how to best implement an Audit Trail in an app, which is backed by MySQL. A few systems access the same database and tables, so I thought triggers would be the best solution. However, I don't want to manually write a trigger for every table, so I'm looking to automatically log the field name and data for each insert/dele...

What good software or scripts are available for managing users and subscriptions on our website?

hi all, Ok so it's not exactly a programing question but does anyone know or have experience with looking for a system for managing users on a website we are building? what is the shortlist of good feature rich secure solutions. we need Php and mysql integration and payment support for main credit cards. We will also want to be able to ...

MySQL Full-text search in Ruby on Rails

I am trying to implement a basic full-text search with MySQL. I wrote this migration: def self.up execute 'ALTER TABLE photos ENGINE = MyISAM' execute 'CREATE FULLTEXT INDEX fulltext_photos ON photos (place, info)' end def self.down execute 'ALTER TABLE photos ENGINE = InnoDB' execute 'DROP INDEX fulltext_photos ON photos' en...

SQL: Help me optimize my SQL

Hi I'm looking to optimize my SQL. My database schema is: HOMES home_id address city state zip primary_photo_group_id HOME_PHOTOS photo_id (primary key) home_id (home primary key) photo_group_id (a photo group is the same image, resize from thumbnail to large size) home_photo_type_id (the size of the image be it a thumbnail or a...

Copy MySQL table using backup

I want to copy the contents of a hot table to another table on the same database. I don't want to rename the original or delete it. One option is to create a new table LIKE the current one and then INSERT INTO... SELECT. However, I think it might be fastest to create a backup of my table using mysqlhotcopy and then load this into the s...

why FK constraint does not work in MySql

I have created two tables ORDERS and ORDERITEMS with the following constraint: alter table OrderItems add constraint FK_Reference_30 foreign key (orderId) references Orders (orderId) on delete restrict on update restrict; If I want delete one entry in ORDERS table and that orderId is used in ORDERITEMS table, I should get error or w...

how to enable innodb in Noinstall Zip Archive MySQL version

I want to enable innodb in MySQL. I am using Noinstall Zip Archive with the following command to install it: mysqld-nt --install. I tried adding the --innodb at the end but seems it doesn't work. I have enabled all commented out partial related to innodb in my.ini file. please help me with step by step instructions. thanks, ...

Any way to optimize this mysql query?

This is the query. Im mostly interested if there is a better way to grab the stuff I use GROUP_CONCAT for, or if thats a fairy good way of grabbing this data. I then explode it, and put the ids/names into an array, and then use a for loop to echo them out. SELECT mov_id, mov_title, GROUP_CONCAT(DISTINCT categories.cat_nam...

I want to do INSERT without record in DB

Attempted translation of the above question from non-native English to English: This is a question about the fastest method to use when inserting a value into a database when the value may already be in the database. INSERT will fail when the value is there and UPDATE will fail when it is not. Which of these choices is the most desirab...

Inserting distinct entries into the database

I have two tables with exactly the same fields. Table A contains 7160 records and table B 7130 records.Now I want to insert distinct records from table A into table B such that B should not have any duplicate entry in it. How should I go about doing this? ...

Batch insertion of data to MySQL database using php

I have a thousands of data parsed from huge XML to be inserted into database table using PHP and MySQL. My Problem is it takes too long to insert all the data into table. Is there a way that my data are split into smaller group so that the process of insertion is by group? How can set up a script that will process the data by 100 for ex...

Mysql Join with limit?

I have a table with category, product and count. All integers. I'm looking for the most efficient query that will give me the top 10 products (highest count) for each category. I've tried several subselects and joins but couldn't figure out how to do it in a single query. Thanks for your help. ...

Is it sensible to connect a desktop client directly to MySQL?

I'm writing a Java desktop client application that retrieves data from a remote MySQL server. For development purposes I have had it connecting directly to the MySQL server (i.e. with DriverManager.getConnection(databaseURL) etc.), but have been intending to move to using a web service (once that'd been built). My question is whether I...

Mysql Duplicate entry error on ON DUPLICATE KEY UPDATE

I'm using a memory table. It has several ids and counter all data is integers. My code updates the counter by 1 if the data exists or creates a line with counter=1 if not. The query I use is: INSERT INTO linked_mem ( id1, id2, id31, id4 cnt) VALUES (31316, 0, 557158967, 261470594, 1) ON DUPLICATE KEY UPDATE cnt= cnt+1 ...