Select from 2 big tables...

[beyzad]

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:

[QuickOldCar]

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

[benanamen]

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

[beyzad]

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?

[benanamen]

Saying something does not work does not help anyone. You need to tell us exactly what is happening. What was the result? What is the expected result?

Edited March 8, 2016 by benanamen

[Barand]

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

If you are using LEFT JOIN, yes.

 

Try it with an index on factor_times.status

 

 

PS. shouldn't this be "<"

 

`factor_times`.`factor_time` > 1440185400

Edited March 9, 2016 by Barand