0

I have a table in place with less than 50,000 records, which tracks the movement of various shipments.

Each shipment is assigned a tracking number.

Each shipment will then go through a series of 'workflows' when it reaches a certain point. There are a little over 10 workflows that a single shipment can go through. Some workflows may get skipped depending on the situation, but every shipment will have a beginning workflow and an ending workflow.

The table is set to track each shipment as it moves through the different workflows, thus saving the history of the workflows. With that said, the table will save over 10 records of the same shipment as it moves through each workflow.

They may look like duplicates, but they are not because the workflow and datestamps will be different.

Now, when a user runs a search for a particular group of shipments coming out of a particular area, the query takes so long to bring back the records. When I say 'so long', I mean it can take well over 30 seconds to a minute.

The primary keys are set to the tracking number, name of the shipper, the delivery location, but not the workflow.

I know this is not much to work with, but off top, how would I go about indexing a table like this?

I was trying to add a view of the table, but I could not figure out how to display it here.

halfer
  • 19,824
  • 17
  • 99
  • 186
John Beasley
  • 2,577
  • 9
  • 43
  • 89
  • The full current table schema would be helpful...but you might want to look at a composite index – AJefferiss Sep 03 '15 at 15:30
  • 1
    You want to apply indexes based on how you search the table. Can you provide some examples regarding how the table is queried or used in joins? – Athens Holloway Sep 03 '15 at 16:09
  • 1
    Evaluate your query execution plan to help identify the source of your problem. 50K rows is not alot of data, but your joins, lack of indexes and possible tables scans could be the source of your problem. Check out https://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html – Athens Holloway Sep 03 '15 at 16:17

2 Answers2

0

You can set index on column u use to search.

Example

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

More docs about mysql index http://www.tutorialspoint.com/mysql/mysql-indexes.htm

Yasin Kocak
  • 1
  • 1
0

One thing to note is that you already have an index for the Primary key - see Is the primary key automatically indexed in MySQL?

It may be quicker to have an index just for the columns that are searched. One thing to note is that you cannot have a unique index for that data as stated.

Community
  • 1
  • 1
Alan Wood
  • 1
  • 1
  • 1
    are you referring to search indexing? Is that the same thing a running a query? – John Beasley Sep 03 '15 at 15:50
  • 1
    Indexes and queries are separate. An index is a permanent structure updated in line with data changes, a query is transient. The article in Yasin's answer looks to be a reasonable read. If you create an index that includes all columns in a standard query, then that query will be improved in most cases – Alan Wood Sep 03 '15 at 16:04