Forum Moderators: open
mysql> describe lyrics;
+--------+------------------+------+-----+---------+----------------+
+ Field + Type + Null + Key + Default + Extra +
+--------+------------------+------+-----+---------+----------------+
+ index + int(10) unsigned + NO + PRI + NULL + auto_increment +
+ artist + int(11) unsigned + YES + + NULL + +
+ title + varchar(255) + YES + + NULL + +
+ lyrics + longtext + YES + + NULL + +
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.50 sec)
mysql> select count(*) from lyrics;
+----------+
+ count(*) +
+----------+
+ 56052 +
+----------+
1 row in set (5.38 sec)i need help with this one guys. obviously, there's another way to design this table, maybe break it into smaller ones perhaps? i need to know if i am doing the right thing with this table.
i am having some doubts if i should continue with this design because it is very slow. it takes many seconds to search the table even if i am only searching the 'title' field.
i am just a beginner. and i need help with scalability. and on how to do it properly.
thanks.
i'm no expert, but i am not sure how you can make this much quicker, because it is a full text search and there are a lot of records to search through.
if you haven't already done so, you should create an index on the fields you are searching on: title, artist.
you could also save the searches which people do. over time you could build up a list of popular searches and cache these results so that they are delivered quicker. that way for many searches you are not even hitting the database.
Are you on shared hosting?
And yes, definitely put an index on Title.
mysql> describe lyrics;
.....
4 rows in set (0.50 sec)
There's something else going on here, although I don't have suggestions as to what. A describe should be so fast it registers as 0.00 sec. I also did a count(*) and it returns in .03 seconds. This is a table with over 700K records.
Indexing is always good, your table is simple, it's something else. Shared and overloaded data server?
i am currently working on my desktop computer, it's not much in hardware, just 512 MB in ram & celeron 2.66 GHz, i use it only as a development platform, currently i have apache & mysql always running.
thanks for the replies. i really appreciated it.
i am currently working on my desktop computer
I also have had slow mysql times when testing remotely. For accurate tests, you should upload to your server if you have one and test it there using a test page.
