List:General Discussion« Previous MessageNext Message »
From:Rick James Date:July 16 2012 6:22pm
Subject:RE: Unique index - opinions sought
View as plain text  
Did you really mean to have start/end_date in both tables?  Are the values identical?  If
they are, that is another reason to use an INT UNSIGNED AUTO_INCREMENT.

Done correctly, JOIN can usually run faster than two separate queries.

    FROM  item_detail AS d
    JOIN  item_spine AS s ON d.guid = s.guid
    WHERE  s.guid = '$guid'
      AND  s.start_date <= NOW()
      AND  s.end_date >= NOW()
      AND  s.location_code = '$query'
      AND  d.start_date <= NOW()
      AND  d.end_date >= NOW()
    ORDER BY  d.sequence 

Also, the value of NOW() could be different between the two queries; it will be the same
in the JOIN verison.

Does it need to say
  s.start_date = d.start_date AND s.end_date = d.end_date ?

I would recommend InnoDB, at least for recovery after a crash.  PRIMARY KEYs are handled
differently between the engines, but I don't have enough details of your app to explain
relevant details.

Do you expect to get more than 1 guid from first query?  Multiple rows from second query?

item_spline needs an index beginning with location_code.
item_detail _might_ benefit from INDEX(guid, sequence).

> -----Original Message-----
> From: Mark Goodge [mailto:mark@stripped]
> Sent: Monday, July 16, 2012 10:13 AM
> To: Mysql General List
> Subject: Re: Unique index - opinions sought
> 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
> 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:
> To unsubscribe:

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