jynus
  • Member for 9 years, 4 months
  • Last seen more than a month ago
MySQL - varchar length and performance
Accepted answer
43 votes

This is a very common "exam/interview question". I will answer as good as I can: In the standard row formats for InnoDB and MyISAM (dynamic/compact) a VARCHAR(50) and a VARCHAR(255) will store the ...

View answer
What is mysql_native_password?
Accepted answer
30 votes

For a long time, MySQL has supported different authentication plugins, basically programable pieces of code to demonstrate that a mysql accounts is owned by whoever claims so. The original way to do ...

View answer
Difference between transaction log and redo log in MySQL
Accepted answer
23 votes

REDO log is Oracle terminology, transaction log is InnoDB terminology. Now that all are Oracle engineers, people use both to refer to the same thing in MySQL. The transaction log is, by default- it ...

View answer
MySQL: Which join is better between left outer join and inner join
Accepted answer
21 votes

There is not a "better" or a "worse" join type. They have different meaning and they must be used depending on it. In your case, you probably do not have employees with no work_log ...

View answer
What's the difference between MySQL Fabric and MySQL Cluster
Accepted answer
20 votes

These 3 things have nothing to do one with each other. I will give you an introduction and you can search or ask for more specifics separately: MySQL Cluster is the marketing name of what many of us ...

View answer
Benefits of Barracuda and Compression
Accepted answer
15 votes

Regarding "Dynamic", the non-compressed Barracuda-only format, very little has changed from compact, mainly on how blobs (and any very dynamic fields) are stored. I have never had any issues with ...

View answer
Using mysql's load infile to only populate certain columns
Accepted answer
15 votes

Check this example: # cat /tmp/db.txt id,col1,col2 1,23,"dsafsdf" 2,-1,"ghfdhg" 7,9,"strsdrt" # mysql test mysql> CREATE TABLE `test` ( `id` int(11) NOT NULL, `mycol1` int(11) ...

View answer
How to set MySQL binlog retention in hours?
Accepted answer
13 votes

Actually, you can now. Since MySQL 8.0.1, while expire_log_days still works, the canonical way to setup expiration is by changing binlog_expire_logs_seconds, which, as you may guess, has second ...

View answer
Can't default date to CURRENT_TIMESTAMP in MySQL 5.5
12 votes

