mysql

Is it possible to drop a primary key index in MySQL?

Is it possible to drop a primary key index in MySQL (version 5.1.47). This is to optimize the insert performance. ...

MYSQL slow queries with PHP

Hello. I'm using mysql and php for registration on my web-site. Registration is ok. Mysql do queries immediately. But in login there strange things begin to happen. I insert a test in my php code to insert test row to database. First test code inserted immediately, but 2nd was inserted after series of refresh and relog actions only after...

MySQL's ibdata file is getting big and the size doesn't reflect how much data I have.

The size of MySQL ibdata is 4GB, but I don't think the data I have should take that much disk space. I am using MySQL InnoDB storage engine. Am I doing something wrong with configuration? How do I reclaim the disk space because deleting rows didn't help at all? Thanks ...

php, MySQL and variables containing å, ä and ö escape problem

Hi. I'm having some problem with my SQL syntax/escaping variables on my LAMP server. The command I want to use is the following: $sql=mysql_query("INSERT INTO '$table' (FirstName, LastName, StartDate, TimeStroke, DueDate, Duration, Price, Retailer, Checksum) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[startdate]','$_POST[tim...

Mysql / Ruby Sequel last insert ID value, what method?

I just want to get the last_insert_id() using Ruby's Sequel: insertret = @con.run("INSERT INTO `wv_persons` ( `id` ) VALUES ( NULL )") pp insertret.inspect # returns "nil", expected that.. last_insert_id = @con.run("SELECT LAST_INSERT_ID() AS last_id;") pp last_insert_id.inspect # returns "nil", should be an ID The SELECT query should ...

Warning: mysql_query(): 10 is not a valid MySQL-Link resource

I got "Warning: mysql_query(): 10 is not a valid MySQL-Link resource" referring to a line in my PHP script where I executed a query using "mysql_query($query, $link_identifier)" ...

mysql/php insert/update on duplicate key

I have a product info table with more than 130 columns/fields. I want to write a php script that adds a new product to the table OR updates the existing product if it already exist. The first field is the product key. The product information is stored in a numbered php array : $product_info[0] to $product_info[130]. Basically somethin...

Optimization SQL Query For Analytics

I have implemented analytics system which is now performing very poorly. To explain it I need to explain table structure queries I have two innodb tables Table1: Contains records about hourly stats (stats_id, file_id, time) Table2: Contains over 8 million rows. Table 2 structure is full_stats ( stats_id Int file_id Int s...

MySQl : How do I use Count on a Sub-Query?

I've got a MySQL statement that selects a name and also makes a ranking. SELECT t.name, (SELECT COUNT(*) FROM my_table1 z WHERE z.type LIKE '%Blue%' AND t.type LIKE '%Blue%' AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.sco...

Using concat in clause MATCH AGAINST - mysql

I really hope that someone can help me with my problem. I would like to realize a match against query with group_concat. I need something like this: Select c.id, p.place from content c join place p on p.object_id = c.id where match(group_concat(p.place)) AGAINST('"string1" "string2" "string3"', IN BOOLEAN MODE) and not ma...

CodeIgniter, active records, JOIN help?

I am trying to figure out why my JOIN is not working on a database query, I have two tables with a user_ID column, but when I return this it returns everything, not just the selected ones per user. What am I messing up any thoughts? Thank you! function user_apps() { $this->db->select('*'); $this->db->from('apps'); $this->db...

PHP & MySQL question.

Is there a tutorial or script that can show me how to build a form that allows me to add categories, sub categories and sub sub sub categories and so on to my web page using PHP & MySQL? ...

Group-wise-max - hierarchical data query help

Hi, the first part of this problem was solved by some good help on here yesterday, but I've been struggling today to complete the query I need. I am trying to pull multiple columns from 5 joined tables based on some conditions, but I want the result set to contain only one distinct "data entry" per p.id (the pet id which is a foreign key...

Problems with serialize() and unserialize() - inserting and selecting data PHP MySQL

Hello I am attempting to grab a date supplied via POST, then generate a list of dates over a 12 week period from the supplied start date. These dates would then go into the DB and a 12 week schedule would be output, which the user can interact with (add/edit/delete). I am successfully taking the start date, generating the 12 week date...

mysqli persistant connection

In short, is there some sort of mysqli_pconnect for high-usage PHP & MySQL servers, or do I need to stick with mysql unimproved? And if so, why did they remove it? ...

How to Change Counter position for SQL field

Hi, I have a field called IntQID, which is an id for each post that is recorded in the SQL database. If a post is 1533, the next entery will be 1534,1535, etc... I recently messed up and inserted a manual entry with an ID of 4000. Now the counter has been using that as the last entry, so now new posted are 4001, 4002, 4003. How do I ch...

php: Creating automatic database sharding logic?

Hi everybody, I did just come up with the following idea, but I lack the knowledge to say if it is applicable to a production application. We have a web application, built on PHP/mySQL to make it easy. A table in the database are prone to grow large - a couple million of records easily, so table sharding might be an option here. Here...

Format date in MySQL

All, I have a MYSQL table that has a column "DT_Created" defined with timestamp datatype. I would like to write a select query to retrieve the timestamp in the following fashion: Sat, MM/DD/YYYY HH:MM:SS AM CST Lengthy Explanation: Day, Month/Date/4 Digit Year Hours:Minutes:Seconds AM or PM TimeZone How can i do that? Thanks ...

Would having x number of UPDATE queries in a MySQL transaction be worse or about the same as putting them into one UPDATE query?

This may be a silly question with an obvious answer, but I've pondered it for quite some time and can't really come up with a good answer on my own. I'm working on a telecommunications website and I've made a mapper framework and query builder of which I'm rather proud. The mapper has a set function (generates an UPDATE query) to update...

How to find repeated Rows?

Hi Friends i am having 75000 row records i need to get how many of them repeated,how can i do that in MySQL Query? ...