Barrera Barrera - 1 month ago 15
SQL Question

Multiple simultaneous selects from mysql database table

I'm working on a database that has one table with 21 million records. Data is loaded once when the database is created and there are no more insert, update or delete operations. A web application accesses the database to make select statements.

It currently takes 25 second per request for the server to receive a response. However if multiple clients are making simultaneous requests the response time increases significantly. Is there a way of speeding this process up ?

I'm using MyISAM instead of InnoDB with fixed max rows and have indexed based on the searched field.

Answer

If no data is being updated/inserted/deleted, then this might be case where you want to tell the database not to lock the table while you are reading it.

For MYSQL this seems to be something along the lines of:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

(ref: http://itecsoftware.com/with-nolock-table-hint-equivalent-for-mysql)

More reading in the docs, if it helps:

https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

The TSQL equivalent, which may help if you need to google further, is

SELECT * FROM TABLE WITH (nolock)

This may improve performance. As noted in other comments some good indexing may help, and maybe breaking the table out further (if possible) to spread things around so you aren't accessing all the data if you don't need it.

As a note; locking a table prevents other people changing data while you are using it. Not locking a table that is has a lot of inserts/deletes/updates may cause your selects to return multiple rows of the same data (as it gets moved around on the harddrive), rows with missing columns and so forth.

Since you've got one table you are selecting against your requests are all taking turns locking and unlocking the table. If you aren't doing updates, inserts or deletes then your data shouldn't change, so you should be ok to forgo the locks.

Comments