Performance tuning for MySQL database (read only) We are developing a reporting tool that is connected to MySQL database of size 500 GB size.  Reporting tool will be fetching the millions of rows from the database.  The tool is used only for reports. 


It won't be updating, inserting or deleting any data.  
There won't be any join we need to use, at a time tool will be interacting with a single table exclusively.
Tool will use only "order by", "where", "limit (for paging)" clause on the the database.
All the table will be having a column "rowno" as a primary key.  There is no FK relationship between the tables.


Edit:

MySQL version: '5.7.16-log'

Database Engine: MyISAM

Tool Used: MySQL Workbech

What's best way of performance tuning for this database (like using specific row_format etc.)?
MyISAM is going to be deprecated (Bill Karwin is a big hitter on stackexchange and previously worked for Percona!) - you should change MyISAM to InnoDB NOW. You can do that by following the instructions here or here

AIUI, even the system tables in MySQL have4/are been/being moved to InnoDB, see here and here. The latter reference appears to indicate that most system tables have moved to InnoDB in the upcoming 8 release.

You can read some of the pros/cons of MyISAM/InnoDB here.

Even if you're not using the capabilities of the InnoDB engine now, you may need to use them in the future. 

To answer the question, you have two performance tuning options:


you can set your InnoDB TRANSACTIONs to READ ONLY from here,


or you can


set the entire server to be READ ONLY as per here.


You may want to test these, but it is my belief that any small performance penalty is worth paying for the advantages that the InnoDB engine brings!


  It won't be updating, inserting or deleting any data.


This is good for performance - there's no need for the engine to maintain linked lists of pointers to new records and/or other transactions in this case. You should set your transactions to read only as indicated above.


  There won't be any join we need to use, at a time tool will be
     interacting with a single table exclusively.


Hmmm... a database without joins - always been a puzzling concept to me. You do realise that the optimiser can make better decisions about which tables to process in which order if there are explicit joins between the tables - those who argue that FOREIGN KEYs incur a performance penalty don't understand RDBMSs. 


  Tool will use only "order by", "where", "limit (for paging)" clause
     on the the database.


And GROUP BY surely? Otherwise, why not simply use a spreadsheet? In any case, presenting a human user with thousands/millions of lines of data (or in this case apparently, records) is pretty pointless - humans will just scan and skip through pages of numbers.


  All the table will be having a column "rowno" as a primary key. There    is no FK relationship between the tables.


See above about FOREGIN KEYs. No problem with having a simple integer PRIMARY KEY as your surrogate KEY.