How to optimize MySQL/MariaDB tables

It’s a good idea to perform database maintenance from time to time. One thing is to do is to optimize the tables. We have two options:

1. OPTIMIZE TABLE command

Reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table.

See below how to use it.

root@web [~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3670
Server version: 10.1.22-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use roundcube
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [roundcube]> OPTIMIZE TABLE cache;
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                          |
+-----------------+----------+----------+-------------------------------------------------------------------+
| roundcube.cache | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| roundcube.cache | optimize | status   | OK                                                                |
+-----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.04 sec)

MariaDB [roundcube]> quit
Bye
root@web [~]#

If you want to run the command for multiple tables from the same database, use:

OPTIMIZE TABLE table1,table2,table3;

OPTIMIZE TABLE works with InnoDB, MyISAM, and ARCHIVE tables.

2. mysqlcheck command

The mysqlcheck client performs table maintenance: It checks, repairs, optimizes, or analyzes tables.

To check one table use: mysqlcheck db_name tbl_name
To check all tables from a database: mysqlcheck –databases db_name
To check the tables from all the databases on the server: mysqlcheck –all-databases

Notice that database tables are locked while mysqlcheck is running. No records can be inserted or deleted from the tables.

root@web [~]# mysqlcheck roundcube
roundcube.cache                                    OK
roundcube.cache_index                              OK
roundcube.cache_messages                           OK
roundcube.cache_shared                             OK
roundcube.cache_thread                             OK
roundcube.contactgroupmembers                      OK
roundcube.contactgroups                            OK
roundcube.contacts                                 OK
roundcube.cp_schema_version                        OK
roundcube.dictionary                               OK
roundcube.identities                               OK
roundcube.searches                                 OK
roundcube.session                                  OK
roundcube.system                                   OK
roundcube.users                                    OK
root@web [~]# 

To optimize a database, use:

root@web [~]# mysqlcheck -o roundcube
roundcube.cache
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_index
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_messages
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_shared
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_thread
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.contactgroupmembers
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.contactgroups
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.contacts
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cp_schema_version                        Table is already up to date
roundcube.dictionary
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.identities
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.searches
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.session
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.system
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.users
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
root@web [~]#

To optimize all the database on the sever use:

root@web [~]# mysqlcheck -o -A

Optimize tables in MySQL automatically with PHP

In previous posts I looked at how to optimize a MySQL table from the MySQL command line interface and from phpMyAdmin by using the optimize [tablename] command to free up unused space. In this post I will look at how to do this with a PHP script which could be run periodically to optimise all non-optimal MySQL tables.

The SQL we'll use to find tables which are non-optimal looks like this:

SHOW TABLE STATUS WHERE Data_free > [integer value]

substituting [integer value] for an integer value, which is the free data space in bytes. This could be e.g. 102400 for tables with 100k of free space. This will then only return the tables which have more than 100k of free space.

An alternative way of searching would be to look for tables that have e.g. 10% of overhead free space by doing this:

SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1

The downside with this is that it would include small tables with very small amounts of free space so it could be combined with the first SQL query to only get tables with more than 10% overhead and more than 100k of free space:

SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400

Using the above SQL, the PHP code would look like this:

