Working with large databse

Do you mean from database side or from laravel side? From database side 100.000 rows is not a big deal. So from database side everything should go well. Independent if you choose something MySQL, Postgres or anything else. From Laravel side - i am not sure, too less experience with so much data. But i am sure it can be answered from some more experienced guys here :)

thanks for answers guys

Just as @stefanbauer and @bashy said 100k is not very big at all BUT you need to make sure that your keys and indexes are assigned where they should be. If not then you could get performance problems on as little as 100k records.

Indices are a big factor, along with how you're executing your queries. I had one query that was taking 15+ seconds. With about 30 minutes of work I was able to take that down to 0.5 seconds by using two indices.

This was also really helpful - the "explain" command will tell you how MySQL is executing a query: http://dev.mysql.com/doc/refman/5.0/en/explain.html

If you outgrow a simple database solution, or need some more flexibility, have a look at the OSS project at prediction.io. It would be a separate service you'd have to run on your server, but these are the types of situations it was made for.

As well as the database schema/performance setup and depending on how the PHP is written, the hardware you use will also have a factor. I've come across high spec servers (multi cores, gigs of ram) dealing with databases with less than 50,000 rows with relatively small row data footprints just because the indexing and server side language wasn't being efficient so worth keeping that in mind too.

One of our production applications has over 20m rows (between various tables) and MySQL handles this fine, as others have mentioned indices and a well designed schema will take you a long way.

Early on we ran into memory issues with Eloquent for queries that returned a large dataset (each model has lots of overhead $attributes, $original etc). We ended up using fluent queries instead of Eloquent.

Yeah indexes help a lot, whatever is searched on my table, it's pretty much all indexed since it's returned as data.

@tappleby To prevent memory leak you can also disable query logging. http://laravel.com/docs/4.2/database#query-logging

@maximebeaudoin thanks for that link, useful when doing bulk imports.

For our use case the overhead of using a Model was enough to cause issues, normally you could solve it with pagination but that wasnt applicable in this case. We still use Models for standard C~~R~~UD operations.

You should also use mysql slow query logs to highlight the un-optimized queries. http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

@tappleby Have you tried the chunk method? Example:

User::chunk(200, function($users)
{
    foreach ($users as $user)
    {
        //
    }
});

This is what Taylor says:

If you need to process a lot (thousands) of Eloquent records, using the chunk command will allow you to do without eating all of your RAM:

Most ORMs can churn out some fairly inefficient SQL ... Depending in what you're doing of course. For example, anything that generates a long IN clause probably will probably cause a bit of gas. But the diagnostic techniques above should flush these out pretty quickly.

@bashy please can you explain your query on how to get large data from DB.

@abdullah_iftikhar Normal query, just DB had everything indexed. The data was just a few varchar columns so no longText or anything. Depends on what data you have.

@bashy Index applies on searching not getting the data from DB.

@abdullah_iftikhar

If you have a million records and you don't use indexes the query will go through each and every one of those records aka full table scan, which is bad. The correct index help the database to scan a range of those values.

Let's say that you have a order_id that is 88888 and you want to find it.

SELECT * FROM some_table WHERE order_id = 88888;

Without the index it will go through all the records in the table, with an index on the order_id it will look at the indexed values to see where the data is stored.

It might look something like this (very simplified)

• 1 - 10000
• 10001 - 20000
• 20001 - 30000

and so on.

So the database will now compare those ranges until it finds one that says 80001 - 90000.

That one is then in it's turn divided the same way

• 80001 - 81000
• 81001 - 82000

and so on so when it finds the one saying 88001 - 89000 it will choose that one and then probably divide it even further until it finds 88888.

So instead of looking at one million records it uses the index to narrow it down to maybe a few hundred records.

You are correct in the a sense that it helps with the searching and not the retrival of the data.

If you do this SELECT * FROM some_table then the index is useless since it will get everything any way.

However if you add an order by to the query the index comes in play again and will make it faster.

SELECT * FROM some_table ORDER BY some_column

Now since doing a SELECT * FROM some_table is almost never done in production code and the resultset is somehow limited you need those indexes to help you.

@abdullah_iftikhar Most of the queries you'll perform will be searching. If you have an example of indexes not working, someone can tell you what's what if you actually show some code.