Jump to content

Select from 2 big tables...


beyzad

Recommended Posts

Hello there.

 

I have 2 table:

 

factors:

CREATE TABLE IF NOT EXISTS `factors` (
  `vendor_id` varchar( COLLATE utf8_unicode_ci NOT NULL,
  `factor_key` varchar(18) COLLATE utf8_unicode_ci NOT NULL,
  `factor_status` enum('0','1','2','3','4','5','6','7','8','9','10','11') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '0: pending. 1: ready. 2:sent. 3:delivered. 4:canceled. 5:returned. 6:return_confirmed. 7: canceled_by_post. 8: banned. 9: waiting. 10: failed CASH. 11: transferred',
  UNIQUE KEY `factor_key` (`factor_key`),
  KEY `vendor_id` (`vendor_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

factor_times:

CREATE TABLE IF NOT EXISTS `factor_times` (
  `factor_key` varchar(18) COLLATE utf8_unicode_ci NOT NULL,
  `factor_status` int(11) NOT NULL,
  `factor_time` int(11) NOT NULL,
  KEY `factor_key` (`factor_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Factor times will save timestamp for each status (0 to 11).

each factor_key can have unlimited statuses and times. for  example,  a factor can go to status 1, then go back to 0, then go to 2, then 3, then back to 1 ....

 

What i want, is to export first time of statuss of (0,2,3,6) for each day of the month.

 

for example, i want to export orders that been in status (0,2,3,6) in November, grouped day by day.

 

What i tried is something like this:

SELECT
			`factor_times`.`factor_status`,
			MIN(`factor_times`.`factor_time`)
		FROM
			`factor_times`
		INNER JOIN
			`factors`
		ON
			`factor_times`.`factor_key`=`factors`.`factor_key`
		AND
			`factors`.`vendor_id`='03010001'
		WHERE
			`factor_times`.`factor_status` IN (0,2,3,6)
		AND
			`factor_times`.`factor_time` > 1437507000
		AND
			`factor_times`.`factor_time` > 1440185400
		GROUP BY
			`factor_times`.`factor_key`

This used to export all times in a period, so i can use PHP code to assign them to each day of month.

 

But it takes so so so so so so long to execute.

 

factor rows: 246,718

factor_times rows: 2,915,127

 

Sorry for poor English :happy-04: :happy-04:

Link to comment
Share on other sites

You are dealing with a large amount of data. I would first ensure you have the tables properly indexed.

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

 

Hello sir.

 

My indexes are shown in the SQL provided above. There are 53.7MB data + 7MB index @ factors table and 88.9MB data + 13.1MB index @ factor_times table.

 

If you have any suggestion changing my indexes, I'll appreciate that.

 

I am thinking your slowdown is because of the AND in your join. Move

 

AND `factors`.`vendor_id`='03010001'

 

to the WHERE and see how it works

 

This is not working. shouldn't the conditions for RIGHT table be in join part of query?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.