MySQL Forums
Forum List  »  Performance

small SELECT change results in huge performance hit
Posted by: Peter Buckner
Date: March 01, 2005 07:21PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
small SELECT change results in huge performance hit
2191
March 01, 2005 07:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.