All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

0
votes
2answers
1k views

Keep total transaction in invoice application

I want to make an application called as Invoice Application with MySQL database. Now I have designed this database. Now I want to know is this database good for Invoice Application. I will use MySQL ...
3
votes
2answers
9k views

How to make a trigger that will compare input with a value of other table?

I had made two tables with the following fields: TABLE 1: "Bookings" FIELD: "fk_flight_number" FIELD: "date_of_reservation" TABLE 2: "Flights" FIELD: "flight_number" FIELD: ...
1
vote
1answer
2k views

How to know the current log sequence number in 5.0.X

I would like to test the optimal value for iblog files on my mysql which is 5.0.77 I found the below procedure in mysqlperformance blog: ...
1
vote
1answer
341 views

Change mysql storage engine from Innodb to MyIsam: Max locks exceeded error

I have about 5 million records in a table which uses Innodb as storage engine. I have about 6000 users who use (do transactions) the same table. It sometimes becomes very slow or hangs. I decided to ...
9
votes
3answers
21k views

What is the benefit of using BOOLEAN over TINYINT(1)?

From MySQL manual, it says: BOOL, BOOLEAN These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true: I created a BOOLEAN column with ...
4
votes
1answer
1k views

Is it possible for a MySQL database to have duplicate tables with the same name?

As in the question title - is it possible for a MySQL database to have duplicate tables with the same name? Is there a directive that can be used for a database to allow this? Normally, when trying ...
3
votes
2answers
840 views

Redirecting MySQL errors and slow logs into another database

I am running a number mysql servers as slaves and masters. It is quite annoying to look for slow logs and errors in every server, so I would like to log all the errors into external database. DB1 ...
3
votes
2answers
9k views

GROUP BY two columns

I want to count two columns, but need to get the results of first column in one row. SELECT country, COUNT(*) FROM table1 GROUP BY country, type This query gives me country type COUNT(*) ...
2
votes
2answers
9k views

Mysql replication and ignore tables

When setting up master/slave replication in Mysql, do you then need to ignore any tables in /etc/mysql/my.cnf or should replicate_wild_ignore_table be leaved out under normal circumstances? This is ...
3
votes
4answers
7k views

mysql : inner join takes 3 minutes

I had a similar question open on SO for postgres - now having the same issue with mysql.. I have two tables - Table A : 1MM rows, AsOfDate, Id, BId (foreign key to table B) Table B : 50k rows, Id, ...
1
vote
0answers
85 views

Mysql: Large Query with subqueries optimization

Any query optimization gurus in the house?? Anyway, I am trying to attack this on two fronts, one being to code a way around it but secondly, I'm thinking some real optimization might help in the ...
1
vote
2answers
533 views

Mysql: Large Query with subqueries optimization

Any query optimization gurus in the house?? Anyway, I trying to attach this on two fronts, one I'm trying to code a way around it but secondly, I'm thinking some real optimization might help in the ...
1
vote
2answers
1k views

MySQL: many Sleep processes

I've queried SHOW processlist as root after a server restert, and got a long list of Sleep threads: +-----+-------------+-----------------+---------+---------+------+-------+------------------+ | Id ...
0
votes
0answers
26 views

keeping track of certain related field changes [duplicate]

Possible Duplicate: Save history editable data RDBMS I am totally new to databases and MySQL. I am working on a project in which I have a few related fields such as question and multiple ...
1
vote
1answer
1k views

Query to take partial backup in MYSQL

I tried to take full backup in mysql using enterprise backup by using the command mysqlbackup -u root -p --backup-dir=g:\full_bck backup But I could not take backup of a single database , I ...
1
vote
1answer
124 views

How can I reinitialize some MyISAM tables without restarting MySQL?

I'm using rsync to copy some measurement data from one PC to the server. Is it possible to load the new MyISAM tables without restarting the MySQL server?
1
vote
1answer
424 views

Should this be an identifying relationship or not?

