Database Design Question?

[cjtaylor]

Hello,

I am designing a grocery store application, I am not new to programming but I am new to database development. So, I need to ask some basic database design questions.

I have a product table with approx. 50,000 records. When I do a lookup (i.e Scan product) I take the upc code and query the database with a select statement like:

select field1, field2, field3, field4 where upc_code = '12345657890'

This lookup takes a long time, approximate a half to one second to complete. When I see other grocery systems, the lookup seems to be almost instantaneous. So, my question is how do I speed this up? Do other systems copy the db and query them locally? Stored procedures? What do I need to design differently.

I am using Visual Studio.Net with MySql 4.1 server and the MySqlConnector 1.06 for .Net

Any insight to this issue would be greatly appreciated.

Thank you.
Chris Taylor

 

[ggggus]

create an index on upc_code

http://dev.mysql.com/doc/refman/4.1/en/create-index.html

check out that link for instructions.

The index will make your query much faster.

***************************************
J. Jacobs

http://www.piercedmedia.com

 

[johnwm]

Check that you are using upc_code as your primary key in the products table as well

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[dexter195]

when you create a primary key, an index (in most databases) is automatically set up. if you have a primary key in your table already you may want to revise the table design and see if a joint primary key is required. this is two table columns combined to make up a primary key (just in case your not familiar with this term)

i presume your using a local database not a distributed one. if thats the case you may need to check that your harddrives are defraged and that the database has been tided up after taking in large amounts of data.

if your product table has that many entries i would suggest checking to see if there are any products that arnt required any more. these can be placed in a backup table with the same attributs as the original table. this will help to improve performance.

 

[cjtaylor]

Hey guys,

Thanks for the suggestions, creating the index on upc_code did the trick. Now my lookups are lightning fast.

Thanks again
Chris Taylor