Solved

Table optimization help needed

Posted on 2011-02-22
3
251 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 77

Accepted Solution

by:
arnold earned 250 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 51

Assisted Solution

by:HainKurt
HainKurt earned 250 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

815 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now