Phptalk

50 %
50 %
Information about Phptalk
Technology

Published on September 9, 2008

Author: shantanuo

Source: slideshare.net

Description

tips for php programmers
http://phpcamp.org

PHP tips by a MYSQL DBA

PHP tips by a MYSQL DBA

Signing query Sign your queries... select * from mytable; SeLeCt * from mytable; Or use comments in the query like... select /* query by shantanu login.php file */ name, age from customers This helps while watching process-list and slow query logs No line breaks in a query and space after , of column name

Sign your queries... select * from mytable; SeLeCt * from mytable;

Or use comments in the query like... select /* query by shantanu login.php file */ name, age from customers

This helps while watching process-list and slow query logs

No line breaks in a query and space after , of column name

Output Query echo the query and make it hidden Display style none as shown below: <span style=&quot;display:none&quot;> <?php echo $qrysearch ?> </span> Anyone can go to View – Source and find the query This will be useful for debugging. Remove the code before taking the code to production server.

echo the query and make it hidden

Display style none as shown below:

<span style=&quot;display:none&quot;> <?php echo $qrysearch ?> </span>

Anyone can go to View – Source and find the query

This will be useful for debugging. Remove the code before taking the code to production server.

MySQL command history [root@databaseserver215 ~]# tail .mysql_history show processlist; show slave status; stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; show slave status; show variables like '%innod%'; show slave status;

[root@databaseserver215 ~]# tail .mysql_history

show processlist; show slave status; stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; show slave status; show variables like '%innod%'; show slave status;

Mysqld --print-defaults [root@localhost mysql]# mysqld --print-defaults --port=3306 --socket=/var/lib/mysql/mysql.sock --skip-external-locking --log-bin=/var/log/mysql/binary/mysql-bin.log --binlog-ignore-db=mysql --binlog-ignore-db=test --server-id=4 --expire_logs_days=15 --max_binlog_size=1024M --report-host=172.29.120.121 --relay-log=/var/log/mysql/binary/cluster1-relay-bin --sync_binlog=1 --group_concat_max_len=500000 --innodb_data_home_dir= --innodb_data_file_path=ibdata1:2800M;ibdata2:2800M:autoextend --innodb_buffer_pool_size=4G --innodb_additional_mem_pool_size=1M --innodb_log_files_in_group=2 --innodb_log_file_size=1G --innodb_log_buffer_size=8M --innodb_flush_log_at_trx_commit=1 --innodb_lock_wait_timeout=50 --log_slow_queries=/var/log/mysql/mysql-slow.log --long_query_time=2 --skip-name-resolve --max_connections=400 --max_user_connections=1600 --max_connect_errors=50 --wait_timeout=1200 --connect_timeout=5 --interactive_timeout=120 --join_buffer_size=1M --read_buffer_size=4M --read_rnd_buffer_size=16M --table_cache=512 --max_allowed_packet=4M --key_buffer_size=6144M --sort_buffer_size=2M --myisam_sort_buffer_size=64M --thread_cache=128 --thread_concurrency=8 --thread_cache_size=40 --thread_stack=128K --low_priority_updates=1 --query_cache_limit=16M --query_cache_min_res_unit=1 --query_cache_size=500M --query_cache_type=1 --skip-bdb --group_concat_max_len=3072 --ft_min_word_len=1 --ft_stopword_file=/etc/stopword.txt

[root@localhost mysql]# mysqld --print-defaults

