mysql query limitations?

KIPAX

KIPAX

Seriously Likeable
Messages
21,426
Name
KIPAX Lancashire UK
Edit My Images
No
I ahve been setting up my own galley on my website since fotopic dissapeared .. using a free little script of the net as a base (why reinvent the wheel)

today i added counters for each pic.. however the unique entry is the folder/collection/file name so can be quite long.. i cant issue an id as an entry doesnt exist until someone clicks on the pic.

the script checks for the url and if not there it makes an entry and starts at 1.. if already there it increases alltime and daily counters..

whats the limitation of mysql.. i currently have a thousand pics.. but will it slow down at 10 thousand.. 100 thousand..

I sem to recall that mysql qould query half a million entries without batting an eye... but that was a long time ago and i dont know how right that is..
 
MYSQL will be fine, you will find the limits of your server first!

You could set up a table like this:

Unique DB generated ID : real name
100001 : image1.jpg
100002 : image2.jpg


And another referenced table like this
100001 : gallery1
100002 : gallery1
100003 : gallery3

Then you can set up paths & URL re-writes

and ask the database questions like this:

"list all the images in gallery1"
it will then tell you the information to deduce the following URL's
site/thumb/image1.jpg
site/thumb/image2.jpg
site/full/image1.jpg
site/full.jpg

You can then rewrite the URLs to contain the gallery field too etc..

I assume you are referencing the image as image xyz.jpg in a folder with path xx/x/x/blah

Most scripts that are well written deal with most of the URL writing and paths in a config file. You dont want to be storing the full real URL in the DB, its ineeficent. By referencing it, you can then crossreference the reference

like

sales for image 10001
rating for image 10001
exif for image 10001
hits for image 10001

etc.
 
the images arnt in a database.. the script just reads them from a folder... the only reason they are in a database is for the counters..

so if i uplaod a folder of pics the gallery software just works.. but the database is empty.. thus the only unique information is the entire url.. well not entire.. just the 2 folders then filename

if the software was getting info for the gallery from mysql then it would be easy to add counters.. but no...

I dont have a size limitation on my mysql databases other than my account disk limitation which is massive
 
KIPAX said:
the images arnt in a database.. the script just reads them from a folder... the only reason they are in a database is for the counters..

so if i uplaod a folde rof pics the gallery software just works.. but the database is empty.. thus the only unique information is the entire url.. well not entire.. just the 2 folders then filename

if the software was getting info for the gallery from mysql then it would be easy to add counters.. but no...
Click to expand...

If you are saying you have a script that reads through a folder and looks for images every time it sends a page out, then that is incredibly innefficent. Its fine for 10 image slideshows, but would be a nightmare for large scale projects

If you are saying the script has a "flat text" database, then ammend the script to utilise that. For the volume of images you are dealing with, you still need a grown up soloution

Double check: Gallery2 + Gallery remote
 
no the script doesnt have any database. flat mysql or otherwise. it simply reads in folders
 
Richard King said:
The script will come to a grinding halt before MYSQL does
Click to expand...

would help if you said why?

the script doesnt get bigger or do more work with more galleries added.. it does the same amount of work wiht one collection or a hundred..

the grinding to a halt would surely depend on amount of users not mount of pics?
 
KIPAX said:
would help if you said why?

the script doesnt get bigger or do more work with more galleries added.. it does the same amount of work wiht one collection or a hundred..

the grinding to a halt would surely depend on amount of users not mount of pics?
Click to expand...

Think what the script does

Think of it as a box with a set of instructions in it

In this case the set of instructions says

"go to the folder, and find all or some of the images, based on the criteria you set out"

If you are dealing with 10 images, its not a big job, if you are dealing with 1000's for every page refresh, it will be going and finding all of the images. I.e. the script is working hard

That is the script looks at all the images for all the users for every page request. Getting a large file list from a server is harder work on the server than querying the DB

Think of the alternative

"send a query to a database, and then do something with the result"... Instead of dealing with 1000's of images, the database organises the corect images each time. MySQL is very good at this sort of query, and when you have an intellegent field structure set up in the database, it can output the results of queries even faster. That leaves your server code (PHP or ASP) comparitivley doing very little per request, and the database engine doing what is designed to do

A step further is that you can make the database cache comon requests, so it doesnt have to work hard either
 
Last edited:
Richard King said:
Think what the script does

If you are dealing with 10 images, its not a big job, if you are dealing with
Click to expand...

it only grabs 16 at a time ..it counts how many files are in there every time... ie 120 at maximum...

I know the alternative of a mysql database driven gallery is better but i havent time to write my own from scratch

i tried gallery and a couple of others and they where all pants... gallery wouldnt for the life of me use a leveleld system and just wanted to show all thumbnails on the first page.. 2 days later i binned it :(
 
I'm trying to follow this thread, but not really sure what you're currently asking. MySQL should have no problems when querying tables containing millions of records as long as the tables have up to date stats, have been analysed, and your query is efficient i.e. it uses indices where possible rather than full table scans.

So, are you creating individual text files for each image containing stats? So, every view of a new page/image creates a new counter file and every subsequent view updates that counter file? If so, then what happens when an image/page is served to multiple concurrent users? Aren't you risking contention over file writing and possibly inaccurate stats?
 
DigitalRelish said:
I'm trying to follow this thread, but not really sure what you're currently asking.
Click to expand...

i think your loking for a hard question :)

DigitalRelish said:
MySQL should have no problems when querying tables containing millions of records as long as the tables have up to date stats, have been analysed, and your query is efficient i.e. it uses indices where possible rather than full table scans.
Click to expand...

and theres my answer :)


DigitalRelish said:
So, are you creating individual text files for each image containing stats?
Click to expand...


good grief no.. I am using mysql..no text files at all.. just jpgs in folders :)
 
Okay, I understand a bit better now :D

As long as you select and update via an indexed column then you shouldn't have any worries :thumbs:
 
You must log in or register to reply here.
Back
Top