为什么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屋！