Solved

Table optimization help needed

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 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