What is the proper MySQL statement that I should use for more than 76,000 records?

maikelsabido

2013-10-21T10:53:19

I have two tables that I would like to JOIN. I'd like to perform an INNER JOIN for these two tables to get the result that I want but it does not seem to be working. Everytime I run the query, I just get a loading... notification that takes too long to display and then after several minutes, I get this message:

Error in Processing Request. Error Code 500. Internal Server Error.

Basically I would want to get a result in this format:

Name Surname, Phone number
List(s) subscribed to (subscriber status) - date subscribed.

ex.) Desired Result:

Jane Doe, 082 980 9514
Home Loan Applications (Active) - 17/07/2013
Credit Report (Free Report) (Unsubscribed) 12/06/2013

Here's the link to the sample tables: Tables

You'll notice there that Jane Doe has subscribed to two lists (Home Loan Applications and Credit Report) so the two lists are listed in the result. Then, she already unsubscribed to credit report that's why there's (unsubscribed) next to it.

This is the SQL statement that I used:

SELECT emailaddress, subscribedate, unsubscribed 
FROM `interspire_subscriber` 
INNER JOIN `interspire_customfield` ON interspire_subscriber.subscriberid = 
                                       interspire_customfield.subscriberid

I don't know why I get the error. Is there a way to achieve this using only mySQL? or what else would I do in PHP to get this?

The script also seems too slow to execute because of the 76,000+ records.

Update: As suggested, I have set the subscriberids on both tables as index and the query worked fine. Currently I use this mysql statement:

SELECT interspire_customfield.subscriberid, interspire_customfield.fname,
interspire_customfield.lname, interspire_customfield.phone, emailaddress,
subscribedate, unsubscribed, interspire_customfield.listid, listname
FROM`interspire_subscriber` INNER JOIN `interspire_customfield` ON
interspire_subscriber.subscriberid = interspire_customfield.subscriberid

but it still does not list down all the lists that the user is subscribed to.

Copyright License：
Author:「maikelsabido」,Reproduced under the CC 4.0 BY-SA copyright license with link to original source & disclaimer.
Link to：https://stackoverflow.com/questions/19485537/what-is-the-proper-mysql-statement-that-i-should-use-for-more-than-76-000-record

About “What is the proper MySQL statement that I should use for more than 76,000 records?” questions

What is the proper MySQL statement that I should use for more than 76,000 records?

How should I use NOT IN statement in MySQL

I am trying to use NOT IN statement with MySQL. However, I get 0 row with code below (no syntax error). I am sure there should be more than 0 row with the statement. What syntax should I adjust? SE...

Fetch and paginate records more than 200000 with only one database interaction in php mysql

I have to fetch more than 2,00,000 records at once and use the paging of 100 or 1000 records per page without interacting to database again and again . Like we can have a virtual cache or hidden r...

After copying with INSERT...SELECT, I have more records than before

I have a strange occurance: I copied ~4.7m records from one table to another in MySQL 5.6.14, using INSERT INTO tabl1 (col1,...) SELECT (col2...) FROM tbl2... and I have more records than before. 6...

How to make a SELECT statement show more than 1000 records in MYSQL?

Hello I would like to know How to display more than 1000 registers when I`m using a SELECT statement in MYSQL

Returning all records and displays only 1 record only when there is more than 1 of the same record

I have an SQL statement that displays all the record of a participants that filters from a contactperson's id. <?php query = mysql_query("SELECT * FROM participants WHERE contactperson = '$

Will a MySQL SELECT statement interrupt INSERT statement?

I have a mysql table that keep gaining new records every 5 seconds. The questions are can I run query on this set of data that may takes more than 5 seconds? if SELECT statement takes more than 5s,

MySQL LIKE Usage One Than More Columns

I want to select records from my table. I want to use this query: select * from crawler.crawler_data WHERE name_surname, category, description LIKE "%de%"; But MySQL doesn't accept this query. ...

sql statement to delete records older than XXX as long as there are more than YY rows

Assume a table with the following columns: pri_id, item_id, comment, date What I want to have is a SQL query that will delete any records, for a specific item_id that are older than a given date,...

How to show mysql records more than once and in a specific order

1) This is a much simplified example of what I am trying to do for ease of explaining the problem. 2) I will be doing thousands of these queries. I have a mysql table like so: NUMBER TEXTCOLOUR...