0

Here is the query:

EXPLAIN
SELECT
f.transaction_id, a.first_name
FROM
gateway_fees_stripe f
INNER JOIN payments_credit_card a
ON a.`transaction_id` = f.transaction_id
WHERE f.organization_id IN (262,252,226) ORDER BY f.transaction_id DESC

When I run this, I receive the following output:

id  select_type  table   type    possible_keys                   key             key_len  ref       rows  Extra                                         

 1  SIMPLE       a       index   (NULL)                          transaction_id  131      (NULL)  453865  Using index; Using temporary; Using filesort  
 1  SIMPLE       f       ref     organization_id,transaction_id  transaction_id  387      func         1  Using index condition; Using where            

I know the explain output is not terribly readable in this form, but what I really don't understand is the following:

1  SIMPLE       a       index   (NULL)                          transaction_id  131      (NULL)  453865  Using index; Using temporary; Using filesort

I have the following indexes:

gateway_fees_stripe (f) (21798 rows)
transaction_id
organization
payments_credit_card (a) (453865 rows)
transaction_id

This query takes basically ~2-3 seconds on a production server. I think it should be faster, but I cannot speed it up. Do I just have unrealistic expectations?

Output of SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%'

Variable_name                          Value         
-------------------------------------  --------------
Innodb_buffer_pool_dump_status         not started   
Innodb_buffer_pool_load_status         not started   
Innodb_buffer_pool_pages_data          6597          
Innodb_buffer_pool_bytes_data          108085248     
Innodb_buffer_pool_pages_dirty         0             
Innodb_buffer_pool_bytes_dirty         0             
Innodb_buffer_pool_pages_flushed       1334991       
Innodb_buffer_pool_pages_free          1024          
Innodb_buffer_pool_pages_misc          571           
Innodb_buffer_pool_pages_total         8192          
Innodb_buffer_pool_read_ahead_rnd      0             
Innodb_buffer_pool_read_ahead          11056014      
Innodb_buffer_pool_read_ahead_evicted  421001        
Innodb_buffer_pool_read_requests       238687143726  
Innodb_buffer_pool_reads               1348054795    
Innodb_buffer_pool_wait_free           15733         
Innodb_buffer_pool_write_requests      64732646   

I have created a pastebin with the create table SQL in it:

 CREATE TABLE `payments_credit_card` (
                        `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
                        `payment_id` int(20) unsigned DEFAULT NULL,
                        `transaction_id` varchar(128) DEFAULT NULL,
                        `ref_transaction_id` varchar(128) DEFAULT NULL,
                        `auth_code` varchar(16) DEFAULT NULL,
                        `response_message` varchar(128) DEFAULT NULL,
                        `gateway_extra_id` varchar(128) DEFAULT NULL,
                        `first` varchar(128) DEFAULT NULL,
                        `last` varchar(128) DEFAULT NULL,
                        `address` varchar(128) DEFAULT NULL,
                        `address2` varchar(128) DEFAULT NULL,
                        `city` varchar(128) DEFAULT NULL,
                        `state_province` char(64) DEFAULT NULL,
                        `postal_code` varchar(18) DEFAULT NULL,
                        `country_code` char(2) DEFAULT NULL,
                        `card_number` varchar(20) DEFAULT NULL,
                        `card_type` varchar(10) DEFAULT NULL,
                        `card_exp_date` char(4) DEFAULT NULL,
                        `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        `response_code` int(11) DEFAULT NULL,
                        PRIMARY KEY (`id`),
                        KEY `auth_code` (`auth_code`),
                        KEY `card_number` (`card_number`),
                        KEY `ref_transaction_id` (`ref_transaction_id`),
                        KEY `first` (`first`),
                        KEY `last` (`last`),
                        KEY `payment_id` (`payment_id`),
                        KEY `transaction_id` (`transaction_id`)
                      ) ENGINE=InnoDB AUTO_INCREMENT=477148 DEFAULT CHARSET=latin1

CREATE TABLE `gateway_fees_stripe` (
                       `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                       `fee_id` varchar(48) COLLATE utf8_unicode_ci NOT NULL,
                       `organization_id` int(11) NOT NULL,
                       `transaction_id` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
                       `amount` decimal(8,2) NOT NULL,
                       `fee_type` enum('fee','refund') COLLATE utf8_unicode_ci NOT NULL,
                       `amount_refunded` decimal(8,2) NOT NULL,
                       `currency` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
                       `deleted_at` timestamp NULL DEFAULT NULL,
                       `date_charged` datetime NOT NULL,
                       `created_at` timestamp NULL DEFAULT NULL,
                       `updated_at` timestamp NULL DEFAULT NULL,
                       `ref_transaction_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                       `processing_fee_bp` int(11) DEFAULT NULL,
                       `surcharge_fee` decimal(5,2) DEFAULT '0.00',
                       `gateway_stripe_id` int(11) NOT NULL,
                       PRIMARY KEY (`id`),
                       KEY `fee_id` (`fee_id`),
                       KEY `organization_id` (`organization_id`),
                       KEY `transaction_id` (`transaction_id`),
                       KEY `date_charged` (`date_charged`),
                       KEY `ref_tx_id` (`ref_transaction_id`),
                       KEY `fee_type` (`fee_type`),
                       KEY `gateway_stripe_id` (`gateway_stripe_id`),
                       KEY `organization_id_2` (`organization_id`,`transaction_id`)
                     ) ENGINE=InnoDB AUTO_INCREMENT=21799 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Created a compound index on (organization_id, transaction_id) which did not improve anything.

  • how does execution plan look if you change WHERE condirions just to "f.organization_id = 262"? – NikitaSerbskiy Oct 10 at 9:50
  • 2
    The datatypes are inconsistent -- You must use the same character set and collation for a column you are JOINing on. Use SHOW CREATE TABLE ... \G so that trailing spaces don't exceed the space limit. – Rick James Oct 17 at 21:57
  • I actually did change the collation and character set so they are consistent. It’s still slow. I know it should be much faster! – Jon Oct 19 at 3:04
  • 2
    @Jon - I ran this - your query fails! In future, could you supply a working fiddle with queries - pastebin is just for storage and might be useful if you wish to post large amounts of DDL and DML which posters here might want to run on their own machines (not sure of the limits on dbfiddle.uk). – Vérace Oct 19 at 7:29
  • You could also run your problematic query on MySQL version >= 8.20 - they've introduced a much more thorough PostgreSQL like EXPLAIN functionality (see section 13.8.2 EXPLAIN Statement). HTH. – Vérace Oct 19 at 7:29
1

Make the CHARACTER SET and COLLATION of transaction_id the same across the tables. That will switch it so that f is looked at first.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c4ed17af818aab569b37178b91d3c2b6

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.