Multiple hierarchy and distant parent don't seem to work properly, as other threads have suggested. PHPMyAdmin gives sevral warnings of improperly constructed tables that sound as though they might be related to some of the problems. Not all are in tables named "category_*" but other than blocks and flood, they all sound as though they could be related..

Drupal 5.1, category module 5.x-1.1 MySQL 5

Here are the problems PHPMyAdmin finds with the different tables

blocks: no index defined
category_cont_distant: Primary and Index both set for column 'cid'
category_hierarchy: ditto
category_node: ditto
flood: no index defined
node: Primary and Index should not both be set for column 'nid'; and More than one index key was created for column 'static'
term_hierarchy: Primary and Index should not both be set for column 'tid'
term_node: ditto

I don't know enough MySQL to know whether these warnings would affect the problems people are seeing (me included), but I assume that if PHPMyAdmin says there's a problem, it indicates something that should be fixed.

Martin

Comments

bdragon’s picture

bdragon commented

blocks: no index defined -- Possibly a problem if you have many blocks
category_cont_distant: Primary and Index both set for column 'cid' -- Index overhead nit, not a problem as such
category_hierarchy: ditto
category_node: ditto
flood: no index defined -- This is on purpose, I believe
node: Primary and Index should not both be set for column 'nid'; and More than one index key was created for column 'static' -- MySQL reads indexes left to right, depending on usage patterns you may NEED more than one index key on a column.
term_hierarchy: Primary and Index should not both be set for column 'tid' -- Index overhead nit.
term_node: ditto

None of these are problems as such (except for the blocks one, that could stand to be fixed some day..)

abramo’s picture

abramo commented

well, I hope this is so - I would also much like to believe that it is so, but I am not totally convinced.
after getting all sorts of database issues with this really excellent in concept module, I have also got the feeling that most of these issues originate from database mess-ups of some sort.

if it were not for the excellent concept and the absolute need for this module, I would have left it till the time most bugs were gone.

bdragon’s picture

bdragon commented

These warnings have nothing to do with any of the errors people are getting. They are suggestions for index optimization. The only bugs index problems in a database can ever cause are speed issues.

The best way to optimize a query is to use EXPLAIN to see what the database is *ACTUALLY* doing. Unless you are very familiar with the internals of the query optimizer in MySQL, anything else is speculation.

http://dev.mysql.com/doc/refman/5.0/en/explain.html

Most of the problem with Category is the need to maintain backwards compatibility with Taxonomy. There are many modules that use the Taxonomy API and tables, and there are still a few issues with the taxonomy wrapper, including the fact that the wrapper has a high level of complexity. (which raises the barrier to entry in regards to maintaining it.)

In summary, there are problems, but they lie elsewhere.

bdragon’s picture

bdragon commented
Status: Active » Closed (fixed)

Closing, we can revisit on a case by case basis.