Indexes make best sense when they match the queries you are running. In a simple form, if your 'where' part queries 'weight,age,height' (all of those) then create one index containing all three.

Text searching can be very slow; but speed improvements can be obtained by being sensible about what you store and search for. Consider making a new column in the table which holds just the interesting parts of the text.

Whenever you are running a given query quite regularly on data which changes irregularly, consider outputting the query to another table, and updating that table only when the source data changes. As you discuss having four tables; I might suggest that you retain your one table, but have your four tables working as saved query results for each type (method and term for this depends on your database software).

Three million records in itself should not be a major problem unless you have particularly long rows or too many expensive indexes which need updating with each insert or update you perform.

In all likelyhood you have unnecessarily long rows making the whole table large in terms of Mb. As the whole table will typically be loaded in to memory, and memory is probably limited on your database server, consider ways to reduce the row length or to increase memory availability on the server.

If your table is using more than the available memory then it will be swapping itself in and out of memory by writing and reading from space on the harddrive. This is very slow and will make everything crawl (although it will work).

As you say you've had an index appearing to hang during creation; if you have physical access to the box, check free memory, CPU load and notice whether the harddrive is working like crazy. True computationally taxing addition of indexes will not give much load to your drive, but will give you a high CPU load.

VinceVinceVince,

I rarely ever will do inserts on this (maybe 1 insert per day at the most.) It will mostly be lookups of the information contained within the table. Saving queries to a table sounds like it will be a very smart thing for me to do. I have looked and when a query is running it uses tmp files which I understand to be the slowest way it can work. I looked online but do not see a way to make a table based on another like you say. Can you give me some guidance and direction? A helpful link to a specific page on it even would help me greatly.

I have looked and when a query is running it uses tmp files which I understand to be the slowest way it can work.

The default temporary table size in RAM is 32MB I think. (If it is MySQL.) Is this database on a local server or is it shared hosting? Also how much RAM is in the computer? Also what db software is it?

Can you post the table schema?

Regards...jmcc

The mysql database is on a VPS. I am unsure of the memory but I believe it is 768 (is there an easy way to find out? I cannot find the manual online from my webhost)

My tables are
Card (which is the largest)
id int(10)
not_a_player tinyint(1)
image_allowed int(1)
sub_card int(10)
subset_id int(16)
set_id int(16)
complete_name varchar (255)
number varchar(255) "numbers can be numeric or alpabetical"
numbered(6)
high_price int(9)
low_price int(9)
flags varchar(255)
sort_order tinyint(5)
sport varchar(20)

Player (I was planning on moving the player information from the above table to this table and replacing with ID, but cannot do anythin cause it is so so slow, All types are same as above)
id
not_a_player
complete_name
sport

SET
id
start_year
end_year
name
sport

I also have a few other tables that don't have much information in them.

The top command might show you how much memory you have (along with a pile of other things).

Have you looked at normalising the data? Can you break the Card table into a set of tables based on sports?

Are you using indexes with the Card table and if so, what is the index on? A query on a table with 3 million rows could easily take hours if the table has no indexes. (I missed the index comment in your earlier post. Creating an index on a large table will take a while so it may not be hanging.)

A large table with only 786MB of RAM is not good.

Regards...jmcc

Mem: 2583M Active, 676M Inact, 231M Wired, 106M Cache, 199M Buf, 47M Free

That is the memory line from Top. I could seperate the card tables into sports but Vince suggested I don't but instead save the query. That data could be normalized, but at the same time I think it may slow it down because it would cause two tables needed for lookups. I have a couple indexs on the card table. Currently I have the following indexes on the table card:

complete_name
not_a_player/complete_name
set_id
not_a_player
complete_name_sport

Thanks, this has been extremely frustrating for me. I have been trying different things for days now.

I could seperate the card tables into sports but Vince suggested I don't but instead save the query.

Breaking the data down into smaller and more manageable tables is a lot better than having one huge table.

That data could be normalized, but at the same time I think it may slow it down because it would cause two tables needed for lookups.