--port=3306 --socket=/var/lib/mysql/mysql.sock --skip-external-locking --log-bin=/var/log/mysql/binary/mysql-bin.log --binlog-ignore-db=mysql --binlog-ignore-db=test --server-id=4 --expire_logs_days=15 --max_binlog_size=1024M --report-host=172.29.120.121 --relay-log=/var/log/mysql/binary/cluster1-relay-bin --sync_binlog=1 --group_concat_max_len=500000 --innodb_data_home_dir= --innodb_data_file_path=ibdata1:2800M;ibdata2:2800M:autoextend --innodb_buffer_pool_size=4G --innodb_additional_mem_pool_size=1M --innodb_log_files_in_group=2 --innodb_log_file_size=1G --innodb_log_buffer_size=8M --innodb_flush_log_at_trx_commit=1 --innodb_lock_wait_timeout=50 --log_slow_queries=/var/log/mysql/mysql-slow.log --long_query_time=2 --skip-name-resolve --max_connections=400 --max_user_connections=1600 --max_connect_errors=50 --wait_timeout=1200 --connect_timeout=5 --interactive_timeout=120 --join_buffer_size=1M --read_buffer_size=4M --read_rnd_buffer_size=16M --table_cache=512 --max_allowed_packet=4M --key_buffer_size=6144M --sort_buffer_size=2M --myisam_sort_buffer_size=64M --thread_cache=128 --thread_concurrency=8 --thread_cache_size=40 --thread_stack=128K --low_priority_updates=1 --query_cache_limit=16M --query_cache_min_res_unit=1 --query_cache_size=500M --query_cache_type=1 --skip-bdb --group_concat_max_len=3072 --ft_min_word_len=1 --ft_stopword_file=/etc/stopword.txt

Use Cron If you want to delete rows older than current date use crontab -e command to edit the crontab file. Make sure that the mysql command can be executed at command prompt. # remove records earlier than current date 1 0 * * * mysql -h 172.219.0.1 -uroot -e'delete from testdb.test_tb where d < current_date()'

If you want to delete rows older than current date use crontab -e command to edit the crontab file. Make sure that the mysql command can be executed at command prompt.

# remove records earlier than current date

1 0 * * * mysql -h 172.219.0.1 -uroot -e'delete from testdb.test_tb where d < current_date()'

Using Log files MySQL can generate different log files like... General, slow, binlog and error Apache error log files also contain some valuable information.

MySQL can generate different log files like...

General, slow, binlog and error

Apache error log files also contain some valuable information.

Error log grep -n &quot;File&quot; -v dataentry_error_log | tail cat -n iro_error_log | tail -300 | more

grep -n &quot;File&quot; -v dataentry_error_log | tail

cat -n iro_error_log | tail -300 | more

Binlog I Binlogs are enabled in order to send insert /update queries to the slave. But you can enable binlogs even if the server is not part of the replication. This log will automatically store all the important statements along with it's date-time and originating IP address.

Binlogs are enabled in order to send insert /update queries to the slave.

But you can enable binlogs even if the server is not part of the replication.

This log will automatically store all the important statements along with it's date-time and originating IP address.

Binlog II mysqlbinlog --stop-datetime=&quot;2008-05-01 00:00:00&quot; mysql-bin.000005 >> prit1.txt grep -i 'PA003111' prit1.txt | sort | uniq > sendprit.txt mysqlbinlog –start-datetime=&quot;`date +%Y-%m-%d' '%H:00:00 -d&quot;1 hour ago&quot;`&quot; --stop-datetime=&quot;`date +%Y-%m-%d' '%H:00:00`&quot; mysql-bin.* | awk '/tbl_company_master/,/;/' | replace tbl_company_master new.tbl_company_master | more

mysqlbinlog --stop-datetime=&quot;2008-05-01 00:00:00&quot; mysql-bin.000005 >> prit1.txt

grep -i 'PA003111' prit1.txt | sort | uniq > sendprit.txt

mysqlbinlog –start-datetime=&quot;`date +%Y-%m-%d' '%H:00:00 -d&quot;1 hour ago&quot;`&quot; --stop-datetime=&quot;`date +%Y-%m-%d' '%H:00:00`&quot; mysql-bin.* | awk '/tbl_company_master/,/;/' | replace tbl_company_master new.tbl_company_master | more

Slow-query-log mysqldumpslow You can save the first 1000 expensive queries into a separate file using built-in mysqldumpslow utility. mysqldumpslow /var/log/mysql/mysql-slow.log | head -1000 > slowextract.txt sort by count instead of time (default) and show actual values of integers and text instead of N and S mysqldumpslow mysql-slow-demaster.log -s c -a > extract.txt

You can save the first 1000 expensive queries into a separate file using built-in mysqldumpslow utility.

mysqldumpslow /var/log/mysql/mysql-slow.log | head -1000 > slowextract.txt

sort by count instead of time (default) and show actual values of integers and text instead of N and S

mysqldumpslow mysql-slow-demaster.log -s c -a > extract.txt