Well I've attached the image to explain what I need to know: I thought it was supposed to be an identifying relationship, but now I kinda thought maybe it wasn't? The thing is the user won't ...
1
vote
2answers
5k views

MySQL Workbench is not finding MySQL service?

I set up a local MySQL server, currently with no databases. I'm trying to create a new server instance profile in MySQL Workbench to manage the server, and during the Create New Server Instance ...
3
votes
1answer
404 views

MySQL licensing [closed]

I have looked at a few questions posted in stackoverflow http://stackoverflow.com/questions/225987/can-someone-explain-mysqls-license-and-what-it-means-to-closed-source-developme ...
4
votes
1answer
716 views

How to removing or reduce the size of redo logs in MySQL cluster

Can anybody tell me that how can we reduce the redo log file size or can we remove the redo log file size in MySQL Cluster and also what will be the impact of it on performance of cluster.Also can we ...
2
votes
1answer
2k views

Is a query with UNIX_TIMESTAMP using indexes in mysql?

If I use .. WHERE UNIX_TIMESTAMP(`some_timestamp`)>NOW() in a SELECT, does this query still make use on an index, set on some_timestamp ?
2
votes
3answers
4k views

Deleting Data From Multiple Tables

Suppose,I've a table called UNIVERSITY containing universities name: universityID universityNAME isACTIVE 7 GNDU 1 6 PU 1 5 ...
3
votes
1answer
2k views

MySQL - Create a function from another function

I want to create a function same like my old functions. When I create a table, I can use `AS to other table as follows: CREATE TABLE dummy2 AS SELECT * FROM dummy1; Can I do the same thing with ...
0
votes
1answer
621 views

INNER JOIN between 2 tables vs WHERE on one table performance

Suppose I have two tables: CREATE TABLE `doctors` ( `id` int(11) NOT NULL AUTO_INCREMENT, `clinic_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; ...
6
votes
1answer
14k views

What's the most efficient way to batch UPDATE queries in MySQL?

I'm writing an application that needs to flush out a large number of updates to the database for an extended period of time, and I've gotten stuck at how to optimize the query. Currently I'm using ...
7
votes
2answers
31k views

How do I automatically grant all privileges to a schema created by a user in mysql?

Someone new joined the company and I created an account for her in mysql using the following: CREATE USER 'username' IDENTIFIED BY 'password' She needs to create her own schema and so used: GRANT ...
6
votes
2answers
361 views

mysql performance / cache configuration enigma [closed]

I have two mysql 5.1 instances (say A, B) hosting the same database schema. If I run (with mysql workbench) the same query on both instances I don't understand why I get very different response times ...
3
votes
2answers
4k views

Can I do anything about a very long running KILL command?

I've got a table with 103M+ records in it and takes up 70GB on the disk. I decided to DELETE (in one statement) 70% of the records. As expected, this took an awfully long time to run. I decided to ...
0
votes
1answer
169 views

What MySQL storage engine and table schema should I use?

I am making a web project and I created necessary and the most important table, but I am not sure that I did it right. In this site users will post data like text, picture, text's category, etc. I ...
1
vote
1answer
223 views

Why does a MySQL MEMORY-backed table take so much more space than on disk?

I have a single InnoDB table on disk, with ~1,107,423 rows and taking up 45.6 MiB disk space. The structure is: messageid int(11) signature varchar(255) When I create a MEMORY-backed version of ...
-2
votes
1answer
3k views

Which is the shortest way to find out if a float IS NULL or equals 0 in mysql [closed]

I want to type the least possible characters, to find out all columns in a table that are 0 or NULL In this example I explain it further: lets assume the table test CREATE TABLE IF NOT EXISTS ...
2
votes
1answer
329 views

MySQL generic trigger to save identification data for later accessing of the changed row

I am pretty inexperienced with this. I need a generic trigger, able to create and save in a fixed table some sort of identification data for a changed row from generic (any) table. The ...
2
votes
1answer
161 views

