Beyond CRUD: Unlock Hidden MySQL Features and Optimization Tricks

Beyond CRUD: How Much Do You Really Know About MySQL?

MySQL Remote Connection and Authorization

To enable remote access, you must create a user and grant privileges. Example commands:

create user username@ip_address identified by 'password'; grant all on *.* to

username@'ip_address';

# Create user
create user root@'192.168.11.%' identified by '123456';
# Grant privileges
grant all on *.* to root@'192.168.11.%';
# Delete user
drop user root@'192.168.11.%';

Client‑Server Connection Process

After setting environment variables, you can log in directly with mysql -p user -h host. MySQL uses TCP/IP for network communication, and the default port is 3306. You can change the port with -P when starting the server.

TCP/IP

When the client and server are on different machines, MySQL communicates over TCP. Use -P to specify the port and -h to specify the host.

Named Pipe and Shared Memory (Windows)

On Windows you can use named pipes or shared memory for inter‑process communication. Enable them with server options --enable-named-pipe or --shared-memory, and connect with client options --pipe or --protocol=pipe (named pipe) and --protocol=memory (shared memory).

Unix Domain Socket

On Unix‑like systems, the client can connect via a Unix socket file (default /tmp/mysql.sock). Use --socket=/path/to/socket or set the host to localhost to trigger socket usage.

Query Optimization

MySQL’s optimizer rewrites queries (e.g., converting outer joins to inner joins) and generates an execution plan that shows which indexes are used and the join order. Use EXPLAIN to view the plan.

When Indexes Are Not Used

Even if an index exists, MySQL may ignore it in several situations:

Index Column Involved in Calculations

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

SELECT sname FROM t_stu WHERE age + 10 = 30; -- No index

Functions on Indexed Columns

Applying functions (e.g., CONCAT) to indexed columns prevents index usage.

SELECT sname FROM stu WHERE CONCAT(sname,'abc') = 'Jaskeyabc'; -- No index

LIKE with Leading Wildcard

Patterns starting with % cause a full table scan.

SELECT * FROM houdunwang WHERE uname LIKE '%suffix'; -- No index

String vs. Numeric Comparison

Comparing a CHAR column to a number disables the index; the reverse (numeric column compared to a string) still uses the index.

SELECT * FROM t1 WHERE a = 1; -- No index (a is CHAR)
SELECT * FROM t2 WHERE b = '1'; -- Index used (b is INT)

Avoid OR Conditions

When an OR combines columns, MySQL cannot use indexes unless every column in the OR has its own index. Consider rewriting with UNION or separate queries.

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

ORDER BY Optimization

MySQL can use indexes for ORDER BY only when the ordering columns are not part of an expression and match the index order. Composite indexes (e.g., (columnX, sort)) help.

LIMIT with Large OFFSET

Using LIMIT offset, count with a large offset forces MySQL to scan and discard rows up to the offset, which is slow. Optimizations include:

Using a WHERE clause on an indexed column to narrow the range (e.g., WHERE id >= 10000 LIMIT 10).

Subquery to fetch primary keys first, then join to retrieve full rows.

Replacing IN with a JOIN for large sets.

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

Ultimately, business‑level adjustments (e.g., limiting queries to recent data) can reduce the need for heavy technical tuning.