MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
184 views

Is it better to put filters on JOIN clauses instead of WHERE clauses

MySQL 5.5.8: Question 1: Is there any difference in the following statements and if so, which is preferred performance wise? Can this even be answered without knowing the row counts of the tables ...
0
votes
1answer
239 views

MySQL Master Slave Replication and backup

I would like to implement the following backup solution as highlgihted on the mysql site: To use replication as a backup solution, replicate data from the master to a slave, and then back up the ...
0
votes
0answers
271 views

zero rows fetched, selected, or processed Mysql

Help me to solve this problem error call cd3(@VAR) 0 row(s) affected, 1 warning(s): 1329 No data - zero rows fetched, selected, or processed 0.000 sec delimiter ;; create procedure cd3(out var ...
1
vote
1answer
125 views

what is the structure of a Mysql multi-column index? [closed]

For example: CREATE TABLE test( col_1 INT, col_2 INT, col_3 VARCHAR(11), col_4 VARCHAR(11) )ENGINE = INNODB; CREATE INDEX mul ON test(col_2,col_3,col_4); Sorry,my English is not good,I mean ...
2
votes
1answer
662 views

mysql “Lock wait timeout exceeded” two transaction with different keys indexed

I have set of tables that I need to delete their records with a specific key and insert new records. Different transactions are used one for each key: Transaction 1: DELETE FROM table1 WHERE id=1; ...
1
vote
1answer
71 views

DROP TABLE on a huge InnoDB table

I have an innodb table with over 140 million rows taking 26GB. I would like to drop this table however this is taking way too long, probably days. How can I speed up this query? processor: Intel® ...
0
votes
1answer
126 views

When and how would MyISAM concurrent insert = always be a problem?

I wish to set concurrent_insert to always on a MyISAM log table, so that inserts wouldn't have to wait for selects to perform when there are some holes in the table. We're talking about 10-20 inserts ...
0
votes
1answer
414 views

Datetime Difference in MySQL when format is Varchar

I have two columns in a table: STOP and START, of type varchar. I want to have the difference between both columns, and I'm using : SELECT DATEDIFF(stop, start) AS difference from T1; It is ...
0
votes
0answers
18 views

Mysql craches ! InnoDB [duplicate]

The full mysql error log, the VPS craches almost every day! I need to add more info but I can't so this is the only info I have : 131117 20:08:47 [Note] Plugin 'FEDERATED' is disabled. 131117 ...
0
votes
1answer
105 views

Update , Select and Join using 3 tables

There are 3 tables: Label (Label_ID,Label_Name), Label_ID is Primary Key. Tracks (Prefix, Label_ID), Prefix is Primary Key,Label_ID is empty column Top100 (Prefix,Label) No keys in this table. My ...
1
vote
1answer
313 views

Mysql update different fileds set depend on condition

I have two tables "a" : CREATE TABLE `a` ( `id` int(11) NOT NULL AUTO_INCREMENT, `i1` int(11) DEFAULT NULL, `i2` int(11) DEFAULT NULL, `ib` int(11) DEFAULT NULL, KEY `id` (`id`) ) ...
1
vote
1answer
64 views

When is the index actually created?

I have noticed that even on huge tables with millions of records when I do a CREATE INDEX the index is created immediately (I get the console back in nsecs). Why is this? Doesn't it create the B-Tree ...
0
votes
1answer
89 views

is keeping idle / unused mysql databases bad?

Is there any harm like eating up resources etc. Or is mySQL engineered to just work for the used ones and keep others at hand. If so, why is there such thing like taking a db offline? Should I do ...
0
votes
2answers
81 views

Where/how does innodb store tables in files?

I read somewhere that InnoDB can store all the tables in a single file or in separate files. Is this a configuration option? What is the default setting? A file for all tables or a file per table? ...
1
vote
1answer
202 views

Debugging error report percona 5.6

I'm running percona 5.6 on a new centos 6.4 server. Things seem to work OK. I personally only really use InnoDB tables for my databases, but only the standard 'mysql' table is in MyISAM format. I ...
1
vote
1answer
62 views

XOR binary values greater than 64bit

I have a binary blob holding the results of a SHA256 hash that I XOR with a script generated SHA256 hash. For testing I am using the values usertokens and methodtoken for the test hashes. hash1: ...
0
votes
0answers
138 views

SQL queries are very slow

I have noticed that my queries are getting slower. Some update queries take about 48-50 seconds. Here's how it looks: [root@db-1 ~]# tail /var/lib/mysql/mysql-slow.log # Time: 131113 17:09:48 # ...
0
votes
1answer
268 views

How to copy a table in a production server without WRITE lock and without writing to a file

I'm trying to copy a table in a production table. When I'm performing on InnoDB tables: INSERT INTO new_table (SELECT * FROM table) I have a write lock on table. Is it possible to avoid this lock? ...
1
vote
1answer
171 views

How to recover from two queries stuck in kill state causing all the database to not respond? [closed]

During a INSERT INTO new_table (f1, f2, ..., fn) (SELECT f1, f2, ..., fn FROM table) I clicked the new_table in php-myadmin which ran a simple SELECT * FROM new_table LIMIT 0, 30 which ofcourse ...
1
vote
2answers
4k views

MySQL Update Trigger that updates multiple columns from values in another table

I have a table PRODUCTS with columns id, name, sku, packaging_type, width, height, weight, case_count. I want a trigger to fire when packaging_type is updated. packaging_type will be an integer ...
1
vote
2answers
4k views

Mysqld.exe memory usage

I am using mysql 5.6.14, For a long time i got large memory usage by mysqld.exe, i was trying many different solutions and my.ini settings to fix this up but didn't succeed, This is my config ...
1
vote
0answers
51 views

Help with Mysql with 100K+ Table

Hi im trying to develop a database design in which its gonna have 100K+ tables residing in it. My major concern here is will Mysql be able to hold that much amount of tables with no performance ...
0
votes
1answer
294 views

MySQL: Error while reverse engineering database from a sql file

I'm trying to reverse engineer a DB in MySql with a sql file generated from an OpenOffice database. while trying to import and run the sql file, I get the following error: ERROR: Line 1: syntax ...
0
votes
2answers
670 views

MySQL getting result using 3 Tables and case statements

I have 3 tables named person,person1, person2. All the 3 table has 2 columns name and phno. Now my requirement is, if i give any particular phno it has to show either yes or no for the corresponding ...
0
votes
0answers
888 views

Database design to track inventory?

Would this be a good starting point for tracking inventory with several warehouses? If yes what is the best approach to sum quantity when displaying stock by warehouse/type/general? mysql structure ...
2
votes
1answer
174 views

Implications of a second index on a timestamp field in a sequential table

I have a MySQL table of a few gigabytes with roughly ~100M rows. I store data sequentially, such that the timestamps increase as the ID also increases. Because ordering and filtering by the timestamp ...
2
votes
1answer
398 views

MySQL InnoDB issue not using indexes correctly [Percona 5.6]

I just installed Percona 5.6 on my new CentOS 6.4 server. It's a fast machine 32 core xenon, 72GB ram, 8x SAS RAID 10 setup. So far so good My old server is a bit less powerful, and was running ...
1
vote
2answers
165 views

MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant ...
-1
votes
1answer
69 views

how to update multiple records for single user in database? [closed]

I want to update multiple roles for user when admin update user roles I have table as follow user_roles username || role_name user1 | role1 user2 | role2 users username || ...
0
votes
1answer
300 views

How can I keep two databases on different sites in sync?

I'm working on a web application that exists at two sites. Each system is hosted on it's own LAMP stack, with access restricted to specific users on the network. When content is updated via user ...
0
votes
1answer
2k views

Slow MySql Server Performance - What and How to check?

start:disclaimer I'm not a MySql Server DBA; I know mostly of MSSQL - which is why i need your help end:disclaimer I've been asked to check why the MySql server engine is performing poorly - i have ...
0
votes
2answers
6k views

Xampp 3.2.1 error: MySQL shut down unexpectedly

I logged into Xampp today and it wouldn't launch MySQL. It said there was an error and it shut down unexpectedly. Here are the errors from the error log. Hoping someone has seen this before, ...
0
votes
2answers
86 views

Finding duplicates

The scenario is as follows, a while ago a colleague accidentally ran a query similar to DELETE FROM app_i18n WHERE disabled = 0 and translation is null or translation = ''; This is the table ...
0
votes
0answers
51 views

can we use MySQL for a large data reporting application?

We are working on a web application with a big database, that's growing exponentially. Since we start the dev 4 years ago, it is difficult to change the database to a new architecture, actually we ...
4
votes
2answers
12k views

MySQL creates temporary tables on disk. How do I stop it?

We are running a site (Moodle) that the users currently find slow. I think I have tracked down the problem to MySQL creating temporary tables on disk. I watch the variable created_tmp_disk_tables in ...
0
votes
2answers
177 views

avoiding unique key collisions across tables

This might be related to the idea of data warehousing, but i'm not sure, though it is extreme theory. We have a table where records get written to first, let's call it tbl_recording which looks like: ...
0
votes
1answer
108 views

Are triggers bad for created/timestamp columns?

We are in need of some better auditing on some of our MySQL tables. Specifically, we'd like to store when the row was inserted and when it was last updated. Originally, I thought DEFAULT ...
0
votes
2answers
69 views

Add column on huge InnoDB table in MySQL 4.1

I need to add a column on a huge InnoDB table (200GB) which resides on MySQL 4.1.21 (SunOS). CPU has 16GB RAM and 200GB of more disk space. Is there any quick way of adding this rather than ALTER ...
0
votes
3answers
1k views

Running an “ORDER BY CASE” query on MySQL view breaks it afterwards

Whenever I create a MySQL view and then run a query ordering the results with ORDER BY CASE WHEN (...), the view becomes unusable. The newly created view works properly and all SQL is correct. This ...
0
votes
2answers
81 views

high traffic mysql hosts

I'm wondering if any of you sql buffs out there have any suggestions for a high speed mySQL server. I need something that can handle roughly 33 updates and 33 queries per second, per user. and for ...
0
votes
1answer
457 views

MySQL: Filter results by joining a vertical table

Situation: I've got a table users and a table preferences along with some others. The table users is aligned horizontally (id, username,...) whereas the table preferences is aligned vertically ...
0
votes
1answer
44 views

Execution plan for select with empty value using wildcards

Short question: with this: foo like '%%', MySQL go through all records or through none? If you are thinking why i need this, it's because i'm concatenating a search string and if this have no ...
0
votes
1answer
95 views

Combine results from two queries?

I need to combine the results from two queries into one result set and order them altogether. This is the working, raw SQL code I have: SELECT * FROM ( SELECT t1.id AS entity_id, ...
0
votes
1answer
33 views

Sorting by a field in child table

There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract). contractupdate also has DATE field startdate and TINYINT field day. I ...
0
votes
1answer
70 views

How to check how the table structure effects the performance of my query

I want to check how the table structure of a certain table effects the performance of a delete query. This is not a generalised question, I have a table, with currently 2.2 million records ...
0
votes
1answer
345 views

Getting the top count of records from a table for each month and user

I have a table where translations are stored along with the user_id and when the translation was added. I want to create a query that shows the 'Translator of the month' (the person who did the most ...
0
votes
1answer
69 views

Are databases only growing in size?

Databases use the file system to store the data. As far as I know it is not possible to delete a record from a random access file. So does that mean that when we do a DELETE FROM TABLE the size of the ...
3
votes
2answers
226 views

Make MySQL server terminate when shell exits or on ctrl-c

If I run mysqld, ie invoke it directly on the command-line, it survives the shell being closed or hitting ctrl-c. Is there any way to run it "interactively", ie so that it will shut down if either of ...
0
votes
0answers
38 views

MySql Report Using CASE THEN WHEN

I am getting a syntax error on the code below. I wish to substitute the report text for each column clarifying the its meaning. SELECT CONCAT(LastName, ', ', FirstName) AS "System Administrator", ...
0
votes
2answers
82 views

Stored Procedure - MySQL

I am developing a database for an application. I have to create stored procedure to tables, so that programmers can simply call the stored procedure to implement the functions. I have a doubt to ...