I am having a database of more than 8 million rows I want to search these 3 columns for a word I want to just know whether this command would put huge load on server? $word is the word I have to search and cname1,cname2,cname3 are columns whether this command would put on huge laod on server if it searches through 8 million rows?
Instead of select * use select columname so you aren't requesting all the data. Also you should set up an index over the columns to make it faster. Maybe limit the results as well. eg LIMIT 10
Realistically suck it and see, without performing it on your own data there is no way to tell. Also how often is the query run? If its run say once a day then there is no real issue if it hammers the server, if its being run several times a second then obviously there will be. As Mad4 has said getting rid of the * is a good idea, even if your bringing back all the columns, always specify the columns you want. Again as Mad4 has said check the indexing of the columns, good indexing should make almost any query fly, bad or no indexing can make the query kill your server. Jen
This query / database design is very bad.. You want for each of the columns (cname1, cname2, cname3) to have an index lookup and not a table scan on such a large table. Problem is that one query will use one index. If you make one index over (cname1, cname2, cname3) it will still do a tablescan for each search, because when uses the index for ie cname1 it still has to check for the word in cname2 and cname3 too at the same time and these aren't ordered. - the index is only ordered for cname1; cname2 and cname2 are in random order So you have 2 solutions: either you change the table layout so you don't have cname1, cname2 and cname3 but just have 1 column with the word: cname, and if you need to know if it was cname1,2 or 3 then add an other column with an integer that says 1,2 or 3. Put an index on this cname column and your searches will go fast. But depending on the other columns you have this may mean you duplicate some data. This is what I would do. If that's not performant enough then even split up the table so you only have a table (id, cname) and using the id you can lookup other columns you need from another table. If the table becomes too long you can even split it up, make a cnameAM that contains words starting with A to M and cnameNZ that contains the rest.. If you cannot change the table layout: instead of using 1 query, use multiple queries Put an index on each of the columns and use 3 queries. So make an index on (id, cname1), make an index on (id, cname2) and (id, cname3) and do: SELECT * FROM 'linktable' WHERE cname1='$word' SELECT * FROM 'linktable' WHERE cname2='$word' SELECT * FROM 'linktable' WHERE cname3='$word' these 3 selects will go very fast as they each do a lookup on their specific index and then join the 3 resultsets for further processing or lookup more columns using the id's you retrieved
First I had like this only ID CNAME The query was very fast But the problem was It had to search other data also every time
We ran into similar issues with our article directory - not 8,000,000 rows but it is still a sizeable database. we created full text indexes of the columns that we were searching. The searchs then flew and ranked based on relvency.