Speed Issues with mysql

[Anon]

Does anyone have a definitive source of information on speed issues with mysql?

I'm finding that tables containing a large blob field are incredibly slow once filled with data.

I initially though it's cause I was doing comparisons on the field containing the blob, but when I rewrote my code to use flags instead of checking for content in the blob field, it didn't solve my problem. Simple queries on the table are eating huge amounts of resources, and slowing my server to a grinding halt. Is this to be expected?

[andreas]

i got a rule that is something like: NEVER EVER use blobs.... they suck..

Insted put that kinda data in separate files (in a folder outside your web catalog (the webserver need to have access to read and write to this folder too)), and just have a pointer in the database to those files...

Whenever you update a row you could check if you updated the file, if you did, check if there is any content in the file, and if there are set a flag in the row in a field like a TINYINT. Put a index on the field containing the flagg.

OBS!!!: This is NOT appicable to all solutions, but i think it will speed up your database.

Andreas

[Anon]

Thank you very much for the advice.. however.. I think I have found the secret that caused my problem.

General database rule #4286. Put indexes on all columns that will be commonly used in WHERE clauses.

Now with this new knowledge, my queries are down from 19 seconds to immesurably small query times.

One question.. is there ever a disadvantage to having an index.. why not index EVERYTHING?

[[deleted]]

Indexing means building a seperate set of information about the records.
In effect you are building an "abbreviated" copy of your tables.
This copy is used to find which records you want without having to read the whole table.

You can probably imagine that if you use small tables or small columns, the index can be just about the same size as the original table, and thus just as slow.
At that point, reading the index can take just as much time as reading the real table, so everything can actually slow down.

Another time when indexes are not really usefull is when your query returns most of the records in the table.
In that case, the engine still has to read most of the real table to get the data, so the speed of finding the records is not a big advantage (the point of the index was to prevent the engine from having to read all records)

There is a nice article about this in the articles section.