my first real db design for own project.

[djemmers]

Hi

I want to optimize a database I have. I expect a growth of the database comming year so I'd better optimize now.
I also want to make an online search engine for it but that is another matter (though it is important information as the db will be mainly used by that engine)

here is what I got to now:

CREATE TABLE IF NOT EXISTS `event` (
  `event_id` int(11) NOT NULL AUTO_INCREMENT,
  `sport_id` int(11) DEFAULT NULL,
  `title` text NOT NULL,
  `description` text NOT NULL,
  `level` text,
  `start` date NOT NULL,
  `end` date NOT NULL,
  `countrycode` varchar(2) NOT NULL,
  `provnum` tinyint(4) DEFAULT NULL,
  `city` text,
  PRIMARY KEY (`event_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;
ALTER TABLE event ADD FULLTEXT(title,description,level);

CREATE TABLE IF NOT EXISTS `Countries` (
  `countrycode` varchar(2) NOT NULL,
  `country` varchar(30) NOT NULL,
  `lang` varchar(2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `Provinces` (
  `countrycode` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `province` varchar(75) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `provnum` int(11) NOT NULL,
  `lang` varchar(2) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `Sports` (
  `sport_id` int(11) NOT NULL,
  `sport` varchar(25) NOT NULL,
  `lang` varchar(2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

off course these won't be the exact names in my db...

where sport_id countrycode and provnum are "linked" with the other tables. In those tables are the names of spotrs/country/provincies
this to be able to load that data in dropdown boxes and the like in my search engine form. Also this takes care of misspelling, and might enable mulit language use.
What I am wondering about: Do those three last need an extra id field as primary key ? to be able to link it with the event table as foreign keys?

Maybe best a small thought about the search engine form.
My searchform will have the following field.

dropdown ->sport
textveld ->title, description
date ->date before
date ->date after
checkbox ->countries
dropdown ->if only 1 country is checked a dropdown with provinces becomes visible
dorpdown ->provinces
text ->city
text ->level

So a user that fills in the form will have roughfly the next querry

SELECT * FROM tblevebt
    WHERE MATCH(title, descirpion,level) AGAINST ('search words from text title,description')
    AND sportid='dropdown sport'
    AND startdate>'date after' AND enddatum<'date before'
    AND (countrycode='country1' or countrycode='country2' or...)
    AND provinces=' dropdown provinces'
    AND city like '%text city%'
    AND MATCH(title, description, level) AGAINST ('search words from level')

I expect there are lots of improvements to be made as I am not used to design databases.
I can code php, html, css and mysql querries though.
I know I can get this working. But I was wondering, will it hold with intensive use? like more then 1000 rows in events and 30 searches per minute?
If more information is needed, I'd be happy to provide!
All tips are welcome.

[nop]

will it hold with intensive use? like more then 1000 rows

well, if you want to optimize based on your table structure just put it all in one table (don't actually do that). also 1000 rows isn't very big. we have db's at work with millions, which i assume is pretty normal.

db optimization is a non-trivial topic and can get pretty vendor-specific. indexing the tables would be a good start.

[djemmers]

Do you have a suggestion as how to index. A good tutorial or manual ?

[WingedPanther]

It depends a LOT on the queries you're using. Look at your where clauses as a start.

[djemmers]

It depends a LOT on the queries you're using. Look at your where clauses as a start.

I don't understand, you see the typical querry above.
that is about the most complex querry.
the others will be without some and parts

[WingedPanther]

What an index does is optimize searching for a set of fields in a table. For each set of fields in the where clause, you have a potential index. The best way to select the indexes you will need is to do query profiling. If your where clause doesn't match the fields in an index, you are back to scanning the entire database.

[papatux]

Check that:
http: 3w.websitedatabases.com/database-index.html

Database indexes for database speed & optimization