Hi I'm getting a problem where two seemely similar queries are running at completly different speeds, even though the relationships data I'm querying is much smaller for the slow query.
Order::has('buyers')->limit(10)->get(); // 16 seconds
Order::has('packages')->limit(10)->get(); // 0.5 seconds
Below I've included the raw query that's generated and everything I know about the table. If anybody could give ideas about what could be causing this or things I could try, that would be very much appreciated.
Buyer Relationship Info:
Query:
select * from `orders` where exists (select * from `buyer` inner join `buyer_orders` on `buyer`.`id` = `buyer_orders`.`buyer_id` where `buyer_orders`.`order_id` = `orders`.`id`) limit 10
Time Taken: 16.63 - REALLY SLOW
Create syntax and info:
CREATE TABLE `buyer` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`full_name` varchar(100) DEFAULT NULL,
`company_name` varchar(100) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
`telephone` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=146932 DEFAULT CHARSET=latin1 PACK_KEYS=0;
Table info:
engine: MyISAM
rows: 93,620
size: 5.6 MiB
encoding: Latin 1
auto_increment: 146,932
Pivot table create syntax and info:
CREATE TABLE `buyer_orders` (
`buyer_id` int(11) NOT NULL,
`order_id` int(11) NOT NULL,
PRIMARY KEY (`buyer_id`,`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
engine: InnoDB
rows: 93,612
size: 3.5 MiB
encoding: Latin 1
Package Relationship Info:
Query:
select * from `orders` where exists (select * from `package` inner join `orders_package` on `package`.`id` = `orders_package`.`package_id` where `orders_package`.`order_id` = `orders`.`id`) limit 10
Time Taken: 0.47 - FAST
Create syntax and info:
CREATE TABLE `package` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`delivery_option_id` int(11) DEFAULT NULL,
`weight_delivery_id` int(11) DEFAULT NULL,
`product_items` text,
`addon_items` text,
`custom_items` text,
`free_items` text,
`discounts` text,
`gift_message` text CHARACTER SET utf8,
`delivery_date` date DEFAULT NULL,
`date_to_be_sent` date DEFAULT NULL,
`recipient_name` varchar(255) DEFAULT NULL,
`recipient_number_name` varchar(255) DEFAULT NULL,
`recipient_add_1` varchar(255) DEFAULT NULL,
`recipient_add_2` varchar(255) DEFAULT NULL,
`recipient_city` varchar(45) DEFAULT NULL,
`recipient_postcode` varchar(45) DEFAULT NULL,
`postal_track_items` text,
`print_admin_check` char(1) DEFAULT '0',
`prepped_check` char(1) DEFAULT '0',
`conf_with_email_check` char(1) DEFAULT '0',
`conf_no_email_check` char(1) DEFAULT '0',
`completion_time` datetime DEFAULT NULL,
`pb_temp_img_name` text,
`pb_img_name` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=74307 DEFAULT CHARSET=latin1 PACK_KEYS=0;
Table info:
engine: MyISAM
rows: 74,157
size: 34.5
encoding: Latin 1
auto_increment: 74,307
Pivot table create syntax and info:
CREATE TABLE `orders_package` (
`order_id` int(11) NOT NULL,
`package_id` int(11) NOT NULL,
PRIMARY KEY (`order_id`,`package_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Table info:
engine: InnoDB
rows: 74,088
size: 2.5 MiB
encoding: Latin 1
Thanks Rob