INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Slow Return from table

Slow Return from table

(OP)
Hi,

The MySQL database I've got is running on a Windows SBS 2003 server, enough memory and only a hand full of users.

We've an orders table that currently holds around 15k of rows.

When I do the following:-

SELECT * FROM orders WHERE CustomerSurname = 'Smith'

I've just selected that CustomerSurname is a secondary index but not altered anything from default.

A search can take a round 20 seconds (when you're on the phone, it's slow!).

What should I be looking at to make the SQL server run faster?

Cheers for any help in advance.
Carl.

RE: Slow Return from table

Frequently the cause of slow responses from a query such as yours is because of no or an inappropriate index.

Can you show us the output from a 'DESC orders' command please?

Andrew

RE: Slow Return from table

(OP)
Hi,

Looking at this I've not got secondary indexes defined correctly? ProcessStatus seems to be the only one with MUL against it.

Carl.


Field Type Null Key Default Extra
OrderCode varchar(50) NO PRI NULL
OrderDate datetime YES NULL
OrderTime datetime YES NULL
DeliveryCost double YES NULL
SubTotal double YES NULL
CustomerName varchar(75) YES NULL
CustomerEmail varchar(50) YES NULL
CustomerPhone varchar(50) YES NULL
DeliveryAddress varchar(255) YES NULL
BillingAddress varchar(255) YES NULL
Amount double YES NULL
Basket text YES NULL
dob varchar(45) YES NULL
sex varchar(45) YES NULL
weight varchar(45) YES NULL
height varchar(45) YES NULL
bloodpressures varchar(45) YES NULL
bloodpressured varchar(45) YES NULL
bmi varchar(45) YES NULL
med1 text YES NULL
med2 text YES NULL
med3 text YES NULL
med4 text YES NULL
q_answers text YES NULL
orderedbefore varchar(45) YES NULL
mailshot varchar(45) YES NULL
foundus varchar(255) YES NULL
status varchar(45) YES NULL
discount varchar(45) YES NULL
discounttype varchar(45) YES NULL
bpsystolic varchar(45) YES NULL
bpdiastolic varchar(45) YES NULL
bpdate varchar(45) YES NULL
sideeffects varchar(45) YES NULL
exportedtosage int(11) YES NULL
trackingcode varchar(45) YES NULL
processstatus tinyint(4) YES MUL NULL
PaymentStatus varchar(45) YES NULL
DoctorApproved tinyint(1) YES NULL
AssignedDoctor varchar(45) YES NULL
PaidDoctor tinyint(1) YES NULL
CustomerSurname varchar(45) YES NULL
OrderEmailed tinyint(1) YES NULL
OrderPrinted tinyint(1) YES NULL
DispatchedEmailed tinyint(1) YES NULL
ProcessedDate date YES NULL
PatientDetailsTransferred tinyint(1) YES NULL
DoctorCode varchar(4) YES NULL
PostShot varchar(45) YES NULL
Receipt int(11) YES NULL
MedLabPrinted tinyint(1) YES NULL
DelLabPrinted tinyint(1) YES NULL
PrescriptionPrinted tinyint(1) YES NULL
NonePrescriptionPrinted tinyint(1) YES NULL
Site varchar(1) YES NULL
HasComments int(11) YES NULL
DateDispatched date YES NULL
OrderType varchar(45) YES NULL
Title varchar(45) YES NULL
TimeUpdated datetime YES NULL
LastStatus int(11) YES NULL
edCheckupWarning varchar(45) YES NULL
ZeroVAT tinyint(1) YES NULL
Country varchar(45) YES NULL
Completed tinyint(1) YES 0
additionalVars text YES NULL
BillingCountry varchar(45) YES NULL
LastOrderDate datetime YES NULL
PaymentTaken tinyint(1) YES NULL

RE: Slow Return from table

I suggest you add an index to your orders table with something like

CODE

ALTER TABLE orders ADD INDEX(surname) 

and you should then experience sub second response time (ignoring network delays).

Andrew

RE: Slow Return from table

(OP)
Hi,

Just doing some further research and this is the index information:-

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
orders 0 PRIMARY 1 OrderCode A 15617 NULL NULL BTREE
orders 1 SECONDARY 1 processstatus A 54 NULL NULL YES BTREE
orders 1 SECONDARY 2 CustomerSurname A 15617 NULL NULL YES BTREE
orders 1 SECONDARY 3 ProcessedDate A 15617 NULL NULL YES BTREE
orders 1 SECONDARY 4 dob A 15617 NULL NULL YES BTREE
orders 1 SECONDARY 5 Receipt A 15617 NULL NULL YES BTREE
orders 1 SECONDARY 6 CustomerName A 15617 NULL NULL YES BTREE

RE: Slow Return from table

(OP)
WOW! That is impressive! I'll do some admin work and now add some indexes!

Cheers for your help
Carl.

RE: Slow Return from table

(OP)
I've now completed that for all the search criteria that I use and it's pulling information back in less than a second (two at the outside).

Thank you very much.
Carl.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close