Faster querying on a large database.

koonie76

I have been thrown into a task to make a PHP-MySQL database query and display quicker.

I have several ideas on what I would like to do, but I don't know if I can do them or how to do them.

Could I load the page with maybe 10 results and then continue the query in the backgroud? And then use pagination to get to the rest of the results.
How could I speed up a query? In general, what are good and bad practices when doing a query on a large database?

I think that I have the basics down, but I'm not necessarily good at making the php work smoothly. It just generally works. So any and all advice is welcome.

I'll take any other ideas as well.

Thanks,
Brian

TheDefender

Hey Brian,
Without seeing your current code, it'd be difficult to really try to determine the best ways to improve on what you've got.

The MySQL manual has info pertaining to how to optimize your queries. Also, you may want to look up info on Database normalisation, and best practices for indexing your tables's keys. Again, without your specifics, it'd be tough to tell you "This is what you can do..."

sneakyimp

Making sure that you create the appropriate INDEXES on your database tables is very helpful. In particular, if you are connecting two tables to each other like this:

TABLE_1
id

TABLE_2
id
field_x
field_y

SELECT t1.*, t2.field_x, t2.field_y FROM TABLE_1 t1, TABLE_2 t2
WHERE t2.field_x = t1.id

Then you should make sure that the field 'id' in TABLE_1 and field_x in TABLE_2 are indexed.

koonie76

Thanks,

That's really helpful. I'm just getting into this stuff so little tips like that are really helpful because I just know how to make things work, but not necessarily work good.

Brian