how to make search fast?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

how to make search fast?

Post by itsmani1 »

Hi
Is there anything for making the search fast in mysql? like indexing in MS Sql Server.
I want to make the search function fast.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Lots of RAM and very high speed hard drives.

Optimizing the table structures and the configuration settings are quite important. What research have you done so far?
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Also depends on what you are searching for. If full text searching this is "very" slow. Text in general is slow. Integers are fast.

Do you have an example table(s) and SELECT query to optimise ?
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Post by itsmani1 »

Well what i got it we can use:

MySQL's support for full-text indexing and searching for this we need to use index of type FULLTEXT with MyISAM tables only for CHAR, VARCHAR, or TEXT columns.

this thing I got on mysql manual.

any other idea because what i want to do is to make an ebay type search. if you see ebay for their search they made a dll and it wors very fast i want to make search like ebay.

i think using Stored procedures can also help a bit, what do you say about this?
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

MySQL caching or any other caching mechanism with indexing will definitely makes the searching faster.
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Post by itsmani1 »

dibyendrah your idea seems great man, have you experienced it before?
can anyone tell me how to use this?
thanks for good idea again.

basically the thing i want to copy ebay model if any one has any good suggestion also suggest me on it and you know they [ebay] people has a huge database can mysql bear this thing or not? or i have to go for oracle?

thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

ebay has clusters of servers which cache most, if not all of their data in RAM.
mentor
Forum Contributor
Posts: 100
Joined: Sun Mar 11, 2007 11:10 am
Location: Pakistan

Post by mentor »

I hope your data is not as big as WikiPedia. WikiPedia is using MySQL. Do not use fulltext on TEXT otherwise search will slow down. You can search in product specs.

These articles will help you get better performance
http://www.informit.com/articles/articl ... Num=2&rl=1
http://www.sitepoint.com/article/optimi ... pplication
http://dev.mysql.com/tech-resources/art ... oning.html

Performance also depends on your DB design. You can start with a single MySQL server, then you can move to MySQL replication or MySQL cluster for better performance.

Take a look at this http://www.mysql.com/industry/web/
Post Reply