Slow-query log - I # Time: 080602 11:37:50 # User@Host: root[root] @ [172.29.0.12] # Query_time: 103 Lock_time: 0 Rows_sent: 82 Rows_examined: 1060213 use d_Jd_ClientFB; Select dosdate, count(*) as SmsCount, operator as Operator from ClientFeedback_backup where SMSSent = 'Y' and CompanyMobile is not null group by dosDate, operator;

# Time: 080602 11:37:50

# User@Host: root[root] @ [172.29.0.12]

# Query_time: 103 Lock_time: 0 Rows_sent: 82 Rows_examined: 1060213

use d_Jd_ClientFB;

Select dosdate, count(*) as SmsCount, operator as Operator from ClientFeedback_backup where SMSSent = 'Y' and CompanyMobile is not null group by dosDate, operator;

Slow-query log II The explain plan shows as below: id: 1 select_type: SIMPLE table: ClientFeedback_backup type: ref possible_keys: SentIndex,CompanyMobileIndex key: SentIndex key_len: 2 ref: const rows: 434148 Extra: Using where; Using temporary; Using filesort

Slow-query log III The index being used is on Sent column which is of low cardinality. It means only 'Y' and 'N' values are stored in this column and it is as good as using no index at all. Because Index column looks for unique values and it did not find such values in this column. A composite index on dosDate, operator in that order is necessary.

The index being used is on Sent column which is of low cardinality. It means only 'Y' and 'N' values are stored in this column and it is as good as using no index at all. Because Index column looks for unique values and it did not find such values in this column. A composite index on dosDate, operator in that order is necessary.

General Log General logs can become very big in a very short time since they have all the selects along with update/ delete Worth enabling on test servers where you are testing your PHP code

General logs can become very big in a very short time since they have all the selects along with update/ delete

Worth enabling on test servers where you are testing your PHP code

Low and High Priority The HIGH_PRIORITY hint can be used on SELECT or INSERT statements to let MySQL know that this is a high priority query. This hint will basically allow the query to skip in line. The LOW_PRIORITY hint can be used on INSERT and UPDATE statements. If you use the LOW_PRIORITY keyword, execution of the query is delayed until no other clients are reading from the table. This means that you may wait a LONG time, or forever on servers with a heavy read volume. insert HIGH PRIORITY into logappointment (emailid, appflag, date1, callerid, tel, mob) values ( 'lucknow@hotmail.com ', 'Y', now(), '2191361', '22579950', '9415023611');

The HIGH_PRIORITY hint can be used on SELECT or INSERT statements to let MySQL know that this is a high priority query. This hint will basically allow the query to skip in line.

The LOW_PRIORITY hint can be used on INSERT and UPDATE statements. If you use the LOW_PRIORITY keyword, execution of the query is delayed until no other clients are reading from the table. This means that you may wait a LONG time, or forever on servers with a heavy read volume.

insert HIGH PRIORITY into logappointment (emailid, appflag, date1, callerid, tel, mob) values ( 'lucknow@hotmail.com ', 'Y', now(), '2191361', '22579950', '9415023611');

sql_big_results The SQL_BIG_RESULT hint can be used with DISTINCT and GROUP BY SELECT statements. It as you might guess, tells MySQL that the result set will be big. According to the MySQL documentation, if invoked MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a key on the GROUP BY elements. SeleCT SQL_BUFFER_RESULT SQL_BIG_RESULT t1.contactid, t1.parentid, t1.companyname, t1.createdby, t1.curTime, group_concat(t1.contract_type) as contract_type, t1.promptype, t1.freez, t1.mask, t1.contract_series FROM tbl_company_master t1 WHERE  t1.contract_type <> 'prompt' GROUP BY t1.parentid ORDER BY t1.companyName ASC, t1.parentId ASC

The SQL_BIG_RESULT hint can be used with DISTINCT and GROUP BY SELECT statements. It as you might guess, tells MySQL that the result set will be big. According to the MySQL documentation, if invoked

MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a key on the GROUP BY elements.

SeleCT SQL_BUFFER_RESULT SQL_BIG_RESULT t1.contactid, t1.parentid, t1.companyname, t1.createdby, t1.curTime, group_concat(t1.contract_type) as contract_type, t1.promptype, t1.freez, t1.mask, t1.contract_series FROM tbl_company_master t1 WHERE  t1.contract_type <> 'prompt' GROUP BY t1.parentid ORDER BY t1.companyName ASC, t1.parentId ASC

