MySQL speed questions...

[kaisellgren]

Hi!

I'm doing a simple project, but I will store lots of data in MySQL...

I'm going to store 100 MB initially and maybe 10-25 MB more per year...

I have a VPS, space is not a problem but what about speed? I dont think 100 MB+ table would be a good idea (yes all data are in one table, its not a complicated script, forum, or anything like that its plain data storing/stats in one table).

So, which of these is the best way to store the data:
1) Storing 100 MB+ in the one table (like it is now, but not all data is loaded yet...)
2) Storing 100 MB splitted into multiple tables (we have around 30 "categories" in our project, so I could split the data into 30 tables, around 3 MB for each table)
3) Having 30 databases.
4) Something else?

Also. Should I use indexes? I'm not worried aabout space usage, only the speed of quering.

Another question: Is MyISAM a good choice for my table(s)?

One thing to add: I'm mainly reading the data. The writing does not need to be fast since a littlle info per day is being added and its not critical info so it can take time to add.

Last Question: Is using LIKE in queries slow?

[brazenskies]

have you done any normalisation?

Post up your table structure and we can take a look at it

[brazenskies]

also, LIKE can be a bit slower. Are you trying to match the data against a search term?

If so I'd recommend FULLTEXT index and using MATCH

[kaisellgren]

Ok, thanks for the FULLTEXT& MATCH tip!

Here's my structure:

CREATE TABLE ss_set_eng (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL DEFAULT '',
year SMALLINT UNSIGNED NOT NULL DEFAULT '2008',
language VARCHAR(255) NOT NULL DEFAULT 'Suomi',
CDs VARCHAR(255) NOT NULL DEFAULT '',
Exhert DECIMAL(6,3) UNSIGNED NOT NULL DEFAULT '23.441',
unix_timestamp INT UNSIGNED NOT NULL DEFAULT '0',
hits INT UNSIGNED NOT NULL DEFAULT '0',
source VARCHAR(255) NOT NULL DEFAULT '',
provider VARCHAR(255) NOT NULL DEFAULT '',
p_addr VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY(id),
INDEX(name));

The name is being searched for through a form using LIKE %keyword% but sometimes a bit more complicated like %keyword1%keyword2% and so on, but I do not need regex though, just * matching..

Edit: there will be initially 468 000 IDs taking up 100MB. Then it will increase by maybe 10 000 IDs per year.

[brazenskies]

What you want to do is create a fulltext index on columns you want to search on and use the following...


SELECT
*, MATCH (columns)
AGAINST
('search_term' IN BOOLEAN MODE)
AS
relevance
FROM
table_name
ORDER BY relevance DESC


Give that a shot and see how it goes. That will match results with 2 words in search term. i.e Searching for 'paris map' wil return results with both words first, then results with 'paris' then results with 'map'

Hopefully this is what you're trying to do!

[brazenskies]

also worth adding...

always remember that many small tables are always better that one bigg'un!

[kaisellgren]

Hey thanks a lot for your help!

Yes I'm splitting up my data into around 30 tables and when the user searches, only one table is being searched for :) (faster).

The fulltext searching seems quick and good. But I have one question for you: Can I somehow match using FULLTEXT searching just like the LIKE does? I mean:

SELECT id FROM a WHERE name LIKE '%a%';

That would find all ids with letter 'a' in somewhere. Is this any way possible using FULLTEXT?

[brazenskies]

You can yes, however i'd create an index on the id column also.

[guelphdad]

also worth adding...

always remember that many small tables are always better that one bigg'un!

Um, no.

[brazenskies]

I didnt mean to have 30 tables of the same data. I meant a well normalised design

[kaisellgren]

You can yes, however i'd create an index on the id column also.

How do I achieve this % -usage with AGAINST () ?

I have right now 31 tables and the data is categorized. When you search, you select a year range (like 1990-2000) and then a specific table (of 30) is used. It's around 2-5 MB per table.

[ST-Mike]

I personally don't think it will be much of a issue dependant on how often the database is being accessed. As you're running from a VPS you have the benefit of fast I/O on very high grade HDD's.. just remember to be using the correct datatypes (of course) and to have indexes in place where required.