# \[SOLVED\] Database Select Query Optimization

**URL:** <https://onehack.st/t/solved-database-select-query-optimization/73242>\
**Category:** Tutorials & Methods\
**Tags:** solved, data-recovery\
**Created:** [March 13, 2020, 3:51pm UTC](https://onehack.st/t/solved-database-select-query-optimization/73242 "2020-03-13T15:51:51Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![4n0nym1ty](https://onehack.st/user_avatar/onehack.st/4n0nym1ty/32/35752_2.png) [@4n0nym1ty](https://onehack.st/u/4n0nym1ty)\
**Post date:** [March 13, 2020, 3:51pm UTC](https://onehack.st/t/solved-database-select-query-optimization/73242/1 "2020-03-13T15:51:51Z")

</div>

Hi All, Anyone Knows how to optimize select query when daily MySQL dump data is more than 10 million.  
I m selecting 8 columns to fetch for last 3 month data with pagination and page size max 100 row return?

---

<div class="post-metadata">

**Author:** ![M.Hassan\_Ali](https://onehack.st/user_avatar/onehack.st/m.hassan_ali/32/22715_2.png) [@M.Hassan\_Ali](https://onehack.st/u/M.Hassan_Ali)\
**Post date:** [March 14, 2020, 6:14am UTC](https://onehack.st/t/solved-database-select-query-optimization/73242/2 "2020-03-14T06:14:32Z")

</div>

can you elaborate what are you trying to fetch? like there are so many ways to fetch data from tables like if you’ve a ECOMMERCE STORE then try to fetch data with PRODUCT BRAND OR for being more precise go with Product ID.

---

<div class="post-metadata">

**Author:** ![reetkr](https://onehack.st/user_avatar/onehack.st/reetkr/32/19290_2.png) [@reetkr](https://onehack.st/u/reetkr)\
**Post date:** [March 14, 2020, 7:38pm UTC](https://onehack.st/t/solved-database-select-query-optimization/73242/3 "2020-03-14T19:38:25Z")

</div>

run this in psql  
EXPLAIN ANALYZE [yOUR SELECT qUERY]

---

<div class="post-metadata">

**Author:** ![Parizaad](https://onehack.st/user_avatar/onehack.st/parizaad/32/174871_2.png) [@Parizaad](https://onehack.st/u/Parizaad)\
**Post date:** [March 15, 2020, 9:47pm UTC](https://onehack.st/t/solved-database-select-query-optimization/73242/4 "2020-03-15T21:47:40Z")

</div>

Here are some innodb examples that work on large tables of approx. 60 to 500 million rows that demonstrate the advantages of a well designed innodb table and how best to use clustered indexes (only available with innodb)

- [MySQL and NoSQL: Help me to choose the right one](https://stackoverflow.com/questions/4419499/mysql-nosql-help-me-to-choose-the-right-one-on-a/4421601#4421601)

- [60 million entries, select entries from a certain month. How to optimize database?](https://stackoverflow.com/questions/5451190/60-million-entries-select-entries-from-a-certain-month-how-to-optimize-database/5451389#5451389)

- [Rewriting mysql select to reduce time and writing tmp to disk](https://stackoverflow.com/questions/3534597/rewriting-mysql-select-to-reduce-time-and-writing-tmp-to-disk/3535735#3535735)

You will also want to read the following:

- [http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html](http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html)

- [http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/](http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/)

Once you’ve sorted out your table designs and optimised your innodb config:

[http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/](http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/)

[http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb\_buffer\_pool\_size/](http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/)

You can try something like:

```auto
start transaction;

insert into target_table (x,y) select x,y from source_table order by x,y;

commit;

```

Hope this helps.

---

<div class="post-metadata">

**Author:** ![4n0nym1ty](https://onehack.st/user_avatar/onehack.st/4n0nym1ty/32/35752_2.png) [@4n0nym1ty](https://onehack.st/u/4n0nym1ty)\
**Post date:** [March 16, 2020, 3:15pm UTC](https://onehack.st/t/solved-database-select-query-optimization/73242/5 "2020-03-16T15:15:36Z")

</div>

Thanks you i will go through it.