为什么 phpmyadmin 会比 mysql 命令行快得多? [英] Why would phpmyadmin be significantly faster than the mysql command line?

问题描述

一切都在数据库所在的同一台机器上运行.这些查询以不同的方式做同样的事情.我使用 mariadb 作为数据库引擎.

Everything is run on the same machine the database is on. These queries do the same thing in a different way. I'm using mariadb as the db engine.

查询 1:

SELECT p.* 
FROM users as u INNER JOIN votes ON u.Id=votes.UserId INNER JOIN posts as p ON p.Id=votes.PostId 
WHERE (SELECT MIN(u2.reputation) 
       FROM users as u2 INNER JOIN votes ON u2.Id=votes.UserId INNER JOIN posts as p2 ON p2.Id=votes.PostId 
       WHERE p2.Id=p.Id) >= {}
ORDER BY p.Id;

mysql命令行大约2.9秒，phpmyadmin大约0.1秒

Takes about 2.9 seconds in the mysql command line, and 0.1 seconds in phpmyadmin

查询 2:

SELECT P.*
FROM posts as P 
    JOIN votes AS V on P.Id=V.PostId
    JOIN users AS U on V.UserId=U.Id
WHERE U.Reputation >={}
    AND P.Id NOT IN 
    (SELECT DISTINCT (P2.Id)
    FROM posts P2, votes V2,users U2
    WHERE P2.Id=V2.PostId
        AND V2.UserId =U2.Id
        AND U2.reputation < {})
ORDER BY P.Id;

在 mysql 命令行中大约需要 3.1 秒，在 phpmyadmin 中大约需要 0.9 秒.

Takes about 3.1 seconds in the mysql command line, and 0.9 seconds in phpmyadmin.

这些时间都取自运行查询后自动显示的时间.

These timings are all taken from times that are automatically displayed after running the query.

为什么 phpmyadmin 会更快?为什么phpmyadmin中的速度百分比差异如此之大，而在mysql命令行中却没有?

Why would phpmyadmin be faster? And why is the percentual difference in speed so big in phpmyadmin, but not in the mysql command line?

推荐答案

像 phpMyAdmin 这样的前端工具通常会使用 LIMIT 子句，以便对结果进行分页而不会使浏览器或应用程序崩溃表.一个可能返回数百万条记录的查询，这样做会花费大量时间，如果受到更多限制，它将运行得更快.

Front-end tools like phpMyAdmin often staple on a LIMIT clause in order to paginate results and not crash your browser or app on large tables. A query that might return millions of records, and in so doing take a lot of time, will run faster if more constrained.

将有限的查询与完整的查询进行比较并不公平，检索时间会显着不同.检查两个工具是否都在获取所有记录.

It's not really fair to compare a limited query versus a complete one, the retrieval time is going to be significantly different. Check that both tools are fetching all records.

这篇关于为什么 phpmyadmin 会比 mysql 命令行快得多?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！