How to optimize MySQL queries?

[iAlex]

I have installed Prestashop 1.5.3.1 and a 75 000 products with combinations and features. Website hasn't visitors.

 

Pages of my website is loading over 20 seconds.

 

Support of my hosting answered that MySQL queries process take very long time and that's why my website is loading over 20 seconds. MySQL queries are not optimized.

 

Is it true, that Prestashop doesn't optimize MySQL queries? It's very strange that so powerful CMS as Prestashop doesn't carry a load 75 thousand of products without visitors on a normal dedicated server. Interesting that versions 1.4 carried such a load.

 

Please advise me how to optimize it.

Thank's in advance.

 

 

Dedicated server Features:

OS: Debian

RAM24 GB DDR3 RAM

Intel® Core™ i7-920 Quadcore incl. Hyper-Threading Technology

Hard disks2 x 750 GB SATA 3 Gb/s HDD (Software-RAID 1)

1 Gbit OnBoard connected at 100 Mbit

Edited January 28, 2013 by iAlex (see edit history)

[fourquet]

Hello,

First , yoou should check where your server spend time to answer. It could be images, your server,... Firebug or Chrome tells you exactly the time spent in every case.

You can also use a CDN server to upload more rapidly your images.

Guillaume

[iAlex]

fourquet, problem with unoptimized MySQL queries. Support of my hosting answered that MySQL queries process take very long time and that's why my website is loading over 20 seconds.

[tomerg3]

Do you have some specific examples of slow queries?

 

There is no magical "optimize queries" button that you can turn on...

 

Prestashop always tries to improve queries and make things more efficient, and while there may room for improvement, if a page takes 20 seconds to load, something else must be wrong or slowing it down.

 

You can try to turn on dev mode (/config/defines,inc.php) which will print out all the queries and processes that run in any page, and how long they took.

 

It should help you identify what is taking so long.

[CUBE-IN]

30k+ products, 30k+ categories, 20+ features running on dedicated server, no problem www.pneumatikator.cz

[tomerg3]

The debug setting I was referring to above (which should be set to TRUE) is:

 

define('_PS_DEBUG_PROFILING_', false);

[iAlex]

tomerg3, thanks a lot.

 

Problem solved, that was a problem with New Products block module, it created very slow MySQL queries - http://i.imgur.com/zEK1zRq.jpg

 

Earlier i have added 75000 products for 3 days, they considered as new and give a huge load.

Then i turned off New Products block and situation improved - http://i.imgur.com/a0fRQIW.png

 

Thanks a lot again.

[buhajus]

The debug setting I was referring to above (which should be set to TRUE) is:

 

define('_PS_DEBUG_PROFILING_', false);

Hello,

 

is it possible to check queries in PS 1.4 ? to set up to true ?

And where i can find printed queries?

I did this but can't see any printed queries

 

Thanks 

[tuk66]

Best optimalization is to find a better hosting - VPS with SSD database should be fine.

 

Otherwise, using EXPLAIN before SQL queries could help.