Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

robgoodliffe's avatar

Query speed problems, one much slower than the other.

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

0 likes
4 replies
Cronix's avatar

I think it might be because of your composite primary key PRIMARY KEY (buyer_id,order_id)

You can run an EXPLAIN query to see if it's using the keys properly. I don't think it is. I think it's doing a full table scan instead of using the keys.

Edit: Also, it would be best to use innodb for all of your tables. You're using it for some, and MyISAM for others.

1 like
robgoodliffe's avatar

thanks for the reply @Cronix, are you referring to the pivot table buyer_orders with regards to the composite primary key. If so why would that differ to the other relationship, which also uses a composite key: PRIMARY KEY (order_id,package_id).

Thanks

robgoodliffe's avatar

Here's the difference on the two explain lines

Fast Query Explain:

DEPENDENT SUBQUERY
orders_package
NULL
ref
PRIMARY
PRIMARY
4
mydb_new.orders.id
1               
100.00
Using where
Using index

Slow Query Explain:

DEPENDENT SUBQUERY
buyer_orders
NULL
index
PRIMARY
PRIMARY
8
NULL
93972   
10.00
Using where
Using index
robgoodliffe's avatar
robgoodliffe
OP
Best Answer
Level 9

deleting the primary keys and re-adding and changing the order seems to have worked

PRIMARY KEY (order_id,buyer_id) as opposed to PRIMARY KEY (buyer_id, order_id)

I've not idea why this should make a difference but seems to have solved the issue for now.

Thanks Cronix for pointing me towards the primary key of the pivot table.

Please or to participate in this conversation.