Speed up "SELECT *" on a table with a MEDIUMTEXT field

[H0nger]

I'm really at a loss here, so i hope someone can help me.

I'm writing an application in VB and i want to search through a database on the server.
I have a table called TEST_TABLE, it has three fields:

id, name, log

"id" is a BIGINT and the primary key
"name" is a VARCHAR(128)
"log" is a MEDIUMTEXT

The table has 5000 entries.

When i perform a "SELECT * FROM TEST_TABLE" it takes almost 10 seconds to complete.

Is there any way to speed this up?

I already tried creating an index, but it doesn't seem to be possible to create an index that holds all the fields.


Greetings,
H0nger

 

[r937]

there's one really easy way to speed it up

change:

select * from ...

to:

select id, name from ...



rudy

http://r937.com/

SQL Consulting

 

[cLFlaVA]

Usually when I have larger fields (mediumtext, text, etc), I would do the following:

[tt]
Table: user
Fields: user_id int(11)
name varchar(50)

Table: text_stuff
Fields: text_id int(11)
user_id int(11)
user_text text
[/tt]

This makes running
SELECT * FROM users
a lot quicker (not that I'd ever promote the use of the *.)

Also, it will make the following more efficient:

SELECT a.user_id, a.name, b.user_text
FROM user a JOIN text_stuff b
ON a.user_id = b.user_id

Hope this helps...

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[H0nger]

Rudy,
That won't do, i need the MEDIUMTEXT field because i want to examine its contents.

But thanks anyway

Arnold

 

[H0nger]

cLFlaVA,

Thats what I would do too, but my predecessors thought it would be handy to just dump everything in one table so I'm stuck with this.

Thanks anyway,

H0nger

 

[rrsub]

After following this thread, is it better to request each field individually than * even if you use 90 to 100% of the fields?

-------------------------------------------

http://www.affordable-stock-photography.com

 

[cLFlaVA]

Yes.

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[r937]

h0nger, if you really need to examine 5000 rows of MEDIUMTEXT, then the query will, of necessity, be slow

my suggestion: use a WHERE clause to decide which of those 5000 rows you really want to examine





rudy

http://r937.com/

SQL Consulting