reading text or sql query?

janwil

Hello people,

I'm wondering what should be faster:
1. doing a simple select (select * from table) on a myisam table which result in 50 rows. There are several select sql queries executed before doing this sql query.

doing include("data.txt") in a php file, the data.txt contains 50 lines of text.

My first guess is 1. is faster, there is no cost of connecting to the database because there is already a connection. But thinking more about 2.; just reading a .txt files isn't that slow.

The database table which the query is run on is mostly used my select queries, sometimes there is a updated and insert query. But i guess insert and updates are about 1 to 2% of the total queries.

Thanks! 😉

dotbob

Unless you have thousands of concurrent connections, I really can't see a problem with either method.

You mentioned that you have several queries on the one page though, why? Could you not combine them using Joins?

janwil

Originally posted by dotbob
Unless you have thousands of concurrent connections, I really can't see a problem with either method.

Thanks, i guess i'll use method 1., it's much cleaner and allows better control.

You mentioned that you have several queries on the one page though, why? Could you not combine them using Joins?

I'll try to explain this with a example: i have a page with comments, these comments are placed in the comment table. I do a "select * from comment where .." to select the comments to be displayed.

Every comment is owned by a user, the users are in the users table. The comment table has a foreign key "userid" which points to a record in the user table.

One important thing is that the comment table is huge (on better days over 1 milj). The user table contains only 100 records.

My thought was: when i do a join (select * from comment left join user on comment.userid=user.id) where ..) mysql must read the whole comment table. That must be a very long task.

My solution was to:
select from comment where .. (only returns 10 rows, and explain also says 10).
select from user
Now i loop with php through the comments resultset and find with php the user which owns the comment.

Am i'm thinking wrong and should a join still be faster?

AstroTeg

select from comment where .. (only returns 10 rows, and explain also says 10).
select from user
Now i loop with php through the comments resultset and find with php the user which owns the comment.

Try SELECT [column list] FROM comments INNER JOIN users ON comments.UserID = users.UserID WHERE [comment where clause goes here to pull out the 10 records]

That should pull out just those comments you need and then link up the users.

janwil

Thank, that seems to be really fast! 🙂