Come together with the global Drupal community in Rotterdam, 28 Sept – 1 Oct 2026. Sessions, contribution, connection, and Early Bird savings until 8 June.the
$result = db_query("SELECT t.word AS realword, i.word FROM {search_total} t LEFT JOIN {search_index} i ON t.word = i.word WHERE i.word IS NULL");
(from search_update_totals)
is very slow (more than 10 minutes!).
My search_total has 14,259 lines
and search_index has 32,533 lines
| Comment | File | Size | Author |
|---|---|---|---|
| #5 | query.jpg | 8.92 KB | JirkaRybka |
| #3 | q.jpg | 25.08 KB | he_who_shall_not_be_named |
Comments
Comment #1
drummPlease paste the results of this SQL query:
EXPLAIN SELECT t.word AS realword, i.word FROM search_total t LEFT JOIN search_index i ON t.word = i.word WHERE i.word IS NULL;
Comment #2
he_who_shall_not_be_named commentedid select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t index NULL PRIMARY 50 NULL 14489 Using index
1 SIMPLE i ALL word NULL NULL NULL 33063 Range checked for each record (index map: 0x4); No...
Comment #3
he_who_shall_not_be_named commented... or this
Comment #4
he_who_shall_not_be_named commentedWell?
Comment #5
JirkaRybka commentedJust for reference - my site (5.1) have even more in these tables: index - 160358 lines, total - 37943 lines. I tried the query from PhpMyAdmin on the production server, and it took just 0.7878 sec. EXPLAIN from my server attached - seems quite different (!). I've not enough experience to find the problem, but I guess there's something wrong with your install (?)
Comment #6
he_who_shall_not_be_named commentedChecked the installation and everything seams OK. (MySQL 4.1.21) Simply, it doesn't want to use the word index from the search_ index. That's it. Commented out the query.
Thank you anyway. Now I know that the problem is not here. Closed the bug.
Comment #7
he_who_shall_not_be_named commentedFound It! For a strange reason (maybe the updates from early Drupal versions) the search_total 'word' column was varbinary instead of varchar. Now it works.
Thanks again.
Comment #8
moonray commentedJust ran into the same problem.
Changing the field type from 'varbinary' to 'varchar' fixed the issue.
It actually brought our server to its knees a few times before we could determine the cause. How did it get to be 'varbinary' in the first place?
Comment #9
dave reidNot sure where this came from. I searched through the system_install file and the old database.*.inc files from D4, and I don't see any reference to the field type 'varbinary'. I also checked all the definitions/updates to the 'word' column in the 'search_total' table, and they're all of type varchar. Maybe a rogue contrib module changed the column type? Marking as need more info.
Also, you can reduce the number of nodes to index on cron in the search settings.
Comment #10
moonray commentedWe had it only indexing 10 nodes, and it still hung the server. The problem is not with indexing the nodes, but updating the search totals (counting the amount of times a word appears). It doesn't matter how many new nodes you index, it still reruns those queries each time cron runs.
I couldn't find any instances of 'varbinary' in any of the *.install files either. This is most curious.
Comment #11
moonray commentedCould it have anything to do with
update_convert_table_utf8()in update.php ormysqlfix_convert_table_utf8()in mysqlfix.php?Comment #12
dave reidThat looks like it. Seems they were switched to varbinary, but were supposed to be fixed back.
Comment #13
jhodgdonSo it looks like this is a bug in the update system for Drupal 5.x, not the search module. Is it still unfixed? Last comment here was more than a year ago...
Comment #14
jhodgdon7.0 is out tomorrow, 5.x is obsolete, won't fix, sorry.