Insert delayed Part I It will return immediately, but it will still wait until other clients have closed the table before executing the statement. Note: INSERT DELAYED only works on MyISAM, MEMORY, and ARCHIVE tables. You can delay INSERT's from happening until the table is free by using the DELAYED hint in your SQL statement. For example: INSERT DELAYED INTO table (col) VALUES ('val'); The above SQL statement will return quickly, and it stores the insert statement in a memory queue until the table you are inserting into is free from reads. This means that if there are multiple inserts in the queue they can be written in one block, which is a more optimal use of IO.

It will return immediately, but it will still wait until other clients have closed the table before executing the statement.

Note: INSERT DELAYED only works on MyISAM, MEMORY, and ARCHIVE tables.

You can delay INSERT's from happening until the table is free by using the DELAYED hint in your SQL statement. For example:

INSERT DELAYED INTO table (col) VALUES ('val');

The above SQL statement will return quickly, and it stores the insert statement in a memory queue until the table you are inserting into is free from reads. This means that if there are multiple inserts in the queue they can be written in one block, which is a more optimal use of IO.

Insert Delayed Part II The downside to this is that it is not transactionally safe at all. You don't really know how long its going to take for your INSERT to happen. If the server crashes, or is forcefully shutdown you will loose your INSERTs. So don't use this on any critical information. One great use for the DELAYED keyword would be for storing web stats in a database. You don't want the client waiting for the stats to insert, and its not that big of a deal if you loose a few stats (for most people).

The downside to this is that it is not transactionally safe at all. You don't really know how long its going to take for your INSERT to happen. If the server crashes, or is forcefully shutdown you will loose your INSERTs. So don't use this on any critical information.

One great use for the DELAYED keyword would be for storing web stats in a database. You don't want the client waiting for the stats to insert, and its not that big of a deal if you loose a few stats (for most people).

Store IP addresses CREATE TABLE Sessions (session_id INT UNSIGNED NOT NULL AUTO_INCREMENT, display_address varchar(15), ip_address INT UNSIGNED NOT NULL, session_data TEXT NOT NULL, PRIMARY KEY (session_id), INDEX (ip_address) ) ENGINE = InnoDB; insert into Sessions values (NULL, '172.29.0.217', INET_ATON('172.29.0.217'), 'some more data'); insert into Sessions values (NULL, '172.29.0.227', INET_ATON('172.29.0.227'), 'data from other IP'); select session_id, display_address, ip_address as ip_raw, INET_NTOA(ip_address) as ip, session_data from Sessions WHERE ip_address = INET_ATON('172.29.0.217') or ip_address = INET_ATON('172.29.0.227') ;

CREATE TABLE Sessions (session_id INT UNSIGNED NOT NULL AUTO_INCREMENT, display_address varchar(15), ip_address INT UNSIGNED NOT NULL, session_data TEXT NOT NULL, PRIMARY KEY (session_id), INDEX (ip_address) ) ENGINE = InnoDB;

insert into Sessions values (NULL, '172.29.0.217', INET_ATON('172.29.0.217'), 'some more data');

insert into Sessions values (NULL, '172.29.0.227', INET_ATON('172.29.0.227'), 'data from other IP');

select session_id, display_address, ip_address as ip_raw, INET_NTOA(ip_address) as ip, session_data from Sessions WHERE ip_address = INET_ATON('172.29.0.217') or ip_address = INET_ATON('172.29.0.227') ;

Replication A client that has the SUPER privilege can disable binary logging of its own statements by using a SET SQL_LOG_BIN=0 statement. As a brief example, if I am loading a large table it could be good to disable logging before beginning the import. mysql> SET SQL_LOG_BIN=0; mysql> LOAD DATA INFILE 'honking_big_file' INTO BIG_TABLE;

A client that has the SUPER privilege can disable binary logging of its own statements by using a SET SQL_LOG_BIN=0 statement. As a brief example, if I am loading a large table it could be good to disable

logging before beginning the import.

mysql> SET SQL_LOG_BIN=0;

mysql> LOAD DATA INFILE 'honking_big_file' INTO BIG_TABLE;

