On 2011-10-24, at 11:14 PM, Jason Pruim <lists@xxxxxxxxxxxxxxxxxxxx> wrote: > > Jason Pruim > lists@xxxxxxxxxxxxxxxxxxxx > > > > On Oct 24, 2011, at 9:20 PM, Bastien wrote: > >> >> >> On 2011-10-24, at 8:50 PM, Jason Pruim <lists@xxxxxxxxxxxxxxxxxxxx> wrote: >> >>> Now that I've managed to list 3 separate programming languages and somewhat tie it back into php here's the question... >>> >>> I have about 89 million records in mysql... the initial load of the page takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL query's... But they just aren't going fast enough... >>> >>> What I would like to do, is pull the data out of MySQL and store it in the HTML files, and then update the HTML files once a day/week/month... I can figure most of it out... BUT... How do I automatically link to the individual pages? >>> >>> I have the site working when you pull it from MySQL... Just the load time sucks... Any suggestions on where I can pull some more info from? :) >>> >>> Thanks in advance! >>> >>> >>> Jason Pruim >>> lists@xxxxxxxxxxxxxxxxxxxx >>> >>> >>> >>> >>> -- >>> PHP General Mailing List (http://www.php.net/) >>> To unsubscribe, visit: http://www.php.net/unsub.php >>> >> >> That's a ton of data. So there are a couple of questions: >> >> 1. Is the data ordered in any way? > > Data is in numeric order in the database, but not specifing a ORDER BY on the SELECT.. >> >> The issue is that you might need to regenerate the files if new data needs to be interspersed. > > Indexes are freshly done... I might need to tweak them and learn more about them... Working on that right now! >> >> 2. Why is pagination slow? > > Pagination isn't all that slow... Just the initial load of the site... The rest of it works fairly well... But the guys with the paychecks aren't going for a 2-3 minute initial load time on a public website >> >> A LIMIT with an OFFSET should be very quick if the table is properly indexed. Is there any tuning that you can do to further filter the results? Say by date or some other criteria? Have you run an EXPLAIN plan on the db to show how the queries are being run? >> >> Other thoughts: >> >> - are your indexes up to date? Have you optimized those index to be clean, not fragmented, and therefore fast? >> > > Just generated the indexes so it should be completely perfect. > >> - can you organize the data by some criteria to better show the results? Like by date? Or by starting alpha character? Or by category? > > Trying to split up the data some... It's phone numbers... So I have the area code, exchange, and the subscriber number stored in 3 separate fields... But that doesn't seem any faster then when I had it all in one field.... > > I think I just need to rethink the design a little bit, and optimize the entire app... But I think that will hit tomorrow. > > Thanks for your suggestions! >> >> Bastien Koert >> 905-904-0334 >> >> >> >> >> >> -- >> PHP General Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> > My preference would be to have that as one numeric field ( bigint ) and search on that. DBs like numbers and should be able to return the data very quickly As for the app start, can you present a tiny ( say 10 rows ) subset that you could read in from a cache? Perhaps the 10 most common searched on or last 10 used? You could cache it for an hour and then refresh it. Same thing applies if the start is like a dashboard with various reports. Cache out what you can and hit the DB for the bare minimum to keep people happy Then your app should be right quick to start Bastien Koert 905-904-0334 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
