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

DarkLight's picture

He has: 287 posts

Joined: Oct 2007

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

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?

ColdCast is an open social network funded by your donations. A place where you can be yourself without Big Brother breathing down your neck.https://coldcast.org

DarkLight's picture

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");
?>

ColdCast is an open social network funded by your donations. A place where you can be yourself without Big Brother breathing down your neck.https://coldcast.org

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.