Normalize your database The first normal form doesn't allow you to store values like this in a single cell. /146/,/834/,/3483/,/4043/,/20852/,/221554/,/221561/,/222987/,/223154/,/223539/ 'Z008677','Z004949','Z008572','Z004951'

The first normal form doesn't allow you to store values like this in a single cell.

/146/,/834/,/3483/,/4043/,/20852/,/221554/,/221561/,/222987/,/223154/,/223539/

'Z008677','Z004949','Z008572','Z004951'

Table Types There are 2 important types of tables. MyISAM when there are many selects or many inserts or when you need Full text index. InnoDB when there are a lot of select, update, inserts and deletes happening simultaneously. This is the only table type that supports transactions and foreign keys. Archive table type supports only inserts and selects. No Update/ delete is allowed. Good for log tables. Federated tables allows you to connect to remote tables as if they are local. Doesn't work for very big tables and is buggy. Memory tables are temporary tables living in memory and are dropped when the server is restarted.

There are 2 important types of tables.

MyISAM when there are many selects or many inserts or when you need Full text index.

InnoDB when there are a lot of select, update, inserts and deletes happening simultaneously. This is the only table type that supports transactions and foreign keys.

Archive table type supports only inserts and selects. No Update/ delete is allowed. Good for log tables.

Federated tables allows you to connect to remote tables as if they are local. Doesn't work for very big tables and is buggy.

Memory tables are temporary tables living in memory and are dropped when the server is restarted.

Column Types Do not use float or double to store numbers. Use decimal or integer Do not use BLOB to store files, images. Save them in a directory and store the path in the table. Avoid 'text' datatype. Use varchar. No need to use varchar(255) since we can now have varchar(3000)‏ But it does not mean that you should have varchar(3000) when varchar(100) is enough. Indexes will have issues with that figure. Do not use set or enum if you are not comfortable with database concepts.

Do not use float or double to store numbers. Use decimal or integer

Do not use BLOB to store files, images. Save them in a directory and store the path in the table.

Avoid 'text' datatype. Use varchar. No need to use varchar(255) since we can now have varchar(3000)‏

But it does not mean that you should have varchar(3000) when varchar(100) is enough. Indexes will have issues with that figure.

Do not use set or enum if you are not comfortable with database concepts.

Learn how to use “load data infile” The method of 'Load data in file' is usually 20 times faster than using INSERT statements.

The method of 'Load data in file' is usually 20 times faster than using INSERT statements.

Load file You can read the file contents from within mysql. This feature is useful to read the file saved on the server and send the contents to the user through PHP. [root@irodb2 mysql]# echo '<b> this is bold in xml file </b>' > mytest.xml [root@irodb2 mysql]# mysql test mysql> select load_file(&quot;/var/log/mysql/mytest.xml&quot;), 'test constant column', bse_code from bsecode limit 1 *************************** 1. row *************************** load_file(&quot;/var/log/mysql/mytest.txt&quot;): <b> this is bold in xml file </b> test constant column: test constant column bse_code: 513375

You can read the file contents from within mysql. This feature is useful to read the file saved on the server and send the contents to the user through PHP.

[root@irodb2 mysql]# echo '<b> this is bold in xml file </b>' > mytest.xml

[root@irodb2 mysql]# mysql test

mysql> select load_file(&quot;/var/log/mysql/mytest.xml&quot;), 'test constant column', bse_code from bsecode limit 1

*************************** 1. row ***************************

load_file(&quot;/var/log/mysql/mytest.txt&quot;): <b> this is bold in xml file </b>

test constant column: test constant column

bse_code: 513375

Balance PHP and MySQL // userID,posts,runningTotal| // output running total // 2, 23434, 28330| // 6, 3443, 4896| // 1, 422, 1453| // 3, 344, 1031| // 4, 344, 687| // 5, 343, 343| echo 'userID,posts,runningTotal|<br>'; $q = mysql_query(&quot;select * from `members` order by `posts` DESC&quot;); while($a = mysql_fetch_row($q)){ echo &quot;$a[0],$a[1],$total|<br>&quot;; $total = $total - $a[1]; }

// userID,posts,runningTotal|

// output running total

