RE: Unique index - opinions sought
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:m...@good-stuff.co.uk] > 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
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
RE: Unique index - opinions sought
How many rows? If 1K, it does not matter. If 1 billion, we need to discuss in more detail. Let's assume 1M... 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. Is this InnoDB or MyISAM? (Please provide SHOW CREATE TABLE, not English, for describing tables.) Let's see the SELECTs that will be hitting the tables. Then we can discuss in more detail. > -Original Message- > From: Mark Goodge [mailto:m...@good-stuff.co.uk] > Sent: Monday, July 16, 2012 8:09 AM > To: mysql > Subject: Unique index - opinions sought > > I have a MySQL table (call it, say, item_spine) which contains three > fields which, together, form a unique key. These three fields are a > guid, a start date and an end date. The guid is alphanumeric (a fixed- > length six characters) and the dates are ISO format dates (-MM-DD). > > I also have another table (item_detail) containing data which is keyed > to the first in that the unique key here is the unique key from > item_spine plus a line id (which is numeric). > > At the moment, I simply have the three fields in the item_spine set as > a unique key, and replicate those three columns in item_detail and have > those plus line_id as the unique key, thus making a four-column key. > > But, for performance reasons, I was wondering if it might make more > sense to create a single column in item_spine containing data which is > generated from the original three and use that as a unique key instead. > I then only need a single column in item_detail to link to item_spine, > and thus my unique key there can be only two columns. > > Another option is to have an autoincrement column as a primary key for > item_spine, and then use that as the link key for item_detail. But I'd > prefer to avoid that, because the content of item_spine has to be > updated on a regular basis from external data and using autoincrement > means I can't do that using REPLACE INTO while still maintaining a key > association with item_detail. > > Any thoughts? How would you do it? > > Mark > -- > Sent from my Turing-Flowers Colossus > http://mark.goodge.co.uk > > > -- > 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
Unique index - opinions sought
I have a MySQL table (call it, say, item_spine) which contains three fields which, together, form a unique key. These three fields are a guid, a start date and an end date. The guid is alphanumeric (a fixed-length six characters) and the dates are ISO format dates (-MM-DD). I also have another table (item_detail) containing data which is keyed to the first in that the unique key here is the unique key from item_spine plus a line id (which is numeric). At the moment, I simply have the three fields in the item_spine set as a unique key, and replicate those three columns in item_detail and have those plus line_id as the unique key, thus making a four-column key. But, for performance reasons, I was wondering if it might make more sense to create a single column in item_spine containing data which is generated from the original three and use that as a unique key instead. I then only need a single column in item_detail to link to item_spine, and thus my unique key there can be only two columns. Another option is to have an autoincrement column as a primary key for item_spine, and then use that as the link key for item_detail. But I'd prefer to avoid that, because the content of item_spine has to be updated on a regular basis from external data and using autoincrement means I can't do that using REPLACE INTO while still maintaining a key association with item_detail. Any thoughts? How would you do it? Mark -- Sent from my Turing-Flowers Colossus http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
