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.
SELECT d.*
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:[email protected]]
> 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
>
> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
