Slow Return from table 1

[Ogi]

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.

 

[towerbase]

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

 

[Ogi]

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

 

[towerbase]

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

ALTER TABLE orders ADD INDEX(surname)

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

Andrew

 

[Ogi]

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

 

[Ogi]

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

Cheers for your help
Carl.

 

[Ogi]

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.