0
Locally I have MAMP installed and if I try to use MySQL select query then it takes about 2ms but the same query on server's MySQL takes about 40ms. Both MySQL environments have same version 5.7.24. Both tables that I'm using are identical - same columns, same indexes, same table type (InnoDB).
What would be the problem? If I have to work with 100k rows then there is huge difference in time.
Maarc
Posted 2019-01-21T09:21:42.940
Reputation: 1
0
I can't tell the difference without knowing much more about your database, query and setup, but you can find it out yourself with profiling:
SET profiling = 1;
SELECT /*your query*/
SHOW PROFILES; /*see the id for this query, probably 1*/
SHOW PROFILE FOR QUERY 1
You may also use the EXPLAIN command for analyzing aspects such as buffering results for sorting and others.
harrymc
Posted 2019-01-21T09:21:42.940
Reputation: 306 093
Thanks for answer. Here are results: starting 0.000132 checking permissions 0.000015 Opening tables 0.000040 init 0.000057 System lock 0.000017 optimizing 0.000007 statistics 0.000021 preparing 0.000021 executing 0.000006 Sending data 0.004991 end 0.009383 query end 0.000045 closing tables 0.000022 freeing items 0.000424 cleaning up 0.000034 – Maarc – 2019-01-21T11:05:59.747
Sending data followed by "end" is the major part, which incorporates query execution and sending the result. The difference might be related to normal network overhead and the size of the result. It would certainly be very slow to send a result set of 100k rows over the network. – harrymc – 2019-01-21T11:19:55.157