MYSQL question....

[tynum]

---

naa mi project karon dealing with keeping records of day to day transactions... sa paminaw pa lang ana kay mag ka anam jd ug ka daku imo DB as the number of records increases...

ask lang ko if mu hinay ba ang speed sa pag query if daku na kaau ang volume sa imo data sulud sa database? especially anang mga not so simple queries...

if yes... unsa man ang mga possible solutions para ma minimize ang time of query sa daku na DB?

if no..... pasalamat ta kay d na hasul.... hhehehe...

ty.........................

BTW we are using MYSQL...

[javapenguin]

MySQL akong gigamit, importante kaau ang index, pagbutang ug primary key, e configure ang my.cnf para maka maximize ka sa imo hardware sa paggamit sa index, adjust ang key_buffer_size ug kanihanlan.

Dako sad tabang ug sakto ang table type imo gigamit, ug daghan INSERT sa akong experience mas maau MyISAM, ug daghan UPDATE mas maau InnoDB, kung sa SELECT, wala ko kabantay ug asa kusog kay mo depende na sa variable settings sa config.

Sulaye sad ang mysqltuner, e google lang, kay mo hatag man siya ug recommendation, gamay ra ni siya nga perl program.

I hope this help

[maddox22]

naa mi project karon dealing with keeping records of day to day transactions... sa paminaw pa lang ana kay mag ka anam jd ug ka daku imo DB as the number of records increases...

ask lang ko if mu hinay ba ang speed sa pag query if daku na kaau ang volume sa imo data sulud sa database? especially anang mga not so simple queries...

if yes... unsa man ang mga possible solutions para ma minimize ang time of query sa daku na DB?

if no..... pasalamat ta kay d na hasul.... hhehehe...

ty.........................

BTW we are using MYSQL...

No. Dili muhinay imo database performance tungod lang kay daghan na ug records na store. Pero muhinay ang performance usually tungod sa:
1. unsay sulod - like if you're storing blob files then even if you have a few records tendency is the performance will be slow. pero depende gihapon ni. daghay ga-debate ani.
2. your business model's architecture - the way your data is laid out in the database. do you have to use joins frequently? although relational database is ideal, too much of it may slow down your application's performance. so try to balance.
3. the amount of data that you pull - (related to #1) of course smaller data means faster performance. so best is to tweak your queries to fetch only those that you need. there are a lot of persistence APIs out there (like Hibernate) that, if you're not careful, will usually fetch all associated objects that you don't really need.

In your case, since you'll be recording day to day activities, I think those records will only be string, timestamp, etc... So no need to worry.

[cold_fusion]

naa mi project karon dealing with keeping records of day to day transactions... sa paminaw pa lang ana kay mag ka anam jd ug ka daku imo DB as the number of records increases...

ask lang ko if mu hinay ba ang speed sa pag query if daku na kaau ang volume sa imo data sulud sa database? especially anang mga not so simple queries...

if yes... unsa man ang mga possible solutions para ma minimize ang time of query sa daku na DB?

if no..... pasalamat ta kay d na hasul.... hhehehe...

ty.........................

BTW we are using MYSQL...

The speed of the query depends on the data transfered to and fro on the network.
Even the data size go up but if you are displaying/transferring only part of it, it will not be critical.

There are also techniques on how make queries faster, such as paging where requesting only the data needed if it is to be displayed on one page.

Another solution is to have database server replication where client computers will connect to different sql servers thus sharing the load.

But you must not forget also to optimize your indexes and proper normalization.

[tynum]

as to what have been said by cold_fusion ug ni maddox22... mag depend ra diay sa data type sa gi query....

so even if you have let's say 10 million records sa imo DB then naa ka e query na picture( max size 200kb ) sa imo DB using a simple SELECT statement... dili mu slow down ang speed sa pag query ana? i mean ang pag search sa picture ana sa DB with its primary key kay dili na xa sequential?

[maddox22]

if that's your problem, it can be easily solved with 'indexing'. index that table's primary key or any column.

don't underestimate the capacity of a computer to perform a task such as that. it may be overwhelming to think but sisiw ra kaayo na sa computer these days. better yet, you might as well do your own testing for that. loop to about 10 million and insert records to your table. then perform a query (w/o joins usa). index your primary key then query again and compare.

just be cautious about file size when fetching data. dili man sad ingon nga muhinay, but dugay lang kay syempre dako gud.

[maddox22]

so even if you have let's say 10 million records sa imo DB then naa ka e query na picture( max size 200kb ) sa imo DB using a simple SELECT statement... dili mu slow down ang speed sa pag query ana? i mean ang pag search sa picture ana sa DB with its primary key kay dili na xa sequential?

And also by the way, sa imo scenario gihatag, dili man sad ka mu query and pangitaon ang file mismo diba? Of course you'll try to look for a file name (w/c is a string) and not the file itself (blob).

[UtOkbOlinAO]

proper indexing lang jud bro... mao man nay gamit sa index gud para mopas.pas ang performance sa imong query... try searching sa yahoo or google about sql tunning bro...

naa dinha ang imong kinahanglan mahibaw.an about tunning sql...

try reading e.books about proper designing sa database... it would help u alot...

[tynum]

tnx mga bro.... will look more into indexing ang sql tunning.... tnx...

[ben77]

good thread... got an idea for our record management and monitoring system para sa thesis...


=)