MYSQL question....

[Klave]

---

Actually, I was handling data na 3T na ang gamit.

Most of the ideas here can only do so much.

Himo na lang ko checklist bro...

Dili nimo kahinanglan buhaton ang sunod if the earlier suggestion works.

Indexing
- aside from primary keys and foreign keys, fields you constantly search with such as dates, names and affiliations should also be indexed. Be careful with your indexes, you can have one index with one field or more. Depending on the use, knowing the pros and cons of using either, will be to your advantage.

Query Tweaking
Dependent with your search criteria and actual database server. Know your database because almost all of the different servers have different ways of searching data. Since, MySQL ka, with 3T worth of data, hinay na kung complex queries maski monthly queries hinay na. Though muingon sila i-normalize ang table, with each inner join you add to your query exponential ang sacrifice nimo sa performance. So onwards to the last suggestion.

Warehousing
Is a denormalized table holding redundant data, sort of like a sacrifice of space for speed. Here is one way of going at it. Usually, formatted gyud na ang reports, though lahilahi, surely naa gyud nay similarities. Find the common format and fill the table. A data warehouse can be implemented by triggers in MySQL. For every inserted or updated data, the warehouse gets the denormalized form of data. You can also implement it through using a job scheduler system, to denormalize a day's/hour's/minute's worth of data. Querying a single table is undoubtedly fast no matter how many columns it has. Be sure to index the columns you usually search with. Ang problema lang ani... if kulang lang ang needed columns...

Please correct me kung nabali nako ang denormalized (no relationships) and normalized (one to many/many to many relationships)

[johnsital]

I optimize lang maau imong script boss the way ka mag query. If mysql latest version ka supported naman ang storedprocedure mas maau kana imong gamiton kay sa program ka mag query bali imong database query naa ra sa myql db nmo wala na sa imong program script. And also importante kaau gi unsa nmo pag design imong database if unsa sad siya ka optimize.

[salbahis]

share lang ko... this is how i optimized by SQL query

as most people use "*" to load all fields, i avoid using it as much as possible, specify your fields you want to view, if you only need to view a specific field, specify it to your query... with that it would minimize the SQL load during querying since it will automatically go to specified field.

"SELECT field1,field2 FROM table"

and also if you can paginate the data, paginate it so it will not load everything......

since VB6 inyo gamit maka sugakod man noon na siya dako nga load pero sa web development especially in PHP we are obliged to optimized it as much as possible para dili mi ma abtan sa timeout!!

just my cents!!

[johnsital]

correct ka dyan Sir..

[tynum]

dugaya na ani nga thread mga bro uy.. but still this was and is still useful para nato tanan... thanks!

[salbahis]

still DB opitmization will never get old coz most developer still commit this mistakes all the time without even noticing it...

i admit i also commit this mistakes until somebody tell me, "hey, your sql code sucks, can you change it?...."

[syntax20075]

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...

bai.. gamiti lang siya'g stored procedure para mo pas.pas...

[syntax20075]

share lang ko... this is how i optimized by SQL query

as most people use "*" to load all fields, i avoid using it as much as possible, specify your fields you want to view, if you only need to view a specific field, specify it to your query... with that it would minimize the SQL load during querying since it will automatically go to specified field.

"SELECT field1,field2 FROM table"

and also if you can paginate the data, paginate it so it will not load everything......

since VB6 inyo gamit maka sugakod man noon na siya dako nga load pero sa web development especially in PHP we are obliged to optimized it as much as possible para dili mi ma abtan sa timeout!!

just my cents!!

I agree nimo bai... the nice thing to do sa imong SQL statement is imo jud e state ang mga name sa fields than using '*'.. kai ma apil man gud ang ala gamit ana... unsa lang to ang needed mao lang to nag e apil sa imong SQL Statement..