Skip navigation links

MySQL Forums :: Performance :: Using Filesort and Using Temporary


Advanced Search

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 10144 Lee Evanochko 07/27/2006 07:53AM
Re: Using Filesort and Using Temporary 4932 Lee Evanochko 07/31/2006 07:37AM
Re: Using Filesort and Using Temporary 5118 Peter Zaitsev 07/31/2006 07:45AM
Re: Using Filesort and Using Temporary 4878 Lee Evanochko 07/31/2006 08:12AM
Re: Using Filesort and Using Temporary 4758 Peter Zaitsev 08/16/2006 01:54PM
Re: Using Filesort and Using Temporary 5552 Ravenous Bugblatter Beast 08/15/2006 04:58PM


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.