mysql query is too slow on windows but very fast on linux

Question

I have a MySQL query which is working on linux server very fast and normal (less than 1 sec). But when i try to run this query on my local windows pc or my windows remote server it takes at least 40 secs for result.

Linux:

Windows:

And here is my query:

    SELECT SQL_CALC_FOUND_ROWS listings.id, GROUP_CONCAT(DISTINCT images.imagelink) as images, title, GROUP_CONCAT(DISTINCT tags.tag) as tags, stores.name
FROM listings LEFT JOIN bayiler ON bayiler.id = listings.used LEFT JOIN stores ON listings.owner = stores.id LEFT JOIN images ON listings.id = images.lid LEFT JOIN tags ON listings.id = tags.lid 

 
WHERE listings.id > 0
GROUP BY images.lid 

 
ORDER BY listings.id ASC
LIMIT 0, 10

I tried to change some vars on my.ini but nothing changed.

innodb-buffer-pool-size=1G
query-cache-limit=20M
query-cache-size=128M
query-cache-type=1
read-buffer-size=32M
tmp-table-size=1504M
max-heap-table-size=1504M
thread-cache-size=30
open-files-limit=50000

Question is: Why this query is so slow on windows but not on linux? And my main problem is i have to work on windows so how can i make more faster this query as like linux?

Also specs of the both windows machines are way better than linux server.

Answer 1

You should try to avoid using too many left joins, especially there are a mass of records in each table of your MySQL database, which will greatly reduce the query efficiency.

Hence, your problem is obviously due to the query way of sql, rather than the kind of OS your sql query running on. And I reommend that you should perform several separate single-table queries and merge the results.