huge table

[krisc13]

I have a table with over 500k customers in it. When my program loads the select statement takes so long the user never gets fully logged in. Is this the right forum to post a select statement for possible advice? It is a mySQL database.

 

[lgarner]

It's the right forum. An index is where I'd start looking for the problem. Try running "explain <query>" where <query> is the login query that's submitted to the DB. A login query on a table that's indexed by user ID should really be very fast.

 

[krisc13]

Oh no I'm sorry I explained it wrong. It's not the login query that causes the problem. It's the select statement that retrieves the customers to display to the user. The login works very well. The problem is the main page won't load because this statement takes too long. Yes the table is indexed...

SELECT DISTINCT
cust_id,
cust_priority priority,
person_name_first f_name,
person_name_last l_name,
person_email_primary email,
person_address_a address,
person_address_a2 address_2,
person_address_a_city city,
person_address_a_zip zip,
person_address_a_state state,
customer__group.cust_group_id group_id,
customer__group.cust_group_shared_by shared_by,
customer__user.cust_user_id owner_id,
UNIX_TIMESTAMP(cust_lead_date) lead_date,
UNIX_TIMESTAMP(customer__user.cust_user_date_added) date_added,
UNIX_TIMESTAMP(customer__user.cust_user_date_viewed) date_viewed,
UNIX_TIMESTAMP(customer__user.cust_user_refer_date_in) date_refered_in,
UNIX_TIMESTAMP(customer__user.cust_user_refer_date_out) date_refered_out,
customer__user.cust_user_refered_by refered_by,
customer__user.cust_user_refered_to refered_to,
customer__user.cust_user_id cust_user
FROM person
JOIN customer
ON cust_person_id = person_id
LEFT JOIN customer__property
ON cust_prop_cust_id = cust_id
LEFT JOIN property
ON prop_id = cust_prop_prop_id
LEFT JOIN customer__group
ON cust_group_cust_id = cust_id
AND cust_group_group_id = 1
LEFT JOIN customer__user
ON cust_user_cust_id = cust_id
AND cust_user_user_id = 46
WHERE
(customer__group.cust_group_id IS NOT NULL OR customer__user.cust_user_id IS NOT NULL)
AND
(person_name_first LIKE '%%%' OR person_name_last LIKE '%%%' OR CONCAT(person_name_first,' ',person_name_last) LIKE '%%%')
ORDER BY customer.cust_lead_date DESC, f_name ASC LIMIT 0,16.

 

[lgarner]

Is the "AND" clause doing anything useful? It seems unnecessary, but I don't know if that would affect the speed. Still, the "explain" keyword might help show how hard the server is working, how many rows it's examining. Might give a tuning hint.

 

[krisc13]

Good call we're taking off the AND. However we tried explain and it times out before finishing as well.

 

[r937]

you have 6 tables in that query -- what indexes have you defined on each of them?

also, i'm pretty sure you won't be needing DISTINCT -- you do realize that DISTINCT causes a complete sort on all columns of the result set, yes?

r937.com | rudy.ca

 

[krisc13]

Yes. Okay we've ruled out everything but the order by. It seems the statement runs in 9 seconds without the order by clause. That's where it slows way down and times out. We have to have the customers ordered in this way when they display. Is there a faster way?

 

[r937]

did you remove the DISTINCT?

r937.com | rudy.ca

 

[krisc13]

Yes and it still timed out or hung rather. When we remove the order by it flies.

 

[r937]

