The query

INSERT INTO gsitemap (nid, last_changed, pid)
SELECT n.nid, n.changed, u.pid FROM beta_node n
LEFT JOIN gsitemap g ON n.nid = g.nid
LEFT JOIN url_alias u ON u.src = CONCAT('node/', n.nid)
and g.nid IS NULL;

can't be executed on my host.

Using explain over the select I get that mysql wont use an index for node table (wich size is 12754 records) and alias table will be full scaned (about 168063 records)

Right now I'm executing the query splited by node ranges, 200 each time.

Comments

darren oh’s picture

darren oh
Primary language English
Location Lakeland, Florida
commented
Status: Active » Postponed (maintainer needs more info)

Must be a database configuration issue. In issue 166716, this query was tested on a site with over 30,000 nodes. I personally tested this query on a site with over 12,000 nodes.

niteman’s picture

niteman commented
Title: Run away query » Run away queries? - Configuration/engine/load issue

Right now I've achvieve to fill my gsitemap table runnig the avobe query by sets of 500 nodes, but I still can't get the sitemap to be shown.

Might it be related with the database engine I'm using?
Queries like:

SELECT COUNT(*) - 300
FROM node n
LEFT JOIN gsitemap g
  ON g.nid = n.nid
INNER JOIN node_access na
  ON na.nid = n.nid
WHERE
(na.grant_view >= 1 AND
  (
    (na.gid = 0 AND na.realm = 'all')
    OR (na.gid = 1 AND na.realm = 'term_access')
  )
)
AND
(
  n.status > 0
  AND
  (
    g.priority_override >= 0 OR g.priority_override IS NULL
  )
  AND n.type NOT IN ('')
  AND n.nid <= 5431
)
;

Takes about 30 seconds each.
Or:

SELECT n.nid, n.type, n.promote, s.comment_count, n.changed, g.previously_changed, s.last_comment_timestamp, g.previous_comment, g.priority_override, u.dst
          FROM node n
          LEFT JOIN node_comment_statistics s
          ON s.nid = n.nid
          LEFT JOIN gsitemap g
          ON g.nid = n.nid
          LEFT JOIN url_alias u
          ON u.pid = g.pid
           INNER JOIN node_access na ON na.nid = n.nid
           WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'term_access'))) AND ( n.status > 0
          AND (g.priority_override >= 0 OR g.priority_override IS NULL)
          AND n.type NOT IN ('')
          AND n.nid <= 7559
          ) ORDER BY n.nid
         LIMIT 2999, 3000;

Taking more than 200 secs.

All involved tables are INNODB and the site is allways under hevy load (from 90 to 160 registered users and about 200 anonymous)

Thank you in advance

darren oh’s picture

darren oh
Primary language English
Location Lakeland, Florida
commented

This module has not been tested before with InnoDB, so that could be the problem. InnoDB is known to be slower. If fewer hosting providers insisted on restricting their service to MySQL, we could just use PostgreSQL and save a lot of trouble. It may be that the only solution for InnoDB is to provide a configuration form where the site administrator can set how many links can be retrieved per query.

niteman’s picture

niteman commented

I have speeded my sites greatly switching to innodb most of my tables (same as drupal.org have done), but if needed i can switch back some of the involved tables. Witch do you suggest me? (NOTE: this is a production site, as I can't simulate the user load)

BTW, can be thoose issues related with taxonomy_access?

niteman’s picture

niteman commented
Title: Run away queries? - Configuration/engine/load issue » Run away queries caused by node_access table innodb
Category: bug » support

I've switched back my node_access table to MyISAM and the issue is solved (no idea of what is the real cause).

Thank you very much for your support, most times just sharing your issue with others and their points of view is the key.

darren oh’s picture

darren oh
Primary language English
Location Lakeland, Florida
commented

InnoDB is only faster if many users are writing to the database at the same time. Try running your queries through db_rewrite_sql() to see what taxonomy access is doing to them.

niteman’s picture

niteman commented

The queries I posted already have been rewrited. In my site switching tables to INNODB have proved to speed it in general, especially sessions and user tables.

I'will leave node_access table as MyISAM althougt.

Thanks again

niteman’s picture

niteman commented
Status: Postponed (maintainer needs more info) » Fixed
Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.