Solved

Table optimization help needed

Posted on 2011-02-22
3
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 78

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 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

751 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