$res = mysql_query('
  SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400
');

while($row = mysql_fetch_assoc($res)) {
  mysql_query('OPTIMIZE TABLE ' . $row['Name']);
}

And that's all there is to it. You could then run this PHP code snippet within a full PHP script and run it via cron once per day.

Related posts:

• Optimize a table in MySQL from phpMyAdmin
• Optimize a table in MySQL from the command line interface

Optimize a table in MySQL from phpMyAdmin

Yesterday I looked at how to Optimize a table in MySQL from the command line interface and today will look at how to do the same thing with phpMyAdmin. This means you can just point and click in a web based interface instead of having to remember the commands and type them in.

To check if there's a lot of wasted free space in any of your MySQL tables, log into phpMyAdmin and select your database. The initial view in the right frame is a list of all the tables in the database and shows an overview of each table including the number of records, size and overhead. This is shown in the screenshot below.

The second table in the above example has just 687 records and is taking up 545.3 MB. There's 533.5MB of "overhead" meaning free space which isn't being used at all but is taking up disk space. This table clearly isn't optimal so follow the steps below to optimize it.

Select the appropriate table by clicking one of the buttons alongside its name (one of the browse, structure, search or insert buttons) and the click the "Operations" button/tab at the top of the page. (Don't click the "Operations" tab on table list page as it will show a different set of operations which relate to the database as a whole instead of the specific table).

Now scroll to the bottom of the page until you find the "Table maintenance" options as shown in the screenshot below. To optimize the table click the "Optimize table" link.

The table will then be optimised and then after a few seconds the page will display the query as shown in the screenshot below.

If we then return to the page displaying the complete list of tables we can now see the table is only 33.1MB in size and there's no overhead.

If you have phpMyAdmin installed this is a much easier method than doing it from the command line because you can easily see which tables need to be optimised and easily optimise them by just clicking a few links.

Tomorrow I'll look at how to automatically optimize MySQL tables if they have become non-optimal using a PHP script.

Related posts:

• MySQL "Incorrect key file for table" error
• How to tell which storage engine a MySQL table uses
• Optimize tables in MySQL automatically with PHP
• Optimize a table in MySQL from the command line interface

Optimize a table in MySQL from the command line interface

If you have MySQL tables that grow large and then have a lot of deletes they will become fragmented and larger than they need to be. This post looks at how to look at check to see if a MySQL table is no longer optimal and how to optimize it.
Tomorrow's post will look at how to do the same thing with phpMyAdmin instead of issuing the commands yourself (i.e. you can just point and click) and then on Sunday a PHP script to automatically optimize all tables that need it within set parameters.
Log into the MySQL command line interface, select your database and then issue the command below, where 'mytablename' is the name of the table you want to query:

show table status like 'mytablename'\G

You can omit the "like 'mytablename'" part and then it will show this information for all tables. However if you have a lot of tables and there's only one or two you want to examine then it's better to specify the particular table.
You can end you query with either ; or \G. I prefer \G for this particular query because it shows each column from the resultset on a new line, whereas ; will show the columns across the screen. This is OK for a resultset with only a few columns with only a small amount of information in each one, but it's not so good for this query.
The result from the above will look something like so:

*************************** 1. row ***************************
           Name: mytablename
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 2444
 Avg_row_length: 7536
    Data_length: 564614700
Max_data_length: 281474976710655
   Index_length: 7218176
      Data_free: 546194608
 Auto_increment: 1187455
    Create_time: 2008-03-19 10:33:13
    Update_time: 2008-09-02 22:18:15
     Check_time: 2008-08-27 23:07:48
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: pack_keys=0
        Comment:

The values that are important for working out if the table is non optimal is the "Data_free" value. If this is high, as in the above example where 564614700 bytes are free (538MB), then the table has a lot of space not being used and should be optimized.
To optimize the table, issue the following command, where "mytablename" is the name of the MySQL table to optimise:

optimize table mytablename;

After doing this (it may take a few seconds dpending on the size of the table, free space etc) and running "show table status" again, the result should look much better:

*************************** 1. row ***************************
           Name: tblmailqueue
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 6145
 Avg_row_length: 7505
    Data_length: 46119636
Max_data_length: 281474976710655
   Index_length: 296960
      Data_free: 0
 Auto_increment: 1191156
    Create_time: 2008-03-19 10:33:13
    Update_time: 2008-09-02 22:24:58
     Check_time: 2008-09-02 22:21:32
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: pack_keys=0
        Comment:
1 row in set (0.00 sec)

In the above example we can see the "Data_free" value is now zero so the table is nicely optimised.
Tomorrow I'll look at how to do the same thing with phpMyAdmin so all you need to do is point and click instead of having to remember the commands and type them in.
 

Related posts:

• How to tell which storage engine a MySQL table uses
• Optimize tables in MySQL automatically with PHP
• Optimize a table in MySQL from phpMyAdmin
• Renaming a table in MySQL

How to optimize the MySQL query with many unions

I have this query which I need help with. So there is a table called insertjobticketwith column called DEL which is a long character field which can have multiple dates in it. I need to create an output table which contains one row for each time there is a date in the DEL field for a certain range of dates.

The reason I can't just do a more simple select ... where ... DEL like "%my_date%" is that the DEL column can contain multiple dates, and if so, I need to return multiple rows to the output set, one row for each date that appears in the DEL column.

The solution I came up with that works, but is very slow looks like this:

create temporary table jobtrack.ship_helpert3 as
select * from
(
    (
    select
        date_format(now() - interval 3 day, '%m/%d/%Y') as `Ship_Date`,
        more_columns
    from
        jobticket.insertjobticket
    where
        DEL like concat('%',date_format(now() - interval 3 day, '%m/%d/%Y'),'%')
    ) union (
    select
        date_format(now() + interval 2 day, '%m/%d/%Y') as `Ship_Date`,
        more_columns
    from
        jobticket.insertjobticket
    where
        DEL like concat('%',date_format(now() + interval 2 day, '%m/%d/%Y'),'%')
    ) union (
    select
        date_format(now() + interval 1 day, '%m/%d/%Y') as `Ship_Date`,
        more_columns
    from
        jobticket.insertjobticket
    where
        DEL like concat('%',date_format(now() + interval 1 day, '%m/%d/%Y'),'%')
    ) union ...
) t;

Each select query checks if there are any rows with a certain date string (date_format(now() + interval @x day, '%m/%d/%Y')) in the DEL field. The query is built programmatically and can get very long, as I would like to be able to make the query check for many many dates.

The insertjobticket table contains 40K rows and is growing, so the query above takes way too long to complete. I understand why it takes so long, because every unioneffectively has to make its own sub-query that scans the whole table again and again for each date. I just don't know how to make this work more efficiently.

Does anyone know how to speed up this query?

Thanks for the help and let me know if we need more clarification.

---

As already stretched in the comments, the only correct solution would be to normalize your data, that means to create a new table with one delivery date and the primary key of insertjobticket per row, and let the application use this table directly instead of the column del, or at least indirectly by a trigger that updates this table everytime the column DEL is updated.

Since you cannot do that, the following workaround should improve your query:

select
  del_dates.Ship_Date,
  othercolumns
from insertjobticket
join (
    select concat(date_format(now() + interval 2 day, '%m/%d/%Y'))
           collate utf8_general_ci as Ship_Date
    union select concat(date_format(now() + interval 1 day, '%m/%d/%Y'))
    union select concat(date_format(now() + interval -15 day, '%m/%d/%Y'))
    ...
) del_dates
on insertjobticket.del like concat('%', del_dates.Ship_Date, '%');

(Change the collation to the one you use in your table or leave it away to see which one, if any, you need).

This will basically do the required normalization step (for the requested dates) every time you execute the query, and will not be able to use indexes. Just make sure your explain output shows using join buffer for the derived table, not for insertjobticket, otherwise replace join with a straight_join.

For 40k rows, this might not be a big a problem, and there is no other way around it anyway, except real normalization. Keep in mind that your query will slow down linearly with the amount of rows (400k rows will take about 10 times the time as 40k), an effect indexes would prevent. So if it is too slow now (or sometimes in the future), you eventually have to normalize (or, as a workaround to the problems created by this workaround, add a column to mark old entries and exclude them in your join condition).

Btw, since you generate your code programmatically, it shouldn't be a problem to create the list of dates, otherwise you can use another subquery to generate a list of general dates and just select the ones in a specific range.

How to optimize the mysql query with a large dataset

I have two tables with the following schema,

CREATE TABLE `open_log` (
  `delivery_id` varchar(30) DEFAULT NULL,
  `email_id` varchar(50) DEFAULT NULL,
  `email_activity` varchar(30) DEFAULT NULL,
  `click_url` text,
  `email_code` varchar(30) DEFAULT NULL,
  `on_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sent_log` (
  `email_id` varchar(50) DEFAULT NULL,
  `delivery_id` varchar(50) DEFAULT NULL,
  `email_code` varchar(50) DEFAULT NULL,
  `delivery_status` varchar(50) DEFAULT NULL,
  `tries` int(11) DEFAULT NULL,
  `creation_ts` varchar(50) DEFAULT NULL,
  `creation_dt` varchar(50) DEFAULT NULL,
  `on_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

The email_id and delivery_id columns in both tables make up a unique key.

The open_log table have 2.5 million records where as sent_log table has 0.25 million records.

I want to filter out the records from open log table based on the unique key (email_id and delivery_id).

I'm writing the following query.

SELECT * FROM open_log
WHERE CONCAT(email_id,'^',delivery_id)
IN (
SELECT DISTINCT CONCAT(email_id,'^',delivery_id) FROM sent_log
)

The problem is the query is taking too much time to execute. I've waited for an hour for the query completion but didn't succeed.

Kindly, suggest what I can do to make it fast since, I have the big data size in the tables.

Thanks, Faisal Nasir

---

First, rewrite your query using exists:

SELECT *
FROM open_log ol
WHERE EXISTS (SELECT 1
              FROM send_log sl
              WHERE sl.email_id = ol.email_id and sl.delivery_id = ol.delivery_id
             );

Then, add an index so this query will run faster:

create index idx_sendlog_emailid_deliveryid on send_log(email_id, delivery_id);

Your query is slow for a variety of reasons:

• The use of string concatenation makes it impossible for MySQL to use an index.
• The select distinct in the subquery is unnecessary.
• Exists can be faster than in.

Mysql How to optimize the SQL query with many conditions

Table station_tbl:

station_name   |  fare_adult |  fare_child |  fare_OKU |  type |    stationid

New City            2.00         1.00         1.00       0            19900
Old Village         2.00         1.00         1.00       0            54900
Old City            5.00         2.50         2.50       1            23100
New Castle          1.00         0.50         0.50       1            22900
Adult                                                    2              0
Child                                                    2              1
OKU                                                      2              2
Single                                                   3              0
Return                                                   3              1

my query: display ticket info

SELECT
    A.ticketid,
    B.station_name AS stationid,
    c.station_name AS destination,
    CONVERT(VARCHAR(5), GETDATE(), 108) AS TIME,
    CONVERT(VARCHAR(12), GETDATE(), 103) AS Date,
    D.station_name AS ticket_type,
    e.station_name AS journey_type,
    amount,
    issuedby
FROM ticketcollections AS A,
    station_tbl AS B,
    station_tbl AS c,
    station_tbl AS D,
    station_tbl AS e
WHERE A.ticketidparent = '" + Request("ParentId") + "'
    AND A.stationid = B.stationid
    AND B.type = 0
    AND A.destination = c.stationid
    AND c.type = 0
    AND A.ticket_type = D.stationid
    AND D.type = 4
    AND A.journey_type = e.stationid
    AND e.type = 3

I have combined all the data in 1 table.

This table just for storing name for station name and also ticket type.

because in table ticketcollections just store code (1, 2, 3, 4). So when I want to print ticket then I will refer to this station_tbl for display naming purpose.

Please help me if this query will make the query too slow.

On my PC it is fast but on user's PC it is slow.

I have refered 2 tables only.

---

try this:

SELECT
    A.ticketid,
    B.station_name AS stationid,
    B.station_name AS destination,
    CONVERT(VARCHAR(5), GETDATE(), 108) AS TIME,
    CONVERT(VARCHAR(12), GETDATE(), 103) AS Date,
    B.station_name AS ticket_type,
    B.station_name AS journey_type,
    amount,
    issuedby
FROM ticketcollections A
    station_tbl AS B,
WHERE A.ticketidparent = '" + Request("ParentId") + "'
    AND A.stationid = B.stationid
    AND B.type in(0,4,3)

How can I optimize the MySQL query with multiple joins?

Any inputs on how can I optimize joins in the MySQL query? For example, consider the following query

    SELECT E.name, A.id1, B.id2, C.id3, D.id4, E.string_comment
    FROM E
    JOIN A ON E.name = A.name AND E.string_comment = A.string_comment
    JOIN B ON E.name = B.name AND E.string_comment = B.string_comment
    JOIN C ON E.name = C.name AND E.string_comment = C.string_comment
    JOIN D ON E.name = D.name AND E.string_comment = D.string_comment

Table A,B,C,D are temporary tables and contains 1096 rows and Table E (also temporary table) contains 426 rows. Without creating any index, MySQL EXPLAIN was showing me all the rows being searched from all the Tables. Now, I created a FULLTEXT index for name as name_idx and string_comment as string_idx on all the tables A,B,C,B and E. The EXPLAIN command is still giving me the same result as shown below. Also, please note that name and string_comment are of type VARCHAR and idX are of type int(15)

    id  select_type table  type  possible_keys         key  key_len  ref rows  Extra
    1   SIMPLE      A      ALL   name_idx,string_idx                     1096
    1   SIMPLE      B      ALL   name_idx,string_idx                     1096  Using where
    1   SIMPLE      C      ALL   name_idx,string_idx                     1096  Using where
    1   SIMPLE      D      ALL   name_idx,string_idx                     1096  Using where
    1   SIMPLE      E      ALL   name_idx,string_idx                     426   Using where

Any comments on how can I tune this query?

Thanks.

---

For each table you should create a composite index on both columns. The syntax varies a bit, but it is something like:

CREATE INDEX comp_E_idx E(name, string_comment)

And repeat for all tables. Separate indices won't help because when it tries to merge they are useless. It searches for the name in the index really fast, but then has to iterate to find the comment

How to optimize the MySQL query for large tables

I am trying to optimize the following query as it is taking an extremely long time to execute. Can anyone provide any advice on how to optimize this and can they recommend any indexing that would speed it up. As a note the edata table contains around 1 million rows and the ddata table has around 15 million rows. There are around 5,000 items selected from ddata if you run the query

SELECT * FROM ddata WHERE DATE(startDate) = DATE(NOW());

The query that I am trying to optimize is:

SELECT e.ID,e.uID,e.sID
FROM edata e
LEFT JOIN ddata d ON e.sID=d.sID
WHERE DATE(d.startDate)=DATE(NOW());

Thanks

---

#1: You probably don't want an Outer Join, so replace it with an Inner Join (MySQL's optimizer is known to be weak determining if an Outer Join can be rewritten as an Inner Join).

#2: Remove the function on d.startDate.

SELECT e.ID,e.uID,e.sID
FROM edata e
JOIN ddata d ON e.sID=d.sID
WHERE d.startDate >= DATE(NOW())
AND d.StartDate < date_add(DATE(NOW(), interval 1 days);




Optimize the Mysql query with a Boolean value

I have this table:

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `supplier_id` int(11) NOT NULL,
  `allowed` varchar(256) NOT NULL,
  `blocked` varchar(256) NOT NULL,
  `approved` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_id`),
  KEY `supplier_id` (`supplier_id`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

'approved' is a boolean 0/1 field
'blocked' and 'allowed' hold country codes such as "US CA FR"

I run this query:

SELECT DISTINCT supplier_id
FROM products
WHERE (
    supplier_id=0 OR
    supplier_id = 1207077 OR
    supplier_id = 1207087 OR
    supplier_id = 1207079 OR
    supplier_id = 1207082 OR
    supplier_id = 1207083 OR
    supplier_id = 1207086 OR
    supplier_id = 1207084 OR
    supplier_id = 1207078 OR
    supplier_id = 1207085 OR
    supplier_id = 1207094 OR
    supplier_id = 1207097 OR
    supplier_id = 1207095 OR
    supplier_id = 1207089 OR
    supplier_id = 1207091
) AND (
    (`blocked` NOT LIKE '%US%' AND `allowed` ='') OR
    `allowed` LIKE '%US%'
) AND approved=1;

It runs in about 0.02s. Any suggestions on how to optimize it? Thank you.

---

The execution speed is the same because OR and non left anchored LIKE clauses cannot use indexes appropriately. You've got bad table design in that US FR etc field, that should be in another table that you join against. If you are stuck with your design and the table is vey large, then create a derived table for the supplier_id OR clauses and then JOIN against the same table in order to find the rest of the matches. This may also require a UNION since you have other OR's. For more information:

http://dev.mysql.com/doc/refman/5.6/en/index-btree-hash.html

How to optimize the SQL query with the functions of the window

This question is related to this one. I have table which contains power values for devices and I need to calculate power consumption for given time span and return 10 most power consuming devices. I have generated 192 devices and 7742208 measurement records (40324 for each). This is roughly how much records devices would produce in one month.

For this amount of data my current query takes over 40s to execute which is too much because time span and amount of devices and measurements could be much higher. Should I try to solve this with different approach than lag() OVER PARTITION and what other optimizations can be made? I would really appreciate suggestions with code examples.

PostgreSQL version 9.4

Query with example values:

SELECT
  t.device_id,
  sum(len_y*(extract(epoch from len_x))) AS total_consumption
FROM (
    SELECT
      m.id,
      m.device_id,
      m.power_total,
      m.created_at,
      m.power_total+lag(m.power_total) OVER (
        PARTITION BY device_id
        ORDER BY m.created_at
      ) AS len_y,
      m.created_at-lag(m.created_at) OVER (
        PARTITION BY device_id
        ORDER BY m.created_at
      ) AS len_x
    FROM
      measurements AS m
  WHERE m.created_at BETWEEN '2015-07-30 13:05:24.403552+00'::timestamp
    AND '2015-08-27 12:34:59.826837+00'::timestamp
) AS t
GROUP BY t.device_id
ORDER BY total_consumption
DESC LIMIT 10;

Table info:

    Column    |           Type           |                         Modifiers
--------------+--------------------------+----------------------------------------------------------
 id           | integer                  | not null default nextval('measurements_id_seq'::regclass)
 created_at   | timestamp with time zone | default timezone('utc'::text, now())
 power_total  | real                     |
 device_id    | integer                  | not null
Indexes:
    "measurements_pkey" PRIMARY KEY, btree (id)
    "measurements_device_id_idx" btree (device_id)
    "measurements_created_at_idx" btree (created_at)
Foreign-key constraints:
    "measurements_device_id_fkey" FOREIGN KEY (device_id) REFERENCES devices(id)

Query plan:

Limit  (cost=1317403.25..1317403.27 rows=10 width=24) (actual time=41077.091..41077.094 rows=10 loops=1)
->  Sort  (cost=1317403.25..1317403.73 rows=192 width=24) (actual time=41077.089..41077.092 rows=10 loops=1)
Sort Key: (sum((((m.power_total + lag(m.power_total) OVER (?))) * date_part('epoch'::text, ((m.created_at - lag(m.created_at) OVER (?)))))))
Sort Method: top-N heapsort  Memory: 25kB
->  GroupAggregate  (cost=1041700.67..1317399.10 rows=192 width=24) (actual time=25361.013..41076.562 rows=192 loops=1)
Group Key: m.device_id
->  WindowAgg  (cost=1041700.67..1201314.44 rows=5804137 width=20) (actual time=25291.797..37839.727 rows=7742208 loops=1)
->  Sort  (cost=1041700.67..1056211.02 rows=5804137 width=20) (actual time=25291.746..30699.993 rows=7742208 loops=1)
Sort Key: m.device_id, m.created_at
Sort Method: external merge  Disk: 257344kB
->  Seq Scan on measurements m  (cost=0.00..151582.05 rows=5804137 width=20) (actual time=0.333..5112.851 rows=7742208 loops=1)
Filter: ((created_at >= '2015-07-30 13:05:24.403552'::timestamp without time zone) AND (created_at <= '2015-08-27 12:34:59.826837'::timestamp without time zone))

Planning time: 0.351 ms
Execution time: 41114.883 ms

Query to generate test table and data:

CREATE TABLE measurements (
    id          serial primary key,
    device_id   integer,
    power_total real,
    created_at  timestamp
);

INSERT INTO measurements(
    device_id,
    created_at,
    power_total
  )
SELECT
  device_id,
  now() + (i * interval '1 minute'),
  random()*(50-1)+1
FROM (
  SELECT
    DISTINCT(device_id),
    generate_series(0,10) AS i
 FROM (
  SELECT
    generate_series(1,5) AS device_id
  ) AS dev_ids
) AS gen_table;

---

I would try to move some part of the calculations into the phase of row insertion.

Add a new column:

alter table measurements add consumption real;

Update the column:

with m1 as (
    select
        id, power_total, created_at,
        lag(power_total) over (partition by device_id order by created_at) prev_power_total,
        lag(created_at) over (partition by device_id order by created_at) prev_created_at
    from measurements
    )
update measurements m2
set consumption =
    (m1.power_total+ m1.prev_power_total)*
    extract(epoch from m1.created_at- m1.prev_created_at)
from m1
where m2.id = m1.id;

Create a trigger:

create or replace function before_insert_on_measurements()
returns trigger language plpgsql
as $$
declare
    rec record;
begin
    select power_total, created_at into rec
    from measurements
    where device_id = new.device_id
    order by created_at desc
    limit 1;
    new.consumption:=
        (new.power_total+ rec.power_total)*
        extract(epoch from new.created_at- rec.created_at);
    return new;
end $$;

create trigger before_insert_on_measurements
before insert on measurements
for each row execute procedure before_insert_on_measurements();

The query:

select device_id, sum(consumption) total_consumption
from measurements
-- where conditions
group by 1
order by 1




How to optimize the selection query with the index

I am not expert. I have following query, which contains 10 tables MainTable has 10 fields 1st Prime Key and rest foreign keys of 9 tables called TableE1 - 10.

The following query is making outer join in each table, i want to optimize this query with index.

I want to know, how can we optimize queries with index, this query is fetching 10 lacs (1 million) records in 36 seconds, how much time we can reduce ?

MainTable contains 10 lacs (1 million) records, TableE1-9 each table contains 5000 records

select M.RecID,
M.E1, E1.Descr as E1_D,
M.E2, E2.Descr as E2_D,
M.E3, E3.Descr as E3_D,
M.E4, E4.Descr as E4_D,
M.E5, E5.Descr as E5_D,
M.E6, E6.Descr as E6_D,
M.E7, E7.Descr as E7_D,
M.E8, E8.Descr as E8_D,
M.E9, E9.Descr as E9_D
from ((((((((tableMain M
    Left Outer Join TableE1 E1 ON (E1.RecID = M.E1) )
    Left Outer Join TableE2 E2 ON (E2.RecID = M.E2) )
    Left Outer Join TableE3 E3 ON (E3.RecID = M.E3) )
    Left Outer Join TableE4 E4 ON (E4.RecID = M.E4) )
    Left Outer Join TableE5 E5 ON (E5.RecID = M.E5) )
    Left Outer Join TableE6 E6 ON (E6.RecID = M.E6) )
    Left Outer Join TableE7 E7 ON (E7.RecID = M.E7) )
    Left Outer Join TableE8 E8 ON (E8.RecID = M.E8) )
    Left Outer Join TableE9 E9 ON (E9.RecID = M.E9)
Order by RecID

---

Indexes are probably not going to help this query very much, because the query has no filtering. You are retrieving a million records. How much of the time spent on the query is retrieving the values and how much is spent processing the query?

SQL Server has a good optimizer, that will use sophisticated join algorithms for doing joins. It is quite possible that the query will run pretty well even with no indexes.

That said, an index on each of the "E" tables with both RecId and Descr could help the query: E1(RecId, Descr), E2(RecID, Descr), and so on. These are covering indexes. For this query, SQL Server would use these indexes without having to read from the data pages . An index only RecId would not work as well, because the Descr data would still need to be looked up on the data pages.

Note that these indexes would be unnecessary (redundant?) if RecId is already the primary key and Descr is the only column in the table.

EDIT:

This is too long for a comment (I think).

Here are some ideas for optimizing this query:

First, are all the rows necessary? For instance, can you just add a top 1000 to get what you need? A lot of time is spent just passing the rows back to the application. Consider putting them into a temporary table (select into). That will probably run much faster.

Second, how much time is the order by taking? Try running the query without the order by to see if that is dominating the time.

Third, how long are the descr fields? If they are very long, even just a few thousand could be dominating the size of the data. Note "very long" here means many kbytes, not a few hundred bytes.

Fourth, are the descr fields varchar() or char() (or nvarchar() versus nchar()). char() and nchar() are very bad choices, because they occupy a lot of space in the result set.

Fifth (probably should be first), look at the execution plan. You have present a pretty simple scenario so I have assumed that the execution plan is a scan of the first table with index lookups into each of the other. If the plan doesn't look like this, then there may be opportunities for optimization.

EDIT II:

I will repeat. Transferring hundreds of megabytes from the server to an application will take time, and 30'ish seconds isn't unreasonable. (The return set has 10 ids = 40 bytes plus the description fields which are likely to be 100s of bytes per record.) The problem is the design of the layer between the database and the application, not the database performance.

Optimize a MySQL query with multiple joins

One of the queries used by a web app we're running is as follows:

SELECT
       p.id, r.id AS report_id, tr.result_id,
       r.report_date, r.department, r.reportStatus, rs.specimen,
       tr.name, tr.value, tr.flag, tr.unit, tr.reference_range
FROM patients AS p
INNER JOIN
    patients_reports AS pr ON pr.patient_id = p.id
INNER JOIN
    reports AS r ON pr.report_id = r.id
INNER JOIN
    results AS rs ON r.id = rs.report_id
INNER JOIN
    test_results AS tr ON rs.id = tr.result_id
WHERE pr.patient_id = 17548
ORDER BY rs.specimen, tr.name, r.report_date;

The explain plan looks like this:

+----+-------------+-------+--------+---------------+-----------+---------+-------------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key       | key_len | ref               | rows   | Extra                                        |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | p     | const  | PRIMARY       | PRIMARY   | 4       | const             |      1 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | rs    | ALL    | PRIMARY       | NULL      | NULL    | NULL              | 152817 |                                              |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY       | PRIMARY   | 4       | demo.rs.report_id |      1 |                                              |
|  1 | SIMPLE      | pr    | eq_ref | PRIMARY       | PRIMARY   | 8       | const,demo.r.id   |      1 | Using where; Using index                     |
|  1 | SIMPLE      | tr    | ref    | result_id     | result_id | 5       | demo.rs.id        |      1 | Using where                                  |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------+--------+----------------------------------------------+

The query returns 27371 rows. There are 152730 rows in test_results at the moment. This is just a small amount of demo data.

I've tried to get the query to be more efficient, but I'm having trouble getting it to execute more quickly. I've had a look at various articles on documentation and questions on stackoverflow, but have not been able to fix this.

I tried removing one of the joins as follows:

SELECT
       pr.patient_id, r.id AS report_id, tr.result_id,
       r.report_date, r.department, r.reportStatus, rs.specimen,
       tr.name, tr.value, tr.flag, tr.unit, tr.reference_range
FROM patients_reports AS pr
INNER JOIN
    reports AS r ON pr.report_id = r.id
INNER JOIN
    results AS rs ON r.id = rs.report_id
INNER JOIN
    test_results AS tr ON rs.id = tr.result_id
WHERE pr.patient_id = 17548
ORDER BY rs.specimen, tr.name, r.report_date;

The query plan is then as follows:

+----+-------------+-------+--------+---------------+-----------+---------+-------------------+--------+---------------------------------+
| id | select_type | table | type   | possible_keys | key       | key_len | ref               | rows   | Extra                           |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------+--------+---------------------------------+
|  1 | SIMPLE      | rs    | ALL    | PRIMARY       | NULL      | NULL    | NULL              | 152817 | Using temporary; Using filesort |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY       | PRIMARY   | 4       | demo.rs.report_id |      1 |                                 |
|  1 | SIMPLE      | pr    | eq_ref | PRIMARY       | PRIMARY   | 8       | const,demo.r.id   |      1 | Using where; Using index        |
|  1 | SIMPLE      | tr    | ref    | result_id     | result_id | 5       | demo.rs.id        |      1 | Using where                     |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------+--------+---------------------------------+

So not much different.

I've tried rearranging the query and using STRAIGHT_JOIN amongst other things, but I'm not getting anywhere.

I'd appreciate some suggestions on how to optimize the query. Thanks.

EDIT: Argh! I did not have an index on results.report_id, but it does not seem to have helped:

+----+-------------+-------+--------+-------------------+-----------+---------+-------------------+--------+---------------------------------+
| id | select_type | table | type   | possible_keys     | key       | key_len | ref               | rows   | Extra                           |
+----+-------------+-------+--------+-------------------+-----------+---------+-------------------+--------+---------------------------------+
|  1 | SIMPLE      | rs    | ALL    | PRIMARY,report_id | NULL      | NULL    | NULL              | 152817 | Using temporary; Using filesort |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY           | PRIMARY   | 4       | demo.rs.report_id |      1 |                                 |
|  1 | SIMPLE      | pr    | eq_ref | PRIMARY           | PRIMARY   | 8       | const,demo.r.id   |      1 | Using where; Using index        |
|  1 | SIMPLE      | tr    | ref    | result_id         | result_id | 5       | demo.rs.id        |      1 | Using where                     |
+----+-------------+-------+--------+-------------------+-----------+---------+-------------------+--------+---------------------------------+

EDIT2:

patients_reports looks like this:

+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| patient_id | int(11) | NO   | PRI | 0       |       |
| report_id  | int(11) | NO   | PRI | 0       |       |
+------------+---------+------+-----+---------+-------+

EDIT3:

After adding the results.report_id index and trying the STRAIGHT_JOIN again as suggested by @DRapp:

SELECT STRAIGHT_JOIN
       r.id AS report_id, tr.result_id,
       r.report_date, r.department, r.reportStatus, rs.specimen,
       tr.name, tr.value, tr.flag, tr.unit, tr.reference_range
FROM patients_reports AS pr
INNER JOIN
    reports AS r ON pr.report_id = r.id
INNER JOIN
    results AS rs ON r.id = rs.report_id
INNER JOIN
    test_results AS tr ON rs.id = tr.result_id
WHERE pr.patient_id = 17548
ORDER BY rs.specimen, tr.name, r.report_date;

the plan looks like this:

+----+-------------+-------+--------+-------------------+-----------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys     | key       | key_len | ref               | rows | Extra                                        |
+----+-------------+-------+--------+-------------------+-----------+---------+-------------------+------+----------------------------------------------+
|  1 | SIMPLE      | pr    | ref    | PRIMARY           | PRIMARY   | 4       | const             | 3646 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY           | PRIMARY   | 4       | demo.pr.report_id |    1 |                                              |
|  1 | SIMPLE      | rs    | ref    | PRIMARY,report_id | report_id | 5       | demo.r.id         |  764 | Using where                                  |
|  1 | SIMPLE      | tr    | ref    | result_id         | result_id | 5       | demo.rs.id        |    1 | Using where                                  |
+----+-------------+-------+--------+-------------------+-----------+---------+-------------------+------+----------------------------------------------+

So I think that looks much better, but I'm not sure exactly how to tell. Also the query still seems to take about the same about of time as before.

---

Is results.report_id indexed? It's failing to find a key and doing a table scan it looks like. I'm assuming results.id is actually the primary key.

Also, if it report_id was the primary key, and it's INNODB, it should be clustered on that index, so absolutely no clue why that isn't screaming fast if it is configured that way.