I have this setup;
tables:
CREATE TABLE `invoice` (
`invoice_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`offer_id` BIGINT(20) UNSIGNED NOT NULL,
`currency_id` INT(11) NOT NULL DEFAULT '0',
`insert_date_invoice` DATE NOT NULL,
`propagation_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`invoice_id`) USING BTREE,
UNIQUE INDEX `propagation_id` (`propagation_id`) USING BTREE,
INDEX `FK_invoice_offer` (`offer_id`) USING BTREE,
INDEX `insert_date_invoice` (`insert_date_invoice`) USING BTREE,
INDEX `FK_invoice_currency` (`currency_id`) USING BTREE,
CONSTRAINT `FK_invoice_currency` FOREIGN KEY (`currency_id`) REFERENCES `db_frost`.`currency` (`currency_id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT `FK_invoice_offer` FOREIGN KEY (`offer_id`) REFERENCES `db_frost`.`offer` (`offer_id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='utf8mb3_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
CREATE TABLE `currency` (
`currency_id` INT(11) NOT NULL AUTO_INCREMENT,
`currency` VARCHAR(100) NOT NULL COLLATE 'utf8mb3_unicode_ci',
PRIMARY KEY (`currency_id`) USING BTREE
)
COLLATE='utf8mb3_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
When running this those two queries I get different performances, described with explain command results. Only difference is that I selected one more field from currency table. Currency table has 3 entries (3 rows).
SELECT invoice.invoice_id, currency.currency_id
FROM invoice
INNER JOIN currency ON (currency.currency_id = invoice.currency_id)
ORDER BY invoice.invoice_id DESC
LIMIT 30
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | invoice | \N | index | FK_invoice_currency | PRIMARY | 8 | \N | 30 | 100.00 | \N |
| 1 | SIMPLE | currency | \N | eq_ref | PRIMARY | PRIMARY | 4 | db_frost.invoice.currency_id | 1 | 100.00 | Using index |
EXPLAIN ANALYZE result:
-> Limit: 30 row(s) (cost=16867 rows=30) (actual time=0.0448..0.0555 rows=30 loops=1)
-> Nested loop inner join (cost=16867 rows=30) (actual time=0.0439..0.0532 rows=30 loops=1)
-> Index scan on invoice using PRIMARY (cost=0.14 rows=30) (actual time=0.0346..0.0377 rows=30 loops=1)
-> Single-row covering index lookup on currency using PRIMARY (currency_id=invoice.currency_id) (cost=0.25 rows=1) (actual time=355e-6..375e-6 rows=1 loops=30)
EXPLAIN FORMAT=JSON result:
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "30667.05"
},
"ordering_operation": {
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "invoice",
"access_type": "index",
"possible_keys": [
"FK_invoice_currency"
],
"key": "PRIMARY",
"used_key_parts": [
"invoice_id"
],
"key_length": "8",
"rows_examined_per_scan": 30,
"rows_produced_per_join": 67454,
"filtered": "100.00",
"cost_info": {
"read_cost": "312.75",
"eval_cost": "6745.40",
"prefix_cost": "7058.15",
"data_read_per_join": "477M"
},
"used_columns": [
"invoice_id",
"currency_id"
]
}
},
{
"table": {
"table_name": "currency",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"currency_id"
],
"key_length": "4",
"ref": [
"db_frost.invoice.currency_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 67454,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "16863.50",
"eval_cost": "6745.40",
"prefix_cost": "30667.05",
"data_read_per_join": "26M"
},
"used_columns": [
"currency_id"
]
}
}
]
}
}
}
Second query:
SELECT invoice.invoice_id, currency.currency_id, **currency.currency**
FROM invoice
INNER JOIN currency ON (currency.currency_id = invoice.currency_id)
ORDER BY invoice.invoice_id DESC
LIMIT 30
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | invoice | \N | index | FK_invoice_currency | FK_invoice_currency | 4 | \N | 67759 | 100.00 | Using index Using temporary Using filesort |
| 1 | SIMPLE | currency | \N | ALL | PRIMARY | \N | \N | \N | 3 | 33.33 | Using where; Using join buffer (hash join) |
EXPLAIN ANALYZE result:
-> Limit: 30 row(s) (actual time=43.8..43.8 rows=30 loops=1)
-> Sort: invoice.invoice_id, limit input to 30 row(s) per chunk (actual time=43.8..43.8 rows=30 loops=1)
-> Stream results (cost=27295 rows=67454) (actual time=19.6..38.2 rows=69067 loops=1)
-> Inner hash join (currency.currency_id = invoice.currency_id) (cost=27295 rows=67454) (actual time=19.6..30 rows=69067 loops=1)
-> Table scan on currency (cost=16.6e-6 rows=3) (actual time=0.0138..0.0169 rows=3 loops=1)
-> Hash
-> Covering index scan on invoice using FK_invoice_currency (cost=7058 rows=67454) (actual time=0.0367..11.5 rows=69067 loops=1)
EXPLAIN FORMAT=JSON result:
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "27295.37"
},
"ordering_operation": {
"using_temporary_table": true,
"using_filesort": true,
"nested_loop": [
{
"table": {
"table_name": "invoice",
"access_type": "index",
"possible_keys": [
"FK_invoice_currency"
],
"key": "FK_invoice_currency",
"used_key_parts": [
"currency_id"
],
"key_length": "4",
"rows_examined_per_scan": 67454,
"rows_produced_per_join": 67454,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "312.75",
"eval_cost": "6745.40",
"prefix_cost": "7058.15",
"data_read_per_join": "477M"
},
"used_columns": [
"invoice_id",
"currency_id"
]
}
},
{
"table": {
"table_name": "currency",
"access_type": "ALL",
"possible_keys": [
"PRIMARY"
],
"rows_examined_per_scan": 3,
"rows_produced_per_join": 67454,
"filtered": "33.33",
"using_join_buffer": "hash join",
"cost_info": {
"read_cost": "1.02",
"eval_cost": "6745.40",
"prefix_cost": "27295.37",
"data_read_per_join": "26M"
},
"used_columns": [
"currency_id",
"currency"
],
"attached_condition": "(`db_frost`.`currency`.`currency_id` = `db_frost`.`invoice`.`currency_id`)"
}
}
]
}
}
}
Just change your current join to a correlated subquery in the SELECT list or move the main query to a subquery and then join to the currency table:
SELECT invoice.invoice_id, currency.currency_id,
(SELECT currency FROM currency WHERE currency_id = invoice.currency_id) AS currency
FROM invoice
ORDER BY invoice_id DESC
LIMIT 30;
or:
SELECT i.*, c.currency
FROM (
SELECT invoice_id, currency_id
FROM invoice
ORDER BY invoice_id DESC
LIMIT 30
) i
INNER JOIN currency c ON c.currency_id = i.currency_id;