Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Table optimization help needed

Posted on 2011-02-22
3
Medium Priority
?
261 Views
Last Modified: 2012-06-27
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
Comment
Question by:EddieShipman
3 Comments
 
LVL 80

Accepted Solution

by:
arnold earned 1000 total points
ID: 34952519
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
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 1000 total points
ID: 34952688
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
 
LVL 26

Author Closing Comment

by:EddieShipman
ID: 34981412
Since I'm searching strictly on inventory_part_number, I don't need the second column in the index.
0

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question