What is the most efficient way of doing this...

DarkLight posted this at 22:02 — 6th February 2012.

Joined: Oct 2007

Hi guys, it's been a while since my last question. But now I'm stuck, so here I am

I have a database (MySQL) with a table containing millions of rows.
I read/write to this table upto 100 times per minute at peek-time.

I was contacted by my hosting company, and told that I didn't have enough power on my shared-host, and needed a VPS.

Well the time has long since came when I needed a VPS, so I intend to update very soon. But here's what I wanna do. I want to let my site grow, and not worry about the queries on the large table. I need to know if there are any other tables that would be faster/more efficient with the same capabilities of MySQL.

I've thought about InnoDB, but not knowing if it would be beneficial, I never went with it...

Just incase it means anything, here's the most commong query on that table...

<?php
$query = mysql_query("SELECT * FROM songs WHERE SongName LIKE '%$terms%' ORDER BY Popularity DESC LIMIT 30");
?>

Any help here would be appreciated, as this sort of stuff isn't my strongpoint.

UPDATED: I changed "%$terms%" to "$terms" to remove the wildcard, would this matter that much and was it a good move? Or will queries be fine with this?

All the best news here: https://newsbotnet.com

DarkLight posted this at 02:05 — 9th February 2012.

He has: 287 posts

Joined: Oct 2007

UPDATE: Would this be any better? I decided to go the FullText route and implemented that instead... Works great, but is it better on millions of rows?

<?php
$result = mysql_query("SELECT *, MATCH(SongName,ArtistName,AlbumName) AGAINST('$terms') AS score FROM songs WHERE MATCH(SongName, ArtistName, AlbumName) AGAINST('$terms') ORDER BY score DESC LIMIT 30");
?>

All the best news here: https://newsbotnet.com