Ranking System with Different Categories

I want to create a system where the user can be ranked by several different factors. For example, the user competes in the following categories Wins Points I need to know what the position of the ...
2
votes
2answers
5k views

How to COUNT number of rows with LIMIT?

I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too? Currently, I use two separate queries to do so as SELECT COUNT(*) FROM col WHERE CLAUSE ...
0
votes
1answer
149 views

MySQL query optimization

I'm quite new to big MySQL queries (I used to extract raw data from tables then manipulate them with PHP) so I'd like to know if my query below is too "heavy" or slow for some reason (it seems to be ...
3
votes
2answers
1k views

Save history editable data RDBMS

I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means ...
0
votes
0answers
177 views

can not insert big data to mysql database [closed]

I am trying to save big data to database I extend the max_allow_packet and post_size but nothing happened. the code and database released to production server and to stage server on production server ...
3
votes
1answer
49 views

MySQL Help with Desktop Based Administration

I am making a MySQL based database for a professor and she would like to have a version uploaded to the web and a version on her desktop for personal use. The web version would be "official" whereas ...
0
votes
1answer
79 views

Max and group by query help [closed]

We have query as below. What we need is to select particular training with latest date and only single data but now when we run the query below we get more then one results. SELECT ...
1
vote
0answers
86 views

MySQL: Assessing performance of a query by using EXPLAIN

It is said that the number of rows are scanned is smaller the performance is higher when use Explain to optimize query. So I wonder When the EXPLAIN output includes many rows, is there any formula ...
1
vote
2answers
1k views

How do I remove duplicate entries in a given timeframe?

My table looks like this: +-----------+ | id | <- primary key | time | <- DateTime | row2 | <- varchar | row3 | | ... | +-----------+ I want to remove all ...
1
vote
1answer
138 views

Simple Slow Query Not Using Indexes

I have a slow query running on a large forum. The EXPLAIN synopsis: mysql> EXPLAIN SELECT COUNT(post_thanks.id) as thanked, post_thanks.postid, post.title, post.dateline FROM ...
6
votes
2answers
1k views

SQL ANSI JOIN precedence

I have a query that looks like this: SELECT * FROM TBLA A LEFT JOIN TBLB B ON A.Col1 = B.Col2 RIGHT JOIN TBLC C ON B.Col3 = C.Col4 JOIN TBLD D ON C.Col5 = D.Col6 In which order ...
2
votes
1answer
2k views

MySql: #1221 - Incorrect usage of UNION and ORDER BY

I have the following query which is intended to retrieve the newest 3 comments for each post in an array of posts. SELECT * FROM comment AS tbl WHERE sscm_id IN ( SELECT sscm_id FROM comment AS t1 ...
3
votes
3answers
3k views

Empty LONGTEXT or NULL?

In my MySQL DB I have one field called html_contents that contains all the html to be shown in a webpage. Obviously the html could be huge, and certaintly bigger than 64KB, therefore I decided to use ...
5
votes
4answers
8k views

Mysql innoDB write operations are extremely slow

I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you ...
0
votes
1answer
525 views

MySQL non-sequential primary key of certain length

what's the best way to create a non-sequential primary key of a certain length in MySQL? I'm looking to use the primary key as a 'Pin' number which real actual human people can use to refer to the ...
2
votes
2answers
4k views

How to analyse innodb status on deadlock in insert Query?

I have a dead lock which i have posted here. I managed to get the innodb status from my server. Here is the Innodb status. OS WAIT ARRAY INFO: reservation count 247864, signal count 247470 Mutex ...
5
votes
4answers
3k views

TRUNCATE TABLE statement sometimes hangs

Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue? I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB. ...
4
votes
2answers
258 views

Choosing between MyISAM & InnoDB - two servers with related tables

I don't want to make a mistake. I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB. I read here and there that InnoDB should be ...