mysql

MySQL Optimization 20 gig table

I have a 20 gig table that has a large amount of inserts and updates daily. This table is also frequently searched. I'd like to know if the MySQL indices can become fragmented and perhaps need to be rebuilt or something similar. I'm finding it difficult to figure out which of the CHECK TABLE, REPAIR TABLE or something similar? Any gui...

Php, mysql selection

I have this table ATTRIBUTE id name um 12 capacity MB;GB;TB And this table2 : id id_attribute id_product name value um 1 12 40 hdd maxtor 30 GB 2 12 41 hdd maxtor 40 GB 3 12 42 hdd y 1 TB How can i...

how to send multiple row to mysql at same time(like bulk copy)?

I am using asp.net + Mysql. I have multiple rows in my dataset; I have to store the rows in my database table. How to do that? ...

How to: database versioning with maven2?

I am finding any maven plugin for versioning database changes. ...

PHP Variable with Array

Hi guys, i'm trying to make a "update user's power" page. It is something similar to those you can find in say, invisionfree forums. I need it to generate a list of members with checkbox [done] Add an option for it [done] What i don't know how to do is to update, to say, give all the selected users the selected power. Then i went sear...

Basic Question : LEFT OUTER JOIN vs Left Join

What is the difference between between Left Join and Left Outer Join? ...

how to use index on a table used for searching using regex or like '%example%'

Dear all, I want to use index on table where i m searching a string using regex '^searchkeywordname$|searchkeywordname' ,this is scanning whole table .how can i retrieve fast result using index or sumthing ...

sql clarification

Can anyone please clarify what this query will return ? SELECT TestCase FROM MyTable WHERE Verdict = 'PASS' AND StartTime > DATE_SUB(NOW(), INTERVAL 2 MONTH) ...

MySQL query to return most proposed books in a month

Hello guys, I have the following MySQL table named "proposals": proposal_id proposal_user (int proposal_book (int) proposal_date (Y-m-d) Users are going to propose books each month so there will be like 50-100 books per month. I would like to know if there's a way of writing a query that can return the most proposed books for a give...

what happens if LIMIT or OFFSET is set to 0?

is 0 the default for them? ...

Cannot mySQL share a foreign key between tables?

Hi everyone, I get error 1005 when inserting my tables. There's multiple tables referencing to the tables in my design made up in mySQL workbench, but doesn't this work? - a shared foreign contraint among several tables? Table one is called languages. It has a id column for each language in the database. Then we have productDescription...

Checking if MySQL Database data does not exist

I have my songs set-up in my MySQL database. Each song is is either assigned multiple locations or has no locations at all. Only the songs that either have no locations assigned in the database or have the location assigned to the ones specified below should be pulled from the database. Hopefully when you understand my query below it ...

MySQL escape string help

I have a pretty large insert statement something like INSERT INTO multimedia (filename, regex, flag) VALUES (('adsfavr.jpg', '<div id="title">', 0), (...), (...)); How do I prepare the query for MySQL.It's too long to do it manually. It includes double quotes so I can't use the php function mysql_real_escape_string() ...

MySQL foreign key constraint disappearing

This is my table: /* oefenreeks leerplan */ CREATE TABLE leerplan_oefenreeks ( leerplan_oefenreeks_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL, leerplan_id INT NOT NULL, oefenreeks_id INT NOT NULL, plaats INT NOT NULL ); /* fk */ ALTER TABLE leerplan_oefenreeks ADD CONSTRAINT fk_l...

MySQL: How to add one day to datetime field in query

Hello, In my table I have a field named eventdate in datetime format like 2010-05-11 00:00:00. How do i make a query so that it adds one day to the eventdate eg if today is 2010-05-11, i want to show in where clause to return all records with tomorrow's date. Update: I tried this: select * from fab_scheduler where custid = 133466605...

mysql IF EXISTS

I have this table UMS id_attribute value order 1 MB 1 1 Gb 2 1 TB 3 ... and this table ATTRIBUTE_VALUE id id_attribute value name ums 1 1 50 hdd GB 2 1 100 hdd TB 3 2 ...

How to create a chart from mysql data? (using Google visualisation api)

Hello, I have some data and want to create some dynamic charts. I have looked on Google visualisation api .. It looks great but the problem is I am not very familiar with it. Any ideas, how I can set the data.setValue from mysql data. <script type='text/javascript'> google.load('visualization', '1', {'packages': ['geomap']}); ...

mysql query clarification

I have a query which I am wondering if the result I am getting is the one that I am expecting. The table structure goes like this : Table : results ID TestCase Set Analyzed Verdict StartTime Platform 1 1010101 ros2 false fail 18/04/2010 20:23:44 P1 2 1010101 ros3 false fail 19/04/2010 22:22:33 P...

MySQL bulk insert from CSV data files

I have some CSV data files that I want to import into mySQL. I would like to do the insert in a shell script, so that it can be automated. However, I am a bit weary of having the username and password in clear text in the script I have the following questions: I am uncomfortable with the idea of a uname/pwd in clear text in the scrip...

Which format is the best for mysql data export?

The best I mean the smallest file size. ...