Query time for large MySQL table

[akomaz]

Hi all,

 

To preface my question I am a rookie in the PHP area, so forgive my incompetence. 

 

I am performing a query on a MySQL table and displaying the fields in a HTML table supported by DataTables, and due to the number of records I have stored in my table (5000+ rows) i have a big problem with the initial load time.  I am using caching methods to enhance navigation beyond the initial load, however I still need a solution for reeling in the carp. 

 

I wanted to use the js datatables plugin to allow users quick access to paginated data, but if the load time hogs their memory and delays first glance...obviously I need to go a different route.  Any suggestions?

 

Thanks!

[Psycho]

5000 records does not seem terribly large. I would suspect either the database server is not adequate or the query is not efficient.

 

I surely hope you are not running queries in a loop.

[mrMarcus]

you can (if you haven't already), create indexes in your database.

 

start by creating an index on the fields that are most commonly used by the query, ie:

 

say you have a table with 5 fields .. id, name, location, dob, ip (very random fields for example).

 

now, the query in question only needs to pull records/data from the fields id, name, location.  you can create an index of those 3 fields, so each time that query is run, it's not passing through all those other fields that it does not need to grab information from.

 

it sounds so much better in my head, so i apologize if i did not explain this as well as i should/could have.

 

doing queries of "SELECT * FROM" don't help either .. unless you need *everything* from that table, don't ask for it.  instead, only select the fields that you need, "SELECT id, name, etc. FROM".

[Mchl]

Are you sure it's MySQL query that takes so much time? You know, creating a table with 5000 entries is not an easy task for browser.

 

Anyway, try analyzing your query using EXPLAIN. See if it uses indexes in the first place.

[mrMarcus]

Are you sure it's MySQL query that takes so much time? You know, creating a table with 5000 entries is not an easy task for browser.

i second that.

 

implement a pagination system if that's the case.

[akomaz]

Thanks for everyones input, very helpful.

 

Okay since my table does have many columns I am only selecting the relevant ones that I want to display, not everything.  Although I am using a basic loop to go through each row and extract the values, it is set to terminate when there are no more values.  So I am assuming the loop would only be bad if it is infinite right?

 

You are right about the browser, it is a large table.  I will definately start using indexing....but it seems that is should still load faster than say 18 seconds on your average developers pc right?  How would one go about circumnavigating the browser inefficiency?  Since I am using the datatables plugin, these values are automatically paginated after the initial load...

 

 

[mrMarcus]

how many rows are you displaying in the outputted table?

[akomaz]

the table is set to display a default of 10 rows on the page, the rest is cached

[Psycho]

Although I am using a basic loop to go through each row and extract the values, it is set to terminate when there are no more values.  So I am assuming the loop would only be bad if it is infinite right?

 

I was not referring to the looping process to extract the data from the result set. One mistake I see a lot is where people do not know how to do a JOIN and instead query the parent table and then loop through each record to do a subsequent query on a child table. These "looping queries" are incredibly inefficient and will generate much longer load times.