List:General Discussion« Previous MessageNext Message »
From:Mark Goodge Date:July 16 2012 5:12pm
Subject:Re: Unique index - opinions sought
View as plain text  
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
Thread
Unique index - opinions soughtMark Goodge16 Jul
  • RE: Unique index - opinions soughtRick James16 Jul
    • Re: Unique index - opinions soughtMark Goodge16 Jul
      • RE: Unique index - opinions soughtRick James16 Jul