-1

I am working on a web-application in PHP-MySQL. I am MySQLi API for database access. I have broadly two questions:

  1. How can I increase the performance of the MySQL database?
  2. How much secure and suitable is MySQL for large, enterprise level database?

For question 1, please note the following points:

  • The main central tables of the database are expected to receive about half a million to one million rows every year. How can I speed up retrieval from those tables? I already have created Index on those tables.

  • I am moving the archived rows to a new table, and keeping only currently being used rows in the main table. But at any moment of time, the main table will still have very large number of rows.

Thanks in advance

Kumar Kush
  • 2,495
  • 11
  • 32
  • 42
  • Did you miss bullet point #3, it was just empty? – home Mar 15 '12 at 08:08
  • 1
    @home You always need a "???" bullet point to be followed by "Profit!!!". Unfortunately the OP left *that* off. – deceze Mar 15 '12 at 08:10
  • 6
    This is hard to answer without more details about the database in question. A few million rows are not necessarily any problem for MySQL, that's just warming it up. What does matter is your schema, the exact indexes you have and, more importantly, the queries you do on this database. – deceze Mar 15 '12 at 08:11
  • [MySQLOptimizer](http://nmmm.nu/mysqloptimizer.htm) is a nice tool. – jpic Mar 15 '12 at 08:12
  • 2
    A million rows is not large for a db, and not even close to very large. mysql out of the box wont have an issue with that. Of course if handled wrongly my could probably force mysql to a crawl with a 10 row db table. –  Mar 15 '12 at 08:37
  • About ???, I don't know where it came from. Sorry – Kumar Kush Mar 15 '12 at 09:47
  • @deceze and Dago. Good to hear that a million rows is not a problem. I will review the schema if I can make it better furthur. One more thing, In some SELECT queries involving 8-10 tables, I have used LEFT JOIN. Is it a good technique? Using EQUIJIONs didn't show any difference while analysis. – Kumar Kush Mar 15 '12 at 10:19
  • @jpic: Thanks. I will see how much can it help. – Kumar Kush Mar 15 '12 at 10:21

1 Answers1

2

I already have created Index on those tables.

Yes, but have you created an efficient index? It couldn't hurt to see if some articles might be able to provide some advice. For example:

http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm

(Warning: First visit creates an ad - sorry about that - it was just the first hit from Google that looked relevant.)

JTeagle
  • 2,196
  • 14
  • 15