Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Slow Slow Slow

Quote Reply
Slow Slow Slow
How can I speed up the SQL. Even retrieving a record with a specified ID takes forever.
I've indexed every catagory that could be searched on and that didn't help.


Also, Can The "Blob" type of data be indexed?


Quote Reply
Re: Slow Slow Slow In reply to
Hi,

Are you sure this isn't your internet connection or server connection speed rather that MySQL?

Try restarting mysql if possible.

Paul Wilson.
http://www.wiredon.net/gt/
http://www.perlmad.com/
Quote Reply
Re: Slow Slow Slow In reply to
Your pages do load slow, but could be attributed to the following factors:

1) Banner advertisements.
2) Multiple photos associated with records.
3) Amount of text you have in one field/column.

About BLOB, my understanding is that you cannot index it.

Regards,

Eliot Lee
Quote Reply
Re: Slow Slow Slow In reply to
Gotta Have the banners. Need the paycheck. :-)
And I do have lots of text in the "Blob" Field. Server runs great on pages that don't have sql calls on them.

Is there anyway to rebuild an index after the data has been entered? I checked the mysql site and followed instructions. I can create the index table but how does the data from the indexed fields get into the data?

The volume of the site is very busy at last check 27,000 people per day. Could this be the wrong software for what I'm trying to do?


Quote Reply
Re: Slow Slow Slow In reply to
Yes...traffic is another indicator/factor that attributes to pages loading slow...

I would recommend changing the field type from BLOB to LONGTEXT.

Regards,

Eliot Lee
Quote Reply
Re: Slow Slow Slow In reply to
I'll give the long text a try.
I've noticed though that there is no data in the new indexes that I've created. Do I have to resave every record?

Quote Reply
Re: Slow Slow Slow In reply to
No...as soon as you "index" columns in the tables, they are automatically indexed...and when you index fields, you cannot see the "index"...much like MS Access and other database programs.

Regards,

Eliot Lee
Quote Reply
Re: Slow Slow Slow In reply to
Ok.
I added the index into the config, re-ran the nph-setup.

I have two fields indexed. category and story. Only story was previously indexed. Using MYSQLMAN I looked at the table. There is the main database table, story_ndx, and category_ndx (along with the other stuff,log, sessions, users)

story_ndx has every word that was saved into the story field from every record.

category_ndx has only has the one record that I modified since I added the index.

In MYSQMAN, I open the database, then the table, click on properties then index. It creates some sort of index at the bottom of the column listings in a seperate html table at the bottom of the screen, but nothing happens to category_ndx.

Also at this point I cannot search the "category" field at this point.

in the nph-setup - sub create index, it's supposed to create the *_ndx files.

So what did I miss?

Quote Reply
Re: [Dan Wheeler] Slow Slow Slow In reply to
How did you speed up your search in DBMAN SQL. My database is way too slow. Can you help with some ideas.