List:General Discussion« Previous MessageNext Message »
From:SGreen Date:December 21 2005 3:00pm
Subject:Re: Do I need to tweak my server variables for this SELECT statement?
View as plain text  
Hank <heskin@stripped> wrote on 12/21/2005 09:39:50 AM:

> Grant,
> 
>   You can just to a "desc pn_pricecompare_catprod" and "desc
> pn_pricecompare_product" and post the results.  The CREATE TABLE
> statements would be OK, but the describes are better.
> 
> The flush the query cache, I think if you do a "flush tables".
> 
> -Hank
> 

I prefer the SHOW CREATE TABLE because it gives you much more information. 
Please compare the two from one of my databases:

localhost.wh_data>desc person_phonenumber;
+----------------+------------------+------+-----+-------------------+-------+
| Field          | Type             | Null | Key | Default           | 
Extra |
+----------------+------------------+------+-----+-------------------+-------+
| Person_ID      | int(11) unsigned |      | PRI | 0                 |   |
| PhoneNumber_ID | int(11) unsigned |      | PRI | 0                 |   |
| Active         | tinyint(1)       | YES  |     | 1                 |   |
| type           | varchar(10)      |      |     |                   |   |
| tsLastModified | timestamp        | YES  |     | CURRENT_TIMESTAMP |   |
+----------------+------------------+------+-----+-------------------+-------+
5 rows in set (0.06 sec)

localhost.wh_data>show create table person_phonenumber\G
*************************** 1. row ***************************
       Table: person_phonenumber
Create Table: CREATE TABLE `person_phonenumber` (
  `Person_ID` int(11) unsigned NOT NULL default '0',
  `PhoneNumber_ID` int(11) unsigned NOT NULL default '0',
  `Active` tinyint(1) default '1',
  `type` varchar(10) NOT NULL default '',
  `tsLastModified` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
CURRENT_TIMESTAMP,
  PRIMARY KEY  (`Person_ID`,`PhoneNumber_ID`),
  KEY `PhoneNumber_ID` (`PhoneNumber_ID`),
  CONSTRAINT `0_105` FOREIGN KEY (`PhoneNumber_ID`) REFERENCES 
`phonenumber` (`ID`),
  CONSTRAINT `0_106` FOREIGN KEY (`Person_ID`) REFERENCES `person` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

I would have never been able to tell from DESCRIBE that there were foreign 
keys or that this was an InnoDB table. You may prefer the simplified 
output of describe but not me.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard21 Dec
  • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
    • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
      • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
        • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
          • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
          • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
            • Re: Do I need to tweak my server variables for this SELECT statement?Hank23 Dec
              • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens23 Dec
                • Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard23 Dec
                  • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens23 Dec
                    • Re: Do I need to tweak my server variables for this SELECT statement?Hank24 Dec
    • Filtering joinEris Ristemena21 Dec
      • Re: Filtering joinPeter Brawley21 Dec
      • Re: Filtering joinGleb Paharenko21 Dec
    • Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard21 Dec
    • Re: Filtering joinEris Ristemena21 Dec
    • Re: Filtering joinEris Ristemena21 Dec
      • Re: Filtering joinSGreen21 Dec
        • Re: Filtering joinEris Ristemena21 Dec
          • Re: Filtering joinSGreen21 Dec
            • Re: Filtering joinEris Ristemena21 Dec
Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
  • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
    • Re: Do I need to tweak my server variables for this SELECT statement?SGreen21 Dec