freel
127
10
Eloquent

Database tables, migration, indexes

Posted 3 years ago by freel

Hello team, I am trying to create portal where customer will be able to advertise cars. For debugging I am using laravel-debugbar When I imported fake 3200 ads - page where I am displaying list and filters were loading around 2.6s laravel-debugbar showing that am using Queries142 Well almost all these queries are When I imported fake 4200 ads got HTTP ERROR 500. Is it normal on localhost?

Well I think that Its not. Trying to understand what is wrong and what i need to change to improve all of that.

When I am selecting make with 120 results response getting after 1.5s and only 1 quiery.

My ads table are huge and there is no indexes (added them manually using phpmyadmin no big difrience). Maybe I need to break in peaces this database?

// Create table storing ads
        Schema::create('ads', function (Blueprint $table) {
            $table->increments('id');
            $table->string('plate_number');

            $table->integer('user_id')->unsigned();
            $table->foreign('user_id')->references('id')->on('users')
                ->onUpdate('cascade')->onDelete('cascade');

            $table->integer('make_id');
            $table->integer('models_id');
            $table->integer('color_id');
            $table->integer('fuel_id');
            $table->dateTime('yearOfManufacture');
            $table->integer('cylinder_id');
            $table->integer('emission_id');
            $table->integer('wheels_id');
            $table->integer('weight_id');
            $table->integer('mileage');
            $table->decimal('price', 8, 2);
            $table->string('vin');
            $table->integer('transmission_id');
            $table->integer('typeApproval_id');
            $table->integer('numberOfDoors');
            $table->integer('bodytype_id');

            $table->string('sixMonthRate');
            $table->string('twelveMonthRate');
            $table->string('taxDetails');
            $table->string('motDetails');
            $table->string('post_code');
            $table->decimal('lat');
            $table->decimal('lon');
            $table->integer('interiorcolor_id');
            $table->string('phone');
            $table->string('email');

            $table->string('video');

            $table->text('comments');

            $table->integer('status');
            $table->integer('auto_category');
            $table->integer('rules');
            $table->integer('paid');
            $table->integer('checked');
            $table->timestamps();

        });

Controller method where I am filtering results.

// Function Index search
        public function Index($id, Request $request){

            if (!empty(Input::get('perpage'))){
                $perpage = Input::get('perpage');

            }else{
                $perpage=10;
            }


            $ads = Ad::where('status',1)->where('auto_category',$id);


           //Makes

            if (!empty(Input::get('make'))){
                $ads = $ads->where('make_id',(int)Input::get('make'));
                $makes = Make::find((int)Input::get('make'));
            }else{
                $makes = Make::all()->sortBy('make_name');
            }

            //Models
            if (!empty(Input::get('model'))){
                $ids = Models::all();
                $ids =$ids->where('model_title',Input::get('model'))->toArray();
                $ids  = array_pluck($ids,'id');
                $ads= $ads->whereIn('models_id',$ids);
                $models = Models::where('model_title',Input::get('model'))->groupBy('model_title')->first();
            }else{
                if (!empty(Input::get('make'))){
                    $models = Models::where('make_id',(int)Input::get('make'))->groupBy('model_title')->get();

                }else {
                    $models = 0;
                }

            }

            //years
            if (!empty(Input::get('year_from'))){
                //dd(Carbon::create(Input::get('year_from'), 1, 1, 0));
                $ads = $ads->where('yearOfManufacture','>=',Carbon::createFromFormat('Y-m-d H', (Input::get('year_from')).'-1-1 0')->toDateTimeString());

            }

            if (!empty(Input::get('year_to'))){

                $ads = $ads->where('yearOfManufacture','<=',Carbon::createFromFormat('Y-m-d H', (Input::get('year_to')+1).'-1-1 0')->toDateTimeString());
            }

            //body
            if (!empty(Input::get('bodytype'))){
                $ids = Bodytype::all();
                $ids =$ids->where('bodytype_name',Input::get('bodytype'))->toArray();
                $ids  = array_pluck($ids,'id');

                //dd($ids);
                $ads = $ads->whereIn('bodytype_id',$ids);
            }else{

                $bodytypes = Bodytype::all();

            }

            //colors
            if (!empty(Input::get('color'))){
                $ids = Color::all();
                $ids =$ids->where('color_name',Input::get('color'))->toArray();
                $ids  = array_pluck($ids,'id');

                //dd($ids);
                $ads = $ads->whereIn('color_id',$ids);
            }else{

                $colors = Color::groupBy('color_name')->get();

            }

            // transmission


            if (!empty(Input::get('transmission'))){
                $ids = Transmission::all();
                $ids =$ids->where('transmission_name',Input::get('transmission'))->toArray();
                $ids  = array_pluck($ids,'id');

                //dd($ids);
                $ads = $ads->whereIn('transmission_id',$ids);
            }else{

                $transmissions = Transmission::all();

            }

            //Price
            if (!empty(Input::get('min_price'))){

                $ads = $ads->where('price','>=',Input::get('min_price'));
            }

            if (!empty(Input::get('max_price'))){

                $ads = $ads->where('price','<=',Input::get('max_price'));
            }

            //milage
            if (!empty(Input::get('min_mileage'))){

                $ads = $ads->where('mileage','>=',Input::get('min_mileage'));
            }

            if (!empty(Input::get('max_mileage'))){

                $ads = $ads->where('mileage','<=',Input::get('max_mileage'));
            }

            if (!empty(Input::get('sortbyprice'))){

                $ads = $ads->orderby('price',Input::get('sortbyprice'));
            }elseif (!empty(Input::get('sortbymileage'))){
                $ads = $ads->orderby('mileage',Input::get('sortbymileage'));
            }else{
                $ads=$ads->orderBy('created_at', 'desc');
            }
            $fullads = $ads->get();
            $ads = $ads->paginate($perpage);


            return view('search.index',compact('ads','makes','models','fullads','colors', 'transmissions','bodytypes'));
        }

Here is the small part from my view

<div class="accordion-group panel">
                <div class="accordion-heading togglize"> <a class="accordion-toggle" data-toggle="collapse" data-parent="#" href="#collapseTwo" >Make<i class="fa fa-angle-down"></i> </a> </div>
                <div id="collapseTwo" class="accordion-body collapse">
                    <div class="accordion-inner">
                        <ul class="filter-options-list list-group">

                            @if (count($makes) == 1)
                                    <li class="list-group-item"><span class="badge">{{$makes->ads->count()}}</span><a href="{{Request::fullUrlWithQuery(array('make' => ''))}}">{{$makes->make_name}} <i class="fa fa-times" aria-hidden="true"></i></a></li>
                            @else
                                @foreach ($makes as $make)
                                    <li class="list-group-item"><span class="badge">{{$make->ads->count()}}</span><a href="{{Request::fullUrlWithQuery(array('make' => $make->id))}}">{{$make->make_name}}</a></li>
                                @endforeach
                            @endif
                        </ul>
                    </div>
                </div>
            </div>

Well As I understand it will break if I will not change how it is done. I am waiting for tips what to improve and change. Thank you very much.

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.