0

I am into some problems atm, that is, I have a database of some words, which is like 900,000 and what I am trying to do is, check whether a word is in it, and if yes, return another field in the row.

I get timedout when I do that.I know I can change the timeout, but what I actually want is to make it faster somehow if possible. My visitor wont like to wait 30+ secs to get what he is looking for.

I cant make 900,000k static files because I have inodes limit in the hosting.

So I am pretty much locked up, so any thing that I can do to make it better?

Table structure :

ID | Word | Otherword

CREATE TABLE `database` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `word` text COLLATE utf8_unicode_ci NOT NULL,
 `md5string` text COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=966277 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Query:

mysql_query("SELECT otherword FROM table WHERE word='$word'");

Another query:

mysql_query("SELECT word FROM table WHERE otherword='$otherword'");

Main query (a file using GET)

$word = $_GET["word"];
echo $word;
$check = mysql_query("SELECT md5string FROM `database` WHERE word='$word'") or     die(mysql_error());
while (mysql_num_rows($check)>0)
{
$out = mysql_fetch_array($check);
$output = $out[0];
}
Kishor
  • 1,513
  • 2
  • 15
  • 25

4 Answers4

2

Add fulltex index and use MyIsam table if the searching is more then update. Othervise add indexes as per the requirement.

Ankit Sharma
  • 3,923
  • 2
  • 29
  • 49
1

Searching over 1 million rows in a MySQL database shouldn't be a problem providing you have a sensible index.

Try adding an index to your database, like this (assuming the word you're looking for is in a column on it's own):

ALTER TABLE your_word_table ADD KEY(word_column(32));

(editted to include key length since it turns out they're TEXT columns)

John Carter
  • 53,924
  • 26
  • 111
  • 144
  • The thing is, the queries comes both for the word, and another field on the database, almost equal in numbers. – Kishor Apr 07 '12 at 01:24
  • So add keys for both columns? From your sample queries I don't see the problem. – John Carter Apr 07 '12 at 01:36
  • What times out? The `ALTER TABLE` statement? – John Carter Apr 07 '12 at 01:48
  • There aren't any useful indexes right now. The only fast lookups based on your table statements given are by "id" and your queries use "word" and "md5string" for lookups. Lookup EXPLAIN in the MySQL documentation to learn more about how MySQL is running your query. – Joshua Martell Apr 07 '12 at 01:49
  • no, what timesout is not not the alter table one, but my other query. I will add an index now. But as you said, 1million wont take 30 seconds+ so there must be something that I am doing wrong. I added the whole query in the main post. Can you give a check? – Kishor Apr 07 '12 at 03:58
  • Apart from the SQL injection risk the query is fine, see what performance is like after adding your index, it should improve dramatically. – John Carter Apr 07 '12 at 04:43
  • `BLOB/TEXT column 'word' used in key specification without a key length` I get this when I try to add index – Kishor Apr 07 '12 at 07:22
  • 1
    @Kishor I've editted my answer to show how to specify a key length. Alternatively you change the column types to make `word` a `varchar` and the MD5 a char(32) (since MD5s have a fixed length) - you don't need to specify key lengths for char/varchar. – John Carter Apr 07 '12 at 23:56
1

1 million shouldnt be 30 secs. Can you give us your query and table structure? Try using fulltext indizes for the fields. Try tricks like defining a Search row with uppercase words only searching for uppercase etc.

ALTER TABLE  `database` ADD FULLTEXT (
 `word` , `md5string` );
worenga
  • 5,776
  • 2
  • 28
  • 50
1

This should make your lookups fast after it finishes. Updated to include how much of the column to index because these are text fields.

ALTER TABLE `database` ADD INDEX word_idx (word (32)), ADD INDEX md5_idx(md5string (32))

Did you name your table "database"? It's a reserved word in MySQL and will probably cause you some headaches....

Joshua Martell
  • 7,074
  • 2
  • 30
  • 37
  • Ha.. Yeah, I named the table Database. I was in the learning course, and came to know it is reserved. So I used backticks. Next time, I will keep an eye on reserved words. – Kishor Apr 07 '12 at 03:59
  • I get this when I try the query. `BLOB/TEXT column 'word' used in key specification without a key length` – Kishor Apr 07 '12 at 06:40
  • Yeah, I added the widths and ran it already. But when I try to run the query I stated in the main post it timesout :( – Kishor Apr 07 '12 at 14:07
  • Can you please paste the output of `EXPLAIN SELECT md5string FROM \`database\` WHERE word="awordhere"`, or whichever query is timing out for you? – Joshua Martell Apr 07 '12 at 17:03
  • Figured out it was something wrong with fetch array part of the code. Should be able to debugg it myself. Thanks for the help mate. :) – Kishor Apr 07 '12 at 17:28