The current query takes approx 6-7 seconds. Data is been fetched from 7 tables. Tables are fully normalized, frequently updated but are mostly read approximately 100 times a day. The same query gets executed 7,00,000 times in a single day with updated data. A query is already optimized and indexed. Table approximately has 300,00,000 rows. Is there any way to reduce the query time? The table works on the InnoDB engine.

I was planning for few methods please correct me if I am wrong

  1. Memcache the rows using PHP
  2. Use replication Master/Slave architecture in slave have MyISAM (Doubt about the engine in this architecture)
  3. Use MySQL caching mechanism
    • (a) query_cache_size = 268435456 query_cache_type=1 query_cache_limit=1048576
    • (b) buffer_pool
    • Can both (a) and (b) perform together? Will it improve the performance for such huge data?

Is there some other technique for improving query execution time?

  • 1
    Step 1: figure out "why is it slow". Post PLAN so we may help. . You also forgot the solution of "add more hamsters". Oh... 3M rows is tiny for 21st century hardware. Oct 25 '18 at 1:05
  • 1
    query cache will hurt the performance, especially on frequently updated tables. DIY memcache will probably hit same troubles - cache invalidation is hard. What is your server version/buffer pool size/SHOW GLOBAL STATUS LIKE "innodb_buffer_pool%"/query/EXPLAIN {query}/table structures.
    – danblack
    Oct 25 '18 at 2:20
  • sorry its typo error. its not 3,00,000 but 300,00,000. innodb_buffer_pool =1.5GB. Ram 3gb. Oct 25 '18 at 6:44
  • Denormalization sometimes is the key to the speeding up. Another way is the trigger that perform the query AFTER UPDATE and store the result for further direct access in the dedicated table.
    – Kondybas
    Oct 25 '18 at 21:27
  • Adding to what @danblack says, I'd say your query cache is much too large. More is not only not better, it is often much worse, beyond a certain threshold (which is very workload dependent). Drop it to maybe 32M or even 16M. But before tinkering, it's important to try to understand what the server is spending so much time doing. MySQL is not inherently slow and 300M rows is not especially large, but things like improper or nonexistent indexes will slow things down. Use the slow query log and also check the query plan for sanity. Oct 25 '18 at 22:33

With 3GB of RAM, you should probably keep innodb_buffer_pool_size at no more than 1G.

The Query cache is of minimal use, and it gets slow when you increase its size. Recommend no more than 32M for query_cache_size.

There is such a thing as "over-normalization"; please provide details.

What indexes do you have? Do you understand when to use a 'composite' index?

Don't use memcached -- that's just putting a cache in front of a cache.

Replication may be useful if you have lots of users contending for MySQL. It can speed up a query only if that is a lot of contention during the query. I don't hear that is the issue.

Please provide the query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.