Getting queries to work with mysql will help you gain confidence that you can select and output the data you had wanted. But, as time goes on and the demands to pull data with relational databases or the need to build applications with the need for speed is a factor, it will become obvious that output is only one factor while query optimization is another.
Fortunately, there are a few simple rules that can be followed to ensure you write better, optimized mysql queries. Three very important rules to use are; select only what you need, use indexes to match columns in various tables and matching columns should have the same structure.
Select What You Need
Selecting what you need should be fairly obvious. For example, select * takes rows from all columns and selecting unused columns adds time to a query. If you just need to select and id from a column in a table that has id, firstname, lastname, email and so forth, you are better off to just select that id in the first place.
Using Indexes
Using indexes on columns which are used in relational queries can create results in a fraction of time, especially if there are hundreds or thousands of rows of data. If you do not use indexes when writing relational queries with 2-4 tables, you could wait, wait and wait for the page to load. We could be taking up to a minute in serious cases while same data using indexes loads in less than a few seconds. So, the next time you are using where statements to match colums on various tables, those matching columns should be indexed. You can add an index with mysql or phpmyadmin.
1) Select the database table
2) Select Structure
3)Next to the desired column and under action, select ‘More’ next to the column
4) Click Add Index
5) You will see the list of indexes under the subheading indexes and it will include the newly made index on the column for which you made an index.
To add an index with the command line,
1) alter table `tablename` add index index_name (columnname);
Matching Columns
Finally, to keep speed as a priority, your columns structures should match. For example, if you have an indexed column called members_id in one column and mymembers_id in another column that hold the same values to define a specific member, it is better to have them with proper structure and exact structure values; such as int(3). If you never plan to have more than 999 members, then int(3) would be about as good as it could get. If one column type was varchar(3) and the other type was int(3) the query would run slower; with all other things equal.
Owner of Fullstack Web Studio
Quality Coding
Digital Marketing
When Performance and Flexibility Matters
Affordable web design,custom web development and mobile apps for individuals and businesses.
Let us analyze your data and competition to acquire more traffic! Professional SEO content writer
© 2018 ALL Rights Reserved | Sitemap
Vancouver | North Vancouver | Burnaby | Richmond | Coquitlam | Port Coquitlam | Surrey | Langley | Maple Ridge | Mission | Abbotsford | Kamloops | Kelowna | Victoria | Squamish | Whistler | Pemberton