Getting the normalisation process right is the key to a good db design. It might appear that having to look up data across two tables would be slower but the reality is that it is often much faster because the tables and their indices are smaller. Smaller tables are both faster to update and search.

I have a couple indexs on the card table. Currently I have the following indexes on the table card:

You have a unique/primary key?

Regards...jmcc

I do have a unique/primary. I will try to split it into the different sports. Which was something I was considering in my first post. The normalization couldn't pull out that much really. Is there a good way to move the data based on sport from one table to another?

You can create the new table and use an insert like the one below:

INSERT into new_table(col1,col2,col3) SELECT col1,col2,col3 FROM Card WHERE sport='sport';

( [dev.mysql.com...] )

Regards...jmcc

jmc,

Thanks that is how I set it up. How long do you see it taking? (3 million records retrieval of about 1/4 each time) Also should I run each one independently or all at the same time.

jmc,

I tried to split it up however it froze up the other site on the same server. I am going to try to download it and do it locally.

How long do you see it taking? (3 million records retrieval of about 1/4 each time)

I don't know but it should take a little bit longer than it takes to select the records from the Card table.

Also should I run each one independently or all at the same time.

Run each one after the other. The table and indices should be in RAM.

If you are transferring data, you can either dump the data using mysqldump or you can select the data into an output file and order this data by sport.

SELECT INTO OUTFILE 'outfilename.txt' col1,col2,col3 FROM Card ORDER BY sport;

then to load into the new local table you could use
LOAD DATA INFILE 'outfilename.txt' INTO TABLE Card(col1,col2,col3);

Regards...jmcc

Actually using WHERE sport='sport' could be used to dump the data into files ready to import for the new sport based card tables.

Regards...jmcc

Did you try simply dropping and recreating the existing indexes for the table? You may have an index that is corrupted and your queries may be resorting to a full table scan (may not be using an index at all).

Just a suggestion before pursuing drastic measures especially if this was working fine until recently and there hasn't been drastic changes in the amount of data in the table(s) or the schema.

[edited by: ZydoSEO at 5:37 am (utc) on Nov. 27, 2007]

I have downloaded the whole DB and bought a server via serverbeach to check and see if it is just slow because of over traffic. I am also deleted the indexes to see if that speeds it up.

SELECT complete_name, sport FROM `card_$sport` WHERE not_a_player = '' OR not_a_player='0' GROUP BY complete_name ORDER BY complete_name LIMIT $from, $max_results

is a query from my site that will run often and is slow. What would be the best index. I have split the sports apart to see if that speeds it up also.

not_a_player = '' OR not_a_player='0'

I remember an issue with MySQL that it won't use available indexes effectively if there is an OR in the WHERE clause. Maybe this is the source of the slow query. On the MySQL manual page [dev.mysql.com...] one user comments with the following:

SELECT * FROM a WHERE index1 = 'foo'
UNION
SELECT * FROM a WHERE index2 = 'baar';

is much faster than

SELECT * FROM a WHERE index1 = 'foo' OR index2 = 'bar';

It may be worth trying to split your query in two and join the result with a UNION.

I will test it with that as well as the IN clause. It seems to be pretty fast on the new server (1 second or so) unless I am looking for results with a high limit, such as LIMIT 30000, 30 which I use to pagnate the results. Are there any tips for that.

You can use SELECT SQL_BUFFER_RESULT to get MySQL to buffer the results in a temporary table. Also SELECT SQL_BIG_RESULT allows MySQL to handle the large number of results more efficiently.

Regards...jmcc

From your table definition:

not_a_player tinyint(1)

From your query:

WHERE not_a_player = '' OR not_a_player='0'

You have defined not_a_player as an integer, but in your query you are comparing with the strings '' and '0'. This causes an extra string conversion during the query evaluation, but more important, MySQL will probably reject to use available indexes because the sorting sequence for integers and strings are different.

Replacing the WHERE clause with WHERE not_a_player=0 should give the same results. If the field not_a_player accepts NULL values, you may have to try WHERE not_a_player IS NULL OR not_a_player=0.

thanks for the tips. I have shaped a few tenths off (after the first big drop in time.)