How to speed up the database process?

Question

I have a MySQL database that contains more than 9000 rows with 10 fields. It was imported from an Excel file, so that is all in one table.

When I run a query that has already narrowed down the selection, I return 7,000 rows to the Codeigniter view page. The browser freezes for 30 seconds and asks me to kill the page because there was no answer. If I wait, I will see that the result appears in the end. I was wondering if there are ways to improve this?

Answer 1

Do you fulfill your requests inside any cycle?

Accept page responses, use constraints and offsets. If you run a 10-page page, then 700 queries. I would use codeigniter pagination lib as follows.

$route['controller/(:num)'] = 'controller/index/$1'; 

-

 public function index($offset=0) { //set a limit of 10 per result $limit = 10; //query the database $q = "SELECT * FROM {table_name} LIMIT={limit} OFFSET={offset} ORDER BY {date} desc"; //count the results $count = count({query results}); //setup pagination config $config = array( 'base_url' => site_url('controller/'), 'total_rows' => $count, 'per_page' => $limit, 'uri_segment' => 2 ); //init the pagigination $this->pagination->initialize($config); //load the view and pagination data $this->load->view('link_to_template', array( 'pagination' => $this->pagination->create_links(), 'results' => {query results} )); } 

Answer 2

Ideally, come up with a better query or offer your users the best search terms. If they ultimately need to look at all 7000 lines, you need some kind of paging. One option is to swap AJAX, as described in this article . Basically, you have a new query for each row group, but the user remains on one page.

Answer 3

Start by reading about the 'limit' in sql chooses to paginate your output.

Answer 4

Add indexes (indices) for specific columns; if the columns are text, then add FULLTEXT indexes