0

I have a mysql table that already have 1 million records and will grow continuously.
But this table is read heavy and slow performance.
I want to speed it up.
I know mysql sharding, partition and master/slave solution.
But it unable to fullfil my requirement.

ps. I have tune the mysql configuration, and the table structure cannot be change.

Chris Gerken
  • 16,221
  • 6
  • 44
  • 59
Magic
  • 1,182
  • 2
  • 18
  • 28

1 Answers1

0

Why don't you consider your the LIMIT clause. Reading millions of records at once will definitely cause problems. Instead of making a single query and wait for too long use multiple queries and either display data as each query works or combine and then display.

SELECT * FROM `whatever` LIMIT 0, 10000;

then

SELECT * FROM `whatever` LIMIT 10001, 10000;

Ofcourse this can be in a loop.

M. Ahmad Zafar
  • 4,881
  • 4
  • 32
  • 44
  • The query result set is only several records. And limit is without use because we need to query the whole table to get the result. There's million record in the table and read heavy. Maybe the problem is rise in the design. – Magic Jul 30 '12 at 01:28
  • Since you already mentioned that the table structure cannot be changed and you are aware of table partitioning etc. How long does it take to run the query `SELECT COUNT(*) FROM whatever` – M. Ahmad Zafar Jul 30 '12 at 03:37
  • I haven't test it strictly. In phpmyadmin, run SELECT COUNT(*) take several seconds. – Magic Jul 31 '12 at 03:57