From: Rick James
Date: July 16 2012 6:22pm
Subject: RE: Unique index - opinions sought
List-Archive: http://lists.mysql.com/mysql/227828
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148892B6F2@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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 =3D s.guid
    WHERE  s.guid =3D '$guid'
      AND  s.start_date <=3D NOW()
      AND  s.end_date >=3D NOW()
      AND  s.location_code =3D '$query'
      AND  d.start_date <=3D NOW()
      AND  d.end_date >=3D NOW()
    ORDER BY  d.sequence=20

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

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

I would recommend InnoDB, at least for recovery after a crash.  PRIMARY KEY=
s are handled differently between the engines, but I don't have enough deta=
ils 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
>=20
> 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...
>=20
> Around 1M in the item_spine table and 10M in item_detail.
>=20
> > 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=3D12
> bytes
> > long.  This is not bad for a million-row table.  If bigger, then the
> > AUTO_INCREMENT should be considered.
>=20
> 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.
>=20
> 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.
>=20
> > Is this InnoDB or MyISAM?  (Please provide SHOW CREATE TABLE, not
> > English, for describing tables.)
>=20
> It's MyISAM. I don't actually have a CREATE yet, as this is still just
> hypothetical :-)
>=20
> > Let's see the SELECTs that will be hitting the tables.  Then we can
> > discuss in more detail.
>=20
> A typical select would be something like this:
>=20
> SELECT guid
> FROM item_spine
> WHERE start_date <=3D NOW()
> AND end_date >=3D NOW()
> AND location_code =3D '$query'
>=20
> followed by
>=20
> SELECT *
> FROM item_detail
> WHERE guid =3D '$guid'
> AND start_date <=3D NOW()
> AND end_date >=3D NOW()
> ORDER BY sequence
>=20
> 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.
>=20
> (I'm avoiding joins because doing multiple selects in the code is
> usually much faster)
>=20
> Mark
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql