Master MySQL Remote Connections and Query Optimization: Tips & Tricks

Beyond CRUD: How much do you know about MySQL?

MySQL Remote Connection Authorization

👉 Remote connection

👉 Authorization

👉 Common privilege tables

Create User, Grant

• Create user syntax: create user username@ip_address identified by 'password'; • Grant:

grant all on *.* to username@'ip_address';

grant select,create on database_name.table_name to username@ip_address;

# Create user
create user root@'192.168.11.%' identified by '123456';
# This allows any user from 192.168.11.% to log in

create user hans@'192.168.11.161' identified by '123456';
# Only hans can log in

create user li@'%' identified by '123456';
# All li users can log in

# Drop user
drop user root@'192.168.11.%';

# Grant
grant all on *.* to hans@'192.168.11.161';
grant select,create on oldboy_test.* to hans@'192.168.11.161';
# Show grants
show grants for hans@'192.168.11.161';
# Refresh privilege tables
flush privileges;

Client‑Server Connection Process

After setting the environment variable, we can log in directly with mysql -p xx -u xx -h xx without starting the server first. The communication between client and server is essentially inter‑process communication. MySQL uses TCP as the network protocol.

We can specify port and host with -P and -h. The default MySQL port is 3306. If the port is occupied, we can start the server with -P3307 or disable networking with --skip-networking.

TCP/IP

When client and server are on different machines, they communicate over TCP.

Named Pipe and Shared Memory (Windows)

Named Pipe

Shared Memory

To use named pipe, start the server with --enable-named-pipe and the client with --pipe or --protocol=pipe. To use shared memory, start the server with --shared-memory and the client with --protocol=memory.

Unix Domain Socket File

When both client and server run on the same Unix host, they can communicate via a Unix socket file, typically /tmp/mysql.sock. The client can connect with --socket=/tmp/mysql.sock or by using localhost as host.

mysqld --socket=/tmp/a.txt
mysql -hlocalhost -uroot --socket=/tmp/a.txt -p1234

Query Optimization

MySQL may rewrite queries for better performance, producing an execution plan that shows which indexes are used and the join order. Use EXPLAIN to view the plan.

When MySQL Uses or Skips Indexes

Indexes are not used in several situations:

Index Column Involved in Calculation

If a WHERE condition applies arithmetic to an indexed column, the index is not used.

SELECT sname FROM t_stu WHERE age = 20;      -- uses index
SELECT sname FROM t_stu WHERE age + 10 = 30; -- does NOT use index
SELECT sname FROM t_stu WHERE age = 30 - 10; -- uses index

Index Column Used with Functions

Applying functions to indexed columns prevents index usage.

SELECT sname FROM stu WHERE concat(sname,'abc') = 'Jaskeyabc'; -- no index
SELECT sname FROM stu WHERE sname = concat('Jaskey','abc');   -- uses index

LIKE with Leading Wildcard

SELECT * FROM houdunwang WHERE uname LIKE 'prefix%';   -- uses index
SELECT * FROM houdunwang WHERE uname LIKE '%suffix';   -- full table scan

To make email suffix searches indexable, store a reversed version of the email and query with LIKE REVERSE('%.com').

String Column Compared with Number

Comparing a CHAR column with a numeric literal disables the index.

CREATE TABLE t1 (a CHAR(10));
SELECT * FROM t1 WHERE a = '1';   -- uses index
SELECT * FROM t1 WHERE a = 1;     -- no index

Conversely, comparing a numeric column with a string literal still uses the index.

CREATE TABLE t2 (b INT);
SELECT * FROM t2 WHERE b = '1';   -- uses index

Avoid OR Conditions

OR prevents index usage unless every column in the OR has an index. Use UNION as an alternative.

SELECT * FROM dept WHERE dname='jaskey' OR loc='bj' OR deptno=45; -- no index
SELECT * FROM dept WHERE dname='jaskey'
UNION
SELECT * FROM dept WHERE loc='bj'
UNION
SELECT * FROM dept WHERE deptno=45;

ORDER BY

If the ORDER BY column is also in the WHERE clause, MySQL may not use the index. MySQL can use indexes for ORDER BY and GROUP BY when appropriate.

ORDER BY Index Optimization

Create an index on the sort column.

WHERE + ORDER BY Index Optimization

Create a composite index on (filter_column, sort_column).

Multiple Columns ORDER BY

Create a composite index matching the WHERE and ORDER BY columns, e.g., (uid, x, y).

OFFSET LIMIT Performance

Using a large OFFSET forces MySQL to scan and discard rows, which is inefficient. Optimizations include:

First Optimization

Use a primary key range condition instead of OFFSET:

SELECT * FROM table_name WHERE id >= 10000 LIMIT 10;

Second Optimization

Use a subquery to fetch primary keys first, then join:

SELECT * FROM table_name WHERE id IN (
    SELECT id FROM table_name WHERE user = xxx LIMIT 10000,10
);

Third Optimization

Replace IN with JOIN:

SELECT * FROM table_name
INNER JOIN (
    SELECT id FROM table_name WHERE user = xxx LIMIT 10000,10
) b USING (id);

Ultimately, business‑level constraints (e.g., limiting logs to the last 15 days) can reduce the need for heavy OFFSET queries.