Check this answer. Your options are: Upgrade to MySQL 5.6.5 Change the column type to TIMESTAMP, as in: ALTER TABLE `downloads` ADD `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; Create a ...

View answer
What is InnoDB Buffer Usage and how to clear it?
Accepted answer
12 votes

Assuming that your dataset is larger than your buffer pool, having it at 95% usage is not only normal, but a desired state. You want as much information as possible on memory- hardware and resources ...

View answer
MariaDB - "ERROR 1205 ... Lock wait timeout exceeded; ..." when doing multiple parallel UPDATEs affecting different rows
Accepted answer
10 votes

What are we doing wrong? Is there a table-level lock even if using InnoDB? Your problem is the lack of a proper index to use. InnoDB does next-key locking, meaning it will lock only the rows that it ...

View answer
mysql replication on windows server
10 votes

Replication setup on a windows server is equivalent to doing it in other systems: The official manual provides you a detailed guide. In short: Configure on both servers a different server-id, and ...

View answer
Best way to implement concurrent table based queue
Accepted answer
10 votes

First: MySQL is one of the worst possible pieces of software to implement this, specially if it is very dynamic. The reason is that engines like MEMORY and MyISAM have only full-table locks while more ...

View answer
Alias using "AS" and without using "AS"
Accepted answer
9 votes

Assuming MySQL, if you execute EXPLAIN EXTENDED on both queries, you get: mysql> EXPLAIN EXTENDED SELECT S.ID,S2.Value FROM dbo.Sample AS S -> INNER JOIN dbo.Sample2 AS S2 ON -> S....

View answer
Difference between On Delete Cascade & On Update Cascade in mysql
9 votes

These two are actions to be performed, respectively, when the referenced record on the parent table changes its id and when it gets deleted. If you execute: UPDATE parent SET id = -1 WHERE id = 1; ...

View answer
MySQL Explain has different row count than slow query log
9 votes

In order to answer this question, you must understand what the rows column on explain means, and the difference between calculations based on statistics and post-execution statistics. When you run ...

View answer
Is there any performance difference in using a unique index on primary key?
Accepted answer
8 votes

A PRIMARY KEY is, by definition, UNIQUE and NOT NULL, so by adding a second UNIQUE keyword on it you are actually creating a separate index. This does not only make things less efficient but, in some ...

View answer
Why do so many queries have aliases?
Accepted answer
8 votes

In some cases, MySQL requires an alias, for example, a self join: mysql> CREATE TABLE test (id SERIAL, name text); Query OK, 0 rows affected (0.02 sec) mysql> SELECT name FROM test JOIN test ...

View answer
Mysql enable remote connection in centos,my.cnf does not have bind-address to comment out
8 votes

The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces. The ...

View answer
InnoDB import performance
8 votes

I wanted to write a comment (as this is not a definitive answer), but it became too long: I am going to give you several broad pieces of advice, and we can go into details for each one, if you want: ...

View answer
Master-master Replication with 3 Nodes
8 votes

There are multiple reason why you wouldn't want to do multimaster replication in a circular fashion, but most of them can be summarized in one: You will have 3 different single points of failure. ...

View answer
how to change mysql table auto_increment primary key
Accepted answer
8 votes

The auto_increment field must be an index, or be the first column of one. If you try to just drop the primary key, MySQL will complain. You have to delete the auto_increment property before moving it: ...

View answer
Which characters are allowed in MySQL/MariaDB user account names?
Accepted answer
7 votes

User names are stored on the mysql.user table. On my system (mariadb 10.3), that table is created as a CHAR(80) with the utf8 character set. You can check yours by running: MariaDB [(none)]> SHOW ...

View answer
Simple query is slow on 4M-rows table
Accepted answer
7 votes

In addition to @tombom's suggestions, creating an index on (user_id, post_id) instead of (or in addition, but the less indexes the better) separate indexes on user_id and post_id will simplify the ...

View answer
Why wouldnt Mysql use index on the Primary query in a nested query
7 votes

You are trying to execute a semijoin. This is a very well know issue with the MySQL optimizer before MySQL 5.6. The only way MySQL knows how to execute it is to perform a full table scan on the left ...

View answer
Appropriate use of mysql Optimize Table
7 votes

Baron Schwartz, the author of that post, is one of the coauthors of High Performance MySQL, 3rd Edition, one of the best books out there regarding MySQL performance. While the argument of authority is ...

View answer
MATCH AGAINST one character words returns empty rows with ft_min_word_len = 1
Accepted answer
7 votes

mysql> SHOW GLOBAL VARIABLES like 'ft_min_word_len'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | ft_min_word_len | 1 | +-----------------+-------+ 1 ...

View answer
UPDATE query with JOIN taking forever
Accepted answer
7 votes

With your current structure, your query may be doing >1,000,000,000 row scans. This is probably due to the FIND_IN_SET - meaning the column cannot be used in an index, resulting in the optimizer ...

View answer
Disable MySQL binary logging with log_bin variable
6 votes

You can see it like this, log_bin should not accept parameters, either you put: log_bin or nothing at all, as it is a boolean parameter. However, if a value is set, logs are enabled and the value ...

View answer
Multiple concurrent connections to same large SQLite file on a network share
Accepted answer
6 votes

Concurrency is not a problem, as long as you only read. SQLite doesn't allow concurrency on writes, because it never was designed for that. I don't know the internals of the python driver and ...

View answer
1
2 3 4 5
15