• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

Table optimization help needed

I've got the following table structure:
CREATE TABLE `linv_inventory` (
  `inventory_id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `inventory_part_number` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `inventory_alt_part_number` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `inventory_condition_code` varchar(5) collate utf8_unicode_ci NOT NULL default 'SV',
  `inventory_quantity` int(11) NOT NULL default '0',
  `inventory_description` text collate utf8_unicode_ci,
  `last_update` date NOT NULL,
  `stripped_part_number` varchar(255) collate utf8_unicode_ci NOT NULL,
  `stripped_alt_part_number` varchar(255) collate utf8_unicode_ci default NULL,
  `deal_item` tinyint(4) default NULL,
  `order_link` varchar(255) collate utf8_unicode_ci default NULL,
  `image_link` varchar(255) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`inventory_id`),
  KEY `inventory_part_number` (`inventory_part_number`(30),`inventory_alt_part_number`(30)),
  KEY `stripped_part_number` (`stripped_part_number`(30),`stripped_alt_part_number`(30))
) ENGINE=MyISAM AUTO_INCREMENT=38425379 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Open in new window

The table has 2.7 million records. When executing this query:
SELECT `inventory_part_number` FROM `linv_inventory` ORDER BY `inventory_part_number` LIMIT 0, 10000

Open in new window

it takes > 5 min to retrieve the records.

Most of the data in the four columns of the secondary indexes is not > 30 chars, there are 5,500 rows where the
inventory_part_number > 30, 6,200 rows where the inventory_alt_part_number > 30. The stripped_????
columns will always be shorter. MAX(LENGTH(inventory_part_number) returns 86 chars. One user has
multiple alt_part_numbers so there are 137 rows where length(trim(inventory_alt_part_number)) > 100
with several truncated at 255.

What can I do to optimize this table so the query will not take so long? Is it OK to have the secondary
indexes use a partial string?
0
Eddie Shipman
Asked:
Eddie Shipman
2 Solutions
 
arnoldCommented:
run:
explain <your query>

Add index for inventory_part_number.

create index inv_part_numb on linv_inventory (inventory_part_number)

varchar is also not a good candidate for indexing.  
part_number would suggest that the column should have numeric data.
0
 
HainKurtSr. System AnalystCommented:
if you create index like this, your query will not read the table at all and just use the index to query and return the result...

KEY `inventory_part_number` (`inventory_part_number`,`inventory_alt_part_number`),
0
 
Eddie ShipmanAll-around developerAuthor Commented:
Since I'm searching strictly on inventory_part_number, I don't need the second column in the index.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now