-4

I have these configurations :
1 - Server Windows 10 CPU E31220 3.1GHz RAM 6Go- Mysql 5.6.30
2 - Server Ubuntu i5 1.70GHz - RAM 6Go - Mysql 5.6.30

This query : 

select sum(fraisprovi0_.montant_ht_provisioire) as col_0_0_ from T_FRAISPROVISION fraisprovi0_ where fraisprovi0_.num_cmmande in (select vente1_.num_commande_detail from T_VENTE vente1_ where vente1_.lot_id=57750)

On Server 1 : execution duration of query : 1.50 second.
On Server 2 : execution duration of query : 0.01 second.

What can cause such a big difference between the 2 servers?

The query is executed locally, network doesn't seem to be a reason.
The database is the same on both mysql servers.
I am the only one user on both servers.

thomas
  • 1,201
  • 2
  • 15
  • 35
  • 2
    All sorts of factors. O/S is different, for a start. So the servers are certainly not "identical". You also didn't mention whether the dataset is the same in both, what CPU/RAM they have, what else is running in these environments, what network bandwidth they have, how many concurrent users etc etc etc. – ADyson Jan 09 '18 at 15:50
  • server/DB being busy? – abhiieor Jan 09 '18 at 15:51
  • 2
    Same query plan? Different? – Sami Kuhmonen Jan 09 '18 at 15:52
  • 1
    From one point of view your difference is 150 times, from another - just one second. To eliminate "something was busy" and "something was in cache" factors you should benchmark your query at least 100 times and take medium value - most likely they will be comparable. If not - make sure that indexes and caching policy are identical – Stanislav Orlov Jan 09 '18 at 15:57
  • This query is executed 100 times, so the difference is significant at the end. About caching and other settings, how can I check the differences between the two mysql server? What are the key properties I should look at? – thomas Jan 09 '18 at 16:04

1 Answers1

0

Not sure what is wrong there, but you can try to JOIN tables usually it brings better result:

SELECT SUM(fraisprovi0_.montant_ht_provisioire) as col_0_0_ 
FROM T_FRAISPROVISION fraisprovi0_ 
INNER JOIN T_VENTE vente1_
ON fraisprovi0_.num_cmmande = vente1_.num_commande_detail
AND vente1_.lot_id=57750

And another thing I want to mention, when you compare 2 serevrs performance I usually add SQL_NO_CACHE keyword to the query to avoid any caching done on server side which affect test results.

Alex
  • 16,739
  • 1
  • 28
  • 51