small SELECT change results in huge performance hit

[Peter Buckner]

Using MySQL as backend for viewcvs database (similar to bonsai). Essentially have tables for each CVS commit, and each CVS tag + tables for filename, directory names, etc.

Compare two queries. First says, 'get all entries which have tagname.description "FOOBAR"'
...runs in 70 seconds (over three million records...) and returns abou 6000 entries. fine.

SELECT checkins.* FROM checkins,repositories,tags,descs tagname,files
WHERE (checkins.repositoryid=repositories.id)
AND (checkins.repositoryid=tags.repositoryid
AND checkins.dirid=tags.dirid
AND checkins.fileid=tags.fileid
AND checkins.revision=tags.revision)
AND (tags.descid=tagname.id)
AND (checkins.fileid=files.id)
AND (repositories.repository='/cvsroot')
AND (tagname.description='FOOBAR')

Second query is exactly the same, but adds clause "filename='BAR'"

SELECT checkins.* FROM checkins,repositories,tags,descs tagname,files
WHERE (checkins.repositoryid=repositories.id)
AND (checkins.repositoryid=tags.repositoryid
AND checkins.dirid=tags.dirid
AND checkins.fileid=tags.fileid
AND checkins.revision=tags.revision)
AND (tags.descid=tagname.id)
AND (checkins.fileid=files.id)
AND (repositories.repository='/cvsroot')
AND (tagname.description='FOOBAR')
AND (files.file='BAR')

Second query runs HOURS -- I've not waited long enough for it to complete.

Comparing Explain outputs: The first has:

+--------------+--------+------------------------------------------+------------+---------+---------------------------------------------+-----------+-------------+
| table.......... | type... | possible_keys............................... | key......... | key_len| ref.................................................... | rows...... | Extra........ |
+--------------+--------+------------------------------------------+------------+---------+---------------------------------------------+-----------+-------------+
| repositories | const.. | PRIMARY,repository....................... | repository | ......64 | const................................................ | 1........... |.................|
| tags.......... | index... | PRIMARY,repositoryid_2,dirid,fileid....| PRIMARY |........47 | NULL................................................. | 3171130 | Using index|
| checkins.... | eq_ref. | repositoryid,repositoryid_2,dirid,fileid |repositoryid|......41 | const,tags.dirid,tags.fileid,tags.revision | 1........... | Using where |
| tagname....| eq_ref. | PRIMARY..........................................| PRIMARY..|........3 | tags.descid........................................| 1............ | Using where |
| files........... | eq_ref. | PRIMARY........................................ | PRIMARY. |........3 | checkins.fileid................................... | 1............ | Using index|
+--------------+--------+------------------------------------------+------------+---------+---------------------------------------------+-----------+-------------+

The second is:

+--------------+--------+------------------------------------------+------------+---------+-----------------+------+-------------+
| table.......... | type... | possible_keys............................... | key......... | key_len| ref................ | rows | Extra........ |
+--------------+--------+------------------------------------------+------------+---------+-----------------+------+-------------+
| repositories | const.. | PRIMARY,repository....................... | repository | ......64 | const............. | 1..... |.................|
| files........... | const.. | PRIMARY,file................................. | file........... |.... 255 | const............. | 1..... |.................|
| checkins.... | ref...... | repositoryid,repositoryid_2,dirid,fileid | fileid...... |.........3 | const.............| 9.... | Using where |
| tags.......... | ref...... | PRIMARY,repositoryid_2,dirid,fileid....| fileid....... |.........3 | checkins.fileid | 48.. | Using where |
| tagname....| eq_ref | PRIMARY..........................................| PRIMARY..|........3 | tags.descid.....| 1.... | Using where |
+--------------+--------+------------------------------------------+------------+---------+-----------------+------+-------------+

Based on another 'performance tuning rule-of-thumb' I understand one can approximate performance by multiplying the values in the 'rows' column together. The Second query would look to be vastly simpler/faster, by that calculation (which would be consistent).

I notice when the queries actually execute, the first query (which completes quickly) consumes a huge amount of RAM (256M) and mostly USER and little SYSTEM cpu time -- this leads me to believe much/most of the work is done in memory.

The second query, which essentially never completes, consumes little RAM (17M), and much more SYSTEM cpu time (upwards of about 35%) which leads me to believe the second query spends most of it's time on disk.

Why?... and is there a tuning parameter (or change to query) to work around this?

-Peter Buckner
Rocket Software