sounds like you need an EXPLAIN expert (which i ain't)

since you are returning unix timestamps instead of date fields anyway, try ORDER BY lead_date instead

r937.com | rudy.ca

 

[krisc13]

Well we've tried everything. You can now login but it takes over 5 minutes for the page to load. Perhaps there is something we can do to the tables in mySQL besides an index? I'm at a loss...

 

[r937]

could you show your current table layouts including indexes please

r937.com | rudy.ca

 

[krisc13]

'customer', 'CREATE TABLE `customer` (

  `cust_id` int(9) unsigned NOT NULL auto_increment,
  `cust_person_id` int(9) unsigned NOT NULL default '0',
  `cust_group_id` int(5) unsigned NOT NULL default '0',
  `cust_priority` int(1) unsigned NOT NULL default '0',
  `cust_pipeline_status` enum('new','prospect','client','pending','closed','contact','dead') NOT NULL default 'new',
  `cust_type` enum('buy','sell','buy/sell') default NULL,
  `cust_credit_rating` int(1) unsigned default NULL,
  `cust_lead_id` int(12) unsigned default NULL,
  `cust_lead_date` timestamp NOT NULL default '0000-00-00 00:00:00',
  `cust_last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `cust_lead_source` varchar(20) default NULL,
  `cust_lead_ad_source` int(3) default NULL,
  `cust_lead_ad_number` int(3) default NULL,
  `cust_lead_looking_for` enum('unknown','agent','home') NOT NULL default 'agent',
  `cust_notes` blob,
  `prev_cust_id` int(10) NOT NULL,
  `prev_agent_id` int(10) NOT NULL,
  PRIMARY KEY  (`cust_id`),
  KEY `cust_lookup` (`cust_person_id`,`cust_lead_date`,`cust_last_modified`,`cust_priority`,
`cust_pipeline_status`,`cust  _type`),

  KEY `prev_cust_id` (`prev_cust_id`)
) 

'person', 'CREATE TABLE `person` (
  `person_id` int(10) NOT NULL auto_increment,
  `person_name_first` varchar(45) default NULL,
  `person_name_last` varchar(45) default NULL,
  `person_name_middle` varchar(45) default NULL,
  `person_address_a` varchar(50) default NULL,
  `person_address_a2` varchar(50) default NULL,
  `person_address_a_city` varchar(35) default NULL,
  `person_address_a_zip` varchar(13) default NULL,
  `person_address_a_state` char(3) default NULL,
  `person_address_a_country` char(3) default 'USA',
  `person_address_b` varchar(45) default NULL,
  `person_address_b2` varchar(45) default NULL,
  `person_address_b_city` varchar(30) default NULL,
  `person_address_b_zip` varchar(13) default NULL,
  `person_address_b_state` char(3) default NULL,
  `person_address_b_country` char(3) default 'USA',
  `person_phone_home` varchar(15) default NULL,
  `person_phone_work` varchar(15) default NULL,
  `person_phone_mobile` varchar(15) default NULL,
  `person_fax_home` varchar(15) default NULL,
  `person_fax_work` varchar(15) default NULL,
  `person_email_primary` varchar(45) default NULL,
  `person_email_secondary` varchar(45) default NULL,
  `person_text_email` varchar(45) default NULL,
  `person_email_signature` varchar(200) default NULL,
  `prev_cust_id` int(10) NOT NULL,
  `prev_agent_id` int(10) NOT NULL,
  PRIMARY KEY  (`person_id`),
  KEY `contact_info` (`person_name_first`,`person_name_last`,`person_address_a`,
`person_address_a_city`,`person_address_a  _state`,`person_phone_home`,`person_phone_mobile`,`person_email_primary`,
`person_email_secondary`),

  KEY `prev_cust_id` (`prev_cust_id`),
  FULLTEXT KEY `name_lookup` (`person_name_first`,`person_name_last`,`person_name_middle`)
) 

'property', 'CREATE TABLE `property` (
  `prop_id` int(9) unsigned NOT NULL auto_increment,
  `prop_status` enum('buying','selling') NOT NULL default 'selling',
  `prop_type` varchar(25) default NULL,
  `prop_style` varchar(25) default NULL,
  `prop_price_min` int(8) unsigned default NULL,
  `prop_price_max` int(8) unsigned default NULL,
  `prop_condition` int(1) unsigned default '0',
  `prop_address` varchar(45) default NULL,
  `prop_address_2` varchar(45) default NULL,
  `prop_city` varchar(30) default NULL,
  `prop_zip` varchar(15) default NULL,
  `prop_state` char(3) default NULL,
  `prop_country` char(3) default NULL,
  `prop_county` varchar(45) default NULL,
  `prop_subdivision` varchar(45) default NULL,
  `prop_school_district` varchar(45) default NULL,
  `prop_yr_built` int(4) unsigned default NULL,
  `prop_sq_feet` int(7) unsigned default NULL,
  `prop_lot_size` float unsigned default NULL,
  `prop_num_bedrooms` int(3) unsigned default NULL,
  `prop_num_baths` int(2) unsigned default NULL,
  `prop_num_baths_half` int(2) unsigned default NULL,
  `prop_num_fireplaces` int(2) unsigned default NULL,
  `prop_type_ac` varchar(30) default NULL,
  `prop_type_heat_source` varchar(30) default NULL,
  `prop_type_heat_system` varchar(30) default NULL,
  `prop_notes` blob,
  `prev_cust_id` int(10) default NULL,

  PRIMARY KEY  (`prop_id`),
  KEY `prop_type` (`prop_type`,`prop_style`),
  KEY `prop_id` (`prop_id`),
  KEY `prev_cust_id` (`prev_cust_id`)
) 

'customer__group', 'CREATE TABLE `customer__group` (
  `cust_group_id` int(9) unsigned NOT NULL auto_increment,
  `cust_group_group_id` int(6) unsigned NOT NULL default '0',
  `cust_group_cust_id` int(9) unsigned NOT NULL default '0',

  `cust_group_shared_by` varchar(50) default NULL,
  `cust_group_` timestamp NULL default NULL,
  PRIMARY KEY  (`cust_group_id`),
  KEY `cust_group_group_id` (`cust_group_group_id`,`cust_group_cust_id`)
) 


'customer__user', 'CREATE TABLE `customer__user` (
  `cust_user_id` int(12) unsigned NOT NULL auto_increment,
  `cust_user_cust_id` int(9) unsigned NOT NULL default '0',
  `cust_user_user_id` int(9) unsigned default NULL,

  `cust_user_date_added` timestamp NULL default NULL,
  `cust_user_date_viewed` timestamp NULL default NULL,
  `cust_user_refered_by` varchar(40) default NULL,
  `cust_user_refered_to` varchar(40) default NULL,

  `cust_user_refer_date_in` timestamp NULL default NULL,
  `cust_user_refer_date_out` timestamp NULL default NULL,
  `cust_user_del_user_id` int(9) unsigned default NULL,
  `newold` int(1) NOT NULL default '0',

  PRIMARY KEY  (`cust_user_id`),
  KEY `cust_user_cust_id` (`cust_user_cust_id`,`cust_user_user_id`),
  KEY `cust_user_refered_by` (`cust_user_refered_by`,`cust_user_refer_date_in`,
`cust_user_refer_date_out`),

  KEY `cust_user_del_user_id` (`cust_user_del_user_id`)
)

I hope this is the right info!

 

[r937]

table customer__group should not have an auto_increment PK, instead the existing key on (cust_group_group_id, cust_group_cust_id) should be the PK, and there should be an additional index on
(cust_group_cust_id, cust_group_group_id)

table customer__user should not have an auto_increment PK, instead the existing key on (cust_user_cust_id, cust_user_user_id) should be the PK (and there should be an additional index on (cust_user_user_id, cust_user_cust_id), although this query won't need it)

if table customer__property (not shown) is like the others, it also should not have an auto_increment PK, the PK should be (cust_prop_cust_id, cust_prop_prop_id) and there should be an additional index on (cust_prop_prop_id, cust_prop_cust_id)

tell me again why you're converting to unix timestamps? you might want to change the ORDER BY to lead_date instead of cust_lead_date

r937.com | rudy.ca

 

[krisc13]

We're using unix timestamps because the code within the program uses unix timestamps. That how we want to save them. We also tried order by lead_date and that did not help. I'll look into the other things you mentioned now.