On 16/07/2012 17:39, Rick James wrote:
> How many rows? If 1K, it does not matter. If 1 billion, we need to
> discuss in more detail. Let's assume 1M...
Around 1M in the item_spine table and 10M in item_detail.
> Dates should be stored in DATE datatype, which is 3 bytes. Your GUID
> is non-standard, but should probably be stored in CHAR(6) CHARACTER
> SET ascii, unless it is expected to have non-ascii characters. Is
> case folding important? Given those, the PRIMARY KEY is 6+3+3=12
> bytes long. This is not bad for a million-row table. If bigger,
> then the AUTO_INCREMENT should be considered.
The guid is case-insensitive. ISO dates map directly to MySQL's internal
DATE type, so that's already taken care of. All data is ascii, and all
alpha data is not case-sensitive.
I should maybe have mentioned earlier that this is external data over
which I have no control (but do have a specification which I expect to
be honoured). My task is to store it and make it searchable for display.
> Is this InnoDB or MyISAM? (Please provide SHOW CREATE TABLE, not
> English, for describing tables.)
It's MyISAM. I don't actually have a CREATE yet, as this is still just
hypothetical :-)
> Let's see the SELECTs that will be hitting the tables. Then we can
> discuss in more detail.
A typical select would be something like this:
SELECT guid
FROM item_spine
WHERE start_date <= NOW()
AND end_date >= NOW()
AND location_code = '$query'
followed by
SELECT *
FROM item_detail
WHERE guid = '$guid'
AND start_date <= NOW()
AND end_date >= NOW()
ORDER BY sequence
where $query is the initial query from the user and $guid is the result
of the first query. location_code is VARCHAR(10) and is an alphanumeric
string.
(I'm avoiding joins because doing multiple selects in the code is
usually much faster)
Mark
