CODEWITHSUNDEEP
phphtmldatabase
user1301430
user1301430

Reputation: 199

Php Optimization: Fetching from database

I have some PHP code where once the next arrow is pressed it basically just gets the next image in the data base. However On mywebsite I sometimes can have over 600 users online all clicking next. Is there any way I can opimtize this php code to execute faster?

Thanks!

$nxtImage = mysql_query ("SELECT * FROM images WHERE active=1 and id>$id and section=$section ORDER BY id ASC LIMIT 1") or die (mysql_error());
$nxtrow = mysql_fetch_array($nxtImage);
$nxtnum = mysql_num_rows($nxtImage);
$nid = $nxtrow['id'];
$npicTitle = $nxtrow['img_title'];
$nimgLink = preg_replace("![^a-z0-9]+!i", "-", $npicTitle);
$nimgLink = strtolower($nimgLink);

Upvotes: 3

Views: 117

Answers (4)

Horen
Horen

Reputation: 11382

You could fetch all the images once in the beginning on the server side and then pass it to the client in Javascript (e.g. with JSON).

Then whenever a person clicks the arrow you just load the right image (e.g. with the next bigger array index) in Javascript.

This way you would eliminate a lot(maybe order by orders of magnitude) of database queries and thus improve your server performance.

Upvotes: 3

Havenard
Havenard

Reputation: 27914

Once you know your users will be often clicking "next", you can implement hashtag navigation, where the JavaScript is briefed with the parameters for the next 10, 20 images already, and performs the navigation using dynamic content, without bothering the PHP and MySQL so often.

Also, MySQL provides tunning parameters where you can increase the values of key_buffer_size and table_cache in order to tell your server to keep more information promptly available in memory for faster searches and immediate responses.

I usually tune it to use most of the unused memory on my server, because unused memory is a wasted resource.

Upvotes: 1

Tarik
Tarik

Reputation: 11209

You could add a column called nextID in your table. I know, it breaks DB normalization but in extreme cases, you may need these tricks to improve performance at the price of a field to maintain. Note that this new field could be maintained using a database trigger.

Upvotes: 0

Mike Perrenoud
Mike Perrenoud

Reputation: 67918

The first thing I would do is change the query to this : SELECT `id`, `img_title` FROM images ..., to ensure that you're only grabbing what you need. The next thing I'd do is build a covered index on id, img_title, section, active so that it doesn't have to read off the data page.

Past that, get more hardware, :D

Upvotes: 6

Related Questions