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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