// 2, 23434, 28330| // 6, 3443, 4896| // 1, 422, 1453| // 3, 344, 1031| // 4, 344, 687| // 5, 343, 343|

echo 'userID,posts,runningTotal|<br>';

$q = mysql_query(&quot;select * from `members` order by `posts` DESC&quot;);

while($a = mysql_fetch_row($q)){ echo &quot;$a[0],$a[1],$total|<br>&quot;; $total = $total - $a[1];

}

Use Joins Do not execute the query and take the rows one at a time to compare it's value with another row. Use joins. Do not use IN Use different joins like inner, left

Do not execute the query and take the rows one at a time to compare it's value with another row. Use joins.

Do not use IN

Use different joins like inner, left

Update 2 tables in one query You can join 2 tables in a single select. You can update those 2 tables in a single statement as well.   select * from packet_date_sent AS pds       INNER JOIN tempAdvSep2007 AS tas         ON pds.enroll_no = tas.enroll_no       INNER JOIN packet_sent AS ps         ON ps.enroll_no = tas.enroll_no            AND ps.material_id = pds.material_id            AND ps.course_id = pds.course_id            AND ps.enroll_date = pds.enroll_date WHERE  pds.date_sent = '2007-09-01'       AND pds.material_id BETWEEN 62 AND 97;

You can join 2 tables in a single select. You can update those 2 tables in a single statement as well.

  select * from packet_date_sent AS pds       INNER JOIN tempAdvSep2007 AS tas         ON pds.enroll_no = tas.enroll_no       INNER JOIN packet_sent AS ps         ON ps.enroll_no = tas.enroll_no            AND ps.material_id = pds.material_id            AND ps.course_id = pds.course_id            AND ps.enroll_date = pds.enroll_date WHERE  pds.date_sent = '2007-09-01'       AND pds.material_id BETWEEN 62 AND 97;

Update 2 tables in one query UPDATE packet_date_sent AS pds       INNER JOIN tempAdvSep2007 AS tas ON pds.enroll_no = tas.enroll_no       INNER JOIN packet_sent AS ps ON ps.enroll_no = tas.enroll_no AND ps.material_id = pds.material_id AND ps.course_id = pds.course_id AND ps.enroll_date = pds.enroll_date SET    pds.sent_mode = '5', pds.postofficecode = 1, pds.system_date = NOW(), pds.branch_id = '99', ps.sent_bit = '1', ps.system_date = NOW(), ps.branch_id = '99' WHERE  pds.date_sent = '2007-09-01' AND pds.material_id BETWEEN 62 AND 97;

UPDATE packet_date_sent AS pds

      INNER JOIN tempAdvSep2007 AS tas ON pds.enroll_no = tas.enroll_no

      INNER JOIN packet_sent AS ps ON ps.enroll_no = tas.enroll_no AND ps.material_id = pds.material_id AND ps.course_id = pds.course_id AND ps.enroll_date = pds.enroll_date

SET    pds.sent_mode = '5', pds.postofficecode = 1, pds.system_date = NOW(), pds.branch_id = '99', ps.sent_bit = '1', ps.system_date = NOW(), ps.branch_id = '99'

WHERE  pds.date_sent = '2007-09-01' AND pds.material_id BETWEEN 62 AND 97;

Execute linux commands You can use quotes to execute Linux command from within PHP <?php echo `mysql -h 172.29.0.131 -uroot -H -e 'set @count:= 0; select @count:= @count + 1 as &quot;sr no&quot;, p,d from test.t' `; ?>

You can use quotes to execute Linux command from within PHP

<?php

echo `mysql -h 172.29.0.131 -uroot -H -e 'set @count:= 0; select @count:= @count + 1 as &quot;sr no&quot;, p,d from test.t' `;

?>

Full Text search Only MyISAM table types supports this. You can use match – against syntax when you want to use this type of search.

Only MyISAM table types supports this.

You can use match – against syntax when you want to use this type of search.

SQL_CALC_FOUND_ROWS mysql> select SQL_CALC_FOUND_ROWS name, country from india limit 1G name: Punjab Plains country: IN mysql> select found_rows(); +--------------+ | found_rows() | +--------------+ | 38202 | +--------------+ 1 row in set (0.00 sec)‏

