Php – How to improve performance of MySQL query with frequent insert rate

mysqlperformancephpscalability

I'm having approx. 200K rows in a table tb_post, and every 5 minutes it has approx. 10 new inserts.

I'm using following query to fetch the rows –

SELECT tb_post.ID, tb_post.USER_ID, tb_post.TEXT, tb_post.RATING, tb_post.CREATED_AT,
       tb_user.ID, tb_user.NAME 
FROM tb_post, tb_user 
WHERE tb_post.USER_ID=tb_user.ID 
ORDER BY tb_post.RATING DESC 
LIMIT 30

It's taking more than 10sec to fetch all the rows in sorted fashion.

Following is the report of EXPLAIN query:

id select_type     table       type    possible_keys   key             key_len     ref         rows        Extra
1   SIMPLE          tb_user     ALL     PRIMARY         NULL            NULL        NULL        20950       Using temporary; Using filesort
1   SIMPLE          tb_post     ref     tb_post_FI_1    tb_post_FI_1    4           tb_user.id  4

Few inputs:

tb_post.RATING is Float type
There is index on tb_post.USER_ID

Can anyone suggest me few pointers about how should I optimize this query and improve its read performance?

PS: I'm newbie in database scaling issues. So any kinds of suggestions will be useful specific to this query.

Best Solution

You need an index for tb_post that covers both the ORDER BY and WHERE clause.

CREATE INDEX idx2 on tb_post (rating,user_id)

=> output of EXPLAIN SELECT ...ORDER BY tb_post.RATING DESC LIMIT 30

"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
"1";"SIMPLE";"tb_post";"index";NULL;"idx2";"10";NULL;"352";""
"1";"SIMPLE";"tb_user";"eq_ref";"PRIMARY";"PRIMARY";"4";"test.tb_post.USER_ID";"1";""

Related Solutions

Mysql – How to output MySQL query results in CSV format

From Save MySQL query results into a text or CSV file:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: That syntax may need to be reordered to

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

in more recent versions of MySQL.

Using this command, columns names will not be exported.

Also note that /var/lib/mysql-files/orders.csv will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.

Sqlite – Improve INSERT-per-second performance of SQLite

Several tips:

Put inserts/updates in a transaction.
For older versions of SQLite - Consider a less paranoid journal mode (pragma journal_mode). There is NORMAL, and then there is OFF, which can significantly increase insert speed if you're not too worried about the database possibly getting corrupted if the OS crashes. If your application crashes the data should be fine. Note that in newer versions, the OFF/MEMORY settings are not safe for application level crashes.
Playing with page sizes makes a difference as well (PRAGMA page_size). Having larger page sizes can make reads and writes go a bit faster as larger pages are held in memory. Note that more memory will be used for your database.
If you have indices, consider calling CREATE INDEX after doing all your inserts. This is significantly faster than creating the index and then doing your inserts.
You have to be quite careful if you have concurrent access to SQLite, as the whole database is locked when writes are done, and although multiple readers are possible, writes will be locked out. This has been improved somewhat with the addition of a WAL in newer SQLite versions.
Take advantage of saving space...smaller databases go faster. For instance, if you have key value pairs, try making the key an INTEGER PRIMARY KEY if possible, which will replace the implied unique row number column in the table.
If you are using multiple threads, you can try using the shared page cache, which will allow loaded pages to be shared between threads, which can avoid expensive I/O calls.
Don't use !feof(file)!

I've also asked similar questions here and here.

Best Solution

Related Solutions

Mysql – How to output MySQL query results in CSV format

Sqlite – Improve INSERT-per-second performance of SQLite

Related Question