biblio module launches the query 

SELECT DISTINCT 
		COUNT(DISTINCT(n.nid))  
	FROM node n  
	left join biblio b 
		on n.vid=b.vid  
	inner join biblio_types bt 
		on b.biblio_type=bt.tid  
	inner join biblio_contributor as bc1 
		on n.vid = bc1.vid  
	INNER JOIN node_access na 
		ON na.nid = n.nid  
	LEFT JOIN node i18n 
		ON n.tnid > 0 AND n.tnid = i18n.tnid AND i18n.language = 'en'  
	WHERE (na.grant_view >= 1 
			AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public'))) 
		AND (n.language ='en' OR n.language ='' OR n.language IS NULL OR n.language = 'de' AND i18n.nid IS NULL) 
		AND ( (bc1.cid = 4557 ) AND (n.type='biblio' ) AND (n.status = 1 ) );

which took 6:30 minutes on the dedicated database server of my hoster.
When running the exact same query on my local host (installed "out of the box") it took only 0.68 seconds to execute.
Running an "analyze select ..." on both machines shows
for my local host (server version: 5.1.36-log SUSE MySQL RPM)

+----+-------------+-------+--------+--------------------------------------------+------------------+---------+-------------------+-------+--------------------------+
| id | select_type | table | type   | possible_keys                              | key              | key_len | ref               | rows  | Extra                    |
+----+-------------+-------+--------+--------------------------------------------+------------------+---------+-------------------+-------+--------------------------+
|  1 | SIMPLE      | n     | ref    | PRIMARY,vid,node_type,node_status_type,nid | node_status_type | 102     | const,const       | 10802 | Using where              |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY,type                               | PRIMARY          | 4       | bgv.n.vid         |     1 | Using where              |
|  1 | SIMPLE      | bc1   | ref    | PRIMARY                                    | PRIMARY          | 8       | bgv.b.vid,const   |     1 | Using where; Using index |
|  1 | SIMPLE      | bt    | eq_ref | PRIMARY                                    | PRIMARY          | 4       | bgv.b.biblio_type |     1 | Using index              |
|  1 | SIMPLE      | na    | ref    | PRIMARY                                    | PRIMARY          | 8       | bgv.n.nid,const   |     1 | Using where              |
|  1 | SIMPLE      | i18n  | ref    | tnid                                       | tnid             | 4       | bgv.n.tnid        |  6224 | Using where              |
+----+-------------+-------+--------+--------------------------------------------+------------------+---------+-------------------+-------+--------------------------+

and for the hosters DB-server (server version: 5.0.90 Gentoo Linux mysql-5.0.90-r2)

+----+-------------+-------+--------+--------------------------------------------+---------+---------+-----------------------------+-------+--------------------------+
| id | select_type | table | type   | possible_keys                              | key     | key_len | ref                         | rows  | Extra                    |
+----+-------------+-------+--------+--------------------------------------------+---------+---------+-----------------------------+-------+--------------------------+
|  1 | SIMPLE      | na    | ALL    | PRIMARY                                    | NULL    | NULL    | NULL                        | 15644 | Using where              |
|  1 | SIMPLE      | n     | eq_ref | PRIMARY,vid,node_type,node_status_type,nid | PRIMARY | 4       | usr_p105240_1.na.nid        |     1 | Using where              |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY,type                               | PRIMARY | 4       | usr_p105240_1.n.vid         |     1 | Using where              |
|  1 | SIMPLE      | bt    | eq_ref | PRIMARY                                    | PRIMARY | 4       | usr_p105240_1.b.biblio_type |     1 | Using index              |
|  1 | SIMPLE      | i18n  | ref    | tnid                                       | tnid    | 4       | usr_p105240_1.n.tnid        |  6245 | Using where              |
|  1 | SIMPLE      | bc1   | ref    | PRIMARY                                    | PRIMARY | 8       | usr_p105240_1.b.vid,const   |    98 | Using where; Using index |
+----+-------------+-------+--------+--------------------------------------------+---------+---------+-----------------------------+-------+--------------------------+

can somebody help my in understanding what is going on?
How can it be that the index of table node is not used in the second case?

Regards
Reiner

Comments

rjerome’s picture

rjerome commented

Hmmm, sorry about almost getting you evicted :-P, although I don't think it's entirely my fault. A lot of the stuff in that query is being added by other modules through the call to db_rewrite_sql() (including that first "DISTINCT" clause.

I honestly don't know why the two systems aren't behaving the same, but one thing you could try on your hosted server is removing the DISTINCT clause at line 112 of biblio.pages.inc so it looks like this...

  $count_selects[] = "n.nid";

Ron.

schildi’s picture

schildi commented

Ron

I'm quite sure this is not a fault of the biblio module. But where else should I place my request for support? Hopefully someone with deeper knowledge of interpreting the "analyze" output will stumble over this post. Sorry for that!

Reiner

rjerome’s picture

rjerome commented

No offense taken, did you try the modification I suggested?

Ron.

schildi’s picture

schildi commented

yes, with no avail

Helmut Neubauer’s picture

Helmut Neubauer commented

Any solution yet?
I've a similar problem. The query crashes my whole Drupal system:

SELECT DISTINCT COUNT(DISTINCT(n.nid)) FROM node n left join biblio b on n.vid=b.vid inner join biblio_types bt on b.biblio_type=bt.tid JOIN biblio_contributor as bc1 on b.vid = bc1.vid JOIN biblio_contributor_data as bcd1 on bc1.cid = bcd1.cid JOIN biblio_contributor as bc2 on b.vid = bc2.vid JOIN biblio_contributor_data as bcd2 on bc2.cid = bcd2.cid JOIN biblio_contributor as bc3 on b.vid = bc3.vid JOIN biblio_contributor_data as bcd3 on bc3.cid = bcd3.cid JOIN biblio_contributor as bc4 on b.vid = bc4.vid JOIN biblio_contributor_data as bcd4 on bc4.cid = bcd4.cid JOIN biblio_contributor as bc5 on b.vid = bc5.vid JOIN biblio_contributor_data as bcd5 on bc5.cid = bcd5.cid JOIN biblio_contributor as bc6 on b.vid = bc6.vid JOIN biblio_contributor_data as bcd6 on bc6.cid = bcd6.cid JOIN biblio_contributor as bc7 on b.vid = bc7.vid JOIN biblio_contributor_data as bcd7 on bc7.cid = bcd7.cid JOIN biblio_contributor as bc8 on b.vid = bc8.vid JOIN biblio_contributor_data as bcd8 on bc8.cid = bcd8.cid JOIN biblio_contributor as bc9 on b.vid = bc9.vid JOIN biblio_contributor_data as bcd9 on bc9.cid = bcd9.cid JOIN biblio_contributor as bc10 on b.vid = bc10.vid JOIN biblio_contributor_data as bcd10 on bc10.cid = bcd10.cid JOIN biblio_contributor as bc11 on b.vid = bc11.vid JOIN biblio_contributor_data as bcd11 on bc11.cid = bcd11.cid JOIN biblio_contributor as bc12 on b.vid = bc12.vid JOIN biblio_contributor_data as bcd12 on bc12.cid = bcd12.cid JOIN biblio_contributor as bc13 on b.vid = bc13.vid JOIN biblio_contributor_data as bcd13 on bc13.cid = bcd13.cid JOIN biblio_contributor as bc14 on b.vid = bc14.vid JOIN biblio_contributor_data as bcd14 on bc14.cid = bcd14.cid JOIN biblio_contributor as bc15 on b.vid = bc15.vid JOIN biblio_contributor_data as bcd15 on bc15.cid = bcd15.cid JOIN biblio_contributor as bc16 on b.vid = bc16.vid JOIN biblio_contributor_data as bcd16 on bc16.cid = bcd16.cid 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.language ='de' OR n.language ='' OR n.language IS NULL) AND ( ( bcd1.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd2.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd3.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd4.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd5.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd6.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd7.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd8.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd9.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd10.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd11.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd12.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd13.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd14.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd15.name RLIKE "[[:<:]]biblio[[:>:]]" ) AND ( bcd16.name RLIKE "[[:<:]]views-view.tpl.php[[:>:]]" ) AND (n.type='biblio' ) AND (n.status = 1 ) )

Looking in my MySQL process list shows the query in statistics state and all other queries are blocked. Having 30 max. allowed connections takes my site offline :(.

Perhaps you can tell me what's the reason for this query? I'm not sure when it is called. I'm using version 6.15.

Thanks,
Helmut

rjerome’s picture

rjerome commented

The only way that query would be generated is if someone tried to generate a listing which was filtered by 16 different author names, and the only results that would yield are those entries who have ALL 16 authors associated with them.

rjerome’s picture

rjerome commented

What version of Biblio are you using?

In looking at the query a bit closer, it would appear that the author name being search for in each case is "biblio" which leads me to believe that this may be an issue parsing the URL which generated the query.

Do you know what the URL associated with that query was?

What is the "base URL" on the biblio settings page set to?

Helmut Neubauer’s picture

Helmut Neubauer commented

I'm using the actual biblio version for drupal 6 (6.15).
The base URL on the biblio settings page is the default 'biblio'.

I tried to get out the URL, but it is too difficult, because I don't know the exact time.

rjerome’s picture

rjerome commented

Are you using Views? I just noticed in that query that the last "author" being searched for was "views-view.tpl.php". This looks like it may be a Views view gone awry.

Helmut Neubauer’s picture

Helmut Neubauer commented

Hmmm ... yes, I'm using views. Perhaps it's a problem of upgrading? I did an upgrade from drupal 5.x to drupal 6.x? There might be views I deleted, but I don't remember. But why should this be a problem?

liam morland’s picture

liam morland
Primary language English
Location Ontario, CA 🇨🇦
commented
Status: Active » Closed (outdated)

This version is no longer maintained. If this issue is still relevant to the Drupal 7 version, please re-open and provide details.