mysql> select SQL_CALC_FOUND_ROWS name, country from india limit 1G name: Punjab Plains country: IN

mysql> select found_rows(); +--------------+ | found_rows() | +--------------+ | 38202 | +--------------+ 1 row in set (0.00 sec)‏

Use MySQL variables mysql> set @count:= 0; Query OK, 0 rows affected (0.00 sec)‏ mysql> select @count:= @count + 1 as myid, p from t; +------+-----+ | myid | p | +------+-----+ | 1 | 20% | | 2 | 20% | | 3 | 30% | | 4 | 30% | | 5 | 50% | | 6 | 50% | +------+-----+ 10 rows in set (0.00 sec)‏

Collation issues SET @@SESSION.collation_connection = 'latin1_general_ci';

SET @@SESSION.collation_connection = 'latin1_general_ci';

Forget select * mysql> explain select * from tbl_compcatarea a inner join bid_details b on a.contactid=b.contractid; +----+-------------+-------+------+---------------+-----------+---------+--------------------+-------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-----------+---------+--------------------+-------+-------+ | 1 | SIMPLE | b | ALL | ContractID | NULL | NULL | NULL | 24386 | | | 1 | SIMPLE | a | ref | contactid | contactid | 47 | d_jds.b.contractID | 1 | | +----+-------------+-------+------+---------------+-----------+---------+--------------------+-------+-------+ mysql> explain select a.contactid, a.compname from tbl_compcatarea a inner join bid_details b on a.contactid=b.contractid; +----+-------------+-------+-------+---------------+------------+---------+--------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------------+---------+--------------------+-------+-------------+ | 1 | SIMPLE | b | index | ContractID | ContractID | 48 | NULL | 24386 | Using index | | 1 | SIMPLE | a | ref | contactid | contactid | 47 | d_jds.b.contractID | 1 | | +----+-------------+-------+-------+---------------+------------+---------+--------------------+-------+-------------+

Standards and conventions for naming columns, tables and indexes The name of the key is misleading in the table tbl_company_source. The key name is 'ContractID' and the column used for indexing is 'contactID'. I overlooked this problem because of the similarity in the key name and column name. In fact we need two different indexes on ContractID and contactID.

The name of the key is misleading in the table tbl_company_source. The key name is 'ContractID' and the column used for indexing is 'contactID'. I overlooked this problem because of the similarity in the key name and column name. In fact we need two different indexes on ContractID and contactID.

Add a comment

Related presentations

Related pages

phptalk (php talk) · GitHub

phptalk has 5 repositories written in PHP and CSS. Follow their code on GitHub.
Read more

Mineralienatlas Lexikon - Talk

Kugeliger Talk (Steatit) mit Mangandentriten, gefunden auf der Halde der Johanneszeche 1981, Stufe 5*5 cm.
Read more

Telegram: Contact @phptalk

If you have Telegram, you can contact @phptalk right away. Send Message. Don't have Telegram yet? Try it now: Get Telegram ...
Read more

HSTalks - Online Lectures by Leading World Experts | HSTalks

HSTalks publishes animated audio visual presentations by world leading experts - advanced content in a user friendly format. We cover biomedicine, life ...
Read more

PHP-Talk - b1gMail

Diskussionen rund um PHP. ... Anzeige-Eigenschaften: Zurzeit aktive Benutzer: Moderatoren: Zeige Themen 1 bis 1 von 1
Read more

PHP-Talk [Archiv] - b1gMail

Archiv verlassen und diese Seite im Standarddesign anzeigen : PHP-Talk. Seiten : [1] 2. Bücher und Info Material rund um PHP; beliebige zeichen in if-abfragen
Read more

GitHub - ianbarber/Firehose-PHP-Talk: Some example code ...

Firehose-PHP-Talk - Some example code from my Building a Firehose talk
Read more

Talk im Hangar-7 - Bite TV Studioservice

Der Talk im Hangar-7 - die Diskussionssendung mit Format Jede Woche empfangen die Moderatoren Imke Köhler, Helmut Brandstätter oder Johannes Willms ...
Read more

Talk:Xrdp - ArchWiki

Issues (on KDE) I'm using KDE. I had xrdp-git working with an older checkout. I re-installed xrdp-git in order to pickup the systemd scripts. I ...
Read more