1

I have a table of around 500k rows (not big data), that I need to query against itself and then delete some of the rows which don't match the filters.

At the moment my query looks like this:

DELETE  tmp
    FROM  `dp_flights` tmp
    CROSS JOIN  
      ( SELECT  gmf.id
            FROM  `dp_flights` gmf
            LEFT JOIN  iatas des ON des.iata = gmf.out_des_iata
            WHERE  supplier = 'goldmedal'
              AND  (out_legs > 1
                 OR  in_legs > 1
                   )
              AND  (TIMEDIFF(CONVERT_TZ(CONCAT(out_arr_date, ' ', out_arr_time),
                           des.timezone, 'Europe/London' ),
                    CONCAT(out_dep_date, ' ', out_dep_time)) > '16:00'
                      OR  TIMEDIFF(CONVERT_TZ(CONCAT(out_arr_date, ' ', out_arr_time),
                           des.timezone, 'Europe/London' ),
                    CONCAT(out_dep_date, ' ', out_dep_time)) < 0)
      ) deletable USING (id); 

It works occasionally. Sometimes it'll lock_wait_timeout and other times it won't (but does take a while).

Is there a better way of achieving this?

CREATE TABLE `flights` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nights` tinyint(4) NOT NULL,
  `out_dep_city` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `out_dep_iata` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `out_des_city` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `out_des_iata` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `in_dep_city` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `in_dep_iata` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `in_des_city` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `in_des_iata` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `out_flight_no` char(6) COLLATE utf8_unicode_ci NOT NULL,
  `out_dep_date` date NOT NULL,
  `out_dep_time` time NOT NULL,
  `out_arr_date` date NOT NULL,
  `out_arr_time` time NOT NULL,
  `in_flight_no` char(6) COLLATE utf8_unicode_ci NOT NULL,
  `in_dep_date` date NOT NULL,
  `in_dep_time` time NOT NULL,
  `in_arr_date` date NOT NULL,
  `in_arr_time` time NOT NULL,
  `price` float(7,2) NOT NULL,
  `child` float(7,2) NOT NULL DEFAULT '0.00',
  `infant` float(7,2) NOT NULL DEFAULT '0.00',
  `currency` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `fare_term` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `fare_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cabin_class` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `out_marketing_carrier` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `out_operating_carrier` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `in_marketing_carrier` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `in_operating_carrier` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `out_legs` tinyint(4) NOT NULL,
  `in_legs` tinyint(4) NOT NULL,
  `supplier` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `brand` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `out_marketing_name` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `out_operating_name` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `in_marketing_name` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `in_operating_name` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `out_flight_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `in_flight_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `iata_city` (`out_dep_city`,`out_dep_iata`,`out_des_iata`,`out_des_city`,`in_dep_city`,`in_dep_iata`,`in_des_city`,`in_des_iata`),
  KEY `flight_no` (`out_flight_no`,`in_flight_no`),
  KEY `dep_in_arr_dates` (`out_dep_date`,`out_arr_date`,`in_dep_date`,`in_arr_date`),
  KEY `a_index` (`nights`),
  KEY `dp_flights_supplier_index` (`supplier`),
  KEY `carriers` (`out_marketing_carrier`,`out_operating_carrier`,`in_marketing_carrier`,`in_operating_carrier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

EXPLAIN output:

SIMPLE  des ALL NULL    NULL    NULL    NULL    6609    NULL
SIMPLE  gmf ALL dp_flights_supplier_index   NULL    NULL    NULL    533231  Using where; Using join buffer (Block Nested Loop)
  • Show us the actual query please and an output of SHOW CREATE TABLE `table`; and SHOW CREATE TABLE another_table;. If you want to rename table and column names, that's fine but we need to know the indexes and which column (a,b,c,d,...) is in which table. – ypercubeᵀᴹ Jun 13 '15 at 12:10
  • @ypercube updated with the real table info. – James Jun 13 '15 at 12:26
  • If the 3-letter cities and iatas are ascii only, use CHARACTER SET ascii, not utf8. Ditto for concurrency. CHAR(3) utf8 takes 9 bytes; CHAR(3) ascii takes only 3. (No, this won't solve the timeout.) – Rick James Jun 13 '15 at 22:32
  • Please provide EXPLAIN SELECT ...; for the embedded SELECT. How many rows are there in that SELECT? Are there other queries hitting this table when the timeout occurs? What are they doing? – Rick James Jun 13 '15 at 22:34
  • @RickJames I've added the EXPLAIN SELECT output. No queries should be hitting the table at the same time. – James Jun 15 '15 at 7:52

Your Answer

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

Browse other questions tagged or ask your own question.