MySQL Forums
Forum List  »  PHP

Seeks Improved Efficency: Redundant Query Data
Posted by: stanley tweedle
Date: April 29, 2013 07:16AM

Greetings. I seek your advisory for the following 2-table simple SQL problem. (i believe, simple, that is). Super-details (eg. Table3), i might add later (Eg. producer, writer, credits), but i need to get the thing moving, first. I've tried to use Std MySQL doc terminology, where applicable (Eg. table1.col1 , table2.col2). Please, don't hesitate to tell me what YOU need / wish to know about my task. Thank you!

Table1, Master:
CREATE TABLE `cp_master` (
  `master_pk` int(11) NOT NULL AUTO_INCREMENT COMMENT 'master table primary key',
  `master_date` date NOT NULL,
  `master_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`master_pk`),
  UNIQUE KEY `master_date` (`master_date`,`master_timestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
Table2, Details:
CREATE TABLE `cp_details` (
  `details_pk` int(11) NOT NULL AUTO_INCREMENT,
  `details_fk` int(11) NOT NULL COMMENT 'insert foreign key',
  `details_date` date NOT NULL,
  `details_toonname` tinytext NOT NULL,
  `details_toonepi` tinytext NOT NULL,
  `details_toontime` time NOT NULL,
  PRIMARY KEY (`details_pk`),
  KEY `details_fk` (`details_fk`,`details_date`)
) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=utf8 COMMENT='Cartoon Planet: Details'
This is a private/ personal DB created for Mgt of DVR media (indeed, @cartoonNetwork’s Cartoon Planet. yikes!).

Using phpMyAdmin to create / admin the thing, i find these queries are quite redundant. Having been much more active in the DB Admin game, years ago in my studies, i'm a bit rusty in terms of
“How might I minimize my query, while maintaining equal results?”

E.G.
INSERT INTO `cartoonplanet`.`cp_details` 
(`details_pk`, `details_fk`, `details_date`, `details_toonname`, `details_toonepi`, `details_toontime`)
 VALUES 
(NULL, '11', '2013-04-05', 
'Courage the Cowardly Dog', 'Klub Katz', '00:27:30');
My thoughts:
First, the timestamp on the master. It's superfluous, from any perspective, truly. I don't /really/ need to know at what time X was entered to Y, etc., but knowing i've usually used TimeStamps for indexing, etc., i stuck it there. Whatever: likely NOT at issue (and would have been of more use, had I placed it instead, in the Table2.)
Second
I'm looking at the query, thinking: i've got a PK (Eg. 11, in the code above), and an index (Eg. 2013-04-05). This is what bothers me. I don't really need both, in my Table2 INSERT Query.
At the crux
In addition to your valued advisory, RE optimization (Eg. you should have placed A at B, and used C instead of D), i wonder: supposing I code a proper interface for this DB, outside of the PMA-zone as ultimately desirable, what is most important, in terms of the data entry, are
table2.`details_date`
and, particular to that bit of data, the
table2.`details_toontime`
, which reflects the counter on the DVR (Eg. 00:30:00 , where 30 -eq 30 mins).

Summary
How might I better submit such a query; prep the table for more efficient PHP; realizing there are multiple perspectives from which to attack this problem, please, how ever you wish to view it, i suspect you understand my general plight.

I've coded PHP / MySQL queries using LEFT JOIN, and INSERT's using SELECT statements. So, i'm not quite at beginner mode here. I'm seeking some feedback, having no one to bounce the topic-off-of, so to speak, other than ye in the cyberverse.

When I look at the query, I think: how am I going to manipulate/ USE this data, from a PHP / interface point-of-view. Should I be placing the table2.ForeignKey-Ref (table2.`details_fk`) AND the date-as-index (table2.`details_date`) in EVERY submit?

I sense i'm encountering a “Which Came First: The Chicken-or-the-Egg scenario”, where my application is going to likely want to See/ rather, Display the dates, not the Keys.
E.G.
 SQL = "INSERT INTO table2 VALUES '','SELECT...','SELECT...','ToonTitle','ToonEpisode','ToonTime'";
But, i'm having difficulty wrapping my head around-it, so to speak (ie. it's been so long since i've done such coding, from a completely home-baked recipe).

Your feedback will be appreciated.

EDIT: SQL Gathered while working so far (gathering into a new Netbeans project, for some sanity)...

Insert a new table1.master row:
$sql = "INSERT INTO `cartoonplanet`.`cp_master` " . 
"(`master_pk`, `master_date`, `master_timestamp`) " .
"VALUES (NULL, \'20XX-XX-XX\', CURRENT_TIMESTAMP);";

Insert a new table2.details row:
$sql = "INSERT INTO `cartoonplanet`.`cp_details` " .
"(`details_pk`, `details_fk`, `details_date`, `details_toonname`, `details_toonepi`, `details_toontime`) ".
"VALUES (NULL, \'11\', \'2013-04-05\', \'Courage the Cowardly Dog\', \'Klub Katz\', \'00:27:30\');";

Find a Toon by Name:
$sql = "SELECT * FROM `cartoonplanet`.`cp_details`\n" . 
"WHERE `details_toonname` LIKE \'TOON_NAME\' ORDER BY `cp_details`.`details_pk` " .
"ASC LIMIT 0, 30 ";

Show what ToonEpi(sodes) are particular to the date(Index):
$sql = 'SELECT *'
        . ' FROM `cp_details`'
        . ' WHERE `details_date` = \'XXXX-XX-XX\';';

best regards!

user is Author of NoviceNotes.Net



Edited 7 time(s). Last edit at 04/29/2013 11:54AM by stanley tweedle.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.