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.