best choice of database for php

Question

My project is a HTTP chat using some Jquery, PHP and Mysql.

Jquery infact polls the database every N seconds to check if I have receieved new messages (polling form a mysql table).

Since chat has now >100 connections, mysql gets now 100 requests within N seconds.

What are the best practices, database choice to optimize it ? (it must remain an HTTP chat without using some server sockets)

regards

PS. Maybe MYSQL is not the good choice

@HugoDozois: He says he's using Mysql. He says he has to use HTTP. He doesn't say he has to use MySQL. — Amadan, Dec 13 '12 at 00:15
If you're looking for speed, you may be able to leverage a noSQL solution like mongodb or cassandra — jchapa, Dec 13 '12 at 00:20
Wouldn't Cassandra be a bit overdoing it? Any reasons to use it in such a setting? — Phil Rykoff, Dec 13 '12 at 00:24
Why are you round tripping chat entries into a database? A socket based solution with NodeJS would probably be a lot more responsive at scale. http://nodejs.org/ — antman, Dec 13 '12 at 00:33

score 3 · Answer 1 · answered Dec 13 '12 at 00:46

MySQL is fine as a database, but your polling usage of it is not best practice.
An RDBMS is the wrong tool for that job.

Look at message queue solutions. Here are some resources:

score 2 · Accepted Answer · answered Dec 13 '12 at 00:17

2

Id go with MySql since it is very light weight. PHP and MySql get along well. Since you will have many updates and many reads to the database the InnoDB engine will suite this scenario the best. You may run into problems if you choose MyIsam since it is suited for one job at a time (either updating or reading).

answered Dec 13 '12 at 00:17

Luke101

63,072
85
231
359

Why not use the Memory engine, isn't it a perfect fit for such a scenario (Wehen choosing MySQL)? – Phil Rykoff Dec 13 '12 at 00:19
Yeah MyISAM locks entire tables during writes, while InnoDB only locks rows within tables. – Rimu Atkinson Dec 13 '12 at 00:20

score 1 · Answer 3 · answered Dec 13 '12 at 00:16

Look into in-memory databases. Chats are normally not something people persist; so a transient database might be just the ticket. For instance, SQLite can do it.

However, I'd be much more worried about your web server, I think. :)

Any particular reason not to use sockets? This scenario is what they're created for. (Probably inflexible hosting service?)

best choice of database for php

3 Answers3

Linked