2

I have a site with approx 6500 products. The are constantly running into problems where they run up against the hosting companies PHP Process limit.

I have been running a SQL profiler against their site today and came up with the following slow queries:

SLOWEST: UPDATE catalogsearch_query SET is_processed = '0' (13s)

The next slow query is the actual search on the catalog search query table. (78,000 rows)

enter image description here

My question: Can I truncate this table?

Here are all the queries by threads during the day in order of slowness

enter image description here

Improve this question
1
  • 1
    Yes - this is a terrible query. Ran into this issue on EE 1.14.1 - I did two things that significantly improved performance. First, make is_processed an index. Second, change the query so that only rows in which is_processed !=0 rather than doing a full scan. The query should be: getTable('catalogsearch/search_query'), array('is_processed' => 0), array('is_processed != 0'))
    – Phil Birnie
    Mar 4, 2015 at 18:13

1 Answer 1

Reset to default
3

That's the table responsible for the list of search queries made on your site. It can be truncated, but you lose all of the historical data.

I think there's another underlying problem here. 78K should only take a few milliseconds perform a SELECT. Even with a full-column select statement, as you are doing in the screenshot, it should be very quick.

Perhaps you have an extension or a program that works on this table? Or too many cron jobs? Try running SHOW PROCESSLIST; and see what kind of database operations are going on when it's slow.

Improve this answer
1
  • I have been running a profiler against the database all day today. It tell me the the slow processes... I am not sure how that will help me with this query? I added the queries ordered by slowness
    – brentwpeterson
    Sep 5, 2014 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.