MySQL Forums
Forum List  »  Performance

Using Filesort and Using Temporary
Posted by: Lee Evanochko
Date: July 27, 2006 07:53AM

Hello,

I have gone thru the various pages on the mysql page on this subject but have not yet found a solution, I was hoping to get rid of filesort and temporary usage from these queries, any suggestions would be VERY welcomed.

To get you up to speed I have run ANALYZE and OPTIMIZE table on each, I have listed the indexes, describe, and creation data for each table as well as the explain for each query.

If you can I would love to hear what your thoughts are.

Row count:
- canada 			1,241,367
- canada_categories 	1,824,185
- categories		   11,653


-- Table "canada" DDL

CREATE TABLE `canada` (
  `id` int(10) NOT NULL auto_increment,
  `recordid` int(7) default NULL,
  `name` varchar(100) default NULL,
  `address` varchar(100) default NULL,
  `city` varchar(100) default NULL,
  `province` varchar(4) default NULL,
  `postalcode` varchar(10) default NULL,
  `phone` varchar(100) default NULL,
  `fax` varchar(100) default NULL,
  `contact` varchar(100) default NULL,
  `description` varchar(100) default NULL,
  `latitude` decimal(10,2) default NULL,
  `longitude` decimal(10,2) default NULL,
  PRIMARY KEY  (`id`),
  KEY `City` (`city`(25)),
  KEY `recordid` (`recordid`),
  KEY `province` (`province`),
  KEY `phone` (`phone`),
  KEY `Name` (`name`),
  FULLTEXT KEY `FTName` (`name`)
) TYPE=MyISAM
-- describe
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(10)       |      | PRI | NULL    | auto_increment |
| recordid    | int(7)        | YES  | MUL | NULL    |                |
| name        | varchar(100)  | YES  | MUL | NULL    |                |
| address     | varchar(100)  | YES  |     | NULL    |                |
| city        | varchar(100)  | YES  | MUL | NULL    |                |
| province    | varchar(4)    | YES  | MUL | NULL    |                |
| postalcode  | varchar(10)   | YES  |     | NULL    |                |
| phone       | varchar(100)  | YES  | MUL | NULL    |                |
| fax         | varchar(100)  | YES  |     | NULL    |                |
| contact     | varchar(100)  | YES  |     | NULL    |                |
| description | varchar(100)  | YES  |     | NULL    |                |
| latitude    | decimal(10,2) | YES  |     | NULL    |                |
| longitude   | decimal(10,2) | YES  |     | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+

-- show indexes
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| canada |          0 | PRIMARY  |            1 | id          | A         |     1241367 |     NULL | NULL   |      | BTREE      |         |
| canada |          1 | City     |            1 | city        | A         |        6115 |       25 | NULL   | YES  | BTREE      |         |
| canada |          1 | recordid |            1 | recordid    | A         |      413789 |     NULL | NULL   | YES  | BTREE      |         |
| canada |          1 | province |            1 | province    | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |
| canada |          1 | phone    |            1 | phone       | A         |     1241367 |     NULL | NULL   | YES  | BTREE      |         |
| canada |          1 | Name     |            1 | name        | A         |     1241367 |     NULL | NULL   | YES  | BTREE      |         |
| canada |          1 | FTName   |            1 | name        | A         |      206894 |     NULL | NULL   | YES  | FULLTEXT   |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

-- Table "canada_categories" DDL

CREATE TABLE `canada_categories` (
  `id` int(10) NOT NULL auto_increment,
  `cid` int(4) NOT NULL default '0',
  `bid` int(10) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `CID` (`cid`),
  KEY `BID` (`bid`)
) TYPE=MyISAM

-- describe
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(10) |      | PRI | NULL    | auto_increment |
| cid   | int(4)  |      | MUL | 0       |                |
| bid   | int(10) |      | MUL | 0       |                |
+-------+---------+------+-----+---------+----------------+

-- show indexes
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table             | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| canada_categories |          0 | PRIMARY  |            1 | id          | A         |     1824185 |     NULL | NULL   |      | BTREE      |         |
| canada_categories |          1 | CID      |            1 | cid         | A         |        6538 |     NULL | NULL   |      | BTREE      |         |
| canada_categories |          1 | BID      |            1 | bid         | A         |     1824185 |     NULL | NULL   |      | BTREE      |         |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


-- Table "categories" DDL

CREATE TABLE `categories` (
  `id` int(4) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `sicname_fulltext` (`name`)
) TYPE=MyISAM

-- describe
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(4)       |      | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  | MUL | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

-- show indexes
+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| categories |          0 | PRIMARY          |            1 | id          | A         |       11653 |     NULL | NULL   |      | BTREE      |         |
| categories |          1 | sicname_fulltext |            1 | name        | A         |        5826 |     NULL | NULL   | YES  | FULLTEXT   |         |
+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

-- The sql statements are:

-- This statement lists the provinces that the result set will contain

select DISTINCT C.province from canada C, canada_categories CC where C.id = CC.bid and CC.cid in ('2405')

--explain:

+-------+--------+---------------+---------+---------+--------+-------+------------------------------+
| table | type   | possible_keys | key     | key_len | ref    | rows  | Extra                        |
+-------+--------+---------------+---------+---------+--------+-------+------------------------------+
| CC    | ref    | CID,BID       | CID     |       4 | const  | 48462 | Using where; Using temporary |
| C     | eq_ref | PRIMARY       | PRIMARY |       4 | CC.bid |     1 |                              |
+-------+--------+---------------+---------+---------+--------+-------+------------------------------+

-- This statement lists the first letters of the results so I know what 
-- letters to display for sorting reasons
select DISTINCT SUBSTRING(C.name,1,1) as letter from canada C, canada_categories CC where C.id = CC.bid and CC.cid in ('2405')

-- explain:

+-------+--------+---------------+---------+---------+--------+-------+------------------------------+
| table | type   | possible_keys | key     | key_len | ref    | rows  | Extra                        |
+-------+--------+---------------+---------+---------+--------+-------+------------------------------+
| CC    | ref    | CID,BID       | CID     |       4 | const  | 48462 | Using where; Using temporary |
| C     | eq_ref | PRIMARY       | PRIMARY |       4 | CC.bid |     1 |                              |
+-------+--------+---------------+---------+---------+--------+-------+------------------------------+

-- This is the main query which returns my results

select C.id,C.name,C.phone from canada C, canada_categories CC where C.id = CC.bid and CC.cid in ('2405') order by C.name limit 0,25

-- explain:

+-------+--------+---------------+---------+---------+--------+-------+----------------------------------------------+
| table | type   | possible_keys | key     | key_len | ref    | rows  | Extra                                        |
+-------+--------+---------------+---------+---------+--------+-------+----------------------------------------------+
| CC    | ref    | CID,BID       | CID     |       4 | const  | 48462 | Using where; Using temporary; Using filesort |
| C     | eq_ref | PRIMARY       | PRIMARY |       4 | CC.bid |     1 |                                              |
+-------+--------+---------------+---------+---------+--------+-------+----------------------------------------------+



Edited 1 time(s). Last edit at 07/27/2006 07:53AM by Lee Evanochko.

Options: ReplyQuote


Subject
Views
Written By
Posted
Using Filesort and Using Temporary
11965
July 27, 2006 07:53AM


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.