I'm on my internship right now, working on Library Management System and thinking about how data will load fast in server-side, it took almost a minute even I used DataTables. It is JUST a 22K+ of library records. Is my only knowledge is just PHP, MYSQL, JS... caused this problem? Would MVC help, i'm only have 200 hours to finish the project but no knowledge yet in MVC

Doubtful, you’ll probably find the SQL queries arn’t optimized enough to work with that data set.

Add EXPLAIN to your queries to get an idea of what it’s doing and where it’s taking too long to collect data via joins or sub selects or unindexed fields you’re using in the where clauses.

I work with 1mill+ records in tables and can return data under 10 seconds without issues.

Where does it take time, fetchinh from db, waiting for db to respond, writing it to client or rendering it on client.

If you render 22k+ item as html table it will take a long time for the browser to receive and render.

If the db takes time to respond its probably index and if its serverside processing you probably will have to split it up in several batches and call through ajax.

@Voxera planning to do that. thanks to your response!

@C0D4 a wow factor, 1 million?! i will try that!

@urhaynes good index makes seaches fast but if you try to fetch to much it gets slow.

1 million is not very much for an sqlservever, even my record is not the highest I have heard about from friends and one of our tables is over 1.7 billion lines of log data :/

@Voxera i use xampp as sqlserver, what's your buddy?

@urhaynes sqlserver enterprise in a cluster.

But sqlserver standard could handle it to.

Xampp uses mysql? Should handle a couple of 10’s of millions of rows without problem unless the server is weak.

Are you trying t fetch many or just a few rows?

@Voxera i only want to fetch what's the only material type in the library, for example there's reprints , that's only i want but that is 22K+ of data. i try to fetch 10 rows, then pagination do the rest. but the loading time sucks

@urhaynes then I guess its an index problem. Without a matching index the server has to read every row to find this. With a good index it might need to read 20 rows from the index.

But designing an index requires knowledge of what data the table contains and how its used, multiple indexes can match more cases but cost performance when adding items.

@Voxera thanks very much, i use SELECT with WHERE statement only

@urhaynes thats the query, index is an addition to the table.

Search for tutorials for index in mysql, its probably THE most important part in database performance.

And without them you will never get good performance out of an sql db over a few thousand rows.

A good index on a large db can make the difference between sub second response and over a minute run time.

@Voxera hello, should i define index of column? like this 0 => 'id', 1 => 'name'?

@urhaynes usually you fo not need to include the id column, its implicitly included but most importantly, you create it in the columns you need to search on, one or more columns.

If you need to search on category create an index for that and if you need category and author you can create one with both or two, one with each.

The later will be a little less efficient but more versatile and could help more diverse searches.

This is the reason you should know how the table is used, not just add indexes for every case.

You could end up with to many index that would cause a huge performance impact every time you add, delete or update a row.

If the table is static, as in no changes, you could add more indexes.

But also remember that every index takes memory and space so if the machine cannot hold all indexes in memory you could still lose performance with to many indexes.

But read up a few tutorials, there are countless books written on this and I am not an expert on mysql and even with mssql I do not know everything ;)

@Voxera super thanks, i got you. right now the only maximum of loading of 22K data is 30 secs. sometimes it is less than 10 secs. I will search more ways, it took my interest on how the data are processing in the web, really really appreciate your response!

@urhaynes good luck and if you only need to show a handful of lines you should be able to get it to do that in a fraction if a second.