Best way to save and search in mysql with 2 or 3 millions of rows

2CODE

Hey Builders,

I want to know what's the best way to search in a table with 2 or 3 million of rows ?

Thanks you.

Weedpacket

Have a well-designed database schema, with appropriate indexes.

2CODE

I use indexes, What else will help with that ?

laserlight

After using appropriate indices, is performance still a problem?

2CODE

I didn't build the search engine yet, I decided to know the good ideas of searching in a huge rows so I will build my code and optimize it in the same time.

So do you think the indexes are enough ? I already done that so I ready to start ?

bradgrafelman

That would depend upon whether you chose appropriate indices.

Could you show us actual examples of the schema you've designed? It would probably be easier to critique something concrete/specific rather than speaking in broad/general terms.

2CODE

Here's the table code

CREATE TABLE `webpages` (
	`pageid` INT(10) NOT NULL AUTO_INCREMENT,
	`pagetitle` VARCHAR(250) NOT NULL,
	`pagecontent` LONGTEXT NOT NULL,
	`pagedate` INT(10) NOT NULL,
	PRIMARY KEY (`pageid`),
	INDEX `pagetitle` (`pagetitle`, `pagedate`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

I've made a index into title, id and date which I want to get them faster in my search operations, Also I saw that InnoDB is also make a performance change.

Waiting your thoughts, There's no PHP code done yet.

sneakyimp

When you say "search" that could mean just about anything. I suspect you'll want to do a full-text search of pagetitle and pagecontent in which case you'll need to familiarize yourself with MySQL Full-Text Search Functions.

2-3 million longtext records sounds like a pretty slow search to me, depending on how much text you actually store.