I think you may find that a composite key on two INTEGERs is faster than a single VARCHAR key (and smaller as well). Besides the composite key allows easier gathering of history for a single item. Compare:
SELECT *
FROM tablename
WHERE key = 12345
ORDER BY version;
and
SELECT *
FROM tablename
WHERE key LIKE '12345,%'
ORDER BY key;
The first most likely will get to use an index on key (especially if you build the index with key first and version second) and the second is most likely a table scan. Here are the singleton selects for the current version
SELECT *
FROM tablename o
WHERE key = ?
AND version = (
SELECT max(version)
FROM tablename i
WHERE i.key = o.key
)
vs.
SELECT *
FROM tablename
WHERE key = (
SELECT max(key)
FROM tablename
WHERE key LIKE '$key,%'
)
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.
|