Why Would Phpmyadmin Be Significantly Faster Than The Mysql Command Line?

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. Query 1: SELECT p.* FROM users as

Solution 1:

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.

Solution 2:

Depending on the storage engine you're using, it is most probably the data is being loaded from a data cache and not a query cache. The time difference is too big, if you clean the cache and run the query again, you will notice the difference

Solution 3:

While I was uploading and inserting data to an existing table, just regular simple insert statement on about 18 million records, I figure that much depends on your setup and hardware. Fine tuning phpmyadmin to for best performance on your hardware, it took me a while to do the job remotely, so decided to to a simple command line upload in the server. Phpmyadmin, breaks about 500 lines at a time, and does send the query again and again, several times, so the connection doesn't time out. It does the download and upload by chunks. While phpmyadmin took about > 10 minutes to do the job, the command line was inmediate.

Whatever time took to read and upload the same SQL import file, it was several times slower phpmyadmin with timeout problems. It has to do with many factors, php.ini settings such as memory and max variables, http server settings (apache settings differ from nginx with php7-fastcgi parameters and so on) even the version of php matters, since phpmyadmin works perfectly with some versions and sometimes doesnt work like it should with others. Better off using direct dump from command line if you do large xfer of data. phpmyadmin is great for details, small imports and exports, and quick fixes, but browser is in the middle, so I guess straight forward data input output is always better and much faster. So, to answer your question, is matter of settings, hardware and the job you are doing and how you are doing it. Thats my humble advice.